Tag Archives: MS SQL Server

BCP or Bulk Copy Program in SQL Server 2005:

Bulk Copy Program (BCP) used to import and export data from command line. It is similar to Bulk Insert. You can refer my article about BULK INSERT in SQL Server at

http://ritesh-a-shah.blogspot.com/2009/03/bulk-operation-or-bulk-insert-in-sql.html

As BCP is command line variation of Bulk operations and an external program you will need authorization to connect to SQL Server.

Let us see it practically by creating one .TXT file for demo.

Ritesh,MIS,echem
Bihag,MIS,CT
Rajan,account,Marwadi
Alka,account,tata
Pinal,DBA,sqlautho
Alpesh,Chemical,echem

Save above records in one .TXT file in “C” drive. I named it “Emp.TXT” in “C” drive.

Once you save the file, you have to have one table in which you would like to transfer data from emp.txt file.

Let us create one blank table for that.

–create one table for demo

use adventureworks

CREATE TABLE emps

(

Name VARCHAR(50),

Dept VARCHAR(10),

Company VARCHAR(15)

)

So now, you are ready to run your first BCP command. Open your command prompt. Start->Run->cmd

Once your command prompt is open. Type down following command there and press enter. (start typing from “BCP” not from “C:\>” ;) )

C:\> BCP adventureworks.dbo.emps in c:\emp.txt –T –c –t, -r \n

Once you run above command you will get message

6 rows copied
Network Packet Size (bytes) : 4096
Clock Time (ms.) Total : 1 Average : (6000.00) row per second.

Last two lines of message may be different based on system. Let us understand above given command.

BCP: run the BCP.EXE utility to import and export data.

Adventureworks.DBO.emps: database name, schema name and table name in which we wants to transfer data.

In: specify that we want to import data from .TXT to SQL table.

C:\emp.txt: path of my file from where I want to transfer data.

-T: its for trusted connection, windows authentication. If your login is not trusted than you should use –U sa(user name) –P sa (password of account)

-c: specifies that data will be loaded as character data.

-t,: field terminator (-t) shows that we will use “,” comma as field terminator.

-r \n: line terminator (-r) shows that we will consider “\n” as line terminator.

Hope you have enjoyed BCP command.

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Microsoft News – OSLO and M Programming Language

Oslo is a code name of Microsoft’s new modeling platform. Modeling is used across a wide range of domains and allows more people to participate in application designs and allows developer to write application at much higher level of abstraction.

“M” is a new programming language and just developed by Microsoft. This language is for building textual domain specific languages and software mode with XAML. Actually “M” is again a code name

You can study following URLs for more information as I got an idea about the same from below URLs:
http://www.microsoft.com/soa/products/oslo.aspx
http://msdn.microsoft.com/en-us/library/dd129519.aspx

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Different Types of Isolation Levels – Microsoft SQL Server 2005 – Part 1

When I was preparing article of ROW level versioning, I have used two types of Isolation level in that Article. 1.) Read Committed Snapshot Isolation Level (RCSI) 2.) Snapshot Isolation level (SI). After finishing that article, I felt to write something about Isolation level available in Microsoft SQL Server 2005.

First of all, question might arise in your mind that what is isolation level and why should we use it? The answer is as simple as question is. I must say that isolation level is two sided blade, if you will not use it in proper manner, it will harm you. Let me put it in this way, if you understand the meaning of ISOLATE then you will know the purpose of that as well. When you wish to isolate the resource for transaction and protect that resource from other transactions. Simply understand that more isolation more locks in your data.

Actually we had four types of isolation in Microsoft SQL Server 2000 and we have those four isolation level in Microsoft SQL Server 2005 as well along with two new isolation level.

All four ISOLATION level given below were available in both MS-SQL 2000 and MS-SQL 2005. List displayed four isolation levels from lower to highest level.

Read UnCommitted Isolation Level
Read Committed Isolation Level
Repeatable Read Isolation Level
Serializable Isolation Level

Apart from above four isolation levels, there is two more isolation level in Ms-SQL Server 2005.

Read Committed Snapshot Isolation Level
Snapshot Isolation Level

Since, this is big topic; I want to cover isolation in parts. This first part is just an introduction of Isolation. Second part will contain detail about four types of isolation level which was available in SQL Server 2000 and 2005 both and finally third part will contain last two types of isolation levels which are introduced in MS SQL Server 2005 only.

Reference: Ritesh Shah

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

“WHERE” Condition with LIKE in ACCESS:

“WHERE” Condition with LIKE in ACCESS:

Generally we used to work with SQL-Server or ORACLE database so we are very well aware with its SQL syntaxes but when sometime we have to deal with ACCESS database, we don’t care to learn it and use our SQL knowledge in ACCESS database. This might lead to logical or syntax error sometime. Once, I have been asked by one of my colleague that simple LIKE is not working in ACCESS database. I had a quick look of it and found one syntax problem in his SQL statement.

Unlike SQL-Server ACCESS database won’t accept”‘%” wildcard. Rather it will accept “*” (asterisk) sign. So if you want to select data from EMPLOYEE table whose first name starts with “R” then you should go for:

SELECT * FROM Employee WHERE FirstName like ‘R*’

Rather than SELECT * FROM Employee WHERE FirstName like ‘R%’

Second SQL statement is wrong in ACCESS database. It’s a logical error.

Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Difference between Microsoft SQL-Server 2000 and Microsoft SQL-Server2005:

Difference between Microsoft SQL-Server 2000 and Microsoft SQL-Server2005:
This question is one of the mostly asked question in an interview but let me tell you that in depth answer of this question could generate one complete book but let us see the difference here in brief.
Almost features of Microsoft SQL-Server 2000 are available in Microsoft SQL-Server 2005 with some improvement and enhancement along with some new feature. Let us see those in brief.
— GUI improvement (SSMS –SQL Server Management Studio)
— T-SQL enhancement
–CLR integration
–Table Partitioning
–Database Mirroring

–DDL trigger support

Along with “Database Mirroring” as a high availability in Microsoft SQL Server 2005 there are old feature of SQL Server 2000 are also available like replication, clustering and log shipping but it was working for only SQL-Server 2000’s enterprise edition but in Microsoft SQL Server 2005, it is available for standard edition also.
Scalability was limited to 2GB and 4CPU in standard edition of Microsoft SQL Server 2000 but in Microsoft SQL Server 2005, it supports 4 CPU and no RAM limitation in standard edition.
As long as encryption concern there was no built in option available in Microsoft SQL Server 2000, you have to have third party tool and expertise to use that tool while in Microsoft SQL Server 2005 it comes up with inbuilt encryption support.
Apart from that, MS SQL Server 2005 comes up with BI reporting tool as known as “YUKON”.
Reference: Microsoft Site
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

WHERE and HAVING clause in Microsoft SQL-Server

WHERE and HAVING clause in Microsoft SQL-Server
People may get confused many time about usage of WHERE and HAVING clause. I am just trying to throw little bit light on WHERE and HAVING clause to make its concept clear. In my many interview session, I asked this simple question to lot of candidate and many of them are aware with “WHERE” condition but not or partial aware with “HAVING” so I felt to write this simple article for them.
“WHERE” is a condition used with T-SQL statement. It always apply to the row and “HAVING” is a clause which always apply to the summarized row (should use with group by).
“WHERE” could be used to make condition on SELECT statement or else can be used with single row function as well. You must have to specify “WHERE” condition before GROUP BY clause.
Whereas, “HAVING” clause is used to make condition on “GROUP BY” data and suppose to be used after “GROUP BY” clause.
Moreover, “HAVING” clause can be used in SELECT statement with “GROUP BY” clause and whenever “GROUP BY’ is not present, “HAVING” will work as “WHERE”.
That’s enough theory, right???? J Anyway, let us see this practically with creating one demo table and query it along with WHERE and HAVING clause.

–Create one table

CREATE TABLE BlogCount

(

BloggerName VARCHAR(10),

Topic VARCHAR(15),

[Year] INT,

Total INT

)

–Insert records in above table

INSERT INTO BlogCount VALUES(‘Ritesh’,‘SQL’,2005,10)

INSERT INTO BlogCount VALUES(‘Ritesh’,‘SQL’,2006,17)

INSERT INTO BlogCount VALUES(‘Ritesh’,‘SQL’,2007,124)

INSERT INTO BlogCount VALUES(‘Ritesh’,‘SQL’,2008,124)

INSERT INTO BlogCount VALUES(‘Ritesh’,‘.NET’,2008,24)

INSERT INTO BlogCount VALUES(‘Alka’,‘SQL’,2007,14)

INSERT INTO BlogCount VALUES(‘Alka’,‘.NET’,2007,18)

INSERT INTO BlogCount VALUES(‘Alka’,‘SQL’,2008,14)

–Query with WHERE and HAVING clause together

SELECT BloggerName,AVG(total) AS ‘Average’ FROM blogcount

WHERE Topic=‘SQL’

GROUP BY BloggerName

HAVING AVG(total)>25


