Category Archives: SQL Server

How to determine which version of SQL Server 2008 is running

To determine which version of Microsoft SQL Server 2008 is running, connect to SQL Server 2008 by using SQL Server Management Studio, and then run the following Transact-SQL statement.
[sourcecode language=”sql”]
SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)
[/sourcecode]

Share

SQL Injection

In the Page_load of a ASP.NET page called inject.aspx:
[sourcecode language=”csharp”]
SqlConnection con =
new SqlConnection(@"data source=(local)TEST;user id=sa;password=sa;database=Northwind");
con.Open();
string sqlstring = String.Format("INSERT INTO Categories (CategoryName, Description, Picture) VALUES (‘naam’, ‘{0}’, null)",
Request.QueryString["desc"]);
Response.Write(sqlstring);
SqlCommand cmd = new SqlCommand(sqlstring, con);
cmd.ExecuteNonQuery();
con.Close();
[/sourcecode]
Direct your browser to “……/…../inject.aspx?test=x’,null); drop table test; –” and your table Test is gone with the (North)wind.

See also this link for more information.

Share

SELECT TOP WITH TIES

[sourcecode language=”sql”]
create table tie
(
id int,
price int
)
insert into tie values (1, 20);
insert into tie values (1, 30);
insert into tie values (1, 40);
insert into tie values (1, 50);
insert into tie values (1, 60);
insert into tie values (1, 60);

select top 5 with ties from tie
order by price asc;
[/sourcecode]
Results
1 20
2 30
3 40
4 50
5 60
6 60
The first 6 rows are part of the result set. 6 rows because row number 5 and 6 have the same price value. The price value is looked at because the WITH TIES clause has an ORDER BY on price. An order by clause is mandatory with the WITH TIES construct.

Share

SQL Joins

Start situation (download the sql script here)

Table A Table B
1 2
2 3
3 4

The INNER join (“normal / regular” join)
[sourcecode language=”sql”]
select a.id as A, b.id as B from a inner join b on a.id = b.id;
[/sourcecode]
Results
A B
2 2
3 3

Only rows that have the same id in both tables appear in the result set.

The LEFT OUTER join
[sourcecode language=”sql”]
select a.id as A, b.id as B from a left join b on a.id = b.id;
[/sourcecode]
Results
A B
1 NULL
2 2
3 3

Every row from the table mentioned on the left (table A in this example) will be part of the result set. If possible the corresponding row (the one with the same id) in table B is also part of the result set. If no corresponding row is found in table B NULL values are provided.
The RIGHT OUTER join
[sourcecode language=”sql”]
select a.id as A, b.id as B from a right join b on a.id = b.id;
[/sourcecode]
Results
A B
2 2
3 3
NULL 4

Every row from the table mentioned on the right (table B in this example) will be part of the result set. If possible the corresponding row (the one with the same id) in table A is also part of the result set. If no corresponding row is found in table A NULL values are provided.
The FULL OUTER join
[sourcecode language=”sql”]
select a.id as A, b.id as B from a full join b on a.id = b.id;
[/sourcecode]
Results
A B
1 NULL
2 2
3 3
NULL 4

Every row from table A and table B is part of the result set. If possible a match is made based on the id. If a match could not be made NULL values are provided for the missing items.
Cartesian product
select a.id as A, b.id as B from a , b;
Results
A B
1 2
2 2
3 2
1 3
2 3
3 3
1 4
2 4
3 4

Every row in table A is joined with every row in table B. So the cartesian product of table A and table B delivers 9 rows in the result set. A cartesian produkt is often specified by mistake (missing join condition).

Share

SQL Server Examples Part 2

[sourcecode language=”sql”]
— **************************************************************************
— * COALESCE FUNCTION *
— * *
— * This functions returns the first non-null value of its arguments. *
— * When all arguments are null; null is returned. *
— **************************************************************************
exec spDeleteObj ‘t1’, 3, 0
go
create table t1
(
id int identity(1,1) not null,
cola int,
colb int,
colc int
)
set nocount on
insert into t1 values (1, null, null)
insert into t1 values (null, 2, null)
insert into t1 values (null, null, 3)
insert into t1 values (null, null, null)
set nocount off
— When none of the columns contains a value 999 is returned
select cast(coalesce(cola, colb, colc, 999) as money) * 2 ColX from t1
— When all values are null the row is in the resultset
select id from t1 where coalesce(cola, colb, colc) IS NULL
[/sourcecode]
[sourcecode language=”sql”]
— **************************************************************************
— * GROUP BY and HAVING clause *
— * *
— * Table "Customer" and table "Region" *
— * – a customer belongs to 1 region. A region has 0 or more customersn *
— * *
— * Only the column on which is grouped can be in the selectlist. All other*
— * columns in the selectlist must have a aggregate function. *
— **************************************************************************
exec spDeleteObj ‘Customer’, 3, 0
exec spDeleteObj ‘Region’, 3, 0
go
create table Region
(
RegionId int identity(1,1) not null,
RegionName varchar(30) not null
constraint pk_region primary key clustered (RegionId)
)
set nocount on
insert into region (RegionName) values (‘friesland’)
insert into region (RegionName) values (‘groningen’)
set nocount off
create table Customer
(
CustomerId int identity(1,1) not null,
CustomerName varchar(30) not null,
CustomerAmount int not null,
CustomerRegion int not null
constraint fk_region foreign key (CustomerRegion) references region(RegionId)
)
set nocount on
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values (‘a’, 1, 1)
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values (‘b’, 2, 1)
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values (‘c’, 3, 1)
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values (‘a’, 4, 2)
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values (‘b’, 5, 2)
insert into Customer (CustomerName, CustomerAmount, CustomerRegion) values (‘c’, 6, 2)
set nocount off
select
region.RegionName,
sum(CustomerAmount) as Total
from
Customer inner join region on Customer.CustomerRegion = region.RegionId
group by
region.RegionName
select
region.RegionName,
sum(CustomerAmount) as total
from
Customer inner join region on Customer.CustomerRegion = region.RegionId
group by
region.RegionName
having
sum(CustomerAmount) > 7
[/sourcecode]
[sourcecode language=”sql”]
— *****************************************************************************************
— * Tables, pages and extends:
— * – Max rowsize is 8060 bytes
— * – Max key length is 900 bytes
— *
— * – 1 page = 8 Kb = 8192 bytes
— * – 1 extend = 8 pages = 8 * 8 = 64 Kb = 123136 bytes
— *
— * – Table contig has a rowsize of 900 + 7135 = 8060 bytes. So every row is 1 page.
— * When we insert the first row 1 page (on the first extend is allocated).
— * DBCC SHOWCONTIG scanning ‘contig’ table…
— * Table: ‘contig’ (1461580245); index ID: 1, database ID: 8
— * TABLE level scan performed.
— * – Pages Scanned…………………………..: 1
— * – Extents Scanned…………………………: 1
— * ….
— *****************************************************************************************
exec spDeleteObj ‘contig’, 3, 0
go
create table contig
(
id int identity(1,1) not null,
keyval char(900),
long1 char(7135)
)
create clustered index c_id on contig(keyval)
set nocount on
insert into contig values (‘1’, ‘1’)
set nocount off
dbcc showcontig(contig)
[/sourcecode]

Share

SQL Thoughts and notes

SQL Server general:

Physical allocation:
1 page  == 8 Kb  == 8 Kb  == 8192 bytes
1 extend == 8 pages = 64 Kb  == 123136 bytes

Two sort of extends; uniform and mixed. SQL Server allocates space in extends.

A page contains a 96 byte header followed by the data rows and then an offset table which point to the records (in reverse order).

Database files and filegroups
Primary data files   *.mdf
Secondary data files   *.ndf
Log files    *.ldf
Allocation maps:

GAM: Global Allocation Map; keeps track of which uniform extends are in use. Holds 64000 extends (of 64000 * 64Kb =~ 4 Gb)

SGAM: Shared Global Allocation Map: keeps track of which mixed extends  are in use AND with a least 1 unused page. Bit = 1; extend is mixed and contains free page(s); bit = 0; extend is uniform or does not have free pages.

SQL Server kent twee methoden voor het beheren van de data pages:

– Clustered tables
Dit zijn tabellen die een clustered index hebben.

– Heaps
Dit zijn tabellen die GEEN clustered index hebben.

Non-clustered indexen hebben geen invloed op de volgorde van de rijen in een tabel. Een tabel

met een clustered index is op volgorde van de clustering-key.

Relational Engine <=======> OLE DB <============> Storage Engine

I/O Architecture:

Het meeste virtuele geheugen wordt gealloceerd voor een buffer voor lees- en schrijfacties (cache).

Logical I/O:
– Read / Write kan beantwoord worden vanuit de cache.

Physical I/O
– Read moet fysiek van de schijf gedaan worden (niet in cache).

Periodiek worden de gegevens weggeschreven naar de disk.

Transaction Architecture:

Een transactie gaat door verschillende fasen:

– Voordat de transactie start is de database consistent

– BEGIN TRANSACTION
– —— modify data ——-
– Als de database wederom consistent is kan de applicatie een commit geven.
– Bij eventuele fouten gedurende het modificeren van de data kan een rollback worden gegeven.

