Index Fundamentals Part 5

Today we have a look at index maintenance. Indexes get fragmented over the time by inserting, deleting and updating indexed rows.
To handle this fragmentation indexes have to be reorganized/rebuilded regularly.
What is the difference between rebuild and reorganize?

Rebuild:
- Rebuild creates the index completly new (single threaded in Standard Edition multithreaded in Enterpriese Edition)
- Rebuild can be done online in Enterprise Edition (not on tables with large object types) but needs a Schema lock at the start that can be a problem on high frequented tables
- In Rebuild you can set a new fillfactor
- Rebuild is a transaction if you cancel it, there is a rollback and it's done offline

Reorganize:
- Reorganize is allways online
- Reorganize can be canceled without a rollback
- Reorganize doesn't need a schema lock there is no problem on high frequented tables

How can I check the fragmentation of an Index?
For checking the percentage of fragmentation use the following script from microsoft:
Microsoft Index Checkscript

Rebuild or Reorganize?
Microsoft Best practice says reorganize for fragmentation <= 30% and rebuild for fragmentation > 30% but in 24/7 environment there is the problem that a rebuild on the Standard Edition is offline and the index is not available. There can be great performance impacts for that issue. So if you have a environment with a maintenance window you can surely rebuild your indexes. For big tables and often used indexes you should use reoarganize.

In the next post I will show you a maintenance script for indexes.

Adding leading zeros to int column

Hi there,

some of my imports needs by design for the importcounter not integers like 1, 2, 3, 4, 5,...
they expect varchar with leading zeros. Today I will show you how to add leading zeros
to your integers by a simple combination of cast() and right().

Let me explain the function:
RIGHT('0000000000000000000000'+cast([Itemno] as varchar (255)), (select cast(LEN(max(itemno)) as int) from [article])) as ImportID

