Pages

Saturday, May 30, 2009

TSql Unit Test Generator

I found one tool which is very interesting to generate the Test Scripts for TSQL programs automatically.

The tool , which I am talking about is TSql Unit Test Generator. This Automatically generates unit tests in SQL for programs in SQL Server. The unit tests created are written in TSQL . This framework is intended to be used with the TSQLUnit framework.

Friday, May 29, 2009

60 Seconds with Michael Coles


Michael Coles is a SQL Server Microsoft Most Valuable Professional. He has written several books and articles on SQL Server. He is a regular columnist in http://www.sqlservercentral.com/ and regularly blogs at SQLblog. Michael is the author of several books, which are Pro T-SQL 2005 Programmer's Guide, Pro SQL Server 2008 XML, Pro SQL Server 2008 Full-Text Search and he contributed to Accelerated SQL Server 2008 .

Lets check out more about his books and his views on SQL Server 2008.

VJ: Please tell us about the motivation behind writing a book on XML?
Michael Coles:
Initially I needed a reference book for SQL Server 2005’s new XML functionality (when it was first introduced), but everything I found seemed to be about the backwards-compatible SQL Server 2000 functionality that was carried forward. I think the lack of a reference that really covered the new features like the XML data type methods and XML Schema collections really made me decide to write Pro SQL Server 2008 XML.

VJ: There are so many features in SQL Server 2008, what do you think are the most useful for developers?
Michael Coles: (I assume this is about new features specifically). I’ve gotten a lot of good use out of a lot of the new features. It’s really hard to say what’s the “most useful” – it really depends on what you need. I’ve found the encryption functionality in SQL Server 2008 a particularly compelling feature, but it hasn’t gotten a lot of fanfare yet. The new functionality is integrated full-text search makes it a lot more useful as well. I also like the spatial data types, but it’s such specific functionality that I don’t know how many developers will get the opportunity to take advantage of it directly, without some sort of off-the-shelf geospatial/mapping software sitting there to use it.

VJ: Please tell us the advantages of storing data in XML in comparison with relational database?
Michael Coles:
Now that’s a trick question :) There’s not really an “advantage” to storing XML versus relational form. What happens is people have requirements to store XML data they may receive from other sources, they may have a requirement to use XML columnsets for a more dynamic database, or they may just need to parse and process XML data.

There are a lot of DBAs who flatly reject XML, and in many cases they’re right. But XML is the lingua franca of the internet and at some point developers and DBAs will have to deal with it. To me some of SQL Server’s most compelling XML features are XQuery support, the FOR XML clause, and the shredding (converting XML to relational format) support of the XML data type. In other words a lot of SQL Server’s best XML features deal with processing XML on the server.

VJ: You are Microsoft Most Valuable Professional, what do you think about this program.
Michael Coles:
It’s a very humbling experience. You get to meet some of the smartest SQL people on the planet – folks like Louis Davidson, Adam Machanic, Hugo Kornelis, Erland Sommarskog, Itzik Ben-Gan (and a bunch of others). To actually meet these people you’ve looked up to for so long is incredible. To have one or two say “I enjoyed your article” or “I like your code sample” is like icing on the cake.

VJ: Please tell us about your favorite authors?
Michael Coles:
Oh wow – I like so many authors. For technical writing, I really like Adam Machanic and Itzik Ben-Gan’s books, Erland Sommarskog’s incredible white papers, and Pinal Dave’s blog (sqlauthority.com). For nontechnical writing I enjoy Simon Singh and Douglas Adams.

VJ: Please tell us about the SQL Server books that are part of your bookshelf?
Michael Coles:
Let’s see – as examples I have the Knight and Veerman books (Pro and Expert SQL Server 2005 Integration Services books), the Dewson Pro SQL Server 2005 Assemblies book, the entire Inside SQL Server 2005 series from Delaney and her cowriters. I usually buy SQL books for reference when I’m doing very specific tasks. When I buy a SQL book I tend to spend a lot of time looking through book indexes for keywords related to a specific problem I’m experiencing. Most of the books on my technical bookshelf are non-SQL books though, like Schneier’s Applied Cryptography and Knuth’s The Art of Computer Programming.

VJ: Please tell us about the enhancements or new features you want to see on XML in next version of SQL Server.
Michael Coles: I’d personally like to see more comprehensive support for the XQuery standard and better overall XML performance. I think over time both of these will be addressed. Keeping in mind the new generation of XML support is only 4 years old, I think it’ll get even better as the features mature and new methods of optimizing XML manipulation and queries. One of the best things I think developers can do is provide Microsoft with plenty of feedback (through the MS Connect website, via user groups, on blogs, etc.) about which XML features they’re using, which features they find most useful, and what they’d like to see improved. This kind of feedback can help get new features and improvements on the SQL Server team’s radar.

VJ: Please share your valuable message for the database Developers
Michael Coles: I think the most important thing is to share your knowledge and experiences with other developers. A lot of the tips and tricks that some developers take for granted aren’t necessarily widely known, and it helps everyone when we contribute to the community together.

VJ: Please tell us about your upcoming books
Michael Coles:
Right now I’m finishing up Expert SQL Server 2008 Encryption with James Luetkehoelter. This is going to be the first book that specifically focuses on encryption technology in SQL Server and how you can leverage it to protect your corporate data.

I’m also considering writing a freely downloadable e-book—I still have to work out the details and find the time. I’ll keep you posted on that one.

I would like to thank Michael Coles for sharing his views with us....

Disable F1 key in Excel Sheet

Yesterday, I was working on excel sheet and I was so frustrated with the function key F1 Key. Generally I use F2 key to edit the contents of the Cell. F1 & F2 are so close together, so accidentally I pressed F1 instead of F2 to edit the cell. All most half of my time went off closing the help window. It is really frustrating.

As you know F1 is the key that would help you, if you are running into problems or need something. But in this case I am running into problems because of F1, Now who is going to help me??

So I decided, either I should remove the F1 key from keyboard or write a program to disable the F1 key. I am a programmer so I thought I should write a program to save my life.

I hope the below code would be useful to you guys. In just five minutes you can disable your F1 key.

1. Open Excel sheet
2. Can you see below icons in your excel tool bar? If not then right click on the tool bar and select Visual Basic 3. Click on Visual Basic Editor Icon
4. Under the Microsoft Excel Objects, double click on ThisWorkbook.
5. Add the below code
Private Sub Workbook_Open()
Application.OnKey "{F1}", ""
End Sub

6. Save the excel sheet and save your life. That’s it.

Thursday, May 28, 2009

Very Common SQL Server Errors And Resolutions Part 2

In this article I would like to list out most common errors and their solutions that occur in SQL Server. This article will also you tell how to reproduce them.

Error: 1
Msg 537, Level 16, State 2, Line 2
Invalid length parameter passed to the LEFT or SUBSTRING function.
Or
Msg 536, Level 16, State 1, Line 2
Invalid length parameter passed to the left function.


Cause:
You will get this error when you try to select some string based on negative value of length parameter in LEFT or SUBSTRING functions.

How to re-produce:
DECLARE @inPut VARCHAR(30) = 'VIJAYA KADIYALA'
SELECT LEFT(@inPut, CHARINDEX('Z', @inPut) - 1)
Or
DECLARE @inPut VARCHAR(30) = 'VIJAYA KADIYALA'
SELECT LEFT(@inPut, -10)


Solution:
Make use SIGN user defined function (UDF) to convert the sign to suppress the error.

DECLARE @inPut VARCHAR(30) = 'VIJAYA KADIYALA'
SELECT LEFT(@inPut,(case SIGN(CHARINDEX('Z', @inPut)) WHEN -1 THEN -1 else 1 end ) - 1)


Error:2
Msg 245, Level 16, State 1, Line 13
Conversion failed when converting the varchar value ',' to data type int.

Cause:
You will get this error when you try to convert INT columns to string or VARCHAR.
Another reason could be doing some operations with INT column data type to look like string or VARCHAR.

How to re-produce:
SELECT 1 + ',' + 2 + ',' + 3 AS COL

Solution:

Use CAST or CONVERT function to convert INT data type values into VARCHAR

SELECT (CAST(1 AS VARCHAR(10)) + ',' + CAST(2 AS VARCHAR(10)) + ',' + CAST(3 AS VARCHAR(10))) as col


Error:3
Msg 8107, Level 16, State 1, Line 1
IDENTITY_INSERT is already ON for table Table_name. Cannot perform SET operation for table Table_Name.


Cause:
At any point in time you can have only one set whose IDENTITY_INSERT ON.
You will get this error when you try to set IDENTITY_INSERT ON for a second table when IDENITY_INSERT is already on for the First Table.

