Use of EXCEPT operator and comparing tables

EXCEPT operator exists in SQL Server 2005 and higher. It is particularly useful when one needs to compare two tables of identical structure and to select those records from the first table that do not exist in the second one.

SELECT column1, column2, column3 FROM Table1
EXCEPT
SELECT column1, column2, column3 FROM Table2

or even

SELECT * FROM Table1
EXCEPT
SELECT * FROM Table2

How to retrieve all the constraints on a particular column in a specific table?

The best way is to use INFORMATION_SCHEMA, INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME column in particular. Here is an example using Northwind database, Products table and ProductName column. SQL below will retrieve all the constraints on ProductName column:

DECLARE @tablename nvarchar(50)
DECLARE @column_name nvarchar(50)

SET @tablename = 'Products'
SET @column_name = 'ProductName'

SELECT INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME 
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS  
INNER JOIN INFORMATION_SCHEMA.COLUMNS ON INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME =@tablename
AND INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME=@columnname 

Constraints and how to disable or drop all the constraints on a specific table?

Sometimes constrains in the table can get in the way, like for instance when one needs to load initial values into a database one table at a time, without worrying with foreign key constraints and checks until all of the tables have finished loading. In that case one might want to delete or better, disable, all the constraints before performing the task, and then re-enabling them.

Although there is one catch with disabling constraints - you can only disable FOREIGN KEY constraint and the CHECK constraint. PRIMARY KEY, UNIQUE, and DEFAULT constraints are always active.


Below are two examples of how to delete and disable all the contstraints on a specific table respectively. I used Northwind database as an example.

There are numbers of ways to delete all the constraints, including deleting them from the system tables, but it's better to create a script that will get all the constraints from INFORMATION_SCHEMA view and then dynamically delete them, rather then deleting them directly from the system tables.

DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

SET @database = 'Northwind'
SET @table = 'Products'

DECLARE @sql nvarchar(255)
WHILE EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog = @database AND table_name = @table)
BEGIN
    SELECT @sql = 'ALTER TABLE ' + @table + ' DROP CONSTRAINT ' + CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE constraint_catalog = @database and table_name AND @table
    EXEC sp_executesql @sql
END

The above will delete all the constraints for Products table in the Northwind database.

And here's how one can disable all the constraints on a particular table:

DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

SET @database = 'Northwind'
SET @table = 'Products'

DECLARE @sql nvarchar(255)
WHILE EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog = @database AND table_name = @table)
BEGIN
    SELECT @sql = 'ALTER TABLE ' + @table + ' NOCHECK CONSTRAINT ' + CONSTRAINT_NAME 
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
    WHERE constraint_catalog = @database AND table_name = @table
    EXEC sp_executesql @sql
END

For disabling all constraints at once there is a simpler way without going through constraints one by one:

DECLARE @database nvarchar(50)
DECLARE @table nvarchar(50)

SET @database = 'Northwind'
SET @table = 'Products'

DECLARE @sql nvarchar(255)

SELECT @sql = 'ALTER TABLE ' + @table + ' NOCHECK CONSTRAINT ALL'
EXEC sp_executesql @sql

What is INFORMATION_SCHEMA?

System views are predefined Microsoft created views for extracting SQL Server metadata. System Views can be found under System Databases -> master -> Views -> System Views.
The first group of System Views belongs to the Information Schema set. INFORMATION_SCHEMA contains 20 different views. Most of the Information Schema view names are self-explanatory. For example INFORMATION_SCHEMA.TABLES returns a row for each table. INFORMATION_SCHEMA.COLUMNS returns a row for each column.

INFORMATION_SCHEMA is contained in each database and each INFORMATION_SCHEMA view contains meta data for all data objects stored in that particular database. For example one can retirieve all the constraint information on a particular table etc.

How to combine several rows in one row, separated by commas

The best way to combine values from multiple rows in one is to create user-defined function that does the job, and then just call the function from the SELECT statement.

For example lets take pubs data author and title tables. Let's say we want to retrieve ALL the titles for each author, separated by comma.

Here is a sample ud function that utilizes COALESCE for concatenation of all the titles for a every author:

CREATE FUNCTION dbo.AuthorTitles ( @author varchar(100) )
RETURNS VARCHAR(8000)
AS
BEGIN
  DECLARE @RES VARCHAR(8000)
  SELECT @RES = COALESCE(@RES + ',','') + title
  FROM titles as t
INNER JOIN titleauthor as ta ON t.title_id=ta.title_id
INNER JOIN authors as a on ta.au_id=a.au_id
  WHERE a.au_lname = @author
  RETURN (@RES)
END

and this SELECT statement will display the results, grouped by author's last name:

SELECT DISTINCT a.au_lname,dbo.AuthorTitles(a.au_lname)
FROM authors as a 
INNER JOIN titleauthor as ta ON a.au_id=ta.au_id
INNER jOIN titles as t ON ta.title_id=t.title_id
GROUP BY a.au_lname,t.title

Check to see if one field is contained within another field in a different table

If you have two different tables where there are two fields that you want to check to find out if one of those fields is contained inside another field while both fields belong to separate tables in the same database. You can use CHARINDEX function to do that.

For example lets take zip code. Table2 has a single zip code, and Table1 has a field with comma separated list of zip codes, i.e. 08036, 08032, 07338, 07339, and you need to check whether single zip code from Table2 matches one of those comma separated zip codes.