First of all
'0000000000000000000000'+cast([Itemno] as varchar (255)
this adds leading zeros before the int field which we cast to varchar(255)
after that we use the right function to take the first X chars from the right side of string.
RIGHT('0000000000000000000000'+cast([Itemno] as varchar (255)), X)
but my imports need as input the a variable lenght addicted by the lenght of maximum int value,
so I replaced the X with select cast(LEN(max(itemno)) as int) from [article] this query returns for the demodata 5 because the max number used is 50000 and len of 50000 = 5.
This way i got my importers working.

Copyable Version:
-- Table with int values
SELECT    [Itemno] ImportID,
        [Itemname],
        [Price]
  FROM [article]

-- Table with leading zeros as varchar
SELECT    RIGHT('0000000000000000000000'+cast([Itemno] as varchar (255)),
        (select cast(LEN(max(itemno)) as int) from [article])) as ImportID,
        [Itemname],
        [Price]
  FROM [article]

Index Fundamentals Part 4

Today we have the focus on nonclustered indexes.
Nonclustered indexes doesn't modify the logical order of tabledata only clustered index does this.
They are working with pointers to the data in a clustered index or the table (heap).

For example we got a table with:
ID;Text;Timestamp
1;Test;01-01-2000 8:50:10:001
2;Test;02-01-2000 8:50:10:001
3;Test;03-01-2000 8:50:10:001
4;Test;04-01-2000 8:50:10:001
5;Test;05-01-2000 8:50:10:001
6;Test;06-01-2000 8:50:10:001
7;Test;07-01-2000 8:50:10:001
8;Test;08-01-2000 8:50:10:001
9;Test;09-01-2000 8:50:10:001
10;Test;10-01-2000 8:50:10:001
11;Test;11-01-2000 8:50:10:001
12;Test;12-01-2000 8:50:10:001
13;Test;13-01-2000 8:50:10:001
14;Test;14-01-2000 8:50:10:001
15;Test;15-01-2000 8:50:10:001
16;Test;16-01-2000 8:50:10:001
17;Test;17-01-2000 8:50:10:001
18;Test;18-01-2000 8:50:10:001
19;Test;19-01-2000 8:50:10:001
20;Test;20-01-2000 8:50:10:001

No we create a nonclustered index on Timestamp.
The pages look like
ROOT PAGE
Timestamp;Pointer to Page
01-01-2000 8:50:10:001;1
06-01-2000 8:50:10:001;2
11-01-2000 8:50:10:001;3
16-01-2000 8:50:10:001;4

PAGE 1
Timestamp;Pointer
01-01-2000 8:50:10:001;1
02-01-2000 8:50:10:001;2
03-01-2000 8:50:10:001;3
04-01-2000 8:50:10:001;4
05-01-2000 8:50:10:001;5

PAGE 2
Timestamp;Pointer
06-01-2000 8:50:10:001;6
07-01-2000 8:50:10:001;7
08-01-2000 8:50:10:001;8
09-01-2000 8:50:10:001;9
10-01-2000 8:50:10:001;10

PAGE 3
Timestamp;Pointer
11-01-2000 8:50:10:001;11
12-01-2000 8:50:10:001;12
13-01-2000 8:50:10:001;13
14-01-2000 8:50:10:001;14
15-01-2000 8:50:10:001;15

PAGE 4
Timestamp;Pointer
16-01-2000 8:50:10:001;16
17-01-2000 8:50:10:001;17
18-01-2000 8:50:10:001;18
19-01-2000 8:50:10:001;19
20-01-2000 8:50:10:001;20

After that we execute
select Text, Timestamp from table where Timestamp = '17-01-2000 8:50:10:001'
SQL Server uses the clustered index, rootpage points to Intermediate Page 4 and Intermediate Page 4 points to Pointer 16 in clustered index or heap and the result retrieves.

As you can see in my example the ID for the clustered index is int. Let us imagine it was a unique identifier
ID 1 int and a unique identifier ID 1C3C7DEA-D440-45C4-923B-6FF988957A41 you can see the difference the storage needed for a unique identifier is much more as for int.
So if you have a unique identifier in clustered index as index column all your nonclustered indexes on the same table use it as pointer and needs lot more space in storage.
To keep your indexes smart and fast you have to use a smart column for clustered index column.

In the next part we have a look at index maintenance.

Script to check free Space in Files

Hi there,

I want to share a smart little script for checking free space in the files of a database.
See the script below:

Copyable Version:
SELECT
    a.FILEID,
    CONVERT(decimal(12,2),ROUND(a.size/128.000,2)) as [Filesize_MB] ,
    CONVERT(decimal(12,2),ROUND(fileproperty(a.name,'SpaceUsed')/128.000,2)) as [Used_Space_MB],
    CONVERT(decimal(12,2),ROUND((a.size-fileproperty(a.name,'SpaceUsed'))/128.000,2)) as [Free_Space_MB],
    a.name as [Database_Name],
    a.FILENAME as [File_Name]

FROM
    dbo.sysfiles a

Check Time till the End of Restore

Hi there,

today i want to share a script with you. You know the problem there is a big database restore and you want to know the estatimated finishtime.
With this script you can see the estatimated finishtime:

Copyable Version:
SELECT    r.session_id,
        r.command,
        CONVERT(NUMERIC(6,2),r.percent_complete) AS [Percent_from_Restore_Complete],
        CONVERT(VARCHAR(20),DATEADD(ms,r.estimated_completion_time,GetDate()),20) AS [Estatimated_Completion_Time],
        CONVERT(NUMERIC(10,2),r.total_elapsed_time/1000.0/60.0) AS [Elapsed_Min],
        CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0) AS [Estatimated_Min],
        CONVERT(NUMERIC(10,2),r.estimated_completion_time/1000.0/60.0/60.0) AS [Estatimated_Hours],
        CONVERT(VARCHAR(1000),
        (    SELECT SUBSTRING(text,r.statement_start_offset/2, CASE WHEN r.statement_end_offset = -1
            THEN 1000
            ELSE (r.statement_end_offset-r.statement_start_offset)/2 END)
FROM sys.dm_exec_sql_text(sql_handle)))
FROM sys.dm_exec_requests r WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')

Index Fundamentals Part 3

Today we have a look at the attributes for clustered indexes.

What is a good choice for a clustered index?

