PIVOT

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output.

http://msdn.microsoft.com/en-us/library/ms177410.aspx

SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 

[0], [1], [2], [3], [4]

FROM

(SELECT DaysToManufacture, StandardCost 

    FROM Production.Product) SourceTable

PIVOT

(

AVG(StandardCost)

FOR DaysToManufacture IN ([0], [1], [2], [3], [4])

) PivotTable

Notes:

• Available from SQL Server 2005.
• When aggregate functions are used with PIVOT, the presence of any null values in the value column are not considered when computing an aggregation.
• Pivot columns are specific values from the column of the source table, and can't be declared dynamic.

JOIN deletion to table that not in the join

DELETE dbo.TableA

FROM dbo.ViewOnTableA a

JOIN dbo.TableB b ON a.ID = b.ID

--> all the rows of dbo.TableA will be deleted!

The deleted table must be in the joins, Otherwise all the rows of the table will be deleted.

The statement above should be written:

DELETE dbo.TableA

FROM dbo.ViewOnTableA a

JOIN dbo.TableA ta ON a.ID = ta.ID

JOIN dbo.TableB b ON a.ID = b.ID

Unique columns to not NULL values

New in SQL SERVER 2008:

We can define an unique column, that NULL is not need to be unique, or in other words: the unique check is only for not NULL values.

CREATE UNIQUE NONCLUSTERED INDEX IX_TableName_UniqueColumn

ON dbo.TableName (UniqueColumn)

WHERE UniqueColumn IS NOT NULL

GO

 SELECT ID, UniqueColumn FROM TableName WHERE ID in (5, 6, 7)

-- for the example. lets say that the results are: 5, NULL ; 6, NULL ; 7, NULL.

UPDATE TableName SET UniqueColumn = 'test' WHERE ID = 5 --will be success

UPDATE TableName SET UniqueColumn = 'test' WHERE ID = 6 --will be failed

UPDATE TableName SET UniqueColumn = NULL WHERE ID = 5 --will be success

smalldatetime

smalldatetime defines a date that is combined with a time of day. The time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds.

If the seconds are 29.998 or less it rounds down to the nearest minute. If the seconds are 29.999 or more rounds up to the next minute.

http://msdn.microsoft.com/en-us/library/ms182418.aspx

Default values to SQL function parameters

Unlike stored procedure parameters, When a parameter of the function has a default value, the keyword 'DEFAULT' must be specified when calling the function in order to get the default value. 

CREATE FUNCTION [dbo].[FUNCTIONNAME] (

@P1 int, 

@P2 int =0 

)........

select * from dbo.FUNCTIONNAME(1)

Result: an error message:

An insufficient number of arguments were supplied for the procedure or function dbo.FUNCTIONNAME.

Solution: 

We have to use the 'DEFAULT' keyword:

Select * from dbo.FUNCTIONNAME(1, DEFAULT)

If the 2 parameters have default, 

CREATE FUNCTION [dbo].[FUNCTIONNAME] (

@P1 int =0, 

@P2 int =0 

)........

we have to use 'DEFAULT' twice:

Select * from dbo.FUNCTIONNAME(DEFAULT, DEFAULT)

CASE without Else expression

The Case expression syntax is:

CASE ..... WHEN ....... THEN ........ [ ...more WHEN ... THEN expressions ] 

     [ ELSE ......... ]  END 

ELSE is not obligatory expression.

And - If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL.

CASE without the Else expression can cause to insertion of NULL values!

For Example:

DECLARE @TestTable (TestColumn int not null)

INSERT INTO @TestTable select 1 union select 2 union select 3

-- case without else expression

UPDATE@TestTable 

SET TestColumn = ( CASE WHEN TestColumn = 2 THEN 22 END )

-- Result: table values are: NULL, 22, NULL

-- if we want to update only the '2' rows:

UPDATE@TestTable 

SET TestColumn = ( CASE WHEN TestColumn = 2 THEN 22 ELSE TestColumn END )

-- Result: table values are: 1, 22, 3

http://msdn.microsoft.com/en-us/library/ms181765.aspx

Linked server

-- get exists linked servers:

select * from sys.servers

-- add new linked server:

exec sp_addlinkedserver 

@server='Dev2008ItaiTest', -- the name of the linked server

@srvproduct= '', -- the product name of the OLE DB data source

@provider='SQLNCLI', -- the unique programmatic identifier (PROGID) of the OLE DB provider that corresponds to this data source

@datasrc='DEV2008', -- server name

@location='', -- the location of the database as interpreted by the OLE DB provider

@provstr='Query Timeout=10' -- another parameters.

Notes:

• If the product name of the linked server is SQL Server, provider_name, data_source, location, provider_string, and catalog do not have to be specified.
• Return Values: 0 for success or 1 for failure.

-- use linked server:

select * from Dev2008ItaiTest.DBName.dbo.ObjectName

-- delete linked server:

sp_dropserver @server='Dev2008ItaiTest'

http://msdn.microsoft.com/en-us/library/ms190479.aspx

Check if an expression is numeric

IsNumeric:

select IsNumeric('0.2') -- return 1

select IsNumeric('asasa') -- return 0

http://msdn.microsoft.com/en-us/library/aa933213(v=sql.80).aspx

Note: parameter with value of NULL is not considered numeric, but parameter with value of empty string ('') will be converted to zero:

DECLARE @P int
SET @P = NULL
SELECT IsNumeric(@P) -- return 0
SET @P = ''
SELECT IsNumeric(@P) -- return 1
SELECT IsNumeric(NULL) -- return 0
SELECT IsNumeric('') -- return 0

Line break in a SQL Server string

In order to insert a line break in a SQL Server string, use CHAR(13) or CHAR(10):

CHAR(13) - carriage return:

print 'line 1' + CHAR(13) + 'line 2'

CHAR(10) - Line feed:

print 'line 1' + CHAR(10) + 'line 2'

Note: if you select those string to grid, it won't be showen as 2 lines, but the values of CHAR(13)/CHAR(10) will be saved.

SET PARSEONLY

SET PARSEONLY { ON | OFF }

When SET PARSEONLY is ON, SQL Server only parses the statement. 

When SET PARSEONLY is OFF, SQL Server compiles and executes the statement.

The setting of SET PARSEONLY is set at parse time and not at execute or run time.

http://msdn.microsoft.com/en-us/library/ms178629.aspx

SET FMTONLY

SET FMTONLY { ON | OFF }

SET FMTONLY returns only metadata to the client. 

For 'regular' tables - the result set only have headers of the results but no data.

SET FMTONLY can be used to test the format of the response without actually running the query.

SET FMTONLY ON

GO

-- no rows will be return (only the headers):

SELECT * FROM [dbo].[StamTable2]

GO

SET FMTONLY OFF

GO

-- all the rows will be return:

SELECT * FROM [dbo].[StamTable2]

GO

http://msdn.microsoft.com/en-us/library/ms173839.aspx

http://msdn.microsoft.com/en-us/library/ms173839(v=sql.100).aspx

What is metadata?

The short definition is that it’s data about data.

Metadata is used to add context and understanding about data that users are accessing, or to hide complexity from end users who aren’t required to know or understand the technical details of data.