How to re-produce:
create table t1(T1_ID INT IDENTITY(1,1))
SET IDENTITY_INSERT T1 ON

create table t2(T2_ID INT IDENTITY(1,1))
SET IDENTITY_INSERT T2 ON



Solution:
When you are using IDENTITY_INSERT then always use this in batch mode.

SET IDENTITY_INSERT T1 ON
GO
-- do you work
GO
SET IDENTITY_INSERT T1 OFF
GO



Error:4
Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table 'T1' that match the referencing column list in the foreign key 'FK_T2'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.


Cause:
You are trying to create a foreign key column and referencing a column in parent table where there is no primary key or unique key defined.

How to re-produce:
create table t1(T1_ID INT IDENTITY(1,1))

create table t2(T2_ID INT IDENTITY(1,1))

ALTER TABLE T2 ADD CONSTRAINT FK_T2 FOREIGN KEY(T2_ID) REFERENCES T1(T1_ID)

Solution:
Make sure you have Primary key or unique key defined on the column that you are going to reference in Foreign Key Constraint.

Error: 5
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table 'T2', because it does not exist or you do not have permission.


Cause:
You are trying to drop a table where you are not the owner of the table or table doesn’t exist in your schema or database.

How to re-produce:
DROP TABLE T2

Solution:
Before you perform any DDL operation make sure you have the permissions on the object.

Undocumented feature to concatenate strings from a column into a single row

There are several ways to create one column with comma separated values from multiple rows. Today, I would like to show you, how to achieve this with very simple query using XML features of SQL Server.
set nocount on
-- Decalare Table Variable
Declare @t Table (c1 int)
-- Insert data into Table
Insert @t
Select 10 Union All
Select 20 Union All
Select 30 Union All
Select 40 Union All
Select 50 Union All
Select 60
-- Select data from table (in Rows)
SELECT C1 AS input FROM @t
-- Select data from table using XML(As One single column)
SELECT (
SELECT CAST(C1 AS VARCHAR(20)) + ','
FROM @t
FOR XML PATH ('')
) as Comma_Separated_String
set nocount off


-- Query Output

input
-----------
10
20
30
40
50
60


Comma_Separated_String
-------------------------------

10,20,30,40,50,60,

60 Seconds with Sugeshkumar Rajendran


60 seconds with Sugeshkumar Rajendran:
Adding the TAG MVP to my name, distinguishes me from other technologists near me.


VJ caught up with Sugeshkumar Rajendran, MVP in SQL Server, to get his view on the Microsoft Most Valuable Professional Program(MVP).


VJ: Please tell us about yourself.
Sugesh:
I am Sugeshkumar Rajendran, a SQL Server MVP. I have been in IT industry for almost 6 years now. After completing Bachelors in Engineering from one of the reputed institutions in Chennai, started my career in IT as a Junior Systems DBA working for one of the Largest ERP Company as a client. I have worked with various clients as PeopleSoft, Windows and SQL Server Administrator. Currently, I am working as a Full-Time SQL Server DBA for one of the largest financial service providers in the globe.

VJ: What motivated you to become an MVP
Sugesh:
Becoming a MVP has always been a dream for me after taking role of a Senior DBA in an IT Services company in India. I started a Blog, website to provide useful information about SQL server and Administration to User community. MVPs are always treated with respect in the community for the knowledge they posses and give to others. I gain and share knowledge by being a MVP.

VJ: Please share with us your journey of becoming an MVP.
Sugesh: It’s a great pleasure being part of this great team of countable MVPs around the globe. It gives us chance to communicate with experts of technologies, answer to questions, and learn new things every day. Adding the TAG MVP to my name, distinguishes me from other technologists near me

VJ: Please tell us about your MVP Summit experience
Sugesh: Attending MVP Summit was a great experience to me. It gave me a chance to know what’s going in the Technology; meeting experts of different technologies, product groups, the session were very useful and informative.

VJ: Any message to the technologists
Sugesh: Innovation is the Key. Think beyond what is possible. Value your time. There is no use in building or enhancing a product or a tool that is already available. Develop something new. That’s where you can prove you are worth more than what others are.

kick it on DotNetKicks.com

Wednesday, May 27, 2009

Tuning Replication for High Performance from SQL Server MVP Hilary Cotter

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Using replication, you can distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

SQL Server MVP Hilary Cotter will explain how to maximize your replication solution for throughput and minimal latency. This is part of SQL Server User Group in New York.

Please Join New York City Microsoft SQL Server User Group and attend the session.

Session Details:
Topic : Tuning Replication for High Performance
Date : 28-MAY-2009
Time : 6:00 PM EST - 8:00 PM EST.

How To: SQL Server Management Studio

Hi
This page contains links to various Tips and Tricks on working with SQL Server Management Studio.

How to Debug TSQL Programs in SQL Server
How to Find out the dependencies using SQL Server Management Studio
Generate DDL of a Table Using SQL Server Management Studio
Intellisense update/refresh on schema changes - SQL Server

How to Debug TSQL Programs in SQL Server

Today I would like to give you simple tip to debug TSQL programs, which would save lot of time in fixing the problems. You don’t have to install any additional software; this option comes with SQL Server Management Studio.

>>>> Open and Login into SQL Server Management Studio (SSMS)
>>>> Choose the database in the Object Explorer
>>>> Click on New Query to open SQL Workspace
>>>> Expand Programmability/Stored Procedures.
>>>> Select the stored procedure to debug
>>>> Go to the Debug menu and click “Start Debugging”call the stored procedure from SQL Workspace(Refer to the below image)


>>>> Now press F11 by selecting Stored Procedure, this action triggers to the debugging mode.
>>>> Now Click F11 again to get inside the stored procedure
>>>> On the Locals Window you can see the values of the variables which are declared/used inside the stored procedure(Refer to below image)

Tuesday, May 26, 2009

Convert list of values seperated by comma stored in a column into Multiple Rows in SQL Server

Today, I would like to give you simple tip to convert list of values seperated by comma stored in a column into Multiple Rows in SQL Server.

-- Create Numbers Table
Create table Numbers(num Int Not Null);
alter table Numbers add constraint pk_Numbers primary key clustered(num);


-- Populate Numbers table
INSERT INTO numbers
SELECT number FROM master..spt_values WHERE type = 'P';

-- Declare the variable and Run the Actual Query to convert Columns to Rows
declare @String varchar(MAX)
set @String = '100,200,250,300,350,450,500'
set @String =','+ @String + ','
SELECT REPLACE(Val_Column,',','')
FROM
(
select
substring(@String,Num,CHARINDEX(',',@String,Num+1)-Num) As Val_Column
from
Numbers
where num < LEN(@String)
) C
where Val_Column like ',%'

Monday, May 25, 2009

Tree Queries in SQL Server with Common Table Expressions

My Friend and Fellow MVP in SQL Server, Jacob Sebastian is conducting a very good contest on TSQL. Every week he and his team are posting a very good challenge to write a SQL query to solve a problem.

You can also find his interview @ 60 seconds with Jacob.

This week I got an email from him about TSQL challenge. If you want to find more details about TSQL challenge then visit http://beyondrelational.com/

The challenge is “about identifying all the employees directly or indirectly reporting to a given manager.” This looks so simple by the statement but not at all. You can write a query with Recurssive Common Table Expressions (CTE) to resolve this, but you should not pass or hard code any values inside the CTE. This is what makes it a bit complex. That is you can’t control the output that is coming from CTEs.

Copied table structure and sample data from TSQL Challenge 8

CREATE TABLE Employees (EmpID INT, EmpName VARCHAR(20), ReportsTo INT)
INSERT INTO Employees(EmpID, EmpName, ReportsTo)
SELECT 1, 'Jacob', NULL UNION ALL
SELECT 2, 'Rui', NULL UNION ALL
SELECT 3, 'Jacobson', NULL UNION ALL
SELECT 4, 'Jess', 1 UNION ALL
SELECT 5, 'Steve', 1 UNION ALL
SELECT 6, 'Bob', 1 UNION ALL
SELECT 7, 'Smith', 2 UNION ALL
SELECT 8, 'Bobbey', 2 UNION ALL
SELECT 9, 'Steffi', 3 UNION ALL
SELECT 10, 'Bracha', 3 UNION ALL
SELECT 11, 'John', 5 UNION ALL
SELECT 12, 'Michael', 6 UNION ALL
SELECT 13, 'Paul', 6 UNION ALL
SELECT 14, 'Lana', 7 UNION ALL
SELECT 15, 'Johnson', 7 UNION ALL
SELECT 16, 'Mic', 8 UNION ALL
SELECT 17, 'Stev', 8 UNION ALL
SELECT 18, 'Paulson', 9 UNION ALL
SELECT 19, 'Jessica', 10