The SQL Server decides nearly always the primary key for a clustered index why does the server do that?

For choosing the perfect column for a clustered index the attribute have to be

Unique - If a column gets duplicated entries the Server has to add a unique identifier to the 

                duplicated entries this unique identifier costs additional space in the 8 K pages and

                expands the whole Index

                For example (in the example we think there is no metaspace on each page for simple 

                calculation) 

                we got a table with 2048 entrys from datatype int (4 byte per row) they need 

                1 x 8 K page in the index (4*2048 = 8196 = 8 K).

                So our Table needs 1 page to store now we suggest the whole column is not unique there is

                in every row the number 5 in the int field. Now the Server has to add to every int a unique

                identifier (16 bytes) for storing so now each row needs 20 bytes (4 int + 16 unique 

                identifier) now we need 5 pages (20 * 2048 / 8196 = 4,99)

                So in this example the index growths by 5 because of duplicated entries.

Static -    The indexed column has to be static because the index is build up ascending or 

                descending and a update on the field destroys the perfect sorting.

                For example our table looks like:

                ID;Text

                1;Text1

                2;Text2

                3;Text3

                4;Text4

                5;Text5

                Now we execute "update table set ID = 888 where ID = 2"

                On this update the Server has to restore the old ID 2 row to a new page between 887 and 

                889 and there is a hole by ID = 2

                At this point we got a Fragmentation in the index and in worst case a page split on the 

                page with 887.

Narrow - The datatype for the key of a clustered index has to be smart because it must be stored as 

                 smart as possible. The Key is stored in every nonclustered index too as a pointer in the 

                 clustered key. So it is much better to have a int (4 bytes) column instead of a unique 

                 identifier (16 bytes).

Increase - If the column for the clustered index is increasing with every insert the server has to add 

                 new entries every time to the last page. This way there is no fragmentation in the index 

                 and it is as smart as possible.

If your column fits all this criteria it is the best choice for the clustered index. Unique Identifiers are not good for a clustered index because they need much storage, are not increasing and not narrow.

A int column that is increasing by a fix incremental is a better choice.

In my next post we have a look at nonclustered indexes.

Stay tuned and have a nice day.

Index Fundamentals Part 2

Today I want to show you the difference between a clustered and a nonclustered index.

There can only be one clustered index on a table but 999 nonclustered indexes. 

A clustered index reorganizes the whole table according to the index. See in Example:

Table before clustered Index:

ID;Country

1;Brasil

2;Germany

3;America

Now we create a clustered index on Country (asc) the new logical table order is:

ID;Country

3;America

1;Brasil

2;Germany

Lets see the same Index on a nonclustered index:

Table before nonclustered Index:

ID;Country

1;Brasil

2;Germany

3;America

Table after nonclustered Index:

ID;Country

1;Brasil

2;Germany

3;America

As you can see there is no difference and this is the point between nonclustered and clustered indexes, 

the clustered index reorganizes the table in the filesystem. A nonclustered Index doesn’t do that.

In the next Topic we have a look at what attributes are the best for my index.

Index Fundamentals Part 1

Today I write a short introduction about Indexes in Microsoft SQL Server.

What exactly is an Index? An Index is a construct like a tree that is upside down to find datarows by indexed columns. There is a root level, a intermediate level and a leaf level or a heap if there is no clustered index. Let me explain with a worldwide phonebook.
The Root Page includes a pointer to the phonebook for every country the phonebook for each country is the intermediate Page and gets a pointer for every name into the leaf page (on a clustered index) or the heap.

In example for the query:

Select name, street phone from myphonebook where country = 'America' and Name = 'Adele, John'

The root page is called for Country = America and says goto leaf 1, on leaf 1 the page says
row with name 'Adele, John' is at pointer 4001 and leafpage or heap retrieves the row with
the query attributes.

On my next post I show the difference between nonclustered and clustered indexes.

Temporary Tables vs. Common Table Expression (CTE)

Today I want to show you how to use CTE and explain the difference
between CTE and a temporary tables.

Temporary Table
local temporary table
Local temporary tables (starting with a single #) are created in the tempdb and are only available in the current session. After closing the session the are dropped automatically.
global temporary table
Global temporary table (staring with two #) are created in the tempdb and are available in all sessions. They are automatically dropped after all user connections are closed.

CTE
CTE is not a table it is a temporary result set with scope on the current query, so it is not limited by the session it is limited by the current select.

Difference to a temporary table
CTE is a temporary result created in memory not in tempdb.
CTE cannot have an index.
CTE is only for the current statement.
CTE is faster (no table Creation)



Lets have a look at the execution plans
CTE (Subtreecost 0,313466)

local temp (Subtreecost 11,7748 for creating temptable + 0,313466 for select from temptable = 12,088266)



global temp (Subtreecost 11,7748 for creating temptable + 0,313466 for select from temptable = 12,088266)



As you can see CTE is not as expensive as temp tables but it can't be indexed and is pointed to current statement.
You should use CTE for inline subquerys or aggregating. For complex procedures or scripts you have to use temptables.

Copyable Version:
-- CTE
;With CTEResult(Itemno, itemname, Price)
AS
(
SELECT Itemno, itemname, Price from article a
)
SELECT * FROM CTEResult cr
WHERE cr.Itemno in (1,2,50)
ORDER BY cr.itemname
-- localtemptable
SELECT Itemno, itemname, Price into #localtemptable from article a
select * from #localtemptable where itemno in (1,2,50) order by Itemname
-- globaltemptable
SELECT Itemno, itemname, Price into ##globaltemptable from article a
select * from ##globaltemptable where itemno in (1,2,50) order by Itemname


drop table #localtemptable
drop table ##globaltemptable

Logon Trigger to audit last Login

Now I will show you how to create a Logon Trigger on a Server
to safe the last login on a server for each user.

This script is useful searching no longer used Logins on a Server. 

Copyable Version:
USE [Login_Audit]

CREATE TABLE [dbo].[Login_Audit](
    [SID] [varbinary](85) NOT NULL,
    [Login] [sysname] NOT NULL,
    [User] [nvarchar](128) NULL,
    [Last_Login] [datetime] NULL
) ON [PRIMARY]

GRANT SELECT ON [dbo].[Login_Audit] TO [public]
GRANT UPDATE ON [dbo].[Login_Audit] TO [public]

INSERT INTO [dbo].[Login_Audit]
select [sid], name, NULL, NULL from sys.server_principals

CREATE TRIGGER [LogonTimeStamp]
ON ALL SERVER FOR LOGON
AS
BEGIN
update [Login_Audit].[dbo].[Login_Audit]
set        Last_Login = GETDATE(), [user] = user
where SID = suser_sid(SYSTEM_USER)
END

ENABLE TRIGGER [LogonTimeStamp] ON ALL SERVER


select *
from [Login_Audit].dbo.[Login_Audit]
where [LOGIN] = 'NT AUTHORITY\SYSTEM' -- use a own user here

-- Insert after Creating new Login
INSERT INTO [dbo].[Login_Audit]
select [sid], name, NULL, NULL
from sys.server_principals
where [sid] not in (select sid from [dbo].[Login_Audit])

Check the Rowsize of each Row

Today I show you a small script that outputs the rowsize for every row in a specified table.


This way you can check your rowsize and find extraordinary rowsizes in your tables.

Copyable Script:
/***** declares the variable for Tablename *****/
declare @t varchar(50)
/***** declares the variable for output ID *****/
declare @ID varchar(10)
/***** declares the variable for the command *****/
declare @sqlcmd varchar(max)

/***** sets variable for the table *****/
set @t = 'dbo.article'
/***** sets the variable for ID column in output *****/
set @ID = 'itemno'
/***** sets the variable for cmd *****/
set @sqlcmd =    'select ' + @ID +' , (0'
                select @sqlcmd = @sqlcmd + ' +case when isnull(len(' + name +
                '), 1) > isnull(datalength(' + name + '), 1) then isnull(len
                (' + name + '), 1) else isnull(datalength(' + name + '), 1) end '
                from syscolumns where id = object_id(@t)
set @sqlcmd =    @sqlcmd + ') as rowsize from ' + @t + ' (nolock) order by rowsize desc'
/**** Prints the execute cmd *****/
select @sqlcmd as Command
/**** Executes the cmd *****/
exec (@sqlcmd)

Cut off leading zeros on numbers in varchar field

Today we want to get rid of leading zeros in numbers on a varchar column.
To customize it you need to replace the ~ with a char that is not used in your column.
See my code below:



Copyable Version:
CREATE Table [dbo].[Leading_Zeros](
    [INT] [bigint] NULL,
    [Varchar] [varchar](250) NULL
    ) ON [PRIMARY]
   
GO

INSERT INTO [Leading_Zeros]
Values    (1, '001'),
        (2, 'Zero0000Zero'),
        (3, '156000748465'),
        (4, '000555000555000'),
        (5, 'TestString'),
        (6, 'Test00000String')
       
select    *,
        REPLACE(REPLACE(LTRIM(REPLACE(REPLACE([Varchar], ' ', '~'), '0', ' ')), ' ', '0'), '~', ' ') Leading_Zeros
from [Leading_Zeros]

Creating XML Data from Table

Today I need to create a xml field from a table. To show you how it works I just created a small 3 row example table.
Lets have a look at the Script:
First of all I created a table with 3 columns (ItemNo, Item_Name, Item_Price) and inserted 3 example rows.
After that I created the XML Table with columns (ID, Article)
A small while loop inserted the XML into the table.
In the End you will see the 3 items as XML and one Testitem as XML Select in Columns.

Copyable Version:
-- Create Source Table
CREATE TABLE [dbo].[article](
        [ItemNo] [bigint] NOT NULL,
        [Item_Name] [nvarchar](250) NOT NULL,
        [Item_Price] [money] NOT NULL
) ON [PRIMARY]
go
INSERT INTO [article]
Select 1, 'Testitem 1', 2.00
UNION ALL
Select 2, 'Testitem 2', 3.99
UNION ALL
Select 3, 'Testitem 3', 5.99
-- XML Table
CREATE TABLE [dbo].[Article_XML](
        [ID] [INT] PRIMARY KEY,
        Article XML
)  ON [PRIMARY]
GO
-- Insert into XML
DECLARE @counter INT = 1
DECLARE @row VARCHAR(MAX)
WHILE @counter <= 3
    BEGIN
        SET @row = (
                SELECT    ItemNo,
                        Item_Name,
                        Item_Price
                FROM article
                WHERE ItemNo = @counter
                FOR XML PATH ('Article')
                )
        INSERT Article_XML Values (@counter, @row)
        SET @counter += 1
    END
GO
-- Select all Fields
Select * from Article_XML
GO
-- Select XML Path
SELECT    Article.value('(/Article/ItemNo)[1]','varchar(50)') AS Artno,
        Article.value('(/Article/Item_Name)[1]','varchar(50)') AS item_name,
        Article.value('(/Article/Item_Price)[1]','money') AS price
From Article_XML
Where Article.exist('/Article[ItemNo=1]') = 1

The Hashbyte Function

Today I got a little problem with a developer that wants to use the Hashbyte function. After a little discussion and reading we saw that the output of the function ist varbinary so there is no way to put it into a varchar field without different results. See the Example:


So if you want to use the Hashbyte function you have to use the right data type.

Copyable Version:
DECLARE @HashThis nvarchar(40)
DECLARE @HashValue nvarchar(42)
DECLARE @HashValue2 varbinary(42)

set @HashThis = 'myteststring'
SELECT HASHBYTES('SHA1', @HashThis) as 'Auto data type'

set @HashValue=HASHBYTES('SHA1', @HashThis)
SELECT @HashValue as 'Varchar data type'

set @HashValue2=HASHBYTES('SHA1', @HashThis)
SELECT @HashValue2 as 'Varchar data type'

Welcome to my new Blog

Hello everyone,

today I want to welcome you on my new Blog about the Microsoft SQL Server and T-SQL Programming.

In the next time I will prepare a few articles about different parts of the SQL Server and I try to publish a short Entry about my daily business and problems every day.