(from MSDN SQL Server Metadata Toolkit: http://archive.msdn.microsoft.com/SqlServerMetadata)

See an article about Using SQL Server meta data to list tables that make up views:

http://www.mssqltips.com/tip.asp?tip=1638

SQL Server Denali introduces new metadata discovery capabilities, removing the need to use the older SET FMTONLY option:
http://www.sqlmag.com/blogs/puzzled-by-t-sql/entryid/76283/denali-t-sql-at-a-glance-metadata-discovery

OPENQUERY

OPENQUERY:

Executes the specified pass-through query on the specified linked server.

OPENQUERY ( linked_server ,'query' )

OPENQUERY can also be referenced as the target table of an INSERT, UPDATE, or DELETE statement.

http://msdn.microsoft.com/en-us/library/ms188427.aspx

OPENQUERY can be a target of select query, like:
SELECT ..... FROM OPENQUERY ( linked_server ,'query' )

SQL Azure

SQL Azure is a cloud-based service from Microsoft offering data storage capabilities.

Useful links:

SQL Azure - microsoft site:

http://www.microsoft.com/windowsazure/sqlazure/

SQL Azure Labs:

http://www.sqlazurelabs.com/

Unsupported Transact-SQL Statements in SQL Azure:

http://msdn.microsoft.com/en-us/library/ee336253.aspx

Connect to SQL Azure through a special project for developers without SSMS, Houston project:

https://manage-sgp.cloudapp.net

INSTEAD OF DELETE trigger on view - ERROR

Error message:

DELETE is not allowed because the statement updates view "VVV" which participates in a join and has an INSTEAD OF DELETE trigger.

Explanation:

A view with an INSTEAD OF UPDATE / INSTEAD OF DELETE trigger cannot be a target of an UPDATE or DELETE statements with a FROM clause.

Work-Arounds:

1. Replace the JOIN with 'IN':

delete dbo.test1 

from dbo.test1 t1 join dbo.test2 t2 on t1.Id = t2.Id

-->

delete dbo.test1 where t1.Id IN (select Id from dbo.test2)

2. Replace the JOIN with EXISTS:

delete dbo.test1

from dbo.test1 t1 join dbo.test2 t2 on t1.Id = t2.Id

-->

delete dbo.test1

from dbo.test1 t1

where exists ( Select top 1 Id from dbo.test2 t2 Where t1.Id = t2.Id )

3. If possible - do the update/deletion on the original tables (= the source table/tables of the View).

SSRS - Expression-based Connection Strings

="data source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks

Specify connection strings parameters as a report parameter.

Expression-based connection strings are evaluated at run time.

http://msdn.microsoft.com/en-us/library/ms156450.aspx#Expressions

SQL Server code name Denali Home page

SQL Server code name Denali Home page:

http://www.sqlserverlaunch.com/Home.aspx?Day=120

What's New in SQL Server Denali:
http://msdn.microsoft.com/en-us/library/bb500435%28SQL.110%29.aspx

Whitepaper of the new features and more can be downloaded from Microsoft:
http://download.microsoft.com/download/d/9/4/d948f981-926e-40fa-a026-5bfcf076d9b9/Spatial_Denali_CTP1.docx

Return value of stored procedure

DECLARE @return_value int

EXEC   @return_value = [dbo].[SP_NAME]  @ID = 131279, ......

SELECT 'Return Value' = @return_value

• When the SP is successful, @return_value would be 0.
• Also @return_value=NULL means an error.

If we want to return an output value from the SP, we have to declare an output parameter in the SP declaration:

CREATE PROCEDURE [dbo].[SP_NAME]

@Param1 int,

....... ,

@SpCount INT OUTPUT

AS

.......

and insert it a value in the SP code.

When execute SP:

DECLARE @Count int

EXEC   @return_value = [dbo].[SP_NAME]  @ID = 131279, ......, 

              @SpCount = @Count OUTPUT

Pattern Matching in the LIKE operator

% - Any string of zero or more characters.

_ - Any single character.

[ ] - Any single character within the specified range ([a-f]) or set ([abcdef]).

[^] - Any single character not within the specified range ([^a-f]) or set ([^abcdef]).

[[] - specific speicial character - in this case '['.

http://msdn.microsoft.com/en-us/library/aa933232(v=sql.80).aspx

Find and kill active connections to Database

select DB_NAME(dbid), ('kill ' + convert(nvarchar(10), spid) ) as kill_statement, * 

from sys.sysprocesses where DB_NAME(dbid) = 'DBNAME'
--or:

DECLARE @SQL nvarchar(max) 
SELECT @SQL=COALESCE(@SQL,'') +'Kill '+CAST(spid AS nvarchar(10))+ '; '  
FROM sys.sysprocesses WHERE DB_NAME(dbid) = 'DBNAME'