Lets see how we can solve this with CTEs.
DECLARE @manager VARCHAR(20)
SELECT @manager = 'Jacob';
WITH EmployeeCTE(empid, empname, ReportsTo, depth, sortcol)
AS(
SELECT empid, empname, ReportsTo, 0, CAST(empid AS VARBINARY(500))
FROM employees
WHERE Empname = @manager
UNION ALL
SELECT E.empid, E.empname, E.ReportsTo, M.depth+1, CAST(sortcol + CAST(E.empid AS BINARY(4)) AS VARBINARY(500))
FROM Employees AS E JOIN EmployeeCTE AS M
ON E.ReportsTo = M.empid
)
SELECT REPLICATE('----', depth) + empname AS empname
FROM EmployeeCTE CTE
ORDER BY sortcol


Output of the above query is:



The logic which I built is based on recursive CTE. The CTE would give me all the employees who are reporting to Jacob directly or indirectly. If you look at the definition of CTE, I am using @manager variable to restrict the result set of CTE.

The real challenge is not to hard code or use any variables inside the CTE which restricts the output.

After thinking a lot, the one idea which came to my mind is to find out the ultimate parent ID for each and every node. To find this I used Row_Number which is SQL Server Analytical function.

DECLARE @manager VARCHAR(20);
SELECT @manager = 'Jacob';
WITH EmployeesCTE
AS (
SELECT EmpName,EmpID, ReportsTo, 1 AS LEVEL,
CAST(row_number() OVER(ORDER BY @@LangID) AS varchar(max)) AS path
FROM dbo.Employees
UNION ALL
SELECT e.EmpName,e.EmpID, e.ReportsTo, EmployeesCTE.LEVEL + 1 AS LEVEL,
path + ',' + CAST(row_number() OVER(ORDER BY @@LangID) AS varchar(max)) AS path FROM dbo.Employees e, EmployeesCTE
WHERE ((e.ReportsTo = EmployeesCTE.EmpID))
)
SELECT REPLICATE(' ', c1.Level) + c1.EmpName as EmpName,
c1.ReportsTo, c1.LEVEL, c1.path
FROM EmployeesCTE c1 ,
(Select c2.EmpName,c2.Path from
EmployeesCTE c2
WHERE LTrim(c2.EmpName) = @manager And c2.Path Not Like '%,%' ) c2
where substring(c1.path+',',1,charindex(',',c1.path+',')-1) = c2.path
ORDER BY c1.path

The way to find out this is constructing the complete path from Node to its ultimate parent node. The code which does this part is
path + ',' + CAST(row_number() OVER(ORDER BY @@LangID) AS varchar(max)) AS path

@@LangID is dummy order by parameter; I had to use some variable to order the results.

Look at the below screen shot for the sample output.



So in this path I am taking the ultimate parent ID by applying CHARINDEX and SUBSTRING functions.


NOTE: The numbers which you are seeing in the Path column are not EmpIDs. These are based on SQL Server Analytical functions.


So now I have the node and its ultimate parent node. Next step is to limit the results based on the input parameter.
To limit the results I am using below query, where I am passing @manager and also placed a condition on the output of CTE where Path shouldn’t contain any special characters. What this means is if this column i.e. path contains any special characters then it has children. The below query would give me the ID of the @manager value.

(Select c2.EmpName,c2.Path from EmployeesCTE c2 WHERE LTrim(c2.EmpName) = @manager And c2.Path Not Like '%,%' ) c2
Using this c2.path column, I am limiting the result set of the outer query.

This solution works in SQL Server 2005. In SQL Server 2008 there is new feature called HierarchyID with this you can easily solve this problem. I haven’t yet explored this feature but very soon I am going to explore and will put something on my blog.

Saturday, May 23, 2009

String Functions in SQL Server -- Final Part

In this article I would like cover and complete rest of the String Functions.

1) CHARINDEX: CHARINDEX (expression1 ,expression2 [ , start_location ] )
expression1 Is sequence of characters that to be found in expression2. start_location is optional parameter. This will be useful if you want to search the expression1 in expression2 from a specific location.
In simple words this is used to find the starting position of a string (i.e. expression1) with in another string (expression2).


SELECT CHARINDEX('Kadiyala', 'Vijaya Kadiyala') AS String_position

Output Is

String_position
---------------
8

SELECT CHARINDEX('ya', 'Vijaya Kadiyala') AS String_position

Output Is

String_position
---------------
5

The below example is based on Start Location:
SELECT CHARINDEX('ya', 'Vijaya Kadiyala',7) AS String_position

Output Is

String_position
---------------
12


2) DIFFERENCE: DIFFERENCE(Expression1, expression2)
This function is used to find the similarity between two expressions. This is very similar to SOUNDEX in fact this is a wraper over SOUNDEX. This function returns value from 0 to 4. 0 means no similarity and 4 means very close similarity. In simple words this is used to search for Phonetically Similar Data.

SELECT DIFFERENCE('Vijaya','KADIYALA') AS SIMILARITY

Output Is

SIMILARITY
-----------
2

SELECT DIFFERENCE('Vijaya','VJA') AS SIMILARITY

Output Is

SIMILARITY
-----------
4


3) PATINDEX: PATINDEX (expression1 ,expression2)
expression1 Is sequence of characters that to be found in expression2.
In simple words this is used to find the starting position of a string (i.e. expression1) with in another string (expression2). So what is the difference between PATINDEX and CHARINDEX? Well PATINDEX can be used with Wild characters but not with CHARINDEX. This is similar to LIKE operation. The big advantage with this is you can even use Regular Expressions.

SELECT PATINDEX('%y_l%', 'Vijaya Kadiyala') AS String_position

Output Is

String_position
---------------
12

(1 row(s) affected)

SELECT PATINDEX('%a_a%', 'Vijaya Kadiyala') AS String_position

Output Is

String_position
---------------
4

(1 row(s) affected)


SELECT PATINDEX('%a[^y]a%', 'Vijaya Kadiyala') AS String_position

Output Is

String_position
---------------
13

(1 row(s) affected)

4) REPLICATE: REPLICATE (expression ,No_Of_Times)
This function is used to copy the same string again and again based on No_Of_Times parameter.

SELECT REPLICATE('www.DotNetVJ.com',3) as REP

Output Is
REP
------------------------------------------------
www.DotNetVJ.comwww.DotNetVJ.comwww.DotNetVJ.com

(1 row(s) affected)

5) LTRIM: LTRIM(char_expression)
This LTRIM (i.e. LeftTRIM) function is used to trim the spaces or blanks on the left side of the string.

SELECT ( '*' + LTRIM (' STEVE ') + '*') AS ltrim1

Output Is

ltrim1
-------------
*STEVE *

(1 row(s) affected)


6) RTRIM: RTRIM(char_expression)
This RTRIM (i.e. RightTRIM) function is used to trim the spaces or blanks on the Right side of the string.

SELECT ( '*' + LTRIM (' STEVE ') + '*') AS ltrim1

Output Is

ltrim1
-------------
*STEVE *

(1 row(s) affected)

Now if you want to trim on both the sides then you need to use LTRIM and RTRIM.


7) LEN: LEN(expression)
The LEN (i.e. LENgth) function returns the length of the expression.
One important point that we need to consider here is the leading blanks are included in the calculation, while trailing blanks are not.


Let’s look at the below example:


SELECT LEN(' VIJAYA ') AS len1

Output Is


len1
-----------
7

(1 row(s) affected)


The above query returns 7, taking into consideration the one leading blank, but ignoring the four trailing blanks.

With this I will end this article, hoping that I have covered the string functions that we use in our day-to-day life.

Thursday, May 21, 2009

Very Common SQL Server Errors Part 1

In this article I would like to list out the Top 5 errors that we get when working with DML commands.

Error: 1
Msg 109, Level 15, State 1, Line 1
There are more columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.


Cause:
When you have Column names in INSERT clause and you didn’t provide all the values to the columns, which are specified in INSERT clause.

How to re-produce:
--Lets Create demo table
CREATE TABLE Emp (Emp_ID INT NOT NULL,ENAME VARCHAR(5),SALARY INT)

--Lets Insert data
insert into Emp(Emp_ID,ENAME,SALARY) VALUES(1,'vijay')

If you look at the INSERT statement I didn’t provide values to all the columns in INSERT statement.

Solution:
Make sure you provide values to all the columns in the INSERT statement.

Error: 2
Msg 213, Level 16, State 1, Line 1
Column name or number of supplied values does not match table definition.

Cause:
You will get this error, when you do not provide values to all the columns in a table.


How to re-produce:
--Lets Create demo table
CREATE TABLE Emp (Emp_ID INT NOT NULL,ENAME VARCHAR(5),SALARY INT)

