Archive for the ‘T-SQL’ Category

There are two webinars which I saw recently and which interrested me:

SQL Azure from Richard Iwasa, Senior Consultant, Solution Architect, Ideaca:
https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032497373&Culture=en-CA
It contains deep info about SQL Azure and it is worth seeing.

Microsoft Virtual Academy also offers more free videos and best practices courses about SQL Azure: https://www.microsoftvirtualacademy.com/tracks/introduction-to-sql-azure

Performance query tunning (it does not contain internals, but contains common principles for query tunning):
http://marketo.confio.com/OnDemand_QueryPerformance_Triplett.html

Share

@@RowCount returns the number of rows affected by the last statement (BOL).

The documentation does not specify behaviour for FOR XML clause in the SELECT statement. When FOR XML clause is used, the behaviour is the same. The FOR XML clause converts the result into one row, but @@RowCount contains number of all affected rows in the SELECT statement.

1
2
3
4
5
6
7
8
9
10
SELECT Top 100
	[number]
FROM 
	[master].[dbo].[spt_values] 
ORDER BY 
	[number] Asc
FOR XML PATH('')  
 
SELECT @@ROWCOUNT
--100
Share

One of the possible ways to paste UniqueIdentifier into text string is:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Declare @lId UniqueIdentifier = NewId()
Declare @x NVarChar(Max) =
	N'
	Select
		[id]
	From
		[table]
	Where
		[column] = '+IsNull(N'N'''+Cast(@lId As NVarChar(64))+'''', 'Null')
Print @x
/*
	Select
		[id]
	From
		[table]
	Where
		[column] = N'094FDFB4-7665-4E6A-89E0-B6A6C231D617'
*/

If Null is in the variable, the output will be:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Declare @lId UniqueIdentifier
Declare @x NVarChar(Max) =
	N'
	Select
		[id]
	From
		[table]
	Where
		[column] = '+IsNull(N'N'''+Cast(@lId As NVarChar(64))+'''', 'Null')
Print @x
/*
	Select
		[id]
	From
		[table]
	Where
		[column] = Null
*/
Share

Microsoft Canada Inc. offers free five live webcasts to help understand the benefits included in the next release of  SQL Server 2012 (Denali), commencing November 9th through December 7th at 1:00 pm EST.

More information, schedule and registration on:

https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032497363&Culture=en-CA#

Share

There is a new free e-book Complete Showplan Operators written by Fabiano Amorim(SQL Server MVP).  More details are here including link for download: http://www.simple-talk.com/books/sql-books/complete-showplan-operators/

Share