SQL Server Reporting Services

SSRSLogoWe have had an SSRS server kicking around for several months and have not done much with it, and being a bit of a noob with SQL Server (I’m really not too much of a noob, but we had a definitive use case for SSRS, we just hadn’t gotten around to setting it up), I didn’t quite know what SSRS was / is capable of. I have to say this – the tool itself is awesome.

I’ll get in to some new how-tos and cool things you can do with it here in the next couple of weeks.

Redgate SQL Monitor

It’s really been a while since I’ve written anything about SQL Server, and I suppose it’s just about that time for one of those tools that really make the life a database administrator a lot easier – and that is Redgate SQL Monitor. Red Gate recently released SQL Monitor 4.0, which is a bit of an upgrade from the old 3.x versions, which now includes advanced query tracing.

In a nutshell, what SQL Monitor does is connect to your SQL Server (it needs its own database for stats and such, as well as having a sysadmin account for reading database stats) and it just reports all sorts of statics on logos-redgate-sql-monitoryour SQL Server – disk and processor queues, RAM usage, database hits, etc… All of this is divided in two main areas – Alerts and Analysis. It’s an application that provides a Web Server (runs on Windows) and has a nice little web GUI that is somewhat configurable.

Alerts provide all kinds of tunable alerts – ranging from job failures to deadlocks to long running queries to even things like table blocking. You can set up email alerting as well and get emailed these alerts as they happen. At first though, I would suggest not enabling the Email Alerts, especially if you have never monitored your server before, because this thing will send you all kinds emails about things it finds (log backups overdue, index fragmentation, etc…). As I mentioned, these alerts are tunable, which is really handy. We have one job that runs in a loop for 8 hours at a time – when we first started monitoring, SQL Monitor would constantly send us emails about a long running query, since, let’s face it, 8 hours is a long running query. You can tune that though and basically tell the alert that “Hey, it’s okay if it runs for 8 hours, but if it deviates from that by more than say… 5%, then send us an alert”. These alerts at first look annoying, but once you sift through them, there is a lot very very useful data.

An example of that is that we have been having about 2 or 3 deadlocks per day. For the longest time, we weren’t sure what was causing them – we could view the first part of the query, but it was so generic that it didn’t provide enough insight in to what was causing the deadlock to really give us any meaningful information. SQL Monitor was able to give us the whole query, the job / stored proc that was causing the deadlock and that, in turn, let us figure out how to fix it (a few indexes here and there fixed that issue).

Analysis also is absolutely invaluable – I spend about 75% of my time doing systems and storage administration, and a big part of that is performance tuning / getting the best possible performance out of our equipment and servers. This includes doing things like query and SQL Job tuning. We use things like LogicMonitor that provides WMI Monitoring of the physical hardware, Nagios / Opsview that provides us with alerts on hardware usage, but nothing else really to give us specifics (and by that I mean deep specifics such as stats over time, cache hits, database size, log size, full scans, etc…) about SQL Server. SQL Monitor fills that gap really nicely. It provides in depth analysis on a daily, weekly, monthly and even yearly basis (the SQLMonitor database gets quite big if you keep data for that long however, we keep SQLMonitor data for 2 months and the database sits around 30 gigs).

Times

I suppose one of the other awesome things I should mention is that it is totally cluster aware. Our previous installation was a 3 node cluster, our current is a 2 node cluster. It monitors both nodes, but of course only monitors the SQL Server instance on the server that owns the SQL Server resources (disks, etc…). They have a free trail available (14 days), so you can give it a shot.

The price tag is somewhat hefty, but if you are running a production database that either your internal users use or you have a lot of external uses, the data and stats it provides are invaluable compared to other tools.

Intermediate SQL – Post 9 – DROP Column, Table, and Database

For this little lesson, we will create a database, a couple of tables, add some data to those tables, alter them, query them, and then drop them.

I’ll get to the syntax of each of those as we go here, but for the CREATE and ALTER portions, you already know from previous lessons – so here we go.

CREATE Database Linux
GO
 