--Lets Insert data
insert into Emp VALUES(1,'vijay')

If you look at the INSERT statement I didn’t provide values to the all the columns in EMP table.

Solution:
Make sure you provide values to all the columns in the table definition.


Error: 3
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Emp'. Cannot insert duplicate key in object 'dbo.Emp'.
The statement has been terminated.

Cause:
You will get this error, when you try to insert duplicate records into the table.


How to re-produce:
--Lets Create demo table
CREATE TABLE Emp (Emp_ID INT NOT NULL primary key,ENAME VARCHAR(5),SALARY INT)

--Lets Insert data
insert into Emp VALUES(1,'vijay',12300)
insert into Emp VALUES(1,'kadiyala',1300)

The first insert statement gets inserted into the table but second one will fail because we have primary Key constraint on Emp_ID column.

Solution:
Make sure you don’t provide duplicate values. Try to use IDENTITY property or have constraint at the application level.

Error: 4
Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'Emp' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Cause:
You will get this error, when you try to insert values into Identity column.

How to re-produce:
--Lets Create demo table
CREATE TABLE Emp (Emp_ID INT IDENTITY NOT NULL ,ENAME VARCHAR(5),SALARY INT)

--Lets Insert data
insert into Emp VALUES(1,'vijay',12300)

You can’t insert value into Identity column in straight pass.

Solution:
If you want to insert the data into Identity column then you need to turn IDENTITY_INSERT ON.

Error: 5
Msg 8152, Level 16, State 14, Line 1
String or binary data would be truncated.
The statement has been terminated.

Cause:
You will get this error, when the Value length in INSERT statement exceeds the length defined at the time of Table creation.

How to re-produce:
--Lets Create demo table
CREATE TABLE Emp (Emp_ID INT IDENTITY NOT NULL ,ENAME VARCHAR(5),SALARY INT)

--Lets Insert data
insert into Emp VALUES('vijay',12300)
insert into Emp VALUES('Kadiyala',12300)

The 2nd INSERT will fail because length of the ENAME column is declared as 5 Characters but the supplied value length is 8 Characters.

Solution:
Either you increase length of the column or shorten the length of the value.

Wednesday, May 20, 2009

Exception Handling at the Table Level

I got a very interesting question from my client asking about the way to implement exception handling at the table level, that is if we insert duplicate records into a table then SQL Server should not throw any Primary Key violation errors.

This is very interesting and challenging question.

--Create a demo table
CREATE TABLE Customers(
Customer_ID int NOT NULL,
Customer_Name varchar(30) NOT NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[Customer_ID] ASC
)
)

--Let’s insert dummy data
INSERT INTO Customers VALUES(1,'VIJAYA KADIYALA')
INSERT INTO Customers VALUES(2,'VIJAYA KRISHNA')
INSERT INTO Customers VALUES(1,'VIJAYA KADIYALA')

When you try to insert 3rd record you will get below error.
Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK_Customers'. Cannot insert duplicate key in object 'dbo.Customers'.
The statement has been terminated.

This is very common behavior of any database. My task is to supress this error message. Initially I thought it is impossible to do it. But the Impossible word itself has “Possible”, so nothing is impossible. After closey observing the various parameters used in Table creation script, finally my search ended with IGNORE_DUP_KEY parameter.

I am not sure how many people would have got a chance to understand this parameter. This is the parameter which tells the SQL Server to throw an error message when it violates the constraints. By default this is set Off. We need to turn it on to enable exception handling at the constraint level.

-- Lets re-create demo table
CREATE TABLE Customers(
Customer_ID int NOT NULL,
Customer_Name varchar(30) NOT NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
(
[Customer_ID] ASC
)WITH (IGNORE_DUP_KEY = ON)
)

--Lets insert dummy data
INSERT INTO Customers VALUES(1,'VIJAYA KADIYALA')
INSERT INTO Customers VALUES(2,'VIJAYA KRISHNA')
INSERT INTO Customers VALUES(1,'VIJAYA KADIYALA')

When you try to insert 3rd record you will get below message.

Duplicate key was ignored.
(0 row(s) affected)


You can also use this clause at the Index Creation script level.
Ex:
CREATE UNIQUE NONCLUSTERED INDEX UNiDX_Customers ON Customers(Customer_Name) WITH (IGNORE_DUP_KEY = ON)

Tuesday, May 19, 2009

TOP 5 ways to delete Duplicate Records in a Table

Today I got an email asking how to delete duplicate records in a table? This is very common and very interesting question. The perfect answer to this question depends on the following points:
1) Volume of the data
2) Downtime of the system
3) Dependency on the table
4) Restrictions on writing the code.

I am sure every database developer deals with “Delete Duplicate Records” issue at least once in a lifetime.

There are so many ways we can eliminate the duplicate data from a table. In this article I would like to explain various ways to delete the duplicate records.

--Create Demo Table
CREATE TABLE #Employee
(
EMP_ID INT,
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30)
)

--Insert Dummy Data
INSERT INTO #Employee Values(1,'Stefan','Vachev');
INSERT INTO #Employee Values(2,'Michael','Allen');
INSERT INTO #Employee Values(3,'Glenn','Jhonson');
INSERT INTO #Employee Values(4,'Ray','Muran');
INSERT INTO #Employee Values(1,'Stefan','Vachev');
INSERT INTO #Employee Values(2,'Michael','Allen');
INSERT INTO #Employee Values(3,'Glenn','Jhonson');
INSERT INTO #Employee Values(4,'Ray','Muran');
INSERT INTO #Employee Values(5,'Vijaya','Kadiyala');
INSERT INTO #Employee Values(5,'Vijaya','Kadiyala');
INSERT INTO #Employee Values(6,'Alexi','Kal');
INSERT INTO #Employee Values(5,'Vijaya','Kadiyala');


Method 1: Using Temporary Table based on all the columns

select * into #DuplicateEmployee from #Employee where 1 = 0
insert #DuplicateEmployee select distinct * from #Employee
begin tran
delete #Employee
insert #Employee select * from #DuplicateEmployee
commit tran
drop table #DuplicateEmployee

This is method is used when all the columns are duplicated.

Method 2: Using Temporary Table based on set of columns

select * into #DuplicateEmployee from #Employee where 1 = 0
insert #DuplicateEmployee select EMP_ID,FIRST_NAME,LAST_NAME from #Employee group by EMP_ID,FIRST_NAME,LAST_NAME having count(*) > 1
begin tran
delete #Employee from #DuplicateEmployee
where #Employee.EMP_ID = #DuplicateEmployee.EMP_ID
and #Employee.FIRST_NAME = #DuplicateEmployee.FIRST_NAME
and #Employee.LAST_NAME = #DuplicateEmployee.LAST_NAME
insert #Employee select * from #DuplicateEmployee
commit tran
drop table #DuplicateEmployee


Method 3: Using Row Count based on set of columns

set rowcount 1
select 1
while @@rowcount > 0
delete #Employee
where 1 < (select count(*)
from #Employee a2
where #Employee.EMP_ID = a2.EMP_ID
and #Employee.FIRST_NAME = a2.FIRST_NAME
and #Employee.LAST_NAME = a2.LAST_NAME)
set rowcount 0

Method 4: Using Analytical Functions based on set of columns

WITH #DeleteEmployee AS (
SELECT ROW_NUMBER ( )
OVER ( PARTITION BY Emp_ID, First_Name, Last_Name ORDER BY Emp_ID ) AS RNUM FROM #Employee
)
DELETE FROM #DeleteEmployee WHERE RNUM > 1


Method 5: Using New Identity Column based on set of columns

Alter table #Employee add UNIQ_ID int IDENTITY(1,1)