SELECT DISTINCT t1.Name, t2.Name, t2.ZipCode, t1.ZipCodes
FROM Table1 as t1 
INNER JOIN Table2 as t2 ON CHARINDEX(t2.ZipCode , t1.ZipCodes ) > 0 

The CHARINDEX function returns the starting position of a character, or a string of characters within another character string. So the above SELECT checks if there are any records where CHARINDEX is greater than 0, meaning single zip code in that row was found among comma separated zip codes.

There is another function called PATINDEX which works similar to CHARINDEX except it supports wildcard charaters.

Here is an example:

SELECT DISTINCT t1.Name, t2.Name, t2.ZipCode, t1.ZipCodes
FROM Table1 as t1 
INNER JOIN Table2 as t2 ON PATINDEX('%33%' , t1.ZipCodes ) > 0 

or

PATINDEX('%BC%', 'ABCD')

or

PATINDEX('AB%', 'ABCD')

How to pass a table name as a parameter to a stored procedure

In order to pass table name along with some fields to a stored procedure that does insert, one has to take advantage of dynamic sql

CREATE PROCEDURE myProc
@TableName as VARCHAR(50),
@fieldlist as VARCHAR(100),
@ID as INT, 
@Name as VARCHAR(50)
AS 
DECLARE @sql VARCHAR(1000) 
DECLARE @str NVARCHAR(1000)

SET @sql = 'INSERT INTO ' + @TableName + '(' + @fieldlist + ')' + ' Values(@ID,@Name)'
Select @str = CAST(@sql as NVarchar(1000))
EXECUTE sp_executesql @str,N'@ID INT,@Name VARCHAR(50)', @ID, @Name

GO

The above stored procedure can be called with the following

EXEC myProc 'Table1', 'ID, Name' ,1,'Taylor Lautner'
GO

Function returning second Tuesday of the month

Function below returns second Tuesday of the month. The month is taken from the datetime variable passed as an argument. One can pass today's date to the function to get second Tuesday of the current month. At my job there is patching done every Tuesday, so a lot of things I deal with depend on that date - second Tuesday

CREATE FUNCTION dbo.getSecondTuesday
(@dt DATETIME)
RETURNS DATETIME
AS

BEGIN

DECLARE @firstDayOfMonth DATETIME

SET @firstDayOfMonth=DATEADD(mm, DATEDIFF(m, 0, @dt), 0)

DECLARE @dayWeek int
SET @dayWeek = DATEPART(DW,@firstDayOfMonth) 
DECLARE @count int
SET @count = ((10 - @dayWeek) % 7) + 7

DECLARE @outputDate datetime

SET @outputdate = CONVERT(VARCHAR(10),DATEADD(day,@count,@firstDayOfMonth),101)

RETURN @outputdate

END

UD function to add leading zeros

The other day I needed to add leading zero to hour and minute parts of the time (listed as integer types) if there was only one digit, and do nothing if there were two digits.

For example I had 4:55 and I needed it to show up as 04:55, or 11:7 to show up as 11:07.

So I used

SELECT dbo.AddLeadingZero(@hour) + ':' + dbo.AddLeadingZero(@minute)

And here is the function I utilized

CREATE FUNCTION dbo.AddLeadingZero
(@val INT)
RETURNS VARCHAR(2)
AS

BEGIN

DECLARE @length INT
DECLARE @output VARCHAR(2)

SET @length=LEN(CAST(@val as varchar))

IF @length > 1
BEGIN
 SET @output=CAST(@val as varchar)
END
ELSE 
      BEGIN
       SET @output= '0' + CAST(@val as varchar)
      END
RETURN @output
END

Some interesting features I found in SQL Server 2005 that did not exist in SQL Server

TOP clause

1. Ability to pass a variable to the TOP clause
   

   
   DECLARE @Rows INT
   SET @Rows = 10
   
   SELECT TOP ( @Rows ) *
   FROM dbo.MyTable

   
   
   @Rows can be replaced with anything that evaluates to a number
   
   
2. TOP clause can be used with INSERT, UPDATE and DELETE statements. For example if you want to delete rows in batches
   
   of 50, you can do that using the TOP clause.
   
   

CROSS APPLY


CROSS APPLY can be used to combine data from a table with table data returned by user-defined function. In SQL Server

2000 there existed functions that return table but there was no CROSS APPLY functionality

CREATE FUNCTION dbo.fn_GetTopTableRecords(@id AS int, @count AS INT)
  RETURNS TABLE
AS
RETURN
  SELECT TOP(@count) *
  FROM dbo.MyTable
  WHERE ID = @id
  ORDER BY Field1 DESC
GO

Then one can just use CROSS APPLY to combine data returned by the function with a data from some other table

SELECT  t1.ID, 
 t2.ID,
 t2.Field1
FROM 
 dbo.Table1 as t1
CROSS APPLY
 dbo.fn_GetTopTableRecords(ID, 5) AS t2
ORDER BY 
 t1.ID ASC, t2.Field1 DESC

The APPLY clause is similar to a CROSS JOIN or an OUTER JOIN without the ON clause. APPLY clause can also be preceded by

either CROSS or OUTER keyword. The OUTER APPLY clause will return all the rows on the left side whether they return any

rows from the function or not. The CROSS APPLY only returns rows from the left side if the function returns rows.