USE Linux
GO
 
CREATE TABLE Distributions
(
ID			int PRIMARY KEY IDENTITY NOT NULL,
Name			varchar (255),
ReleaseDate		date,
VersionNumber	        varchar (16),
CodeName		varchar (25)
)
GO
 
SET NOCOUNT ON
 
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Ubuntu','12/10/2012', '12.10', 'Quantal')
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Ubuntu','12/04/2012', '12.04', 'Precise')
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Ubuntu','10/01/2011', '11.10', 'Oneiric')
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Mint','12/10/2012', '14', 'Nadya')
INSERT INTO Distributions (Name, ReleaseDate, VersionNumber, CodeName)
VALUES ('Mint','11/04/2012', '13', 'Maya')
GO
 
ALTER TABLE Distributions
DROP COLUMN ReleaseDate
GO
 
DROP TABLE Distributions
GO
 
USE msdb
GO
 
DROP DATABASE Linux
GO

Alright – so with that, we created a table and then added some random stuff to that table. After that we altered the table and dropped the column called “Release Date”. Note that dropping a column removes the entire column and all data in it. This is different than a delete statement. A delete statement to remove the values from one row or to make every single row NULL for a particular column is a totally different query. This removes the entire column and all data in it, so that our table no longer has that column. If you wanted to delete a single column from a row, you would do this:

DELETE ReleaseDate
FROM Distributions
WHERE ID = 3

If you wanted to set all columns to NULL, you could do this:

DELETE ReleaseDate
FROM Distributions

Neither of these delete the column however, it just sets all the data in them to NULL – if you want to remove an entire column, you need to DROP it using ALTER TABLE.

The next one you see is that we dropped the table. This means that the entire table gets removed from the database. Once again, it’s not DELETE. It’s DROP TABLE.

The last drop you see is to DROP DATABASE. This one in particular you have to be careful with (for obvious reasons). Firstly, no one can be using it. If they are using it, you’ll get a warning. Since I assumed that you created this database and you are the only one using it, I just decided to USE msdb, and then drop it. If there are other open connections on that database, you need to set it offline to drop it.

ALTER DATABASE Linux
SET OFFLINE
WITH ROLLBACK IMMEDIATE
GO
 
DROP DATABASE Linux
GO

Just be warned – this will force disconnect anyone who is connected and then drop the database. This means deleting all tables and data in those tables. If you don’t have a backup, all that data is gone. So once again, be extremely careful with this. Ask your system admin if you’re not comfortable with it, and if it’s your own server, then use your own judgement.

Intermediate SQL – Post 8 – ALTER TABLE

Well, finally I can get  back to writing some SQL goodies. I have to say I’m pretty excited.

We left off creating some tables – and those tables are all good. Let’s say now that we need to change something in a table – such as how many characters or the data type of a column that we created (we’ll get to datatypes later).

Let’s do this quick so we can see what we are looking at:

USE AdventureWorks2012
GO
 
SELECT DISTINCT City FROM Person.Address
WHERE len(City) = (SELECT max(len(City)) FROM Person.Address)
 
SELECT max(len(ltrim(rtrim(City)))) FROM Person.Address

That first query will give us the name of the longest city in that column, and the second will tell us how many characters it is. For times sake, let’s say it’s a 21 character long string. That’s all fine and dandy, considering City is a varchar (60) field.

But what we need to change a city to something like Taumatawhakatangihangakoauauotamateapokaiwhenuakitanatahu (which, incidentally, is a real place)

If we run this query:

UPDATE Person.Address
	SET City = 'Taumatawhakatangihangakoauauotamateaturipukakapikimaungahoronukupokaiwhenuakitanatahu', ModifiedDate = getdate()
WHERE AddressID = '26158'

We will get an error back that looks like this:

Msg 8152, Level 16, State 4, Line 1
String or binary data would be truncated.

Oh noes, what do we do? That error usually means your column won’t fit the data that you’re trying to put in to it. In this case we are attempting to stick a 85 letter (character) name in to a field that is only 60 characters long. No bueno.

What we have to do to make this work is alter our table. A simple query can do this. The syntax of ALTER TABLE is right here:

ALTER TABLE tablename
ALTER COLUMN columnname datatype

For example, to make this city fit, we could do this:

ALTER TABLE Person.Address
ALTER Column City varchar (90)

You may get an error when you do this though – something like this:

Msg 5074, Level 16, State 1, Line 1
The index 'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode' is dependent on column 'City'.
Msg 4922, Level 16, State 9, Line 1
ALTER TABLE ALTER COLUMN City failed because one or more objects access this column.

That means that this table has an Index on it and we can’t do anything to the column that is indexed unless we remove and recreate the index. We’ll get in to indexes later. For now you can see here:

 

What we want to do is “Drop and Create to”. You can either have it on your clip board and paste it in your current query window or have it open a new query window. Usually I do the second one.

The first thing we want to do is run the “Drop Index Portion”.

USE [AdventureWorks2012]
GO
 
EXEC sys.sp_dropextendedproperty @name=N'MS_Description' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'INDEX',@level2name=N'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode'
 
GO
 
/****** Object:  Index [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]    Script Date: 11/29/2012 12:11:49 PM ******/
DROP INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address]
GO

No we can run our “Alter Table” statement:

ALTER TABLE Person.Address
ALTER Column City varchar (90)

And then we want to re-create the index:

/****** Object:  Index [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode]    Script Date: 11/29/2012 12:11:49 PM ******/
CREATE UNIQUE NONCLUSTERED INDEX [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] ON [Person].[Address]
(
	[AddressLine1] ASC,
	[AddressLine2] ASC,
	[City] ASC,
	[StateProvinceID] ASC,
	[PostalCode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
 
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Nonclustered index.' , @level0type=N'SCHEMA',@level0name=N'Person', @level1type=N'TABLE',@level1name=N'Address', @level2type=N'INDEX',@level2name=N'IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode'
GO

Once all that is done, we can successfully change the city name for our huge city name:

UPDATE Person.Address
	SET City = 'Taumatawhakatangihangakoauauotamateaturipukakapikimaungahoronukupokaiwhenuakitanatahu', ModifiedDate = getdate()
WHERE AddressID = '26158'

And there we go.

Intermediate SQL – Post 7b – CREATE TABLE – Examples

So in Post 7a we created some tables with some specific data fields (varchar, char, etc…). Here will begin populating those tables. I haven’t had much time to write these queries, and this is only a portion of it.

-- A few customers
INSERT INTO Customers
VALUES ('Jim','Franklinson','223','North Burrows St.','New York City','NY','55555')
INSERT INTO Customers
VALUES ('David','Gray','192','East Bush Lake Road','Thrillsville','OH','22222')
INSERT INTO Customers
VALUES ('Edward','Delvin','1221','West Coral Blvd','Pocahontas','AL','42321')
INSERT INTO Customers
VALUES ('Marilyn','Dobbs','1312','West Michester Way','Albany','NY','55555')
INSERT INTO Customers
VALUES ('Richard','Edval','3441','Nork Fork Lane','Ashberry','NY','55555')
INSERT INTO Customers
VALUES ('Earl','Ottoman','4561','Samhill Road','Buffalo','NY','55555')
INSERT INTO Customers
VALUES ('Travis','Egbert','5512','Freeport Curve','Freeport','NY','55555')
GO
 
-- A few suppliers
INSERT INTO Suppliers
VALUES ('Dell','3321','Dell Way','Ashland','WI','55121')
INSERT INTO Suppliers
VALUES ('Lenovo','441','Lenovo Way','Superior','WI','55125')
INSERT INTO Suppliers
VALUES ('Apple','5512','Steve Jobs Circle','New York City','NY','55121')
INSERT INTO Suppliers
VALUES ('Sony','7781','Sony Curve West','Sony','TX','35121')
GO
 
-- Some categories
INSERT INTO Categories
VALUES ('Desktops','Desktop Computers and Monitors')
INSERT INTO Categories
VALUES ('Notebooks','Notebook Computers and Accessories')
INSERT INTO Categories
VALUES ('Servers','Servers and Server Accessories')
INSERT INTO Categories
VALUES ('Blades','Blade Servers and Blade Chassis')
INSERT INTO Categories
VALUES ('Ultrabooks','Ultrabook Computers and Accessories')
INSERT INTO Categories
VALUES ('Tablets','Tablet Computers and Accessories')
INSERT INTO Categories
VALUES ('Accessories','Notebook and Desktop Accessoires')
GO
 
-- And now a lot of products
INSERT INTO Products
VALUES ('Dell D9120','Desktop Computer - 1TB HDD, Intel i7 2600, 8GB DDR3 1600Mhz RAM','700','899','1','1')
INSERT INTO Products
VALUES ('Dell D9122','Desktop Computer - 1.5TB HDD, Intel i7 2600k, 8GB DDR3 1866Mhz RAM','1100','1399','1','1')
INSERT INTO Products
VALUES ('Dell D1720','Desktop Computer - 500GB HDD, Intel i5 2500, 6GB DDR3 1333Mhz RAM','499','599','1','1')
INSERT INTO Products
VALUES ('Dell D1750','Desktop Computer - 1TB HDD, Intel i5 2500k, 8GB DDR3 1333Mhz RAM','599','699','1','1')
INSERT INTO Products
VALUES ('Dell Brostro 2290','Laptop Computer - 500GB HDD, Intel i5 2510m, 4GB DDR3 1333Mhz RAM','599','699','1','2')
INSERT INTO Products
VALUES ('Dell Insparion 1120','Laptop Computer - 320GB HDD, Intel i3 2240m, 4GB DDR3 1333Mhz RAM','499','599','1','2')
INSERT INTO Products
VALUES ('Dell Latitize E5412','Laptop Computer - 256GB SDD, Intel i7 2614m, 8GB DDR3 1600Mhz RAM','1199','1399','1','2')
INSERT INTO Products
VALUES ('Dell Insparion 1913','Laptop Computer - 128GB SDD, Intel i3 2240m, 4GB DDR3 1333Mhz RAM','599','699','1','2')
INSERT INTO Products
VALUES ('Dell Power Knife','Blade Server - Dual 128GB SDD, Intel Xeon 5650, 96GB DDR3 1333Mhz ECC RAM - CL=7','2300','2999','1','4')

This will just dump a bunch of data in to the tables we created earlier. I’ll finish this for Post 7c. =D

Intermediate SQL – Post 7a – CREATE TABLE – Examples

I figured I would put up a few more examples of making tables – rather than a real example per se, this is just going to be fairly large script that creates tables and then allows us to see what is going on in them. We can then run some SELECT statements against them and you can see how Primary Keys and Foreign Keys will work.

So, let’s start shall we – this is only the first part of the script – the next part will be coming in the next day or so.

USE Intermediate
GO
 
CREATE TABLE Customers
(
CustomerID                     int IDENTITY PRIMARY KEY NOT NULL,
FirstName                      varchar (32),
LastName                       varchar (68),
StreetNumber                   varchar (7),
StreetName                     varchar (27),
City                           varchar (50),
[State]                        char (2),
ZipCode                        int
)
GO
 
CREATE TABLE Suppliers
(
SupplierID                     int IDENTITY PRIMARY KEY NOT NULL,
SupplierName                   varchar (50),
StreetNumber                   varchar (7),
StreetName                     varchar (27),
City                           varchar (50),
[State]                        char (2),
ZipCode                        int
)
GO
 
CREATE TABLE Categories
(
CategoryID                     int IDENTITY PRIMARY KEY NOT NULL,
CategoryName                   varchar (50),
CategoryDesc                   varchar (500)
)
GO
 
CREATE TABLE Products
(
ProductID                     int IDENTITY PRIMARY KEY NOT NULL,
ProductName                   varchar (50),
ProductDescription            varchar (500),
PurchasePrice                 money,
SellPrice                     money,
SupplierID                    int,
CategoryID                    int,
CONSTRAINT fk_SupplierID
     FOREIGN KEY (SupplierID) REFERENCES Suppliers(SupplierID),
CONSTRAINT fk_CategoryID
     FOREIGN KEY (CategoryID) REFERENCES Categories(CategoryID)
)
GO
 
CREATE TABLE Orders
(
OrderID                      int IDENTITY PRIMARY KEY NOT NULL,
ProductID                    int NOT NULL,
CustomerID                   int NOT NULL,
CONSTRAINT fk_ProductID
     FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
CONSTRAINT fk_CustomerID
     FOREIGN KEY (CustomerID)REFERENCES Customers(CustomerID)
)
GO

That query just makes a few tables along with some primary keys and foreign key constraints.  A foreign key more or less looks at a primary key  in another table – and is bound to that if you will. Foreign keys can be null, but that sort of defeats the purpose of them. Coming up the next few posts we will look at all the things we can do with these constraints to make life a lot lot easier for you.

Intermediate SQL – Post 7 – CREATE TABLE

Now that we know some data types, we can start creating both databases and tables. Creating a database is fairly easy. The syntax goes as follows:

CREATE DATABASE database_name

Yes, it’s that easy. So, let’s make one for the next few examples we have.

CREATE DATABASE [Intermediate]

You’ll notice that I have the word “intermediate” in brackets – at times, you’ll run in to words in your database that are also database functions – one I get a lot in my job is “Status”. This is both a SQL function and the name of a column in a table. To discern one from the other, the function will never have brackets around it, and the column will. Generally if you are using SQL Server Management Studio (SSMS) and you see a word that you just wrote turn blue, you’re seeing SSMS tell you that it is a function. If you’re not sure, you can always use brackets, though I find that this makes writing queries much more difficult to write.

In any event, now we have a database called “Intermediate”. Let’s use it, and create a table.

USE Intermediate
GO

Now, let’s create a table. The syntax of creating a table goes like this:

CREATE TABLE table_name
(
     column_1 datatype (num) NULL PRIMARY KEY,
     column_2 datatype (num) NOT NULL,
     column_3 datatype
)

You’ll see that some have a number behind the datatype and some do not – some datatypes require you to specify the field – such as “varchar” – you have to specify the max length of the field. With others, such as “int”, you do not. PRIMARY KEY means that this is a unique number – generally starting at 1 and moving upwards as it goes. NULL, and NOT NULL – these mean that the column can be NULL, or are not allowed to be NULL. If you choose not to write NULL or NOT NULL, it will default to NULL. I generally just get used to always writing NULL or NOT NULL so I do not forget if the time comes that I am designing a table.

So, let’s create a table – I’ll explain each one as we go through them and why I chose that datatype and number – I’ll use some datatypes that you’ll commonly use:

CREATE TABLE Names
(
	NameID			int IDENTITY PRIMARY KEY NOT NULL,
	FirstName		varchar (32)	         NOT NULL,
	LastName		varchar (68)	         NOT NULL,
	Title			varchar (30)	         NULL,
	BirthDate		datetime	         NOT NULL,
	[Address]		nvarchar (120)	         NOT NULL,
	City			varchar (35)	         NOT NULL,
	[State]			varchar (2)	         NOT NULL,
	ZipCode			int		         NOT NULL
)

First off – I created a table called “Names” without specifying a schema (schema is the little bit before names – IE, HR.Employees – HR is the schema). If you do not specify a schema, it will default to “.dbo”. I’ll get in to schemas at a later post – for now .dbo will suffice.

You’ll see I created a column called NameID – it’s an integer field that is a “Primary Key” – it’s also an Identity Column. This just means it will auto populate – we don’t need to insert an ID in to that. Each time you “INSERT” into that table, NameID will automatically be added, starting at 1 and going up from there.

FirstName – I chose this is as varchar because it’s variable characters – You probably could make it text, but what if someone has a hyphenated first name such as “Lou-Anne” or “John-Jacob”. I also created it to be 32 characters long because honestly, who do you know that has a 32 character first name?

LastName is very similar as it to the FirstName field we created. I personally have a hyphenated last name, so it needs to be varchar as well. Variable Characters. I chose for it to be 68 characters long because once again, who do you know who has a 68 character last name?

Title should also be a varchar. There are titles out there of all sorts of people – Sales Representatives, CFOs, CEOs, Master Plumber, who knows what.

Birthdate – I created this as a datetime field. Datetime is very commonly used field in SQL Server circles. If you’d like to see the way it looks, you can run this:

SELECT getdate()

[Address] – First off, we need to bracket that one because it is a server function. Secondly – I did it as nvarchar for no other reason other than to show you that there aren’t really much differences between varchar and nvarchar.

City – This should also be varchar. It’s variable – Cities have all sorts of weird names. There may be a city like St. Mary’s Point.

[State] – Once again this is a server function and should be bracketed. I made this 2 characters because rather than use the full state name, we are going to use the abbreviation.

ZipCode – I made this as an int – I am only collecting the person’s 5 digit zip code, not the +4.

Now that you know why I created it the way I did, let’s test some INSERTS and UPDATES.

INSERT INTO Names
VALUES ('George','Harrison','Operations Manager','1981-02-21 00:00:00.000','2213 Statesman Ave','Fort Lewis','CA','90211')
INSERT INTO Names
VALUES ('Edgar','Wheeler','Machinist','1958-12-02 00:00:00.00','5512 Earl Street W','Modesto','CA','92112')
INSERT INTO Names
VALUES ('Felix','Ottoman','EDM Machinist','1949-5-18 00:00:00.000','991 West 17th St. North','Modesto','CA','92112')

Now if run this:

SELECT * FROM Names

We’ll get a whopping 3 records back. Since we have an identity column, each row is unique based on it’s NameID. Because of that, we can use that in other tables rather than using things like, first name, etc… I’ll do more on this in the next post. That means we can updated based solely on the NameID.

For example:

UPDATE Names
SET FirstName = 'Joseph'
WHERE NameID = 3

Identity columns are very very important in SQL – they make updating and interlinking tables extremely easy.

Keep this table – we’ll be using it more in the next post.

Intermediate SQL – Post 6 – Data Types

I apologize – I have been working on so many other projects that I have been neglecting SQL Server lately – but we will get back on it with this post.

In the last post, I may have had you create a table. A table is a section of a database that contains data. Let’s first examine how a database in SQL Server is structured.

First you have a database – think of that like a folder in a system file structure hierarchy. For example, C:\. After that you have tables – think of those as sub-folders of the main (root) folder. In Windows, that would be things like C:\Users. Each one of those folders contains certain data. You also have programmability – think of that as some of the programs in C:\Program Files. There are also functions – this would be like C:\Windows – things like regedit.exe, services.msc, gpedit.msc all would be considered functions.

In any event, tables are the structure that contain the data. The structure of a table is very similar to what you would see in Excel – there are rows and columns. Each row contains a set of data pertaining to each column.

Now – before we can create a table, we need to look at “data types”. Each column has to have a data type. There a host of different data types all of which are in their own area and have their own strengths and weaknesses and work better with certain types of data. It very largely depends on what you plan on putting in the column you want to create. I’m going to give an extremely brief synopsis of each data type, and then we will get creating some tables.

1. Exact numerics – these are numbers that are exact. When I say exact, it’s an integer. The number 1 is a numeric. The number 5410321 is an exact numeric. The number 2.123412 is an exact numeric. There are a certain set of data types that go with exact numerics and those are: INT, BIGINT, DECIMAL, BIT, MONEY, NUMERIC, SMALLINT, SMALLMONEY, TINYINT. Click on any one of them for more info.

2. Approximate numerics – these are numbers that are approximate. “Floating point data is approximate; therefore, not all values in the data type range can be represented exactly.”  FLOAT and REAL are the two datatypes that part of approximate numerics. A lot of times I see money that is set as float.  Usually a float is a decimal with no specific place where the . goes in the decimal.

3.  Date and Time – this one is huge. I use this a LOT in my business because it is very handy and finding out when a row was last changed, modified, added, etc… There are several different types of Date and Time, all of which are awesome and very scalar and easy to modify – those are: DATE, DATETIME, DATETIME2, DATETIMEOFFSET, SMALLDATETIME, TIME. When in doubt, just use DATETIME or SMALLDATETIME.

4. Character and Unicode Character Strings – these are basically any type of character. Unicode is a type of character encoding. Character Strings are these: CHAR, VARCHAR, TEXT. Unicode Character strings are: NCHAR, NVARCHAR, NTEXT. So what is the difference between using VARCHAR and NVARCHAR? I’ll quote this from Stack Exchange: “An nvarchar column can store any Unicode data. A varchar column is restricted to an 8-bit codepage. Some people think that varchar should be used because it takes up less space. I believe this is not the correct answer. Codepage incompatabilities are a pain, and Unicode is the cure for codepage problems. With cheap disk and memory nowadays, there is really no reason to waste time mucking around with code pages anymore.

All modern operating systems and development platforms use Unicode internally. By using nvarchar rather than varchar, you can avoid doing encoding conversions every time you read from or write to the database. Conversions take time, and are prone to errors. And recovery from conversion errors is a non-trivial problem.

If you are interfacing with an application that uses only ASCII, I would still recommend using Unicode in the database. The OS and database collation algorithms will work better with Unicode. Unicode avoids conversion problems when interfacing with other systems. And you will be preparing for the future. And you can always validate that your data is restricted to 7-bit ASCII for whatever legacy system you’re having to maintain, even while enjoying some of the benefits of full Unicode storage.”

I tend to stick with varchar in most cases personally, but that is up to you.

4. Binary strings – these are strings of 0 and 1. Only that. I like using them for true and false statements and as a flag. They are BINARY, VARBINARY, and IMAGE.

5. And last but certainly not least are the “others” that don’t really fall in to these categories – such as XML, cusor, etc…

Intermediate SQL – Post 5 – UPDATE

The update statement is what you think it is – to update a row that is already there. The syntax goes something like:

UPDATE Table_1
SET column_1 = 'value1', column_2 = 'value2'
WHERE column operator {value}

Now before we start, if you do an update and do not specify a WHERE clause, it will literally update every row in your table. For example (DO NOT RUN THIS) if we ran this on our LearningSQL Table:

USE LearningSQL
GO
 
UPDATE dbo.Nums
SET n = '1'

Every row in that table will be turned to ‘1’. If the column you are updating is an identity column, you will get an error back. An identity column is one that contains a unique identifier for that row, or in other words, a unique piece of data (usually a number). If you attempt to change that, you’ll get an error back because SQL will not want to change that to a potentially non-unique ID.

For example, if we ran this:

UPDATE HR.Employees
SET empid = 1

You would get this back:

Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'empid'.

You can’t update an identity column unless you have identity insert on – which is not something advisable at this point in our learning. We can, however, update every other column in that row. For example:

UPDATE HR.Employees
    SET lastname =		'Johnson',
	firstname =		'Joey',
	title =			'CFO',
	address =		'5320 2nd St.'
WHERE EmpID = '10'

Since we are not updating the identity column, this update will work just fine. You can also update based on a SELECT statement. For example:

UPDATE Sales.Customers
SET contactname = e.LastName + ', ' + e.Firstname
FROM HR.Employees e
WHERE custid = 3
	AND e.empid = 3

So we added the lastname, plus a comma and a space, plus the first name in to that column. Yay. Not too hard right? You can do some massive UPDATE statements as well using a combination of SELECTS and values.

UPDATE Sales.Customers
SET companyname = 'Strauss and Klein Music Co',
    contactname = 'Gemendstadt, Klaus'
WHERE custid = 1
 
UPDATE Sales.Customers
SET companyname = 'Pedros Boarding House',
    Contactname	= 'Jimenez, Juan'
WHERE custid = 2