Adding a primary key can be done either after a table is created, or at the same a table is created. It is important to note, that by default a primary key is clustered. This may or may not be the preferred method of creation. For more information on clustered vs non clustered indexes, please see this article. Either way, you can specify the clustered / non clustered option when creating a primary key. To create a clustered primary key on an existing table: [cc lang=”sql”] ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (PersonID); [/cc] To create a non clustered primary key on an existing table: [cc lang=”sql”] ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED (PersonID); [/cc] To create a composite primary key on an existing table: [cc lang=”sql”] ALTER TABLE dbo.Person ADD CONSTRAINT PK_Person PRIMARY KEY CLUSTERED (PersonID, DOB); [/cc] For an existing table, there are two places that can be used to specify the primary key. The first is inline to the column. Using this method, you cannot create a composite primary key: [cc lang=”sql”] CREATE TABLE [dbo].[Person]( PersonID [int] IDENTITY(1,1) CONSTRAINT PK_Person PRIMARY KEY NONCLUSTERED NOT NULL, FirstName VARCHAR(50) NULL ) [/cc] The second way is to specify the primary key after the creation of the table, this will enable you to create a composite primary key. Keep in mind that in order to use a column in the primary key, the field must not be nullable. [cc lang=”sql”] CREATE TABLE [dbo].[Person]( PersonID […]
Continue reading ...
The SQL Server Select statement is the first statement used when returning data. It is the most used and most important statement in the T-SQL language. The Select statement has many different clauses. We will step through each clause further in the tutorial, however now, we will look at Select itself. The following [cc lang=”sql”] SELECT ‘Mike’ AS FirstName [/cc] As we can see from our results, the code returns one row, one column. The data of the column returned is the value: ‘Mike’. The column’s name is ‘FirstName’. The ‘AS’ allows you to specify a column alias. This alias could be anything, in this case we made it ‘FirstName’. We can also Select multiple columns of data by separating the columns using a comma: [cc lang=”sql”] SELECT ‘Mike’ AS FirstName, ‘Nichols’ AS LastName [/cc] These examples so far, have only used the Select statement by itself. Now we will explore using Select in addition with the ‘From’ clause. The From Clause allows a source to be specified for the Select Statement. For the purposes of this example, the source is always a table. Let’s look at an example. [cc lang=”sql”] SELECT * FROM Person.Person [/cc] Phonetically the above query is pronounced: Select star from Person dot Person. The star (*) or asterisks is notation for saying “all columns”. Basically, the query is saying give me all columns from the Person table contained within the Person Schema. The person schema is basically a class, or a way to better organize […]
Continue reading ...
SQL Server 2012 actually employs a different method for pagination that is built in using the ORDER BY Clause. However for other versions, the most efficient pagination method is actually simple. Aren’t most things like this? It involves using (the mighty powerful) ROWCOUNT statement — twice. Anyways, the code: [cc lang=”sql”] CREATE PROCEDURE [dbo].[spUsersPaged] ( @RowBegin int, @MaxRows int ) AS BEGIN DECLARE @StartID int SET ROWCOUNT @RowBegin SELECT @StartID = UserID FROM dbo.Users u ORDER BY UserID — Set maximum number of rows to return SET ROWCOUNT @MaxRows SELECT * FROM dbo.Users u WHERE UserID >= @StartID ORDER BY u.UserID SET ROWCOUNT 0 END [/cc] That’s it for the first pagination sample. You may try other methods, however I challenge all Ye to overcome thy double ROWCOUNT method. Comment below please. To give a variety, I’m sharing another pagination method. This one seems more elegant. It uses a Common Table Expression) CTE, and the RowNumber function. [cc lang=”sql”] SET @rowsPerPage = 10 SET @pageNum = 3 WITH SQLPaging AS ( SELECT TOP(@rowsPerPage * @pageNum) ResultNum = ROW_NUMBER() OVER (ORDER BY id) ,id FROM dbo.Table1 ) SELECT * FROM SQLPaging WHERE ResultNum > ((@pageNum – 1) * @rowsPerPage) [/cc] Yet is it elegant, with speed?? ..uh not so much.
Continue reading ...
Comparing the results of two different stored procedures output can be difficult task. The first difficulty is to return the result sets of the stored procedures into temporary tables. There are two different methods of doing this outlined in the link above. For this example we will use the local linked server method. Your method may differ depending upon whether your system setup will support one of the methods. It may also differ if you are looking at passing dynamic parameters (this method is also covered in the link above). After the result sets are in the temp tables, we need to compare the datasets. We typically need to see the data in one of three ways: What is in one result set and not in the other All results that do not match All results that do match First, let’s dump the procedures into tables: [cc lang=”sql”] — Where local server is the name we gave to our local linked server SELECT * INTO #Procedure1 FROM OPENQUERY(LOCALSERVER, ‘set fmtonly off exec dbo.Procedure1 param’) SELECT * INTO #Procedure2 FROM OPENQUERY(LOCALSERVER, ‘set fmtonly off exec dbo.Procedure2 param’) [/cc] Now, considering we have SQL 2005 and above, we will compare what is in Procedure1 that does not match what is in procedure2. We do this using the new EXCEPT Statement. [cc lang=”sql”] — Give us what IS in procedure1 SELECT * FROM #Procedure1 EXCEPT — That does not match procedure2 SELECT * FROM #Procedure2 [/cc] Next, we will get what is in […]
Continue reading ...
The following short code will return the date and time for the very end of the month passed in. It works by first getting the beginning of the month for the date passed in, adding one month to that date, then subtracting 3 milliseconds. This gives the latest possible date for the end of the month as the DateTime data type has a granularity of 3 milliseconds. [cc lang=”sql”] DECLARE @DateIn datetime SET @DateIn = GETDATE() SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,@DateIn)+1,0))) [/cc]
Continue reading ...