Autocommit mode:
Iedere transactie wordt, bij succes, direct gecommit, bij een fout ge-rollbacked.

OLTP: Online Transaction Processing (order placing / tracking)
OLAP: Online Analytical Processing (data warehouses)

SQL Server indexen:
Twee typen indexen:
– Clustered
– Non Clustered

Er kan maximaal 1 clustered index per tabel bestaan. De tabel wordt fysiek gesorteerd op clustering index. De clustering-key wordt opgenomen bij alle non-clustered indexen. Neem deze key dus niet al te groot.

Het is verstandig om eerst de clustering index (indien nodig) aan te maken en vervolgens alle non-clustering indexen.

Het is zeer onverstandig om de primary key clustered te maken (Hoewel Enterprise Manager dit voorstelt)!

Als bij een tabel een clustered index een insert plaats vind kan dit een page-split tot gevolg hebben (als de betreffende page vol is). Dit veroorzaakt fragmentatie.

Bij een table-scan is fragmentatie slechts nieuws!

Range retrieval is goed nieuws met een clustered index; records worden dan ook gesorteerd opgeleverd (er is ook geen extra overhead als op de clustering key gesorteerd wordt).

Fill-factor: geeft aan hoe gevuld een tabel moet zijn. Read only tabellen kunnen een fill-factor van 100% hebben. Een fill factor van 50% is bij table-scans zeer slechts nieuws want dan moeten er 2x zoveel io’s worden uitgevoerd.

Covering query: het fysieke record hoeft niet gelezen te worden want de index levert al alle gegevens op.

Voorbeeld:
[sourcecode language=”sql”]
create table test1
(
id int,
  naam char(100),
  tekst char(200)
)

create clustered index idx_id on test1(id)
create index idx_naam on test1(naam)
create index idx_tekst on test1(tekst)

select * from test1 where id = 1
-– Clustered index seek
select * from test1 where naam = ‘x’
-– Index seek met een bookmark lookup voor de rest van het record
select naam from test1 where naam = ‘x’
-– Index seek zonder bookmark lookup want de index is covering
select id, naam from test1 where naam = ‘x’
-– Index seek zonder bookmark lookup want de index is covering aangezien
-– ook id (de clustering key) in de index is opgenomen
[/sourcecode]

Foreign keys
Foreign keys verwijzen naar kolommen in andere tabellen. Nadat de benodigde tabellen zijn aangemaakt kunnen de foreign keys gedefinieerd worden:

[sourcecode language=”sql”]
create table book
(
  id int identity(1,1) not null,
  aut_id int not null,
  title char(255)
)

create clustered index book_title_idx_ci ON book(title)
alter table book add constraint prim_key_book PRIMARY KEY (id)

create table author
(
  id int identity(1,1) not null,
  aut_name char(255)
)

create clustered index author_name_idx_ci ON author(aut_name)
alter table author add constraint prim_key_author PRIMARY KEY (id)

create table BookAuthor
(
  id int identity(1, 1) not null,
  book_id int not null,
  aut_id int not null
)

create unique clustered index bookauthor_idx_ci ON bookauthor(book_id, aut_id)
alter table bookauthor add constraint prim_key_bookauthor primary key nonclustered (id)

insert into author (aut_name) values (‘berend’)
insert into author (aut_name) values (‘marian’)
insert into author (aut_name) values (‘nynke’)

insert into author (aut_name) values (‘marije’)
insert into author (aut_name) values (‘Lazy author’)

insert into book (title) values (‘Berends book’)
insert into book (title) values (‘Marians book’)
insert into book (title) values (‘Nynkes book’)

insert into book (title) values (‘Marijes book’)

insert into book (title) values (‘Marije en Berends book’)

insert into bookauthor (book_id, aut_id) values (1, 1)
insert into bookauthor (book_id, aut_id) values (2, 2)
insert into bookauthor (book_id, aut_id) values (3, 3)

insert into bookauthor (book_id, aut_id) values (4, 4)
insert into bookauthor (book_id, aut_id) values (5, 1)
insert into bookauthor (book_id, aut_id) values (5, 4)

-– Relations
alter table BookAuthor add constraint aut_fk foreign key(aut_id) references author(id)
alter table BookAuthor add constraint book_fk foreign key(book_id) references book(id)
[/sourcecode]

INNER, OUTER, FULL JOIN

De inner-join is eigenlijk de equi join (=). Een voorbeeld van de inner join is (met
gebruik making van de bovenstaande tabel definities):

[sourcecode language=”sql”]
SELECT
  book.title, author.aut_name
FROM
  bookauthor
inner join author on author.id = bookauthor.aut_id
inner join book on book.id = bookauthor.book_id
[/sourcecode]

Met een left join worden in ieder geval alle rijen uit de linker tabel opgeleverd en, indien mogelijk, gekoppeld aan een entry (op basis van de join condition) uit de rechter tabel.

[sourcecode language=”sql”]
SELECT
  book.title, author.aut_name, bookauthor.id</span></div>
FROM
  author
left outer join bookauthor on author.id = bookauthor.aut_id
left outer join book on book.id = bookauthor.book_id
[/sourcecode]

Dit levert ALLE rijen uit de author tabel. De rijen uit de author tabel worden op basis van de join conditie samengevoegd. Voor de “Lazy author” lukt dit niet en hiervoor wordt een NULL in de resultset geplaatst:
title                          aut_name                       id
—————————— —————————— ———–
Berends book                   berend                         1
Marije en Berends book         berend                         5
Marians book                   marian                         2
Nynkes book                    nynke                          3
Marijes book                   marije                         4
Marije en Berends book         marije                         6
NULL                           Lazy author                    NULL

GROUP BY, HAVING
[sourcecode language=”sql”]
create table titles
(
id int  identity(1, 1) not null,
  type int,
  price decimal,
  title char(30)
)

insert into titles (type, price, title) values (1, 1, ’1′)
insert into titles (type, price, title) values (1, 2, ’2′)
insert into titles (type, price, title) values (2, 2, ’3′)
insert into titles (type, price, title) values (3, 2, ’4′)
insert into titles (type, price, title) values (3, 3, ’4′)

select
type, avg(price) as avg_price from titles
group by
type
[/sourcecode]

type        avg_price
———– —————————————-
1           1.500000
2           2.000000
3           2.500000

(3 row(s) affected)

Er wordt gegroepeerd op type vandaar dat er drie records worden opgeleverd (er zijn
drie verschillende types). Per groep wordt de gemiddelde (avg) prijs bepaald.

Met behulp van de HAVING clause kan weer een selectie losgelaten worden op de groep; de HAVING clause is eigenlijk de WHERE van de GROUP BY.
[sourcecode language=”sql”]
select
type, avg(price) as avg_price from titles
group by
type
having
sum(price) = 5
[/sourcecode]
type        avg_price
———– —————————————
3           2.500000

(1 row(s) affected)

Share

SQL Server Examples Part 3

[sourcecode language="sql"]
-- ****************************************************************************
-- * DEADLOCK situations: *
-- * *
-- * FIRST CONNECTION SECOND CONNECTION *
-- * begin tran begin tran *
-- * delete from a delete from b *
-- * select * from b select * from a *
-- * - FIRST connection waits for resource b and holds lock on a *
-- * - SECOND connection waits for resource a and holds b *
-- * WE HAVE A DEADLOCK SITUATION HERE *
-- * After a certain amount of time one of the transaction is chosen to be *
-- * the victim of the deadlock situation. This transaction is aborted and *
-- * rolledback. *
-- ****************************************************************************
exec spDeleteObj 'a', 3, 0
create table a
(
id int identity(1,1) not null,
col1 char(10)
)
insert into a values ('1')
exec spDeleteObj 'b', 3, 0
create table b
(
id int identity(1,1) not null,
col1 char(10)
)
insert into b values ('1')
[/sourcecode]

[sourcecode language="sql"]
-- ****************************************************************************
-- * LOCKING WITH CURSORS: *
-- * *
-- * The STANDARD CURSORS control concurrent access through several options. *
-- * these controls are PESSIMISTIC and OPTMISTIC. *
-- * PESSIMISTIC concurrency: *
-- * Two types of pessimistic concurrency control: *
-- * - Readonly which does not permit data updates *
-- * - Scroll locked in which rows are locked when they are fetched inside a *
-- * user-initiated transaction and no other user can gain access to these *
-- * rows until the locks are released. *
-- * OPTIMISTIC concurrency: *
-- * Does not place locks on the database, allowing multiple users to access *
-- * the same rows, and must resolve any update conflicts that may occur as *
-- * a result of simultaneous updates. *
-- * *
-- * The UPDATEABLE CURSORS support data modification statements that update *
-- * rows through position updates. When positioned on a row in an *
-- * updateable cursor, update or deleteoperations that target the base table *
-- * rows used to build the current row in the cursor can be performed. The *
-- * positioned updates are performed on the same connection that opened the *
-- * cursor. This allows the data modification to share the same transaction *
-- * space as the cursor, and prevents the updates from being blocked by *
-- * locks held by the cursor. Positioned updates in a cursor can be *
-- * performed through Transact-SQL by using the WHERE CURRENT OF clause on *
-- * an UPDATE statement and can be placed within explicit transactions. *
-- ****************************************************************************
[/sourcecode]

Share

SQL Server Examples Part 1

Share