DELETE FROM #Employee
WHERE UNIQ_ID < (SELECT MAX(UNIQ_ID) FROM #Employee a2
where #Employee.EMP_ID = a2.EMP_ID
and #Employee.FIRST_NAME = a2.FIRST_NAME
and #Employee.LAST_NAME = a2.LAST_NAME)

Alter table #Employee DROP column UNIQ_ID

Monday, May 18, 2009

Filtered indexes in SQL Server 2008

One thing which I always wanted to see is, can I create an index with selective list on a column? Lets say I have one table called Employee and 80% of the cases I query this table based on Dept_Name = ‘HR’.

SELECT Emp_id,Name FROM EMPLOYEE WHERE DEPT_Name = ‘HR’.

By looking at the query, DEPT_Name is right column to create an index. In addition to this I always retrieve the Emp_ID, Name columns from Employee. So I would like to include these columns as leaf level nodes in my index definition using Covering Index feature in SQL Server.

--Create demo table
CREATE TABLE Employee(
Emp_ID INT IDENTITY(1,1),
Name VARCHAR(30),
Date_Of_Join DATETIME,
Dept_Name VARCHAR(10))

-- Lets insert some dummy data
DECLARE @i INT = 100000
DECLARE @Dept VARCHAR(30)
WHILE @i > 0
BEGIN
IF @i <= 500

BEGIN SET @Dept = NULL
END
IF @i <= 500
BEGIN
SET @Dept = 'HR'
END
INSERT INTO Employee(Name,Date_Of_Join,Dept_Name)
VALUES ('TIM' + CAST(@i as varchar),GETDATE(),@Dept)
SET @i = @i - 1
END


Let’s create NonClusttered Covering Index
CREATE NONCLUSTERED INDEX iNdx_Employee ON dbo.Employee(Dept_Name)
INCLUDE (Emp_id,Name)


Lets the run the below query see the execution plan
SELECT Emp_ID,Name FROM Employee WHERE Dept_Name = 'HR'




If you look at the execution plan it is going for Index Scan and
Estimated SubTree Cost is 0.0045542, Estimated CPU Cost is 0.000707 and Estimated I/O Cost is 0.0038472. Which is very good.
But still I am not satisfied with the performance!! So is there any way to improve the query performance with out changing the table structure or the query??

This is where we can utilize the “Filter Index” feature in SQL Server 2008 where you can create the Index based on the list of values. This is very simple just you need to put WHERE clause to the Index creation.

CREATE NONCLUSTERED INDEX iNdx_Employee ON dbo.Employee(Dept_Name)
INCLUDE (Emp_id,Name)
WHERE Dept_Name = 'HR'


Lets the run the below query see the execution plan
SELECT Emp_ID,Name FROM Employee WHERE Dept_Name = 'HR'



If you look at the execution plan it is going for Index Seek and
Estimated SubTree Cost is 0.003832, Estimated CPU Cost is 0.000707 and Estimated I/O Cost is 0.003125. Which is extremely good.


Guidelines to create Filtered Index:
1) When you are querying the smaller sub-set of data very frequently

Advantages:
1) Improved Query performance
2) Less overhead on Index Maintenance

Restrictions on Filtered Index:
1) Must be on Non Clustered Columns
2) Can’t be created on Computed Columns.

Thursday, May 14, 2009

String Functions in SQL Server Part 1

In this article I would like to give you an overview on various string functions that are available in SQL Server and are widley used at work.

In this IT world 50% of the databases consist of CHAR, NCHAR, VARCHAR, and NVARCHAR as data types to store the values. When you are joining the tables based on the string family columns then you need to use string functions in order to return right output from your queries or modify t he out put to make it more meaningful.

Why do we need to use string functions?
Instead of writing couple of lines explaining about this, I would like explain with a simple example.
Let’s look at the below table:

CREATE TABLE EMPLOYEE
(EmployeeID INT,
FirstName VARCHAR(10),
MiddleName VARCHAR(10),
LastName VARCHAR(10)
);


Use the below statements to insert the data:

Insert into EMPLOYEE (EMPLOYEEID, FIRSTNAME, MIDDLENAME, LASTNAME)
Values (1, 'steve', 'Kris', 'Burg');
Insert into EMPLOYEE (EMPLOYEEID, FIRSTNAME, MIDDLENAME, LASTNAME)
Values (2, 'StEve', 'NICK', 'ross');
Insert into EMPLOYEE (EMPLOYEEID, FIRSTNAME, MIDDLENAME, LASTNAME)
Values (3, 'STEVE', 'A', 'FOX');

If you look at the data, Names are stored in mixed case.
EmployeeID = 3 stored in upper case, EmployeeID = 2 stored in mixed case and EmployeeID = 1 in Title Case.
So if you want to find out all the employees whose first name is “steve” with out looking at the case? This is where string functions come into the picture.

1) UPPER : UPPER(Input)
The UPPER() function converts all the input characters in a string into uppercase.
Let's look at the below example where we are converting the FirstName to upper case.

SELECT UPPER(FirstName) as FirstName FROM EMPLOYEE

FirstName
-------------
STEVE
STEVE
STEVE

2) SUBSTRING: SUBSTRING(expression,start_integer,length_integer)
The SUBSTRING() function is bascailly returns a part of the string based on the starting point to specified number of characters.

SELECT SUBSTRING(FirstName,1,3) as First_3 FROM Employee

First_3
-----------
ste
StE
STE

The function returned three characters from the FirstName

3) STUFF : STUFF(char_expression1,start_integer,length_integer,char_expression2)

The STUFF() function is very similar to REPLACE function where it is used replaces certain characters not based on the pattern but based on the starting position and length. It replaces characters in char_expression1 based on length_integer starting from start_integer with the char_expression2.

Let's look at the example:

SELECT
STUFF('STEVESTEVE',6,5,NULL) AS remove_multiple1,
STUFF('STEVESTEVE',6,5,'') AS remove_multiple2
remove_multiple1 remove_multiple2
-------------------- ----------------
STEVE STEVE

4) SPACE: SPACE(expression)
This function is used to return No.Of spaces or blanks based on the expression.
SELECT LEN(SPACE(24) + 'v') AS No_of_Spaces No_of_Spaces
---------------
25

We will continue next set of string functions in my next article.

Wednesday, May 13, 2009

Covering Index in SQL Server

There is a new type of index called “Covering Index” in SQL Server 2005. This feature gives us the option to put a Non-indexed column to the leaf node of the indexed column. If I am confusing lets look at the below example.

Let’s create a table
--Create demo table
CREATE TABLE Employee
(Emp_ID INT IDENTITY(1,1),
Name VARCHAR(30),
Date_Of_Join DATETIME,
Dept_id INT
)


Let’s insert some dummy data into this table
DECLARE @i INT
SET @i = 1000
WHILE @i > 0
BEGIN
INSERT INTO Employee(Name,Date_Of_Join,Dept_id) VALUES ('TIM' + CAST(@i as varchar),GETDATE(),@i%10)
SET @i = @i - 1
END




Let’s create Non Clustered Index on Emp_ID, Name column.
CREATE NONCLUSTERED INDEX IDX_EMPLOYEE ON EMPLOYEE(DEPT_ID)

Execute the below query and see the execution plan.

SELECT Emp_ID,NAME FROM Employee WHERE Dept_id = 9





If you look at the execution plan it is going for Full table scan because you don’t have index on Dept_ID column. So what we normally do is we add this column to the index definition. This certainly increases maintenance overhead and also if we try to add these various combinations then we will hit max no. of indexes on a table or a max index size etc.

Instead of this, we can make use of Covering Index feature, then with out adding Dept_id column to the Index definition, Optimizer would still pickup the index to return the data.

Let’s create a Covering Index:
CREATE NONCLUSTERED INDEX IDX_EMPLOYEE ON EMPLOYEE(EMP_ID,NAME) INCLUDE (DEPT_ID)

You can create Covering index only for Non Clustered Index types.

Execute the below query and see the execution plan.
SELECT Emp_ID,NAME FROM Employee WHERE Dept_id = 9


Declaring a variable in SQL Server 2008

Previous release of the SQL Server we used to have two lines code to declare a variable and assign a value to that variable.
Ex:
DECLARE @i INT
SET @i = 10000

From 2008 onwards, you can declare and assign a value in single line
Ex:
DECLARE @i INT =10000

This is one of the new feature in SQL Server 2008.

Tuesday, May 12, 2009

Generate and Insert the data into a table in SQL Server

Today, I would like to give you a very simple tip to generate and insert the data into a table in few seconds.

Let’s create a table:

--Create demo table
CREATE TABLE Employee
(Emp_ID INT IDENTITY(1,1),
Name VARCHAR(30),
Date_Of_Join DATETIME,
Dept_id INT
)


Lets write simple code to generate and insert the data using WHILE loop.

DECLARE @i INT
SET @i = 10000
WHILE @i > 0
BEGIN
INSERT INTO Employee(Name,Date_Of_Join,Dept_id)
VALUES ('TIM' + CAST(@i as varchar),GETDATE(),@i%10)
SET @i = @i - 1
END

Explanation:
1) Created a variable and assigned value 10000.
2) Used while to loop insert the data.

That’s it :)

Getting Started with Windows Azure on Thursday

Windows Azure is a cloud services operating system that serves as the development, service hosting and service management environment for the Azure Services Platform.
For more information please refer to the below link.
http://www.microsoft.com/azure/windowsazure.mspx

There is a Session from "Erik Stepp" on "Getting Started with Windows Azure" as part of NY .Net User Group.

Visit the below link for more information.
http://www.nycdotnetdev.com/EventDetail.aspx?f=list&event=5/21/2009

Monday, May 11, 2009

Non Clustered Index in SQL Server

