Microsoft SQL Server පටන් ගමු!!

මම කලින් දාපු පෝස්ට් වල තියන SQL ස්ටේට්මන්ට් රන් කරලා පුරුදු වෙලා බලන්න විදිහක් මට පෝස්ට් කරන්න බැරි වුනා. ඇත්තටම සරල SQL ස්ටේට්මන්ට්ස් හැම එකක්ම වගේ ඕනම ඩේටාබේස් මැනෙජ්මන්ට් සිස්ටම් එකක රන් කරලා බලන්න පුලුවන් (Oracle DB, SQL Server, Access, MySQL). එත් මම පාවිච්චි කරන්නේ SQL Server ඉතින් මම හිතුවා SQL Server ඉගෙන ගන්න කැමති අයට ඒකට අවශ්‍ය දේවල් ඩව්න්ලෝඩ් කරගන්න තැන් පෝස්ට් කරන්න.

http://www.microsoft.com/en-us/download/details.aspx?id=29062

ඉහත දීලා තියන ලින්ක් එකෙන් ගිහින් ENU\x64\SQLEXPRWT_x64_ENU.exe හරි ENU\x86\SQLEXPRWT_x86_ENU.exe ඩව්න්ලෝඩ් කරගෙන ඉන්ස්ටෝල් කරගන්න. ඇත්තටම ඉන්ස්ටෝල් කරන එක එච්චර අමාරු නෑ. ඒවගේමයි මේ එඩිෂන් එක ඉගෙන ගන්න අයටම වෙන් කරපු එකක් ඒක හන්දා ලයිසන් ප්‍රශ්නයකුත් නෑ. තමුන්ගේ මෙහෙයුම් පද්ධතියට ගැලපෙන විදිහට 64බිට් හරි 32 බිට් හරි ඩව්න්ලෝඩ් කරගෙන ඉන්ස්ටෝල් කරගන්න. මේකේ ඇත්තටම වැදගත්ම දේ SQL Server Management Studio Express එක SSMS එකෙන් ලේසියෙන්ම අවශ්‍ය Queries ලියන්න පුලුවන්.

ඊට පස්සේ SQL Server ඉගෙන ගන්නවානම් ගොඩක් වැදගත් වන දෙයක් තමා සාම්පල් ඩේටාබේස් එක. ඇත්තටම AdventureWorks කියලා ගොඩක් ප්‍රසිද්ද සාම්පල් ඩේටාබේස් එකක් තියනවා. ඒක හැමෝම පාවිච්චි කරනවා. ඉතින් ඒකත් ඩව්න්ලෝඩ් කරගෙන SQL Server එකට ඇටැජ් කරගත්ත නම් SQL Server ඉගෙන ගන්න ගොඩක් ලේසි.

http://msftdbprodsamples.codeplex.com/releases/view/55330

ගිහින් පුලුවන් ඒ සාම්පල් ඩේටාබෙස් එක ඩව්න්ලෝඩ් කරගන්න. AdventureWorks2012 Data File කියන එක ඩව්න්ලෝඩ් කරගත්තා නම් ලේසි. නැත්නම් AdventureWorks2012-Full Database Backup ඩව්න්ලෝඩ් කරගෙන රිස්ටෝර් කරන්නත් පුලුවන්

Advertisements

Programming Microsoft SQL server 2012

catBefore few weeks I got a book from Oreilly Blogger Review program to review a book. I took enough time to read this book. This book covers most of the new trends and features in SQL Server 2012 with good explanation.

I was addicted to first chapter to get to know about the SQL Server Data Tools. Because previously I was studying development things like C# and Visual Studio because of that I was not be able to find out about SQL Server Development Tools. With SQL Server 2012 SQL Server Data Tools were introduced. In first chapter completely describes about SSDT from the point of Developers and Dev DBA’s.

All over the book I didn’t feel like reading a tech book. It is well organized and I felt that I read a story book. But this book is not good for absolute beginners who is looking for SQL Server Development. And this book also balanced one, It doesn’t cover only XML or CLR. Its covering most of the topics with useful things. This is like a essence for database developers who uses SQL Server 2012 or who is planning to use SQL Server 2012.

Each chapter had lot of things to learn. Actually this is a good book. Read it if you are a dev DBA in SQL Server 2012!!! Programming Microsoft SQL Server 2012 was a pretty interesting book for me.

How to compare data in two tables?

In the beginning of this week I got a task from Susantha to modify a stored procedure. After the modification I had to test to compare the result sets before modification and after modification. That procedure only returns a data table. So I had so many test data on another table. Then I wrote a script to go through the script one by one. Actually I wanted to check whether those results are identical. I mean not the table structure. I wanted to perform a data comparison.

DECLARE @COUNTN INT;
DECLARE @COUNTO INT;
DECLARE @COUNTU INT;

CREATE TABLE #TEMPN
    (
        --Columns Here
    )

CREATE TABLE #TEMPO
    (
        --Columns Here
    )

CREATE TABLE #TEMPU
    (
        --Columns Here
    )

INSERT INTO #TEMPN 
    --Select From new table         

