Partition Scheme DMVs

-- Partition Functions:

SELECT PF.function_id, PF.name, PF.fanout AS NumPartitions, 

CASE WHEN PF.boundary_value_on_right = 0 THEN 'LEFT' ELSE 'RIGHT' END AS RangeType, 

PP.parameter_id, 

CASE WHEN PP.system_type_id = PP.user_type_id THEN T1.name ELSE T2.name END AS ParameterDataType

FROM sys.partition_functions AS PF

JOIN sys.partition_parameters AS PP ON PF.function_id = PP.function_id

JOIN sys.types AS T1 ON T1.system_type_id = PP.system_type_id

JOIN sys.types AS T2 ON T2.user_type_id= PP.user_type_id

-- Index - Partitioned Table - Partition Scheme - Partition - Function

SELECT SI.name AS IndexName, OBJECT_NAME(SI.object_id) AS PartitionedTable, 

DS.name AS PartitionScheme, PF.name AS PartitionFunction

FROM sys.indexes AS SI

JOIN sys.data_spaces AS DS ON DS.data_space_id = SI.data_space_id

JOIN sys.partition_schemes AS PS ON PS.data_space_id = SI.data_space_id

JOIN sys.partition_functions AS PF ON PF.function_id = PS.function_id

WHERE DS.type = 'PS' AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U' AND SI.index_id IN(0,1)

-- Index - Partitioned Table - Partition Scheme - Partition - Function - more detailed

SELECT SI.name AS IndexName,  OBJECT_NAME(SI.object_id) AS PartitionedTable, DS.name AS PartitionSchemeName, PF.name AS PartitionFunction, 

P.partition_number AS PartitionNumber, P.rows AS PartitionRows, FG.name AS FileGroupName

FROM sys.partitions AS P

JOIN sys.indexes AS SI ON P.object_id = SI.object_id AND P.index_id = SI.index_id

JOIN sys.data_spaces AS DS ON DS.data_space_id = SI.data_space_id

JOIN sys.partition_schemes AS PS ON PS.data_space_id = SI.data_space_id

JOIN sys.partition_functions AS PF ON PF.function_id = PS.function_id

JOIN sys.destination_data_spaces AS DDS ON DDS.partition_scheme_id = SI.data_space_id AND DDS.destination_id = P.partition_number

JOIN sys.filegroups AS FG ON DDS.data_space_id = FG.data_space_id

WHERE DS.type = 'PS' AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U' AND SI.type IN(0,1)

Comparison to the previous cell in a SSRS table

IIF(Fields!Cell.Value <> Previous(Fields!Cell.Value),"Solid","None")

SSRS Format

Date format :

format(Fields!Timestmp.Value,"dd/MM/yyyy HH:mm")

Number format:

FormatNumber(First(Fields!CastingActualAvgSlabWeight.Value),3) 

SSRS 'If' statement syntax

iif(conditionm, if_true, if_false)

Cannot resolve collation conflict

In SQL SERVER, the collation can be set in column level. When compared 2 different collation column in the query, this error comes up.

WHERE ItemsTable.Collation1Col = AccountsTable.Collation2Col

“Cannot resolve collation conflict for equal to operation“.

To resolve the collation conflict add following keywords around “=” operator:

WHERE ItemsTable.Collation1Col COLLATE DATABASE_DEFAULT

 = AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT

Collation can affect following areas:

• Where clauses.
• Join predicates.
• Functions.
• Databases (e.g. TempDB may be in a different collation database_default than the other databases some times).

Get Windows directories Structure from SQL

xp_dirtree 'C:\Windows'

Insert default values to table

-- Insert default values to specific column:

INSERT INTO StamTable (StamTableStr, StamTableInt) VALUES ('example1', DEFAULT)

-- Insert default values to all columns in the table:

INSERT INTO StamTable DEFAULT VALUES

Join to Dynamic SQL code

The situation: 

