See beyond the SQL SERVER 2005 – Latest version of SQL SERVER “Denali”

Hello friends,
As you know i am SQL SERVER 2005 user now i am moving on some latest tool to play with so here we go.

Microsoft introduced a new CTP 3 released for SQL Server Latest Version “Denali”.Now if you go through in detail of this you will find “Denali” having many new features and lots of new learning for us.
Today I am going to share one the new updates in this version which is Data Types. Although many things you will find in just previous version like SQL SERVER 2008 & R2 but I am comparing it with SQL SERVER 2005 version.

So, in Microsoft SQL SERVER latest version “Denali” you will find many new data types like Time, DateTime2, Date Time Offset, Geography, Geometry, and Hierarchy Id.

So here we will discuss all the above data type one by one.
1) Time:-
Sometime the business need is to capture time only at that in our previous SQL Server version we don’t have any specific Time Data type if we need to capture time then we use date Time data Type but now in “Denali” we have specific Time data Type . Which store time in hh:mm:ss[.nnnnnnn]. Below is the range for each individual area.
hh is two digits, ranging from 0 to 23, that represent the hour.
mm is two digits, ranging from 0 to 59, that represent the minute.
ss are two digits, ranging from 0 to 59, that represent the second.
n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.
We can store time up to 7 precision. And it requires 5 byte for storage.

2) DateTime2:-
DateTime2 is an addition on Date Time data type. It is more robust more depth range. It store data in YYYY-MM-DD hh:mm:ss[.fractional seconds] format. And it has huge date time range. The date range starts with 0001-01-01 through 9999-12-31. It requires 6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes.

3) DateTimeOffset:-
By the name it is clear that it store Time offset. Some time we have to store data time zone wise like “2011-09-12 12:30:30.12345 -07:00” then in that situation this data type is very useful. The default storage template is YYYY-MM-DD hh:mm:ss[.nnnnnnn] [{+|-}hh:mm]. And it requires 10 byte to store data. And below is range of each individual factor.
YYYY is four digits, ranging from 0001 through 9999 that represent a year.
MM is two digits, ranging from 01 to 12, that represent a month in the specified year.
DD is two digits, ranging from 01 to 31 depending on the month that represent a day of the specified month.
hh is two digits, ranging from 00 to 23, that represent the hour.
mm is two digits, ranging from 00 to 59, that represent the minute.
ss are two digits, ranging from 00 to 59, that represent the second.
n* is zero to seven digits, ranging from 0 to 9999999, that represent the fractional seconds.
hh is two digits that range from -14 to +14.
mm is two digits that range from 00 to 59.
4) Geography: – (Available in SQL SERVER 2008 also I am comparing with SQL SERVER 2005)
You can find geography data type in SQL SERVER 2008 also but it is really new for me. By the name it clear that it will restore some geography related things. And great our sixth sense worked here it store geography coordinates like latitude, longitude. It store coordinate that is relevant to GPS. Later on we will discuss it separately in my new post specific for this data type.
5) Geometry: – (Available in SQL SERVER 2008 also I am comparing with SQL SERVER 2005)
Again this is old data type you can find in SQL SERVER 2008 but it is really new for me. So again by the name it is clear that it store geometry instance like point, rectangle, path etc. Mostly it is 2 dimension coordinate for x and y axis. We will discuss it later on separately in my specific post for this.

6) HierarchyId: – (Available in SQL SERVER 2008 also I am comparing with SQL SERVER 2005)
This is something more unique and attractive data type in “Denali” and I am pretty much sure that I will use in my coming up projects. It uses to save hierarchy id. The database use varBinary to store internally. You can easily traverse the hierarchy data by using some in build function provided by Microsoft SQL SERVER. You can move in both direction top to bottom or bottom to top easily. We will go in detail later on.
I hope you like many new data type in latest version of SQL SERVER. In next post I will provide some great function and features introduced in “Denali”.

Till than thanks a lot enjoy learning
“Denali” fever
Rajat Jaiswal

Create Silverlight Gallery in easy steps part-II

Hello Friends,
Today I am going to share Silverlight Image gallery no 2.

Silverlight image gallery

 If you don’t have idea of Silverlight Image gallery no 1 then please refer link. part-1
So, here we go.

 The concept is same as we did earlier. We will show image thumbnails on left side in a wrap panel (new control in Silverlight 4 just downloads the Silverlight 4 toolkit if you don’t have) and right side panel we will show large image with some fade in/fade out feature as we did in our earlier Silverlight image gallery.
Although I am doing this thing with some fix values but you can use XML instead of that that will be great idea with quick steps.
Step 1:-
So basic concept is I copied all the images in a folder in Silverlight project and rename then with numbers like 1.jpg, 2.jpg and so on.
Step 2:-
Just design a page with two panels. Left panel is wrap panel and right panel is stack panel. In right panel we have a image control with some fade in /out feature using by Storyboard.
Step 3:-
  Add image controls at runtime in wrap panel according to image count as shown below.