INSERT INTO #TEMPO
    --Select From old table     

SELECT  @COUNTU = COUNT(1)
FROM    #TEMPU
SELECT  @COUNTN = COUNT(1)
FROM    #TEMPN
SELECT  @COUNTO = COUNT(1)
FROM    #TEMPO

IF @COUNTN <> @COUNTO 
    BEGIN
        SELECT  *
        FROM    #TEMPN
        SELECT  *
        FROM    #TEMPO
        PRINT 'ERROR' 
    END

IF @COUNTN <> @COUNTU 
    BEGIN
        SELECT  *
        FROM    #TEMPN
        SELECT  *
        FROM    #TEMPO
        PRINT 'ERROR' 
    END

IF @COUNTU <> @COUNTO 
    BEGIN
        SELECT  *
        FROM    #TEMPN
        SELECT  *
        FROM    #TEMPO
        PRINT 'ERROR' 
    END

DROP TABLE #TEMPN
DROP TABLE #TEMPO
DROP TABLE #TEMPU    

This will check whether your data in two tables are identical or not. if there’s any mismatch it will print error and show the both result sets. And there’s another issue on this script if one table returns NULL and the other table returns empty table it will take it as matched. Then I corrected it again.

DECLARE @COUNTN INT;
DECLARE @COUNTO INT;
DECLARE @COUNTU INT;

CREATE TABLE #TEMPN
    (
        --Columns Here
    )

CREATE TABLE #TEMPO
    (
        --Columns Here
    )

CREATE TABLE #TEMPU
    (
        --Columns Here
    )

INSERT INTO #TEMPN 
    --Select From new table         

INSERT INTO #TEMPO
    --Select From old table     

SELECT  @COUNTU = COUNT(1)
FROM    #TEMPU
SELECT  @COUNTN = COUNT(1)
FROM    #TEMPN
SELECT  @COUNTO = COUNT(1)
FROM    #TEMPO

IF @COUNTN <> @COUNTO 
    BEGIN
        SELECT  *
        FROM    #TEMPN
        SELECT  *
        FROM    #TEMPO
        PRINT 'ERROR' 
    END

IF @COUNTN <> @COUNTU 
    BEGIN
        SELECT  *
        FROM    #TEMPN
        SELECT  *
        FROM    #TEMPO
        PRINT 'ERROR' 
    END

IF @COUNTU <> @COUNTO 
    BEGIN
        SELECT  *
        FROM    #TEMPN
        SELECT  *
        FROM    #TEMPO
        PRINT 'ERROR' 
    END

--New Code Block here

IF EXISTS(#TEMPO) BEGIN IF NOT EXISTS(#TEMPN) BEGIN SELECT * FROM #TEMPN SELECT * FROM #TEMPO PRINT 'ERROR' END END IF EXISTS(#TEMPN) BEGIN IF NOT EXISTS(#TEMPO) BEGIN SELECT * FROM #TEMPN SELECT * FROM #TEMPO PRINT 'ERROR' END END

DROP TABLE #TEMPN
DROP TABLE #TEMPO
DROP TABLE #TEMPU

Easy way to analyse results

I have seen in many video tutorials they have different tabs for Editor, Results, Messages and Execution Plans. And they are streched over complete screen. Most of the times need to resize the result area to see our results. Actually its annoying me. Then I noticed in on of Paul Randal’s video he uses Separate tabs for Results editor and messages in SQL Server Management Studio (SSMS). Then I wanted to find how to get those tabs to my SSMS (Often I call it SMSS don’t know why Winking smile).

1. Open you SQL Server Management Studio (SSMS)

2. Go to Tools –> Option

3. Check “Display results in a separate tab” and “Switch to results tab after the query executes” both.

image

4. Click ok and enjoy Results like this.

image

If you don’t like to use it always you can use this option to single script by click on Query option on tool bar and follow 3rd step.

image

SQL Server is not releasing memory?

I got few questions to answer quickly in last week just like a test. Then I suddenly answered then after that I tried to find correct answers to them. I found answers for most of them. And one of them was there as it was.

A Windows server has 32 GB of memory and dedicated for SQL Server database. Every time you start the server the memory utilization of SQL Server gradually increases, until it takes almost all the memory and remains there for days even when there is no database activity. What will you do ?

Actually I answered

We can set maximum memory limit. Some how gradually SQL Server uses available memory.

I knew that SQL Server uses and usually doesn’t release memory, although he don’t need to use it. But I didn’t knew why is that. Then I got to know that Not releasing memory is a feature of SQL Server. On servers we don’t run our day to day applications and most of the times we have allocated separate box for SQL Server. Then nobody will use that memory. If nobody uses that why should we release and allocate again and again. When we are discussing about this problem our production server had reached its maximum memory too Winking smile. And this is not our production servers Smile.

sql server memory leak

Preethi told that this happens often. People who don’t know about SQL Server behavior when saw “task manager” like above they are suggesting to double the memory of server. Then until it takes few days they are happy. Again they are having same issue. Sad smile. Actually it’s not a problem with SQL Server.

If you really want to release that memory you can easily restart SQL Server. Then it begins everything from the beginning. Other wise you can execute those commands.

But even in the TechNet they haven’t mentioned that it is recommended running those commands against production servers. Somehow as I think theirs no need to flush memory manually. Because if you are using SQL Server on your laptop, every time that you restart your laptop it will flush again and again. In production servers we don’t need to flush it manually. Let SQL Server to use that memory as he want.

If you are using one box to install SQL Server and Application both you can set maximum memory limit to SQL Server. Then it will not exceed that limit. But I found this thread from SQLServerCentral.

http://www.sqlservercentral.com/Forums/Topic982342-1550-1.aspx

In that thread he is saying that SQL Server exceeds that maximum memory limit. But technically it should not happen. And I haven’t tested it also. But there may be a reason for that too. If you are setting maximum memory limit you should restart SQL Server. If you don’t restart it, it uses previous memory limit. Somehow below link says another thing. I have to learn about it further. Smile

Yup. Perfectly normal.
Max server memory is the max size of the buffer pool, the memory area that contains the data cache, plan cache and a whole bunch of other caches. SQL also uses memory outside the buffer pool for things like backup buffers, thread stack, linked server drivers, CLR and a few other things. This is outside of the buffer pool, so it’s not part of ‘max server memory’
On 32-bit SQL, that’s referred to as MemToLeave (memory to leave unallocated when assigning the buffer pool). On 64 bit that term has no meaning.

http://www.sqlservercentral.com/Forums/Topic1197388-1550-1.aspx

Picture: http://piglings.blogspot.com/2009/10/sql-server-2008-memory-leak.html

Be careful if you are using CTE!

Previously I wrote a post about CTE (Common Table Expressions). And Common Table Expressions are valid for a single statement only. But single statement can use many CTEs. And statement also should be within same batch.

USE AdventureWorks2012
GO

--Defining CTE
WITH ctePerson 
AS
(
    SELECT * 
    FROM Person.Person
)
--First Statement will run 
SELECT FirstName FROM ctePerson 
--Second statement will occur an error
SELECT FirstName FROM ctePerson

And in my previous post I have mentioned about many CTEs for one statement. And there is another important thing that is be careful when you are naming CTEs. Take a look on below example.

USE tempdb
GO

--Creating two tables
CREATE TABLE cteSampletbl1
(
id int,
name varchar(50)
)
GO

CREATE TABLE cteSampletbl2
(
id int,
name varchar(50)
)
GO

--Populating tables
INSERT INTO cteSampletbl1 VALUES
(1,'Satheeq'),
(2,'Preethi'),
(3,'Dinesh'),
(4,'Angelo')
GO

INSERT INTO cteSampletbl2 VALUES
(1,'Shamil'),
(2,'Hasitha'),
(3,'Abhinandana'),
(4,'Susantha')
GO

/*
    This CTE is defined with existing table name without any error
*/
WITH cteSampletbl2
AS(
    SELECT * FROM cteSampletbl1
)
--This statement uses CTE
SELECT * FROM cteSampletbl2
--This statement uses existing table
SELECT * FROM cteSampletbl2

--Dropping tables
DROP TABLE cteSampletbl1
DROP TABLE cteSampletbl2
GO

Below example it lets us to create a CTE with a name of existing table without any warnings or errors. And it gives priority to CTE in first statement. Then it uses existing table in second statement. So it would be a best practice to use a separate prefix for CTEs. Then take a look into next sample which is taken from Dinesh’s presentation for SQL Server Universe Group.

USE tempdb
GO

--Creating two tables
CREATE TABLE cteSampletbl1
(
id int,
name varchar(50)
)
GO

--Populating tables
INSERT INTO cteSampletbl1 VALUES
(1,'Satheeq'),
(2,'Preethi'),
(3,'Dinesh'),
(4,'Angelo')
GO

--This CTE got error
WITH cteSampletbl1
AS(
    --Same name could not be used as name and in CTE
    SELECT * FROM cteSampletbl1
)
SELECT * FROM cteSampletbl1

--This CTE don’t have any error
WITH cteSampletbl1
AS(
    --full name of object should provide
    SELECT * FROM dbo.cteSampletbl1
)
SELECT * FROM cteSampletbl1

--Dropping tables
DROP TABLE cteSampletbl1
GO

You can’t use same table name as the name of the CTE and inside the CTE.

How varchar data is stored?

In SQL Server we use varchar, varbinary to save disk space in our tables. But its not saving data always as it sounds. When the data stores it takes few extra bytes to describe the data length. Because of that there is no gain using varchar(2). If you use varchar(2) it will take 4 or 3 bytes to store data. varchar(8000) or below should have 2Byte offset to store the variability of character count. If you are using varchar(max) is different. It should have 4 Bytes offset. But I’m still looking for that to confirm it takes 4 Bytes. And it is stored in Variable Column Offset Array.

062911_1840_Themysteryo1

Picture: http://www.sqlservercentral.com/blogs/aschenbrenner/2011/06/29/the-mystery-of-the-null-bitmap-mask/