You have a dynamic SQL code that return a select results, and you want to make it available to join other queries (you don't know what will be the queries).

Solution:

1. Insert the dynamic SQL code into stored procedure.

2. Join it using OpenRowSet:

Select *

From OpenRowSet( 'SQLNCLI',

'Server=(Local);Trusted_Connection=Yes',

'Set FmtOnly Off Exec DB_Name.dbo.SP_Name'

) d

JOIN ...

Notes:

1.You need to configure the server 'Ad Hoc Distributed Queries' property to 1:

SP_Configure 'Ad Hoc Distributed Queries',1;

Go

Reconfigure With Override;

Go

2. Stored procedure that executed from OpenRowSet can't get parameters.

3. Check performance.

Error message when execute USE DB

If DB name include special characters (like '-', '.'), error messages will be displayed when execute:

USE Ddddd.7 

--> error msg: Incorrect syntax near '.7'.

The problem is in the DB name.

Solution:

USE [Ddddd.7]

It can be more dangerous when execute it in dynamic SQL:

DECLARE @DBName nvarchar(128) = DB_NAME()

DECLARE @Sql nvarchar(500)

SET @Sql = 'USE ' + @DBName + ........

EXEC (@SQL)

--> error msg: Incorrect syntax near '.7'.

It recommended to write it in this syntax:

.....

SET @Sql = 'USE [' + @DBName + ']'  + ........

EXEC (@SQL)

Few notes about Dynamic SQL

Any USE statement in the dynamic SQL will not affect the calling stored procedure.

Temp tables created in the dynamic SQL will not be accessible from the calling procedure since they are dropped when the dynamic SQL exits. 

The block of dynamic SQL can access temp tables created by the calling procedure.

The effect of SET command in the dynamic SQL lasts for the duration of the block of dynamic SQL only and does not affect the caller.

When using stored procedures, users do not need permissions to access the tables accessed by the stored procedure. This does not apply when you use dynamic SQL!

The query plan for the stored procedure does not include the dynamic SQL!!!

The block of dynamic SQL has a query plan of its own.

The first parameter @stmt of sp_executesql is Unicode - nvarchar/ntext. varchar is not valid!

LEFT+RIGHT JOIN

FULL OUTER JOIN

CREATE TABLE #StamTable

( StamTableStr nvarchar(50) NULL )

INSERT INTO #StamTable (StamTableStr) VALUES ('111')

INSERT INTO #StamTable (StamTableStr) VALUES ('222')

GO

CREATE TABLE #StamTable2

( StamTable2Str nvarchar(50) NULL )

INSERT INTO #StamTable2 (StamTable2Str) VALUES ('111')

INSERT INTO #StamTable2 (StamTable2Str) VALUES ('333')

GO

select * 

from #StamTable a FULL OUTER JOIN #StamTable2 b 

on a.StamTableStr = b.StamTable2Str

drop table #StamTable

drop table #StamTable2

Performance checklist - few hints

Indexes speed the performance of queries. Create index considering the INSERT, UPDATE, DELETE and SELECT. If there is more use in one of those, consider it more than the others.

Using of table variable is stored in the plan cache. Temporary tables can be indexed. Check which one of them is better to a specific situation.

'OR' can be replaced by 'UNION' and vice versa. 

SELECT ... WHERE ColA = 5 OR ColB = 2

SELECT ... WHERE ColA = 5 UNION SELECT ... WHERE ColB = 2

Check which one of them is better to a specific situation.

'DISTINCT' can be replaced by 'GROUP BY' and vice versa. Check which one of them is better to a specific situation.

'JOIN' can be replaced by sub-query and vice versa. Check which one of them is better to a specific situation; usually 'JOIN' is better than sub-query.

sp_executesql is usually better than EXECUTE statement.

Usually using 'IF EXISTS' is better than count by SELECT COUNT (*).

Usually using 'EXISTS' is better than using sub-query.

Check Parameter Sniffing - is sometimes good and sometimes not.

http://copypastenet.blogspot.com/2011/01/sql-server-parameter-sniffing.html

Try to update statistics, recompile stored procedure, re-build indexes. They need to be refreshed from time to time.

http://copypastenet.blogspot.com/2011/01/update-statistics.html

http://copypastenet.blogspot.com/2011/01/recompile-stored-procedure.html

'SET NOCOUNT ON' can speed the performance - in queries and procedures.

If not all the objects are owned by the same schema - call the objects with the schema - SchemaName.ObjectName.

Avoid using cursors.

Don't repeatedly reuse the same function or calculation in a T-SQL code.

Sort queries (ORDER BY) only when necessary.

SELECT DISTINCT only when it necessary.

Try to avoid using 'SELECT *'. Select the columns that you need.

When using 'LIKE', try to use it with leading characters in the clause: LIKE 'm%' instead of LIKE ‘%m%’.

Check Blocking and locks.

don't return more data (rows and columns) than you need to the client or middle-tier and then the data you really need at the client. This wastes SQL Server resources and network bandwidth.

Rename filegroup

ALTER DATABASE DBName MODIFY FILEGROUP [OldFileGroupName] NAME = [NewFileGroupName]

GO

Select SQL version

SELECT @@version as sql_version

An example of @@version output:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor) 

Get SET Options for the Current Session

SELECT @@OPTIONS

@@OPTIONS returns an integer that represents the bit values of the SET Options.

Here are the values of the SET options:

http://www.mssqltips.com/sqlservertip/1415/determining-set-options-for-a-current-session-in-sql-server/

Here is a code to display which SET options are turned on:

DECLARE @options INT

SELECT @options = @@OPTIONS

IF ( (1 & @options) = 1 ) PRINT 'DISABLE_DEF_CNST_CHK' 