For intI = 1 To 8
Dim Img As New Image
Dim imgsource As ImageSource
imgsource = New BitmapImage(New Uri("images/" + intI.ToString() + ".jpg", UriKind.Relative))
Img.Width = 100
Img.Height = 100
Img.Margin = New Thickness(10)
Img.Name = "img" + intI.ToString()
Img.SetValue(Image.SourceProperty, imgsource)
Dim ef As New Effects.DropShadowEffect()
ef.ShadowDepth = 10
Img.Effect = ef
AddHandler Img.MouseEnter, AddressOf img_MouseEnter
We have added handler also on this image controls “Basically Mouse enter”
Step 4:-
On mouse enter event of image control we start the animation of fade in/fade out and replace the center image source with the new image source (The thumbnail on which we have clicked)
Private Sub img_MouseEnter(ByVal sender As System.Object, ByVal e As System.Windows.Input.MouseEventArgs)
        Me.imgMain.Source = CType(sender, Image).Source
    End Sub

Step 5:-
  For auto image change we use a dispatcher Timer function which changes the center image in each 50 sec.  And increase the image counter by one and if image counter reached to max value then reset to 0.
Private Sub TimerClick(ByVal sender As System.Object, ByVal e As EventArgs) Handles timer.Tick
        If intcounter >= 8 Then
            intcounter = 0
        End If
        Dim imgsource As ImageSource
        imgsource = New BitmapImage(New Uri(“images/” + intcounter.ToString() + “.jpg”, UriKind.Relative))
        Me.imgMain.Source = imgsource
        intcounter = intcounter + 1
    End Sub

Step 6:- run it.
For more information you can download the code from link.

I hope you will enjoy Silverlight Image Gallery part –II.

Soon I will share more samples related to image gallery.
Enjoy Silverlight
Thanks & Best Regards
Rajat Jaiswal

SQL SERVER Great Feature “Output Clause” :)

Hello friends,
So some time we faced situation like we have to insert huge amount of data and also keep the trace for the id columns. Or like some time we have to delete the record and also need to keep the track record which ID we have deleted. So here usually people use either cursor or while loop just because they are not aware of the new opportunity which Microsoft SQL SERVRER 2005 has and that opportunity is “OUTPUT Clause”.
So let understand this by some simple examples. I took here 2 examples. In first example we will talk about how output clause helpful with Delete operation and in second example we will talk how output clause helpful in Insert operation.
So Example first here:-

Now consider a delete operation where you need to delete records with some specific condition and whatever record you have deleted you need to provide IDS of those deleted item.
Suppose the table name is tblItem then we will write below query
[name] VARCHAR(100))
DECLARE @tblInsertedId AS TABLE (id INT)
INSERT INTO @tblItem ([name]) OUTPUT Inserted.Id INTO @ tblInsertedId
SELECT Right(UserName,1)
FROM dbo.users

SELECT * FROM @tbl tblInsertedId
So when you run above query you will get identity column values corresponding to tblItem table. Which we have inserted in @tblAllIds table with output inserted clause.
In similar way suppose now we want to delete the records from @tblItem and we need to capture all the delete records ids then also this clause will helpful see below example.
[name] VARCHAR(100))

DECLARE @tblAllDeletedIds AS TABLE( id INT,Name varchar(100))
INSERT INTO @tblItem ([name])
SELECT Right(UserName,1)
FROM dbo.users
— Now suppose we want to delete all the item with name start with Y
— and also want to keep the track what are the ids we have deleted then we use output deleted
FROM @tblItem tmp WHERE name like ‘y%’

SELECT * FROM @tblAllDeletedIds

In similar way we can use this output clause with update query also.
So it’s a great feature just enjoy.
It helpful in avoiding cursor and while loop so enjoy this feature.
It helped me a lot hopefully it will helpful to you also.

Thanks & Best Regards
Rajat Jaiswal

A unique feature of SQL SERVER Loop with Go statement :)

Hello friends,
I hope you are aware of this SQL Server feature already but just sharing it for those who don’t know.
Suppose in some situation you want to repeat a particular SQL Statement block number of times than in that situation go statement help you.
Suppose I have a debug table with column name id. And I need to insert 1000 ids then I can use following statements.
INSERT INTO dbo.Debug(id) VALUES(@id + 1)
GO 1000

Now when you run this statement you will get more 1000 rows with different ids.
So it is a very nice feature you can enjoy looping with this .

Rajat Jaiswal 🙂

How to kill a running SQL Statement ?

ello Friends,

As in my previous post you are aware of what are the different SQL Statement is currently running on your machine. Now each statement having particular individual process id. You can find the column spid in the Statement result.
So just pickup that process id and run following command.
KILL “process id”

Example :- KILL 59


Thanks & Regards
Rajat Jaiswal

How to find what is currently running on your SQL SERVER?

Hello friends,
Sometimes you are keen to know that what is currently running on SQL SERVER. I faced a problem recently and the below query help me a lot. Actually my production database log was increasing so fast and I was not aware of that why it was increasing with this much amount? I run the below query and it provided me list of statement with their process id and source of running.
And with the help of this SQL statement I found the culprit statement which increasing the log.
So hope fully it will help you also.

SELECT [Spid] = session_Id
, ecid
, [Database] = DB_NAME(sp.dbid)
, [User] = nt_username
, [Status] = er.status
, [Wait] = wait_type
, [Individual Query] = SUBSTRING (qt.text,
(CASE WHEN er.statement_end_offset = -1
ELSE er.statement_end_offset END –
,[Parent Query] = qt.text
, Program = program_name
, Hostname
, nt_domain
, start_time
FROM sys.dm_exec_requests er
INNER JOIN sys.sysprocesses sp ON er.session_id = sp.spid
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle)as qt
WHERE session_Id > 50 — Ignore system spids.
AND session_Id NOT IN (@@SPID) — Ignore this current statement.

Thanks a lot

Rajat Jaiswal