New function in DENALI SQL SERVER 2012 (CHOOSE)

Hello friends,

This is another post related to new functions in DENALI (SQL SERVER 2012).

So today I am going to discuss “CHOOSE” functions .By the name it is clear that it will choose value.

In a simple sentence you can say it array which give value according to index parameter.

Its syntax is very simple.

CHOOSE (VALUE,CHOICE1, CHOICE2….CHOICEN)

Below is example.

 

In the example we are passing month value in CHOOSE function and according to value it provide corresponding string value of month.

 

DECLARE @tblFriends AS TABLE (Prefix VARCHAR(10) ,
Fname VARCHAR(200),
LName VARCHAR(100),
DOB SMALLDATETIME)

INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('DR','Ravindra','Verma','1/1/1981')
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('Mr','Rajul','Verma','3/3/1981')
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('Sir','Suyash','Kokje','4/4/1981')
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('Sardar','Ravindra','Dhariwal','2/2/1981')
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES('Pandit','Shyam','Purohit','5/5/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Shrikant','Yadav','6/6/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Sawan','Gupta','7/7/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Pankaj','Tomar','8/8/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Vaibhav','Joshi','9/9/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Vikram','Chouhan','10/10/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Subhash','Patidar','11/11/1981')
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES('Vinay','Karma','12/12/1981')

SELECT CONCAT(Prefix,' ' ,FName, ' ',LName) AS ConcatName,
CHOOSE (datePart(m,DOB),'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC') AS DOBMONTH
FROM @tblFriends

so when you execute this script you will get following result.

I hope you enjoyed learning this.

ConcatName DOBMONTH
DR Ravindra Verma JAN
Mr Rajul Verma MAR
Sir Suyash Kokje APR
Sardar Ravindra Dhariwal FEB
Pandit Shyam Purohit MAY
Shrikant Yadav JUN
Sawan Gupta JUL
Pankaj Tomar AUG
Vaibhav Joshi SEP
Vikram Chouhan OCT
Subhash Patidar NOV
Vinay Karma DEC

You can view previous post related with this at

Thanks

Rajat Jaiswal

 

 

 

New Functions in SQL SERVER 2012 ,DENALI (CONCAT)

Hello friends,
This is another post related to new functions in DENALI (SQL SERVER 2012).
So today I am going to discuss “CONCAT” functions .By the name it is clear that it has concatenation property.

Its syntax is very simple.
CONCAT (string1, string2….StringN)
The best part is it handles NULL value also we don’t have to work on NULL value explicitly.
Below is example.
DECLARE @tblFriends AS TABLE (Prefix VARCHAR(10) ,
Fname VARCHAR(200),
LName VARCHAR(100),
DOB SMALLDATETIME)

INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘DR’,’Ravindra’,’Verma’,’1/1/1981′)
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘Mr’,’Rajul’,’Verma’,’3/3/1981′)
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘Sir’,’Suyash’,’Kokje’,’4/4/1981′)
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘Sardar’,’Ravindra’,’Dhariwal’,’2/2/1981′)
INSERT INTO @tblFriends(Prefix,Fname,LName,DOB) VALUES(‘Pandit’,’Shyam’,’Purohit’,’5/5/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Shrikant’,’Yadav’,’6/6/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Sawan’,’Gupta’,’7/7/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Pankaj’,’Tomar’,’8/8/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Vaibhav’,’Joshi’,’9/9/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Vikram’,’Chouhan’,’10/10/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Subhash’,’Patidar’,’11/11/1981′)
INSERT INTO @tblFriends(Fname,LName,DOB) VALUES(‘Vinay’,’Karma’,’12/12/1981′)

SELECT CONCAT(Prefix,’ ‘ ,FName, ‘ ‘,LName) AS ConcatName
FROM @tblFriends

When you run the above script you will get following resultset.
 
ConcatName
DR Ravindra Verma
Mr Rajul Verma
Sir Suyash Kokje
Sardar Ravindra Dhariwal
Pandit Shyam Purohit
Shrikant Yadav
Sawan Gupta
Pankaj Tomar
Vaibhav Joshi
Vikram Chouhan
Subhash Patidar
Vinay Karma