IF ( (2 & @options) = 2 ) PRINT 'IMPLICIT_TRANSACTIONS' 

IF ( (4 & @options) = 4 ) PRINT 'CURSOR_CLOSE_ON_COMMIT' 

IF ( (8 & @options) = 8 ) PRINT 'ANSI_WARNINGS' 

IF ( (16 & @options) = 16 ) PRINT 'ANSI_PADDING' 

IF ( (32 & @options) = 32 ) PRINT 'ANSI_NULLS' 

IF ( (64 & @options) = 64 ) PRINT 'ARITHABORT' 

IF ( (128 & @options) = 128 ) PRINT 'ARITHIGNORE'

IF ( (256 & @options) = 256 ) PRINT 'QUOTED_IDENTIFIER' 

IF ( (512 & @options) = 512 ) PRINT 'NOCOUNT' 

IF ( (1024 & @options) = 1024 ) PRINT 'ANSI_NULL_DFLT_ON' 

IF ( (2048 & @options) = 2048 ) PRINT 'ANSI_NULL_DFLT_OFF' 

IF ( (4096 & @options) = 4096 ) PRINT 'CONCAT_NULL_YIELDS_NULL' 

IF ( (8192 & @options) = 8192 ) PRINT 'NUMERIC_ROUNDABORT' 

IF ( (16384 & @options) = 16384 ) PRINT 'XACT_ABORT'

Another way to check the settings of sessions is:
SELECT * FROM sys.dm_exec_sessions WHERE session_id in (68,61)

A list of the SET options:

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

Errors and optional solutions while querying LDAP

ERROR:

An error occurred while preparing the query ... for execution against OLE DB provider ADSDSOObject for linked server.

Optional Solutions/problems:

Add the server name to the LDAP address: LDAP://CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2 --> LDAP://SERVERNAME/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2.

ERROR:

The OLE DB provider "ADSDSOObject" for linked server "adsi_test" reported an error. The provider indicates that the user did not have the permission to perform the operation.

Optional Solutions/problems:

Run the query with user that have relevant permissions in the LDAP.

ERROR:

Cannot fetch a row from OLE DB provider "ADSDSOObject" for linked server "adsi_test".

Optional Solutions/problems:

Maybe the data from the LDAP is wrong.

ERROR:

An error occurred while preparing the query "

...........

" for execution against OLE DB provider "ADSDSOObject" for linked server "adsi_test". 

Optional Solutions/problems:

- Wrong syntax of the query.

- Maybe there is a problem to run the query from other machine that it's not the SQL Server.

Querying LDAP through SQL Server

In order to querying LDAP (Active Directory, LDS, etc.) through SQL Server, we need to create a Linked Server to the LDAP, and use OPENROWSET statement to select data from it.

-- create new linked server

exec sp_addlinkedserver  

  @server = 'adsi_test',-- linked server name

  @srvproduct = 'LDAP',  

  @provider ='ADSDSOObject',-- provider name

  @datasrc = ''  

go

-- create linked server login for sa

exec sp_addlinkedsrvlogin

  @rmtsrvname = 'adsi_test',-- linked server

  @useself = false,

  @locallogin = 'sa',-- sqlUser

  @rmtuser = 'domain\master_user',-- domainName\userName

  @rmtpassword = 'password'-- domain User Password

go

-- 2 options of query from LDAP:

--1:

select * FROM OPENQUERY(adsi_test, '<LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2>;(&(name=*));name,distinguishedName;subtree

')

--2:

select *  

from  openquery(adsi_test, '  

select  userPrincipalName

from    ''LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''  

where   objectCategory = ''Person'' and objectClass = ''user''  

')

• CN is containers that are usually defined as users, contacts, groups.
• OU is organizational units that usually contain objects such users, contacts, groups and other OUs.
• DC is domain containers that are created by separating the full internal domain name.
• LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2 = LDAP:// SERVER NAME : port / Root of the LDS/AD tree
• (&(name=*)) = WHERE NAME = * (no filter by name)
• ;name,distinguishedName - fields to select from LDS
• ;subtree - include all the tree from the declared root

Examples:

-- select all groups:

select distinguishedName from  openquery(adsi_test, '  

select name, distinguishedName

from    ''LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''  

where   objectCategory = ''Group''  

and  objectClass = ''Group''

')

-- select all users of group 'Managers' in AAA

select *

from  openquery(adsi_test, '  

select objectGUID, name, givenname, sn, distinguishedName

from    ''LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''  

where   objectCategory = ''Person''  

and  objectClass = ''user'' 

AND memberOf = ''cn=Managers,CN=AAA,CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2''

')

-- list of the groups that the user itaig is a member of: (the function 'member')

select * FROM OPENQUERY(adsi_test, 

'<LDAP://SERVERNAME:port/CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2>;

(&(objectCategory=group)(member=CN=itaig,CN=aaa,CN=OuTree,DC=aaa1,DC=aaa2));name,distinguishedName,objectGUID

')


Search Filter Syntax (msdn):
http://msdn.microsoft.com/en-us/library/Aa746475

Permanently deletion in case of instead of delete trigger

Permanently deletion is possible also in case of 'instead of delete' trigger on the table:

Delete statement in the 'instead of delete' trigger performs permanently deletion:

• The original deletion statement will not be executed - the code in the trigger will be executed instead.
• Deletion statement in the trigger will be execute as regular DELETE without the trigger code (and will not start infinite loop).

The common use of deletion from 'instead of delete' trigger is to write a row in LOG table before the deletion.

For example:

CREATE TABLE [dbo].[DelTests]

(

DelId INT IDENTITY(1,1) NOT NULL PRIMARY KEY,

DelStr nvarchar(50) NULL

)

GO

CREATE TRIGGER  [dbo].[trg_DelTests_delete] ON [dbo].[DelTests]

   instead of delete

AS 

BEGIN

DECLARE @Str nvarchar(50) = 'row that inserted from trg_DelTests_delete instead of'

INSERT INTO DelTests

select @Str from deleted

DELETE  DelTests WHERE DelId IN (select DelId from deleted)

END

GO

INSERT INTO DelTests (DelStr) VALUES ('DelStr1')

INSERT INTO DelTests (DelStr) VALUES ('DelStr2')

INSERT INTO DelTests (DelStr) VALUES ('DelStr2_1')

GO

select * from DelTests

GO

DELETE FROM DelTests WHERE DelStr = 'DelStr1' -- 1 row

GO

select * from DelTests

GO

/*

-- results:

DelStr2

DelStr2_1

row that inserted from trg_DelTests_delete instead

*/

DELETE FROM DelTests WHERE DelStr Like '%DelStr2%' -- 2 rows

GO

select * from DelTests

GO

/*

-- results:

row that inserted from trg_DelTests_delete instead

row that inserted from trg_DelTests_delete instead

row that inserted from trg_DelTests_delete instead

*/

Reporting Execution Log

SELECT * 

FROM [ReportServer].[dbo].[ExecutionLog2] 

ORDER BY TimeStart desc

http://blogs.msdn.com/b/robertbruckner/archive/2009/01/05/executionlog2-view.aspx

CONCAT_NULL_YIELDS_NULL

CONCAT_NULL_YIELDS_NULL controls whether or not concatenation results are treated as null or empty string values.

declare @strNull nvarchar(50) = null

SET CONCAT_NULL_YIELDS_NULL ON

select (@strNull + 'aaa') as str_result -- result: NULL

SET CONCAT_NULL_YIELDS_NULL OFF

select (@strNull + 'aaa') as str_result -- result: 'aaa'

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

Can not connect SQL Server from SSMS

When you Can not connect SQL Server from Management Studio (SSMS), Try:

1. Enable TCP/IP in the SQL Server Configuraion Manager:

2. Check Firewall settings on the server. If you can connect to the server when The firewall is disabled, the problem is the Firewall settings.

GROUPING SETS

A GROUP BY clause that uses GROUPING SETS can generate a result set equvalent to that generated by a UNION ALL of multiple simple GROUP BY clauses.

SELECT TestName, year, SUM(TestSales)FROM TestTable

GROUP BY GROUPING SETS ((TestName), (TestYear))

The results will be the same as:

SELECT NULL as TestName, TestYear, SUM(TestSales)FROM TestTable GROUP BY TestYear

UNION ALL

SELECT TestName, NULL as TestYear, SUM(TestSales)FROM TestTable GROUP BY TestName

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

sp_executesql ERROR: Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'

Error:

EXEC sp_executesql N'SELECT @x', '@x int', @x = 2

--or:

EXEC sp_executesql 'SELECT @x', N'@x int', @x = 2

This error message will be displayed:

Msg 214, Level 16, State 3, Procedure sp_executesql, Line 1

Procedure expects parameter '@parameters' of type 'ntext/nchar/nvarchar'.

Explanation:

The first and the second parameters of sp_executesql are Unicode - nvarchar/ntext (depend on the MSSQL version).

varchar is not valid!

Solutions:

--1.

EXEC sp_executesql N'SELECT @x', N'@x int', @x = 2

-- 2.

DECLARE @stmt nvarchar(max), @params nvarchar(max)

SET @stmt = 'SELECT @x' -- @stmt was declared as nvarchar, so the N is not required

SET @params = '@x int' -- @params was declared as nvarchar, so the N is not required

EXEC sp_executesql @stmt, @params, 2

QUOTENAME

QUOTENAME returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.

SELECT QUOTENAME('abcdef') --result: [abcdef]

SELECT QUOTENAME('abc-def') --result: [abc-def]

SELECT QUOTENAME('abc[]def') --result: [abc[]]def]

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

Slow in Application, Fast in SSMS

There is not unusual situation that query or stored procedure run very slow from the application, but run fast from the SSMS (SQL Server Management Studio).

There are some optional reasons for this situation, I'll describe in this post.

Parameter Sniffing

http://copypastenet.blogspot.com/2011/01/sql-server-parameter-sniffing.html

Different Plans for Different Settings

Different Settings between SSMS and the application create different plans.

Different Default Setting between SSMS and the application is for ARITHABORT – in Applications using ADO .Net, ODBC or OLE DB is OFF, and in SSMS is ON. This can cause different plans. In many the ARITHABORT settings is the reason for performance problems in the application.

Check settings of sessions using:

SELECT * FROM sys.dm_exec_sessions 
WHERE session_id in (68,61) --SSMS session VS APP session.

http://copypastenet.blogspot.com/2011/10/get-set-options-for-current-session.html

Blocking

Blocking between statements and/or transactions from/in the applications.

Permissions on the remote database

Permissions on the remote database can be different than the permissions of the user in SSMS, and this can cause to different statistics, query plans, etc.

For simulate the application execution, try to execute in SSMS with: 

EXECUTE AS USER = 'AppUser'

Bad SQL Code

Bad SQL Code is always bad.....

Plans for Dynamic SQL

Unparameterised SQL is bad for the performance:

Not good:

SELECT @sql = 'SELECT mycol FROM tbl WHERE keycol = ' + convert(varchar, @value)

EXEC(@sql)

Or in C#:

cmd.CommandText = "SELECT mycol FROM tbl WHERE keycol = " + value.ToString();

Good:

EXEC sp_executesql N'SELECT mycol FROM dbo.tbl WHERE keycol = @value',

                   N'@value int', @value = @value

Or in C#:

cmd.CommandText = "SELECT mycol FROM dbo.tbl WHERE keycol = @value";

cmd.Parameters.Add("@value", SqlDbType.Int);

cmd.Parameters["@value"].Value = value;

Application cache

Similar to Parameter Sniffing.

Note: Beside of my experience, in this post I also based on one of the best articles that I ever read:

http://www.sommarskog.se/query-plan-mysteries.html

NULLIF

NULLIF returns a null value if the two specified expressions are equal.

SELECT NULLIF (colA, 0)

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

NULLIF is good to prevent 'divide by zero' issues, by converts the column that can be zero to NULL, and than the result will be NULL and not ERROR (divide by NULL = NULL).

SQL Server Denali (2011) main features and innovations

WINDOW Functions

Window Functions are functions on subsets of rows that meet a pre-defined, user defined criteria. Any subset of user-defined records or rows is called a “window”.

SQL Server Denali takes the Window Functions to the next step by adding functionality.

Window Functions are, as examples, ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE(), OVER(), LEAD and LAG.

LEAD and LAG

Return the previous (LAG) and the next (LEAD) value

LAG (scalar_expression [,offset] [,default]) OVER ( [ partition_by_clause ] order_by_clause )

SELECT LAG(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS 'LAG' FROM .....

http://msdn.microsoft.com/en-us/library/hh231256%28v=SQL.110%29.aspx

LEAD ( scalar_expression [ ,offset ] , [ default ] ) OVER ( [ partition_by_clause ] )

SELECT LEAD(SalesQuota, 1,0) OVER (ORDER BY YEAR(QuotaDate)) AS 'LEAD' FROM .....

http://msdn.microsoft.com/en-us/library/hh213125%28v=SQL.110%29.aspx

FIRST_VALUE and LAST_VALUE

Return the first or the last value in the "window".

FIRST_VALUE ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause [rows_range_clause ] )

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

LAST_VALUE ( [scalar_expression ) OVER ( [ partition_by_clause ] order_by_clause rows_range_clause ) 

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

More T-SQL stuff

TRY_CONVERT(), PARSE() and TRY_PARSE()

TRY_CONVERT() - Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] )

SELECT TRY_CONVERT(float,'test') -- result: NULL

SELECT TRY_CONVERT(datetime2, '12/31/2010') -- result: 2010-12-31 00:00:00.0000000

PARSE() - Returns a value cast to the specified data type if the cast succeeds; otherwise, raise an error.

SELECT PARSE('10' AS TIME) -- result: ERROR!

SELECT PARSE('10' AS Float(10,2)) -- result: 10.00 

TRY_PARSE() - Returns a value cast to the specified data type if the cast succeeds; otherwise, return null.

SELECT TRY_PARSE('10' AS TIME) -- result: NULL

SELECT TRY_PARSE('10' AS Float(10,2)) -- result: 10.00 

FORMAT()

Returns a value formatted with the specified format and optional culture.

Mainly useful for Dynamic SQL.

FORMAT ( value, format [, culture ] )

SELECT FORMAT(@d, N'yyyy-MM')

SELECT FORMAT(@d, N'yyyy-MM-dd'),

SELECT FORMAT(@d, N'dddd, MMMM dd, yyyy', N'fr-fr')

SELECT Format(0.5,'000.0')

SELECT Format(1234,'#,###')

http://technet.microsoft.com/en-us/library/hh213505%28SQL.110%29.aspx

IIf()

IIf(Condition, value_if_true, value_if_false)

SELECT IIF(GETDATE() > '20110101', '2011', 'earlier)'

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

Choose

Choose(index, val_1, val_2 [, val_n ])

SELECT Choose(3, 'A', 'B', 'C', 'D') -- result: 'C'

SELECT Choose(ColInt, 'one', 'two', 'three', 'four', ...) FROM TABLENAME

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

Concat ()

Concat is similar to the operator +, with those differences: 

• convertion of numbers and dates without 'Cast'.
• Null = '' and not make all the result to be null.

Select Concat(@S,',',object_id) From sys.objects 

-- result: all the IDs from sys.objects, separated by ','.

Datetime Functions creation

DateTimeFromParts

DateTime2FromParts

DateTimeOffsetFromParts

SmallDateTimeFromParts

Functions that create datetime from divided parts.

WITH RESULT SETS

Change the names and/or data types of the returning result set of a stored procedure.

http://copypastenet.blogspot.com/2011/04/with-result-sets.html

SQL built-in Paging

http://copypastenet.blogspot.com/2010/12/sql-paging-in-sql-server-2011.html

Returns only metadata

SET FMTONLY returns only metadata to the client.

http://copypastenet.blogspot.com/2011/06/set-fmtonly.html

SEQUENCE

SEQUENCE allows you to define a central place where SQL Server will maintain a counter of values for you.

Using a sequence number in multiple tables is one of the adventages of SEQUENCE.

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


--CREATE SEQUENCE:
CREATE SEQUENCE dbo.Seq1
    AS INT -- could be any type of int
    MINVALUE 1 -- default: min value of the defined type
    MAXVALUE 100 -- default: max value of the defined type
    START WITH 1
    INCREMENT BY 1 -- or any other number
    CYCLE
GO
-- note: CYCLE is not must parameter - if it chosen, when arrive to the max. vakue, the SEQUENCE will start again from the min. value.

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

-- get new number from SEQUENCE:
SELECT NextID = NEXT VALUE FOR dbo.dbo.Seq1
http://msdn.microsoft.com/en-us/library/ff878370(v=sql.110).aspx

-- ALTER SEQUENCE:
ALTER SEQUENCE dbo.Seq1 RESTART WITH 15


Notes:

1. SEQUENCE will not provide you a transactionally consistent and gap-free stream of values.  Like IDENTITY, if your transaction rolls back, the next value is still taken.

2. One of the nice adventage of SEQUENCE relatively to IDENTITY is that you can get the next ID before the insert statement:
DECLARE @NextID int ;
SET @NextID = NEXT VALUE FOR dbo.Seq1
.........
INSERT TABLENEME (ID, Name) VALUES (@NextID, 'Itai')

Column based database - Apollo Project

Column Store Index

Column Store Index is new type of index which stored columns in separate set of data pages instead of rows stored in data pages.

Column Store Index, that can improve performance, mostly for queries which are used aggregation , warehouse processing and BI application.

Column Store Index is read only.

CREATE NONCLUSTERED COLUMNSTORE INDEX MyIndex ON MyTable(col1, col2, …)

SQL Server Developer Tools, Codename "Juneau"

"Juneau" is the new SSMS for developers in Denali.

"Juneau" (SSDT) is a tool for on and off-premise database development within Visual Studio. 

The Server Explorer in VS gives you an SSMS-like view of your live database objects. 

SSDT also provides database developers with code navigation, IntelliSense, language support that parallels what is available for C# and VB, platform specific validation, debugging and declarative editing in the TSQL Editor, as well as a visual Table Designer for both database projects and online database instances.

http://www.microsoft.com/web/gallery/install.aspx?appid=JUNEAU10

Reporting Services - Project Crescent

"Crescent" is the new Reporting Services in Denali.

Project “Crescent” is a new interactive data exploration and visual presentation experience. It will offer a fun, visual, and powerful drag-and-drop ad hoc reporting experience. It is an web-based end-user BI tool based on Silverlight.

Demo for SQL Server Project "Crescent":

http://www.youtube.com/watch?v=75szAtMrkNs&feature=player_embedded

HADR - AlwaysOn

HADR, (or AlwaysOn, or Availability Groups)

The Always On Availability Groups feature is a high-availability and disaster-recovery solution that provides an enterprise-level alternative to database mirroring. 

AlwaysOn Availability Groups maximizes the availability of a set of user databases for an enterprise. 

Deploying AlwaysOn Availability Groups involves creating and configuring one or more availability groups. 

http://msdn.microsoft.com/en-us/library/ff877884(SQL.110).aspx

More Useful links:

MSDN: What's New (SQL Server "Denali")

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

Download the SQL Server Denali Community Technical Preview 3 (CTP3) Product Guide:

http://www.microsoft.com/download/en/details.aspx?id=27069

SQL Server code name Denali Links:

http://copypastenet.blogspot.com/2011/06/sql-server-code-name-denali-home-page.html

Script of DB backup Job creation

-- 1. replace all 'DataBaseName' to your DB Name.

-- 2. replace DomainName\master to your user name.

-- 3. execute on master DB.

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'DataBaseNameBackup', 

@enabled=1, 

@notify_level_eventlog=0, 

@notify_level_email=0, 

@notify_level_netsend=0, 

@notify_level_page=0, 

@delete_level=0, 

@description=N'DataBaseName Backup', 

@category_name=N'[Uncategorized (Local)]', 

@owner_login_name=N'DomainName\master', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'DataBaseNameBackup', 

@step_id=1, 

@cmdexec_success_code=0, 

@on_success_action=1, 

@on_success_step_id=0, 

@on_fail_action=2, 

@on_fail_step_id=0, 

@retry_attempts=0, 

@retry_interval=0, 

@os_run_priority=0, @subsystem=N'TSQL', 

@command=N'BACKUP DATABASE [DataBaseName] TO  DISK = N''D:\Backup\DataBaseName.Bak'' WITH  INIT ,  NOUNLOAD ,  NAME = N''DataBaseName Backup'',  SKIP ,  STATS = 10,  NOFORMAT', 

@database_name=N'master', 

@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N'DataBaseNameBackup', 

@enabled=1, 

@freq_type=4, 

@freq_interval=1, 

@freq_subday_type=1, 

@freq_subday_interval=0, 

@freq_relative_interval=0, 

@freq_recurrence_factor=0, 

@active_start_date=20110201, 

@active_end_date=99991231, 

@active_start_time=30000, 

@active_end_time=235959, 

@schedule_uid=N'47e12b03-58e7-4103-b196-f6f812f34a79'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

WITH CHECK OPTION

'WITH CHECK OPTION' Forces all data modification statements executed against the view to follow the criteria set within select_statement. 

When a row is modified through a view, the WITH CHECK OPTION makes sure the data remains visible through the view after the modification is committed.

Example:

CREATE VIEW CheckOptionTest

AS

SELECT [StamTableId], [StamTableStr], [colIntA]

FROM StamTable

WHERE [colIntA] > 5

GO

-- select #1:

select * from CheckOptionTest

GO

-- update #1:

update CheckOptionTest set [colIntA] = [colIntA] - 2

GO

-- select #2:

select * from CheckOptionTest

-- less rows than select #1 (because of the update)

GO

ALTER VIEW CheckOptionTest

AS

SELECT [StamTableId], [StamTableStr], [colIntA]

FROM StamTable

WHERE [colIntA] > 5

WITH CHECK OPTION

GO

-- update #2:

update CheckOptionTest set [colIntA] = [colIntA] - 2

GO

-- in case of rows that colIntA is not bigger from 5 in more than 2:

-- update will be failed: can't update rows from the view with values that don't follow the criteria set within the select statement.

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

@@TRANCOUNT - TRANSACTIONs count

@@TRANCOUNT returns the number of BEGIN TRANSACTION statements that have occurred on the current connection.

SELECT @@TRANCOUNT

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

Plan guide for associating query

sp_create_plan_guide creates a plan guide for associating query hints or actual query plans with queries in a database.

EXEC sp_create_plan_guide 

    @name =  N'Guide1',

    @stmt = N'SELECT *

              FROM Sales.SalesOrderHeader AS h 

              INNER JOIN Sales.Customer AS c 

                 ON h.CustomerID = c.CustomerID

              INNER JOIN Sales.SalesTerritory AS t 

                 ON c.TerritoryID = t.TerritoryID

              WHERE t.CountryRegionCode = @Country_region',

    @type = N'OBJECT',

    @module_or_batch = N'Sales.GetSalesOrderByCountry',

    @params = NULL,

    @hints = N'OPTION (OPTIMIZE FOR (@Country_region = N''US''))';

EXEC sp_create_plan_guide 

    @name = N'Guide1', 

    @stmt = N'SELECT TOP 1 * 

              FROM Sales.SalesOrderHeader 

              ORDER BY OrderDate DESC', 

    @type = N'SQL',

    @module_or_batch = NULL, 

    @params = NULL, 

    @hints = N'OPTION (MAXDOP 1)';

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

sp_control_plan_guide drops, enables, or disables a plan guide.

EXEC sp_control_plan_guide N'DROP', N'Guide1'

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

Does my database contain edition-specific features?

sys.dm_db_persisted_sku_features is used to list all edition-specific features that are enabled in the current database.

SELECT * FROM sys.dm_db_persisted_sku_features

GO

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

sp_MsForEachDB

sp_MsForEachDB perform an action on all of the databases.

exec sp_MsForEachDB @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand

• @command1 - the command to be executed by sp_MsForEachDB.
• @replacechar - a character in the command string that will be replaced with the database name being processed (default '?').
• @command2 and @command3 are two additional optional commands that can be run for each database (@command2 will be run after @command1, @command3 will be run after @command2).
• @whereand - an additional constraints to help identify the rows in the sysobjects database that will be selected.
• @precommand - a command to be run prior to processing any database.
• @postcommand - a command to be run after all commands have been processed against all databases.

Examples:

declare @cmd1 varchar(500)

declare @cmd2 varchar(500)

declare @cmd3 varchar(500)

set @cmd1 = 'if ''?'' <> ''tempdb'' print ''*** Processing DB ? ***'''

set @cmd2 = 'if ''?'' <> ''tempdb'' backup database ? to disk=''c:\temp\?.bak'''

set @cmd3 = 'if ''?'' <> ''tempdb'' dbcc checkdb(?)'

exec sp_MSforeachdb @command1=@cmd1,

                    @command2=@cmd2,

                    @command3=@cmd3

GO

EXEC sp_MSforeachdb 'USE ?; EXEC sp_spaceused'

sp_MSforeachtable

sp_MSforeachtable perform an action on all of the tables within a database.

exec sp_MSforeachtable @command1, @replacechar, @command2, @command3, @whereand, @precommand, @postcommand

• @command1 - the command to be executed by sp_MSforeachtable.
• @replacechar - a character in the command string that will be replaced with the table name being processed (default '?').
• @command2 and @command3 are two additional optional commands that can be run for each table (@command2 will be run after @command1, @command3 will be run after @command2).
• @whereand - an additional constraints to help identify the rows in the sysobjects table that will be selected.
• @precommand - a command to be run prior to processing any table
• @postcommand - a command to be run after all commands have been processed against all tables

Examples:
declare @cmd1 varchar(500)
set @cmd1 = 'exec sp_Help "?"'
exec sp_MSforeachtable @command1=@cmd1
GO


EXEC sp_MSforeachtable @command1="print '*'; DBCC DBREINDEX ('*', ' ', 80)" ,@replacechar='*'
GO

RANK OVER, DENSE_RANK OVER

--Returns the rank of a result set:

SELECT StamTableId, StamTableStr, colIntA, 

RANK() OVER (ORDER BY colIntA) as Rank_over

FROM StamTable

--Returns the rank of rows of a result set, without any gaps in the ranking:

SELECT StamTableId, StamTableStr, colIntA, 

DENSE_RANK() OVER (ORDER BY colIntA) as Rank_over

FROM StamTable

--Returns the rank of rows within the partition of a result set, without any gaps in the ranking:

SELECT StamTableId, StamTableStr, colIntA, 

RANK() OVER (PARTITION BY StamTableStr ORDER BY StamTableStr, colIntA) as Rank_over

FROM StamTable

RANK():

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

DENSE_RANK():

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

Get the output of INSERT, DELETE, and UPDATE statements

DELETE

FROM StamTable2

OUTPUT deleted.*

WHERE StamTableId > 11

INSERT INTO StamTable2 (StamTableStr, StamTableStr2)

OUTPUT inserted.*

SELECT StamTableStr, StamTableStr FROM StamTable WHERE StamTableId > 11

UPDATE StamTable2

SET StamTableStr2 = StamTableStr2 + '_UPDATED'

OUTPUT deleted.*, inserted.*

WHERE StamTableId > 11

Note: This OUTPUT doesn't work when there are 'instead of' triggers.

User can not connect remotely to SQL Server

Problem:

An user can not connect remotely to SQL Server, but he can connect in the server machine.

Solution:

Check that TCP/IP is enabled in SQL Server Configuration Manager --> SQL Server Network Configuration --> Protocols for MSSQLSERVER:

sp_change_users_login

sp_change_users_login @Action='update_one', @UserNamePattern='itai', @LoginName='itai'

GO

Maps an existing database user to a SQL Server login.

@Action can be:

• Auto_Fix - Links a user entry in the sys.database_principals system catalog view in the current database to a SQL Server login of the same name.
• Report - Lists the users and corresponding security identifiers (SID) in the current database that are not linked to any login.
• Update_One - Links the specified user in the current database to an existing SQL Server login.

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