In my previous article on "Clustered Index in SQL Server" we talked about creating a Clustered Index. In this article we will talk about creating a Non-Clustered Index.

Non Clustered Index:
Non Clustered index is a separate object. This doesn’t depend on how the data is stored in the table. So you can have more the one index on a table.

When you create a primary key on any table by default SQL Server creates the clustered index. To avoid this you need to use NONCLUSTERED keyword in the primary key creation script.

Create a Primary Key with Non Clustered Index:
1) First let’s create a student table
CREATE TABLE STUDENT (
STUDENT_ID INT IDENTITY(1,1),
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30))

2) Lets define Primary key on STUDENT_ID column
ALTER TABLE STUDENT
ADD CONSTRAINT PK_STUDENT PRIMARY KEY NONCLUSTERED (STUDENT_ID)

Create a Non Clustered Index on Non Primary Key Column:
1) First let’s create a student table
CREATE TABLE STUDENT_EXAM_RESULTS (
STUDENT_ID INT IDENTITY(1,1),
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30),
RANK INT)

2) Let’s define Non clustered Index on RANK column
CREATE NONCLUSTERED INDEX cidx_rank ON STUDENT_EXAM_RESULTS (RANK)

Where Indexes should be used:
1) Columns often used in WHERE conditions of the query.

Where Indexes should not be used:
1) When the table is very small having less than 400 Records.
Ex: You have a COUNTRY table. This table stores around 150 Countries information. So you don’t need an index on this table in order to access the data.

2) Don’t create the index on the free flow text columns.
Ex: You have one table called PROJECT with Project_Name,Project_Desc columns.
CREATE TABLE PROJECT
( PROJECT_ID INT,
PROJECT_NAME VARCHAR(30),
PROJECT_DESC VARCHAR(300)
)
Don’t create the index on PROJECT_DESC column.

3) Don’t create too many indexes and also don’t duplicate the indexes. These will slowdown your DML operations.
Ex: If you have 30 indexes on a table, when user issues a UPDATE statement, based on the updating column SQL Server has to update all the indexes which depends on this column.

Saturday, May 9, 2009

SQL Server Performance Tuning Training on Saturday

Start Date & Time: 5/16/2009 8:15:00 AM EST
End Date & Time: 5/16/2009 3:00:00 PM EST

Performance tuning is very important topic in SQL Server from developer point of view. There is a Traning session from Microsoft Most Valuable Professionals (MVP) on various topics with in SQL Server Performance Tuning.

This training is part of NJ SQL Server User group and it is absolutely FREE. It doesn't get better than this.....

Registration Link: https://www.clicktoattend.com/invitation.aspx?code=137968

Hurry up there are very few seats available.

Agenda:
8:15am - 9:00am: Attendee Check-In and Breakfast
9:00am - 9:15am: Welcome
9:15am - 10:15am: Stress Testing SQL Server (Hilary Cotter - MVP)
10:15am - 10:30am: Break
10:30am - 11:30am: Query Plan Optimization / T-SQL Performance Tricks (Alec Lazarescu)
11:30am - 12:15pm: Lunch
12:15pm - 1:15pm: Performance tuning XML and XQuery in SQL Server 2008 (Michael Coles - MVP)
1:15pm - 2:15pm: Disk I/O Related Performance Tuning (Linchi Shea - MVP)
2:30pm - 2:45pm: Wrap-Up and Raffle/Swag
Note: Agenda subject to change, please see www.njsql.org for updates.

Seminar Location:
Microsoft Corporation
194 Wood Ave S
Prudential Building, Sixth Floor
Iselin, NJ 08830
USA

Friday, May 8, 2009

Required Field Validation Control in ASP.NET

Required Field Validator:

Suppose in a form,there are some fields which have to be mandatory to the user i.e the user should fill in those fields compulsorily.In that case we can use Required field validator.

Lets say there is a textbox for user to enter his name with id "txtName" and this is mandatory field,then a required field validation control can be used like below:

The required field validator has attribute "ControlToValidate" which is set to the id of the control.If the user does not enter value in the textbox,then an error message "Name is required" is shown to the user.


Thursday, May 7, 2009

60 seconds with Jacob Sebastian

60 seconds with Jacob Sebastian:


VJ caught up with Jacob Sebastian, MVP in SQL Server, to get his view on the Microsoft Most Valuable Professional Program(MVP).


VJ: Please Tell us about your self.
Jacob:
I am a SQL Server Consultant based in Ahmedabad, India. I am a Microsoft MVP (SQL Server) and a Moderator at MSDN and Technet Forums. I volunteer with the Professional Association for SQL Server (PASS) as PASS Regional Chapter Coordinator for Asia and I run a SQL Server User Group in Ahmedabad, India. I am a regular columnist at SQLServerCentral and I blog regularly at http://beyondrelational.com/blogs/jacob/default.aspx. I just completed my first book; "The Art of XSD - SQL Server XML SChema Collections" and I am a contributing author in Paul Nielson's book: "SQL Server 2008 Bibile."I started my database career in the early nineties with Dbase and then moved to Clipper, Foxpro and finally to SQL Server. I have been working with SQL Server for over 11 years starting with SQL Server 6.5. I am a regular speaker at local User Groups and SQL Server Events. I am also a SQL Server trainer and I teach at various SQL Server classes across the country.

VJ: What motivated you to become an MVP
Jacob:
I don’t have a clear answer to this question, but I am very grateful to Microsoft giving me the MVP award and my MVP friends who nominated me and supported me always.

VJ: Please share with us your journey of becoming an MVP.
Jacob: I had been contributing to the SQL Server community through my blogs, articles at SqlserverCentral and on the MSDN forums. I think this is what brought me the MVP award.

VJ: Tell us about your MVP Summit experience
Jacob:
The MVP Summit experience was great. The most interesting part was the meeting with the SQL Server Product Team. We spent a number of quality hours at Microsoft campus and it was just great.

VJ: Any message to technologists
Jacob: Almost all of us have a very tight work schedule and finding time for learning is really hard. I would like to encourage everyone to plan the schedules in such a way so as to allocate some time for learning regularly.

Clustered Index in SQL Server

In this article I would like to talk about Indexes in SQL Server. Indexes are used to improve the performance of the queries. Database Index is similar to the one at the back of any book. You have one big book with 1000 pages and want to find information about Transactions then either you can read page by page till you find the information or just open the Index page and look for the information and use that information to find the full length content.

Index is a separate object by itself. SQL Server automatically updates the indexes based on any data modifications to the table data.

However, you need to maintain the indexes for better performance.

There are mainly two types of indexes:
1) Clustered Index.
2) Non-Clustered Index.

The only differentiating factor between them is interims of storage.

Clustered Index:
Clustered index is a special type of index which forces the SQL Server to store the data based on the Cluster Key. Cluster key is nothing but the columns in the index. When you create the Clustered Index on a table, SQL Server physically orders the database on the Cluster Key. Table can be physically sorted in only one way. So that’s the reason you can have only one clustered index per table.

When you create a primary key on any table, by default SQL Server creates the clustered index.

Create a Clustered Index on Primary Key Column:

1) First let’s create a student table
CREATE TABLE STUDENT (
STUDENT_ID INT IDENTITY(1,1),
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30))

2) Lets define Primary key on STUDENT_ID column
ALTER TABLE STUDENT
ADD CONSTRAINT PK_STUDENT PRIMARY KEY (STUDENT_ID)

Create a Clustered Index on Non Primary Key Column:

1) First let’s create a student table
CREATE TABLE STUDENT_EXAM_RESULTS (
STUDENT_ID INT IDENTITY(1,1),
FIRST_NAME VARCHAR(30),
LAST_NAME VARCHAR(30),
RANK INT)


2) Let’s define Non clustered primary key on STUDENT_ID column
ALTER TABLE STUDENT_EXAM_RESULTS
ADD CONSTRAINT PK_ STUDENT_EXAM_RESULTS PRIMARY KEY NONCLUSTERED (STUDENT_ID)


3) Lets create a Clustered Index on RANK column.
CREATE CLUSTERED INDEX cidx_rank ON STUDENT_EXAM_RESULTS (RANK)


Clustered Index Design Guidelines:
You can have only one clustered index per table so you have to be very careful interims of selecting the right column and right index type.
Guidelines are:
1) Querying the table based on range of values
2) No updates to the cluster key columns.

Wednesday, May 6, 2009

Useful Stored procedures in SQL Server -- Final Part

6) SP_HELPCONSTRAINT: SP_HELPCONSTRAINTS [TABLE]
The sp_helpconstraint stored procedure is used to display information about the constraints created on the columns based on the input parameter. Constraint information like contains type, name, delete action, update action, and status of constraints. The below sample code displays the constraints information on Employee Table.
USE Pubs;
GO
EXEC sp_helpconstraint 'HR.Employees';
GO

