Simple example how to use GROUP and ORDER BY in child nodes:

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

Create Table vendor(
	id_vendor int not null primary key,
	name varchar(100)
)
 
Create Table model(
	id_model int not null primary key,
	id_vendor int foreign key (id_vendor) references vendor(id_vendor),
	name varchar(100)
)
 
Insert Into vendor 
(
	id_vendor, 
	name
)
Select	
	1, 'Audi'
Union
Select
	2, 'Opel'
 
Insert Into model
(
	id_model, 
	id_vendor,
	name
)
Select	
	1, 1, 'A3'
Union
Select	
	2, 1, 'A1'	
Union
Select	
	3, 1, 'A4'
Union
Select
	4, 2, 'Vectra'
Union
Select
	5, 2, 'Astra'
Union
Select
	6, 2, 'Omega'
 
;With cte As 
(
Select 
	vendor.id_vendor, 
	vendor.name As vendor_name, 
	model.id_model, 
	model.name As model_name
From 
	vendor
Inner Join 
	model
On vendor.id_vendor = model.id_vendor
)
Select 
	id_vendor, 
	vendor_name,
	(Select 
		id_model, 
		model_name
	From 
		cte As detail
	Where
		detail.id_vendor = cte.id_vendor
	Order By model_name
	For Xml Path('detail'), Type
	)	
From 
	cte
Group By 
	id_vendor, 
	vendor_name 
Order By vendor_name Desc	
For Xml Path('vendor'), Root('root')
Share

Posted by Jan Novak on March 12, 2012 at 18:43:11 under T-SQL.
Comment on this post.

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.

« Previous Entries