I hope you enjoyed learning this.
You can view previous post related with this at https://indiandotnet.wordpress.com/2011/12/23/new-function-in-sql-server-denali-iif/

Thanks
Rajat Jaiswal

Unable to Login SQL SERVER forgot password

Hello Friends,
I have an interesting thing to share.

 Just day before yesterday I forgot my Denali server password & server not having windows Authentication.

 I am unable to login to the server.  So I just tried a trick and it worked successfully.
I just open the service account of SQL SERVER and change its login to Local System Account as shown in below fig.
And this trick worked successfully and now I can login using windows account.


I hope this will help to you also if by chance you forgot your password to and same situation exists.

Thanks
Rajat Jaiswal

New function in SQL SERVER DENALI (IIF)

Hello friends,

As a continuation of Denali exploration series

(https://indiandotnet.wordpress.com/2011/09/23/see-beyond-the-sql-server-2005-latest-version-of-sql-server-denali/ )

I would like to share some new functions also here. I am sure you like shorthand function “IIF”. Denali now providing IIF function so Instead of writing CASE statement you can easily use IIF function.

 Its syntax is same as we doing in our other programming language.

 IIF (Condition, true value, false value)

 Example:-

DECLARE @genderMale AS BIT
 SET @genderMale = 1 -- where SELECT IIF (@genderMale = 1,’ Smart’, ‘Beautiful’) as property

 

I hope you will use it soon.
Happy Holidays!!!!


Thanks
Rajat Jaiswal

Simplest way of providing Comma seperated column values in SQL SERVER

Hello friends,
Most of the time we require comma separated value of a column according to some relation.
The simplest way of this to create a user define function and create a comma separated value according to their respective other relation value.
Just understand with below example.
Suppose we have a normalize database design in which we have two table
1. State: – State Master is table which contains State of India. And it has following columns
a) StateId (Auto incremental value)
b) Name (State Name)
2. City: – Which contain cities of States according to their respective relation. It has following columns
a) CityId ( Auto incremental Id)
b) StateId (Reference of State table Id
c) City name
Please take a look on below fig with some value of city and state

Now suppose we require following result

Then we are going to use here a simple operator which is “FOR XML Path(‘’)” option so below is use of XML path.
Please run the below query for desire result.


DECLARE @StateCities AS TABLE (StateId INT,
StateName VARCHAR(500),
Cities VARCHAR(5000))

INSERT INTO @StateCities(StateId,StateName )
SELECT StateId,StateName
FROM State

UPDATE tmp
SET Cities = (SELECT c.CityName + ‘,’
FROM dbo.City c
WHERE c.StateId = tmp.StateId
ORDER BY c.CityName
FOR XML PATH (”))
FROM @StateCities tmp

SELECT * FROM @StateCities WHERE Cities IS NOT NULL
.
I hope it would help you.

Happy  Holidays 
Thanks & best regards,
Rajat Jaiswal

Paging easy in “DENALI” (OFFSET & FETCH OPTION)

Hello friends,
Let’s talk today one more important clause of “DENALI” which is very helpful in fetching records, paging are “OFFSET” & “FETCH”.
It is helpful in performance improvement also.
Just go through below example and it will clear more
Suppose I have state table which have all the state rows of INDIA.


Now with offset clause I can say from where I can start row fetching and FETCH NEXT will give me option how many rows I want.
So suppose I want 5 records after and skip first 2 rows then see below SQL statement
SELECT * FROM state ORDER BY StateName OFFSET 2 ROWS FETCH NEXT FROM 5 ROWS ONLY


Hope you enjoyed it and will use sooner or later.
Thanks & Regards
Rajat Jaiswal

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
Thanks
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
DECLARE @tblItem AS TABLE (id INT IDENTITY,
[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.
DECLARE @tblItem AS TABLE (id INT IDENTITY,
[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
DELETE tmp
OUTPUT DELETED.ID,DELETED.Name INTO @tblAllDeletedIds
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
Enjoy SQL SERVER OUTPUT CLAUSE.

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.
Go
DECLARE @id AS INT
SELECT @id = COUNT(1) FROM dbo.Debug WITH(NOLOCK)
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 .

Thanks
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

Enjoy SQL SERVER.

Thanks & Regards
Rajat Jaiswal