7) SP_RENAME: SP_RENAME OLD_NAME, NEW_NAME, TYPE_OF_OBJECT
The sp_rename stored procedure is used to modify the names of database objects or user-defined data types. In SQL Server each object is associated with Object ID. When you provide a new name for an existing database object, other database objects using the renamed object continue to refer to the renamed object.
old_name: parameter refers to the existing object name.
new_name: parameter refers to the name you want to assign.
type_of_object: parameter refers to the type of database object whose name you want to modify.
Renaming a table
USE Pubs
GO
EXEC sp_rename 'HR.Employees', 'EMP';
GO

Renaming a column
USE Pubs
GO
EXEC sp_rename 'HR.Employees.EmployeeID', 'EMPID', 'COLUMN';
GO

8) SP_PASSWORD: SP_PASSWORD OLD_PASS,NEW_PASS [, LOGIN_ID]
Your password is shared and wants to change the password then you need to use sp_password stored procedure. The sp_password stored procedure is used to modify the password of a login ID. You need to pass the old password of a login ID as a parameter to create a new password. You need to specify the old password as NULL when you modify the password of the SA login ID. Microsoft is planning to remove this stored procedure usage in future versions and suggesting us to use “ALTER LOGIN” command.

9) SP_WHO
The sp_who stored procedure is used to display all database users who are currently logged onto SQL Server. The stored procedure also displays the processes currently running on SQL Server. Look at the below example to report all current users
USE master
GO
EXEC sp_who
GO

10) SP_PRIMARYKEY: SP_PRIMARYKEY TABLE,COLUMN1[, COLUMN2,....COLUMN(N)]
If you don’t know the syntax of primary key creation then this is the right procedure which you can use to create primary key. This stored procedure is used to apply the primary key constraint on a column or columns that is passed as a parameter. The column1, column2, and column(n) parameters contain the column names on which you want to create the primary key constraint. A primary key can contain more than one column.
Use Pubs
GO
SP_PRIMARYKEY 'HR.EMP','empid
GO

11) SP_RECOMPILE: SP_RECOMPILE
The sp_recompile stored procedure is used to recompile certain database objects. When you create a database object for the first time, the database object is compiled and a query plan is generated if all the dependencies are met. If you change the data in database objects, the stored procedures and triggers should be recompiled because the query plans generated for the database objects are no longer valid and new query plans should be generated. The below example causes stored procedures that are using EMP table to be recompiled the next time they are run.
USE Pubs;
GO
EXEC sp_recompile N'HR.EMP'
GO

Tuesday, May 5, 2009

Useful Stored procedures in SQL Server Part 1

There are several predefined stored procedures are available in SQL Server which we are using in our day-to-day life to resolve some of the issues like below.

1) I want to see the all columns of the tables.
2) I want to see who are all logged into Database.
3) I want to see the Constraint information.
4) I want to see all the indexes which are created on a table.
5) I want to rename the objects etc.

Today i am going to cover the most common stored procedures that we need in our day-to-day work.

1) SP_RENAMEDB: SP_RENAMEDB OLD_NAME, NEW_NAME
This stored procedure is to rename the database from old names to new name.
Microsoft is planning to remove this from future releases and also suggesting to use ALTER DATABASE command to implement the similar functionality.
Let’s look at the sample example
USE MASTER;
GO
CREATE DATABASE BANK_DB;
GO
EXEC SP_RENAMEDB N'BANK_DB', N'INVESTMENT_BANK_DB';
GO
SELECT NAME, DATABASE_ID, MODIFIED_DATE
FROM SYS.DATABASES
WHERE NAME = N'INVESTMENT_BANK_DB';
GO

We are creating a Database name called BANK_DB and then later we are modifying/renaming it to INVESTMENT_BANK_DB. Query the SYS.DATABASES table to see whether it’s modified or not.

2) SP_HELPUSER: SP_HELPUSER [NAME_OF_USER]
The sp_helpuser stored procedure is used to display the user(s) and role(s) present in a database based on the input. Name of the user is optional filed; if you pass a specific user then it displays information, such as user name, login name, group name, default database, User_ID, and sid of the user. The procedure displays information about the current database user if no parameter is passed.
EXEC sp_helpuser
The above stored procedure is used to display all the users in the current database.

3) SP_HELP: SP_HELP [OBJECT USER_DEFINED_DATATYPE]
The sp_help stored procedure is used to display information about a database object that is passed as a parameter. The parameter is optional. If you want to display all the objects with in the current database then don’t pass any parameter. If you are looking for specific objects information then pass the object name.
USE master
GO
EXEC sp_help
The above code is used to display information about each object in Sysobjects.


4) SP_COLUMNS: SP_COLUMNS OBJECT, [OWNER_NAME], [TABLE_QUALIFIER], [COLUMN_NAME], [ODBCVER]
The sp_columns stored procedure is used to display information about table columns or view columns. The procedure accepts the table or view name as a parameter.
OBJECT: This is nothing but table or view.
OWNER_NAME: Is nothing but owner of object. This is optional Parameter.
TABLE_QUALIFIER: parameter refers to the database name in which the table or view exists. This is optional Parameter.
COLUMN_NAME: parameter specifies the name of a column that you pass as a parameter whose information is to be displayed.
ODBCVER: Parameter specifies the ODBC version used.
USE Pubs
GO
EXEC sp_columns @table_name = N'Employee', @table_owner = N'HR';

5) SP_HELPINDEX: SP_HELPINDEX (TABLE)
The sp_helpindex stored procedure is used to display the indexes that are created on a table based on the input parameter. In addition to the indexes it will also display the columns which are part of the indexes.
USE Pubs;
GO
EXEC sp_helpindex N'HR.Employee';
GO

Keep watching for more information

.NET Tutorials

Populating PDF from ASP.Net using iTextSharp
Validate Email Address using Regular Expression Control in ASP.NET
Validate Numbers Using Regular Expression in ASP.NET
Validate ZIP in Java Script
Required Field Validation Control in ASP.NET
Setting a connection string in Web.Config
Google Maps in ASP.NET
code analysis for C#
Code Analysis in .Net
Reverse String in C#
NUnit

Populating PDF from ASP.Net using iTextSharp

iTextSharp is a very popular third party library which is used by programs that write to PDF files. Suppose there is a requirement where we want to write data into PDF files.Then we use iTextSharp.

The steps in doing this:
1.Download iTextSharp DLL.
2.In Visual Studio,create a project and add iTextSharp DLL as a reference.
3.Then add directive "Imports iTextSharp.text.pdf" to the top of the form.
4. A reader in iTextSharp is represented by the appropriately-named PDFReader object.Then we have to instantiate the PDFReader object.
5.Then we have to get the PDF file into stamper object.
6.Setting PDF form fields.

dim reader as new PdfReader("Specify Pdf path")
Using fs as new FileStream("PDF PATH",FileMode.Create)
Dim stamper as new PDFStamper(reader,fs)
Dim fields as AcroFields=stamper.AcroFields
fields.SetField("PDF Field Name","Data you want to write")

Suppose you have a field in PDF form with ID "txtName"
fields.SetField("txtName","Vijay")




Thats it :)

Monday, May 4, 2009

Alter Identity Column Value in SQL Server

In my previous article I talked about creating identity column and insert data into Identity column. In this article I would like to talk about following topics:

1) Identity columns in a database
2) Current Identity Column value
3) Alter Identity Columns
1) Find all the Identity Columns in SQL Server:

SELECT Table_name,Column_Name
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE COLUMNPROPERTY(
OBJECT_ID(c.TABLE_NAME),
c.COLUMN_NAME,'IsIdentity') = 1



2) Find the Current Identity Column value
Now take any table name from the above list and call CHECKINDENT function.

Listing 1:
DBCC CHECKIDENT ('DOMAIN', NORESEED)
Output is:
Checking identity information: current identity value '7', current column value '7'.DBCC execution completed. If DBCC printed error messages, contact your system administrator.


Listing 2:
DBCC CHECKIDENT ('CUSTOMER', NORESEED)
If you try to pass a table which doesn’t have the Identity column then it will return following error message.
Msg 7997, Level 16, State 1, Line 1'CUSTOMER' does not contain an identity column.

3) Alter Identity Column Value
Listing 1:
DBCC CHECKIDENT ('DOMAIN', RESEED,999)
Output is:
Checking identity information: current identity value '99', current column value '999'. DBCC execution completed. If DBCC printed error messages, contact your system administrator.
So when you insert next value it will start 999+1.

Sunday, May 3, 2009

SQL Server Scripts

This section contains all the use ful scripts that we need in our day-to-day life
Find out all the Identity Key columns in SQL Server
List all the Primary Keys in SQL Server Database
List all the Foreign Keys in a SQL Server
Foreign Key and Primary Key in SQL Server
Finf out all the child tables of a parent table

SQL Server Interview Questions

This section contains all the interview questions on various concepts in SQL Server.

SQL Server Interview Questions -- Constraints

SQL Server Interview Questions -- Constraints

1) What is a Primary Key?
A Primary Key in a table uniquely identifies each and every row with out allowing Nulls. There are two types of Primary Keys:
1.1) Simple Primary Key ==> Defining primary key on a single column is called Simple Primary Key.
1.2) Composite Primary Key==> Defining Primary Key on more than one column is called Composite Primary Key.

2) What is Unique Key?
A Unique Key in a table uniquely identifies each and every row and allowing Nulls per column Combination. There are two types of Unqiue Keys:
1.1) Simple Unique Key ==> Defining Unique key on a single column is called Simple Unique Key.
1.2) Composite Unique Key==> Defining Unique Key on more than one column is called Composite Unique Key.

3) What is the difference between Primary Key and Unique Key?
3.1)Primary Key by definition cannot be null, where as unique key can accept null values but if the unique key is defined on a column which is not null , then this unique key can also be used as an alternate primary key functionality to identify unique rows in a table.
3.2)By definition you can have only one primary key defined on a table where as you can have multiple unique keys defined on a table
3.3)Also by default Primary key is created as clustured index and unique key is created as non clustered index.

4) What is Check Constraint?
Check constraint specifies a condition that is enforced for each row of the table on which the constraint is defined. Once constraint is defined, insert or update to the data within the tables is checked against the defined constraint.

5) What is RULE?
A rule specifies the acceptable values that can be inserted into a column. This is similar to CHECK constraint.

6) What is the difference between RULE and Check Constraint?
The major difference between rule and Check is reusability. Check constraint is associated with columns in a Table. So these can't be re-used. Rules are defined with in a database and can be applied to any number of columns.

7) How to create a relationship between two tables?
Using Foreign Keys. Create a Foreign Key on child table referencing Unique Key or Primary key of Parent table.

8) Can we create a Foreign Key with out Primary Key?
Yes. If the table has Unique Key then it is posible to create a Foreign key constraint.

9) What is the table name,that contains the Primary Key, Unique Key and Foreign Key Information?
INFORMATION_SCHEMA.TABLE_CONSTRAINTS, where CONSTRAINT_TYPE column stores the information of Constraint Type.

10) What is the table name, that contains Check Constraint Definition?INFORMATION_SCHEMA.CHECK_CONSTRAINTS, where CHECK_CLAUSE column stores the Definition of Check Constraint.

11) What is the table name, that contains RULE Constraint definition?
Syscomments is the table which contains the RULE definition.Use the below query to get the information.
select name as Rule_Name,sc.TEXT as Rule_Definition
from sysobjects so inner join syscomments scon so.id = sc.idwhere so.xtype='R'

12) Can we have RULE and Check Constraint on the same column?
YES

13) Can we apply Integrity Constraints on Computed Columns?
YES

14) Can you write a query to find out the Child Table Name and Parent Table Name and the Columns to join?
SELECT TC.CONSTRAINT_NAME AS FOREIGN_KEY_CONSTRAINT_NAME,TC.TABLE_NAME AS FOREIGN_KEY_TABLE_NAME, KCU.COLUMN_NAME, ORDINAL_POSITION AS COLUMN_POSITION,RC.UNIQUE_CONSTRAINT_NAME AS PARENT_PRIMARY_KEY_NAME, TC_PK.TABLE_NAME AS PARENT_TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC, INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU, INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC, INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC_PK
WHERE TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
AND RC.CONSTRAINT_NAME = TC.CONSTRAINT_NAME
AND RC.UNIQUE_CONSTRAINT_NAME = TC_PK.CONSTRAINT_NAME

15) Can you drop a Parent Table with out affecting its child tables?
No. First you need to drop all the Foreign Key relationships and then only you can drop Parent Table.

16) How to disable and Enable the constraints?
You need to use ALTER TABLE statement to disable constraint.
ex: ALTER TABLE ACCOUNT NOCHECK CONSTRAINT CHECK_IN_AMOUNT;

17) What is the order of Constraints execution?
There is no predified order. All the constraints on the column gets executed.

18) what is difference between Primary Key, Unique Key and Alternative Key?
Primary Key ==> Is used to uniquely identify records and doesn't allow NULL values.
Unique Key ==> Is also used to uniquely identify records but does alow NULL Values per column combination.
Alternate Key ==> Is another type of Unique key, which is used to identify each record uniquely. This is not a constraint. This is just a terminology.

SQL Server Tutorials

Database:
Designing and creating a database


Tables:
Create Table Using Select in Sql Server
Change Data Type in SQL Server
Computed Columns in SQL Server
Drop table in SQL Server
Rename Table in SQL Server
Exception Handling at the Table Level
Find out all the child tables of a parent table
Sql Database Table Design

Data Type:
Is Your MONEY Safe in SQL Server
Get Current date and time in SQL Server using CURRENT_TIMESTAMP, GETDATE(), SYSDATETIME()

Identity:
Create Table with Identity in SQL Server
Adding Identity to an existing table in SQL Server...
Insert Values into IDENTITY column using IDENTITY_INSERT...

Constraints:
Add NOT NULL Constraint in SQL Server 2005
Add Primary Key in SQL Server
Add Foreign Key in SQL Server 2005
List all the Primary Keys in SQL Server Database
List all the Foreign Keys in a SQL Server
Foreign Key and Primary Key in SQL Server
DEFAULT value in SQL Server
Drop DEFAULT Constraint in SQL Server
Check Constraint in SQL Server
Unique Constraint in SQL Server
RULE in SQL Server
Disable Constraint in SQL Server

Indexes:
Clustered Index in SQL Server
Non Clustered Index in SQL Server
Covering Index in SQL Server
Filtered indexes in SQL Server 2008
DROP INDEX - Must specify the table name and index name for the DROP INDEX statement

Simple Queries:
Select the nth best amount with simple query
Convert Rows to Columns with aggregate functions
Case Sensitive Search on a Case Insensitive SQL Server
Order By Clause is not accepting alias Name in SQL Server
Search For Columns in SQL Server

Analytical Functions:
Custom Pagging in SQL Server using ROW_NUMBER Function

Working with Data:
Join algorithms
TOP 5 ways to delete Duplicate Records in a Table
Generate and Insert the data into a table in SQL Server
Insert Multiple Rows with Single INSERT statement in SQL Server
What Happens When You Issue INSERT statement

String Functions:
String Functions in SQL Server Part 1
String Functions in SQL Server -- Final Part
Split Full Name into First Name, Middle Name, Last Name in SQL Server using PARSENAME function


Date Functions and Formats:
Date Format using CONVERT function in SQL Server
Get the records nearest to GETDATE() in SQL Server
DATEDIFF Function in SQL Server


Views
Find out the objects on which the view depends either directly or indirectly

T-SQL Coding Standards
T-SQL Coding Standards Part - 2
T-SQL Coding Standards Part - 1

T-SQL Fundamentals
Declaring a variable in SQL Server 2008

T-SQL Stored Procedures
Useful Stored procedures in SQL Server Part 1
Useful Stored procedures in SQL Server -- Final Part

SQL Server Performance
What to do when SELECT query is Running Slowly?
Why we should avoid * in SELECT Clause to improve query performance
Why We should Use EXISTS instead of DISTINCT to improve Query Performance

SQL Server Errors and Resolutions
Very Common SQL Server Errors And Resolutions Part 1
Very Common SQL Server Errors And Resolutions Part 2
Very Common SQL Server Errors And Resolutions Part 3
Could not open a connection to SQL Server

Misc
MERGE in SQL Server 2005
Load text or csv file data into SQL Server
OUTPUT in SQL Server 2005
REVERSE String in SQL Server
Oracle Sequence Vs IDENTITY in SQL ServerCompare Rows in SQL Server
SQL SPY is returning
Information Schema Views in SQL Server
Enable SQL Full-text Filter daemon Launcher Process
How to find whether Full Text is Installed or not?
Why Upgrade to SQL Server 2008?
Get Database Traffic Report in SQL Server
http://www.dotnetvj.com/2010/01/how-to-get-server-name-in-sql-server.html
When to use fully qualified names in SQL Server
Convert Number to Varchar in SQL Server using CAST or CONVERT or STR or Any other String Functions


SQL Server 2005 Features
New features of SQL Server 2005 -- Part 1
New features of SQL Server 2005 -- Part 2
New features of SQL Server 2005 -- Part 3

SQL Server 2008 Features
Using T-SQL MERGE command in SQL Server 2008