Archive for the ‘T-SQL’ Category

« Previous Entries

Assign a value into variable from empty set cause the value remains the same.
Assign a value into variable from empty set which include an aggregate cause the value will be set to the value returned from aggregate. One row is returned everytime, even if no rows were aggregated.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36

declare @x int
declare @table table(
	a int not null
)
 
insert into @table values(10)
 
--1)
set @x =-1
select 
	@x = min(a)
from
	@table
where 
	1=2
 
select @x
 
--2)
set @x =-1
select 
	@x = a
from
	@table
where 
	1=2
 
select @x
 
/*
-----------
NULL
 
-----------
-1
*/
Share

Posted by Jan Novak on January 4, 2012 at 21:11:45 under T-SQL.
Comment on this post.

There are a few ways how to write Xpath expression. Not every expression may return expected data. Here is a review and Subtree cost in execution plan (SQL Server 2008 R2, 64bit) for each tested method. Xml contains 100 nodes, some of them are empty:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156

declare @x xml
set @x = N'<root>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>
			<item>D8749A19-B608-40D0-90F3-B66791303342</item>
			<item>63C3002A-98CA-4BCA-B05A-0AB5EBB43B65</item>			
			<item>77B04F73-206C-4992-A9DF-3FA2E73313DD</item>
			<item></item>
			<item>DB61F27A-C282-4D0D-89C2-FC1C624FA119</item>			
		  </root>'
 
--With defined singletons:
--First node
select @x.value(N'(root[1]/item[1]/text()[1])', N'UniqueIdentifier') 
--Select estimated subtree cost: 0.42
--If in the element is empty value, it is converted sucesfully to Null due text() 
--at the end of Xpath expression.
--more here http://www.xquery.com/tips_and_tricks/using_text_in_XPath.html
 
select @x.value(N'(root[1]/item[1]/text()[1])[1]', N'UniqueIdentifier') 
--Select estimated subtree cost: 0.42
--If in the element is empty value, it is converted sucesfully to Null due text()
--at the end of Xpath expression.
 
select @x.value(N'(root[1]/item[1])', N'UniqueIdentifier') 
--Select estimated subtree cost: 1.23 
--If in the element is empty value, the statement fails with message 
--"Conversion failed when converting from a character string to uniqueidentifier."
 
--Node 93:
select @x.value(N'(root[1]/item[93]/text()[1])', N'UniqueIdentifier') 
--Select estimated subtree cost: 1.68
--This Subtree cost stay the same with node 990.
 
select @x.value(N'(root[1]/item[93]/text()[1])[1]', N'UniqueIdentifier') 
--Select estimated subtree cost: 1.69
 
select @x.value(N'(root[1]/item[93])', N'UniqueIdentifier') 
--Select estimated subtree cost: 4.31
 
--------------------------------------------------------------------------
 
--There are not defined singletons, it may cause not expected behaviour.
--First node
select @x.value(N'(root/item/text())[1]', N'UniqueIdentifier') 
--Select estimated subtree cost: 0.005 
--May return unexpected data, more bellow.
 
select @x.value(N'(root/item)[1]', N'UniqueIdentifier') 
--Select estimated subtree cost: 1.02
--If in the element is empty value, the statement fails with message 
--"Conversion failed when converting from a character string to uniqueidentifier."
 
--Node 93:
select @x.value(N'(root/item/text())[93]', N'UniqueIdentifier') 
--Select estimated subtree cost: 1.004 
--XPath selects only nodes with text. This XPath will contains Null, because 
--93th node does not exist.
 
select @x.value(N'(root/item)[93]', N'UniqueIdentifier') 
--Select estimated subtree cost: 55.405
--If in the element is empty value, the statement fails with message 
--"Conversion failed when converting from a character string to uniqueidentifier."
Share

Posted by Jan Novak on January 3, 2012 at 20:49:19 under T-SQL.
Comment on this post.

Stored procedures, tables and views in master database with prefix sp_ are accessible in user databases. If in user database exists the same object as in the master database, the user database object has higher priority to be used. Here is a demo:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

use master
go
create procedure sp_v1
as
select 1
go
use myDb
go
exec sp_v1
--1
go
create procedure sp_v1
as
select 2
go
exec sp_v1
--2
Share

Posted by Jan Novak on December 30, 2011 at 21:31:15 under T-SQL.
Comment on this post. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58

Alter Function [dbo].[fn_format_date] (@Datetime DateTime, @FormatMask NVarChar(32))
Returns NvarChar(32)
As
Begin
    Declare @StringDate NVarChar(32)
    Set @StringDate = @FormatMask
 
    If (CharIndex (N'YYYY',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'YYYY', DateName(YY, @Datetime))
    End
    If (CharIndex (N'YY',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'YY', Right(DateName(YY, @Datetime),2))
	End  
    If (CharIndex (N'MM',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'MM', Right(N'0'+Convert(VARCHAR,DatePart(MM, @Datetime)),2))
	End
    If (CharIndex (N'M',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'M', Convert(VARCHAR,DatePart(MM, @Datetime)))
	End
    If (CharIndex (N'DD',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'DD', Right(N'0'+DateName(DD, @Datetime),2))
	End
    If (CharIndex (N'D',@StringDate) > 0)
    Begin
       Set @StringDate = Replace(@StringDate, N'D', DateName(DD, @Datetime))
    End
Return @StringDate
End
 
Go 
Set Nocount On
Declare @lDateTime Datetime = '20110506'
Print [dbo].[fn_format_date] (@lDateTime, 'MM/DD/YYYY')
Print [dbo].[fn_format_date] (@lDateTime, 'DD/MM/YYYY')
Print [dbo].[fn_format_date] (@lDateTime, 'M/DD/YYYY')
Print [dbo].[fn_format_date] (@lDateTime, 'M/D/YYYY') 
Print [dbo].[fn_format_date] (@lDateTime, 'M/D/YY')
Print [dbo].[fn_format_date] (@lDateTime, 'MM/DD/YY')
Print [dbo].[fn_format_date] (@lDateTime, 'YYYY/MM/DD')
Print [dbo].[fn_format_date] (@lDateTime, 'YYYYMMDD')
Print [dbo].[fn_format_date] (@lDateTime, 'YYYY-MM-DD')
 
/*
05/06/2011
06/05/2011
5/06/2011
5/6/2011
5/6/11
05/06/11
2011/05/06
20110506
2011-05-06
*/
Share

Posted by Jan Novak on December 12, 2011 at 19:25:12 under MS SQL Server, T-SQL.
Comment on this post.

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

Posted by Jan Novak on December 8, 2011 at 20:43:11 under T-SQL.
Comment on this post.

« Previous Entries