First WHERE clause filter the record set and then it will be passed to GROUP BY and HAVING for further filtering.
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Use of RANK() and Partition by clause in SQL-Server 2005

Use of RANK() and Partition by clause in SQL-Server 2005
We will start by creating one table for demonstration and enter some records in it.
--Create one table
CREATE TABLE BlogCount
(
BloggerName VARCHAR(10),
Topic VARCHAR(15),
[Year] INT,
Total INT
)
--Insert records in above table
INSERT INTO BlogCount VALUES(‘Ritesh’,‘SQL’,2005,10)
INSERT INTO BlogCount VALUES(‘Ritesh’,‘SQL’,2006,17)
INSERT INTO BlogCount VALUES(‘Ritesh’,‘SQL’,2007,124)
INSERT INTO BlogCount VALUES(‘Ritesh’,‘SQL’,2008,124)
INSERT INTO BlogCount VALUES(‘Ritesh’,‘.NET’,2008,24)
INSERT INTO BlogCount VALUES(‘Alka’,‘SQL’,2007,14)
INSERT INTO BlogCount VALUES(‘Alka’,‘.NET’,2007,18)
INSERT INTO BlogCount VALUES(‘Alka’,‘SQL’,2008,14)
Once, you are done with above task, let’s think of usability of RANK() and Partition by clause in our real world. Suppose you wish to get record set of all the blogger with their highest total of blog. What will you do? You may use sub-query or/and GROUP BY clause. Right???? Doesn’t it seems tedious and bit difficult? Here is easy solution for the same.
First we will use Rank() and Partition By clause and then we will filter our record set. Partition is like Group by, we want list sorted based on “BloggerName” so we will be putting it in Partition by clause and Order by clause in below query will give Ranking 1 to highest “Total”.
SELECT BloggerName,Topic,[Year],Total,
Rank() OVER (Partition by BloggerName Order by Total DESC) as ‘Ranking’
FROM
BlogCount
After running above query you will get result set like:
BloggerName Topic Year Total Ranking
———– ————— ———– ———– ——————–
Alka SQL 2007 18 1
Alka .NET 2007 18 1
Alka SQL 2008 14 3
Ritesh SQL 2007 124 1
Ritesh SQL 2008 124 1
Ritesh .NET 2008 24 3
Ritesh SQL 2006 17 4
Ritesh SQL 2005 10 5
(8 row(s) affected)
Now, we may like to see only highest total article by bloggers, we can’t put direct “where condition” in above query so we will wrap it up like:
SELECT *
FROM
(
SELECT BloggerName,Topic,[Year],Total,
Rank() OVER (Partition by BloggerName Order by Total DESC) as ‘Ranking’
FROM
BlogCount
) Testing
where Ranking<2
Here is the output:
BloggerName Topic Year Total Ranking
———– ————— ———– ———– ——————–
Alka SQL 2007 18 1
Alka .NET 2007 18 1
Ritesh SQL 2007 124 1
Ritesh SQL 2008 124 1
(4 row(s) affected)
See above result set, we got only 4 records out of 9, which have the highest “Total”. See “Ritesh’s” record # 3 and #4. Both belongs to SQL and has same total so in that case, I may wish to see only one records of year 2008. In this case, year is a tie breaker. Use below given query for breaking a tie.
SELECT *
FROM
(
SELECT BloggerName,Topic,[Year],Total,
Rank() OVER (Partition by BloggerName Order by Total DESC, [year] DESC) as ‘Ranking’
FROM
BlogCount
) Testing
where Ranking<2
Now, you will get following output.
BloggerName Topic Year Total Ranking
———– ————— ———– ———– ——————–
Alka SQL 2007 18 1
Alka .NET 2007 18 1
Ritesh SQL 2008 124 1
 (3 row(s) affected)
How easy is this???? Much much better than using sub-query and group by.

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Sharing IT tips at “Quick Learn

Note: Microsoft Books online is a default reference of all articles.

TOP clause takes INT variable – SQL-Server 2005

TOP clause takes INT variable – SQL-Server 2005

This is TIPS about new feature of SQL-Server 2005. In SQL-Server 2005 you can use variable in TOP clause which was not possible in any of the older version of Microsoft SQL Server.
You can supply any integer value beside TOP clause in Microsoft SQL-Server 2005 and in 2008. BTW, you can now use TOP clause in INSERT, UPDATE and DELETE also.
USE AdventureWorks
GO
DECLARE @ReturnRow INT
SET @ReturnRow=5
SELECT TOP (@ReturnRow) * from Purchasing.Vendor
Reference: Ritesh Shah
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah