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.

ZUNO banka nabízí spoření s denním úročením, při kterém se do částky 127500 neplatí 15 % daň z úroků. Výše úrokové sazby je 2% p.a.

Dále nabízí spoření plus s úrokovou sazbou 2,5% p.a. s tím, že úroky jsou připisovány měsíčně a daněny 15% daní. Peníze v daném měsíci není možné převést jinam, jinak se úroky za daný měsíc nepřipíší.

Zde je srovnání vkladu 100 000 Kč:

Spoření plus (2,5% s daní 15 %):

Úrok za rok: 2500,00 Kč

Daň z úroku: 375,00 Kč (15%)

Čistý úrok za rok: 2125 Kč

Úrok za 31 dní: 180,47 Kč

 

Spoření (2% s daňovým zvýhodněním – denní připisování úroků):

Čistý úrok za rok: 2025,66 Kč

Úrok za 31 dní: 172,04 Kč

 

Spoření u jiné banky (2,0% s daní 15 %):

Úrok za rok: 2000,00 Kč

Daň z úroku: 300,00 Kč (15%)

Čistý úrok za rok: 1700 Kč

Úrok za 31 dní: 144,38 Kč

Share

Posted by Jan Novak on December 22, 2011 at 12:06:49 under Ostatní.
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.

« Previous Entries