OUTPUT in SQL Server 2005

In this section, I would like to talk about one interesting feature of SQL Server 2005. Before I say anything about this feature let me ask you a simple question.

Can u write a single query to display all the records that are affected with an UPDATE statement?

It is impossible to find out this information with the UPDATE statement.

In most of the cases what we do is after the UPDATE we will go and query the table to see whether UPDATE ran successfully or not. I mean whether it updated the records which we are expecting.

This is two step process. What if there is a simple SQL statement that tells you what records are affected with the UPDATE statement? You will save lot of time with this.

Instead of waiting for some more time, the feature which I am talking about is OUTPUT keyword.

Let’s get straight into the example to understand what you need to do to get the updated records.

Quickly create the table:
CREATE TABLE EMPLOYEE_WORKING_HRS(
EMP_ID int NULL,
ENAME varchar(30) NULL,
YR varchar(4) NULL,
MON varchar(3) NULL,
WORKING_HRS int NULL,
RATE int NULL,
SALARY AS (WORKING_HRS*RATE)
)

Let’s insert few records into this table.
INSERT INTO EMPLOYEE_WORKING_HRS VALUES(100,'SAM',2009,'JAN',176,100);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (101,'LIMO',2009,'JAN',176,110);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES(102,'NAT',2009,'JAN',176,120);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (103,'BECK',2009,'JAN',176,130);

Due to the financial crisis I want to Add the Hourly to -20 if it is greater than 100.

declare @Emp_ids table(ID int)
update EMPLOYEE_WORKING_HRS set RATE = RATE - 20
output inserted.EMP_ID into @Emp_ids
from EMPLOYEE_WORKING_HRS
WHERE RATE > 100
SELECT * FROM @Emp_ids

In this piece of code I declared a variable called @emp_ids which is of table type.
Then I have used UPDATE clause with INSERTED emp_id of table into @Emp_Ids table.

Finally selecting the data from @Emp_Ids table.

Isn’t it kool!!!!!

Next section I will talk about how to mimic the MERGE functionality in SQL SERVER 2005.

Load text or csv file data into SQL Server

Today I will talk about loading a CSV or comma or txt file into SQL Server using BULK INSERT.

Using BULK INSERT, in few steps you can load the file into database.

Let’s go with an example

Let’s say I have following data in a file.

CustomerID,Customer_Name, Customer_Location
1,SAM,NY
2,BEN,NJ
3,HARY,HYD

Now the task is to load this file into SQL Server table. To do this, create one table in SQL Server with the similar structure.

CREATE TABLE Cusomer
(
CustomerID INT,
Customer_Name VARCHAR(50),
Customer_location VARCHAR(10)
)

Structure is exactly the same as the file. Use the below query to load the data.

BULK INSERT Cusomer
FROM 'c:\vijay_sql\Customer.txt'
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

Important Parameters:
1) FROM ==> this is to specify the File Location from which we want to load the data.
2) FIRSTROW ==> This is to specify which row we want to load the data. This field is not about skipping the header.
3) MAXERRORS ==> This is to specify the maximum number of syntax errors allowed in the data before the bulk-import operation is canceled. Each row that cannot be imported by the bulk-import operation is ignored and counted as one error. If this field is not specified at all, then default is 10. In this case this value is set to 0 which means if there is a single error then, don’t load the data.
4) FILEDTERMINATOR ==> this is to specify how fields are separated. In this case field separator is “,”.
5) ROWTERMINATOR ==> this is to specify how to differentiate the different rows. In our case it’s the new line character.

There are various options available with the BULK INSERT like Fire the Triggers or Keep the Identity etc. I will show you some flavors/options of BULK INSERT in my next postings.

Change Data Type in SQL Server

Today, I will talk about how to change the data type of a column which has data.

Let’s create one simple table

CREATE TABLE EMPLOYEE_WORKING_HRS(EMP_ID INT, ENAME VARCHAR(30), YR INT, MON VARCHAR(3), WORKING_HRS INT, RATE INT);

Let’s insert few records into this table.

INSERT INTO EMPLOYEE_WORKING_HRS VALUES (100,'SAM',2009,'JAN',176,100);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (101,'LIMO',2009,'JAN',176,110);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (102,'NAT',2009,'JAN',176,120);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (103,'BECK',2009,'JAN',176,130);

Now I want to change the data type of YR column from INT to VARCHAR.

SP_HELP 'EMPLOYEE_WORKING_HRS'



Normally for people who are coming from different database backgrounds, it’s really a painful task. That is you need to add a column with this new data type and then update this column and rename the columns. This is really a lengthy process. With the SQL Server you don’t have to do all these. Just one simple ALTER STATEMENT can change this.

ALTER TABLE EMPLOYEE_WORKING_HRS ALTER COLUMN YR VARCHAR(4)

Simple isn’t it.

SP_HELP 'EMPLOYEE_WORKING_HRS'

Drop table in SQL Server

One of common query that we execute on day-to-day to basis is Dropping and re-creating a table when you are as beginner.

You can use DROP TABLE to drop table from database. But the table which you are trying to drop has child references then you can’t drop this table.

Let’s try to drop DEPT table.

By looking at the message you can’t really say what are all the other tables that have relationship with this table. So now the BIG task is to find out the tables which have relationship with this table.

The below SQL query will help you to find out the child tables associated with this DEPT table.

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
AND TC_PK.TABLE_NAME = 'DEPT'

Computed Columns in SQL Server

Today I will talk about one of the very interesting feature called "Computed Columns" in SQL Server. This is a column in table but you will not assign any specific data type to this as you do it in regular CREATE TABLE statement.

You can ask this question, if there is no data type associated with this column then what kind of data i can store? And how to insert the data into this column?

Before I answer these questions, let's look at the basic syntax of table creation.

Let's take an example; I want to create a table to store Employee Working hours and Monthly Rate.

CREATE TABLE EMPLOYEE_WORKING_HRS(EMP_ID INT, ENAME VARCHAR(30), YR INT, MON VARCHAR(3), WORKING_HRS INT, RATE INT);



Let’s insert few records into this table.

INSERT INTO EMPLOYEE_WORKING_HRS VALUES
(100,'SAM',2009,'JAN',176,100);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (101,'LIMO',2009,'JAN',176,110);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES
(102,'NAT',2009,'JAN',176,120);INSERT INTO EMPLOYEE_WORKING_HRS VALUES (103,'BECK',2009,'JAN',176,130);





Now if you want to find out what is the salary of each of these employees then you need to multiply WORKING_HRS with RATE.

select EWR.*, EWR.WORKING_HRS*EWR.RATE AS SALARY from EMPLOYEE_WORKING_HRS EWR



When ever we want to find out the salary then we have to multiply RATE column with WORKING_HRS. It’s an extra overhead on the developer to have this conversion. Instead you can have this column in table itself but its violation to the normal forms.

So now you are in mixed state, you don’t want to store this information in the table and you don’t want to have this Multiplication in the query each time. The solution for this is “Computed Columns”.

CREATE TABLE EMPLOYEE_WORKING_HRS
(EMP_ID INT,
ENAME VARCHAR(30),
YR INT,
MON VARCHAR(3),
WORKING_HRS INT,
RATE INT,
SALARY as WORKING_HRS*RATE);

Created a column SALARY and the definition of the column is WORKING_HRS*RATE.

Let’s insert same old records into this table.

INSERT INTO EMPLOYEE_WORKING_HRS VALUES (100,'SAM',2009,'JAN',176,100);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (101,'LIMO',2009,'JAN',176,110);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (102,'NAT',2009,'JAN',176,120);
INSERT INTO EMPLOYEE_WORKING_HRS VALUES (103,'BECK',2009,'JAN',176,130);


Now Lets query the table.



The fundamental principle behind this is when the data is returned; the computation is applied to return the result.

However, you can force a computed column to physically store data by using PERSISTED keyword. With this keyword, this will be just like any other column and gets modified whenever there is an UPDATE/INSERT on the underlying columns.

Foreign Key and Primary Key in SQL Server

Check out the below script to find out the Foreign Key information like Name of the Foreign Key , Name of the Table which has foreign Key and List of columns in that Foreign Key definition. In addition to this the foreign key which is referencing the Primary Key and Primary key table or Parent table.

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

List all the Foreign Keys in a SQL Server

The below script will give you list of Foreign Keys in a database and columns which are part of the Foreign key definition.

If you don't know how to add a Foreign key on a table then you need to check out the "Adding Foreign Key on a Table" first.

SELECT

TC.CONSTRAINT_NAME,
TC.TABLE_NAME ,
KCU.COLUMN_NAME,
ORDINAL_POSITION AS COLUMN_POSITION

FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC, INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
WHERE 1=1
AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
AND TC.TABLE_NAME = KCU.TABLE_NAME
AND TC.CONSTRAINT_TYPE = 'FOREIGN KEY'

There could be several ways to get the same kind of information but from my point of view this is most simple way to do it.

Please let me know if you have a query which is much simple than this.....

Add Foreign Key in SQL Server 2005

In this section we will learn on defining a Foreign Key Constraint. In simple words lets try to define a relationship between two tables.

Lets create a Employee table with below structure.

CREATE TABLE Employee(
EMP_ID int IDENTITY(1,1) NOT NULL,
ENAME varchar(50) NOT NULL,
DOB datetime NOT NULL,
SALARY decimal(18, 0) NOT NULL,
DEPTNO int NOT NULL
)

Now lets define Primary Key on EMP_ID column, though this is not mandatory but it is a good practise. Check out the link on Adding a Primary Key on the table.

Lets look at the syntax:

ALTER TABLE EMPLOYEE ADD CONSTRAINT FK_EMPLOYEE_01 FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO)

It is very simple isn't it. Similar to Primary Key syntax with a little change.

Add Primary Key in SQL Server

One of the cirtical factor in database/table designing is to prevent duplicate records. There are several ways to do this. You can place Unique Index or Unique Constraint or add a primary key. Well there is only slight difference between these things.

I will go one by one to describe about these but for now lets concentrate on adding a primary key to a table.

There are two ways to add a primary key constraint:

1) While Creating the Table using CREATE TABLE command.
2) Adding Primary key after table creation using ALTER TABLE statement.

The simple thing is to create the table first and define the Primary key later. Again each one of us will have different opinion but to me this is simple step. Though you will end up in having multiple SQL statements but you will have more control on what you are doing.

The pre-requisite to define a Primary Key on a column is, that column should be NOT Nullable.
Check out Adding NOT NULL constraint article to meet the pre-requisite of Primary key.

To verify whether the column is NOT NULL or not, run the below query:

SELECT TABLE_NAME,COLUMN_NAME,IS_NULLABLE FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME = 'DEPT'

If IS_NULLABLE is "NO" means column has Not Null Constraint.


Lets say I want to add Primary Key constraint on DEPTNO Column.

ALTER TABLE:

ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO);

We are trying to add a Primary Key Constraint on DEPT table and the column is DEPTNO.
The Name of the Constraint is PK_DEPT. Its very important to give a name to the constraint.
As the general standards Primary key constraint name should always start with PK_ and followed by the table Name Or you can have _PK.

CREATE TABLE:

CREATE TABLE DEPT(DEPTNO INT PRIMARY KEY, DNAME VARCHAR(30))

As you can see i didn't specify constraint name to Primary key. So Tell me is there any way to put a constraint name in the CREATE TABLE statement??

DataTypes in SQL Server

Use the below query to find out the data type of all the columns of a particular table .

SELECT syscolumns.name AS ColumnName, systypes.name AS Datatype
FROM sysobjects, syscolumns, systypes
WHERE sysobjects.id = syscolumns.id
AND syscolumns.xtype = systypes.xtype
AND sysobjects.name = 'table1'

In the above just replace the table1 with the your desired table.

NestLevel in SQL Server Stored Procedures

@@NESTLEVEL
Returns the nesting level of the current stored procedure execution

Each time a stored procedure calls another stored procedure, the nesting level is incremented. When the maximum of 32 is exceeded, the transaction is terminated.

Identity in SQL Server

@@IDENTITY
Returns the last-inserted identity value.

After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. If a trigger is fired after an insert action on a table that has an identity column, and the trigger inserts into another table that does not have an identity column, @@IDENTITY will return the identity value of the first insert. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

@@IDENTITY, SCOPE_IDENTITY, and IDENT_CURRENT are similar functions in that they return the last value inserted into the IDENTITY column of a table.

@@IDENTITY and SCOPE_IDENTITY will return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope. @@IDENTITY is not limited to a specific scope.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'

Error Numbers in SQL Server

@@ERROR
Returns the error number for the last Transact-SQL statement executed.
When SQL Server completes the execution of a Transact-SQL statement, @@ERROR is set to 0 if the statement executed successfully. If an error occurs, an error message is returned.

@@ERROR returns the number of the error message until another Transact-SQL statement is executed. You can view the text associated with an @@ERROR error number in the sysmessages system table.

Because @@ERROR is cleared and reset on each statement executed, check it immediately following the statement validated, or save it to a local variable that can be checked later.

USE pubs
GO
UPDATE authors SET au_id = '12345'
WHERE au_id = "54321"
IF @@ERROR = 547
print "A check constraint violation occurred"

Active Connections in SQL Server

@@CONNECTIONS
Returns the number of connections, or attempted connections, since SQL Server was last started.

Connections are different from users. Applications, for example, can open multiple connections to SQL Server without the user observing the connections.
To display a report containing several SQL Server statistics, including connection attempts, run sp_monitor.

SELECT GETDATE() AS 'Today''s Date and Time',
@@CONNECTIONS AS 'Login Attempts'

How to Get SQL Server Version

@@VERSION
Returns the date, version, and processor type for the current installation of SQL Server.

The information returned by @@VERSION is similar to the product name, version, platform, and file data returned by the xp_msver stored procedure, which provides more detailed information.

SELECT @@VERSION

Active transactions for the current connection

@@TRANCOUNT

Returns the number of active transactions for the current connection.

The BEGIN TRANSACTION statement increments @@TRANCOUNT by 1. ROLLBACK TRANSACTION decrements @@TRANCOUNT to 0, except for ROLLBACK TRANSACTION savepoint_name, which does not affect @@TRANCOUNT. COMMIT TRANSACTION or COMMIT WORK decrement @@TRANCOUNT by 1.

BEGIN TRANSACTION
UPDATE authors SET au_lname = upper(au_lname)
WHERE au_lname = 'White'
IF @@ROWCOUNT = 2
COMMIT TRAN
IF @@TRANCOUNT > 0
BEGIN
PRINT 'A transaction needs to be rolled back'
ROLLBACK TRAN
END

differences strored procedures and triggers

Stored Procedure:

A stored procedure can be created with no parameters, IN parameters, OUT parameters. There can be many parameters per stored procedure.

One has to manually call this Object to do some actions.

Trigger:

It is a fragment of code that tells Database to fire or run BEFORE or AFTER a table is modified.

It has the power to make sure that a column is filled in with default information make sure that an audit row is inserted into another table after finding that the new information is inconsistent with other stuff in the database.

Cursors in SQL Server

SQL Server is very good at handling sets of data. For example, you can use a single UPDATE statement to update many rows of data. There are times when you want to loop through a series of rows a perform processing for each row. In this case you can use a cursor.

cursors are the SLOWEST way to access data inside SQL Server. The should only be used when you truly need to access one row at a time. The only reason I can think of for that is to call a stored procedure on each row.

cursors are over thirty times slower than normal SQL queries.

Reference : http://www.sqlteam.com/article/cursors-an-overview

differences between primarykey and uniquekey

One of the basic question in any interviews is What is the difference between primary key and unique key?

Just give the below ponts, he/she will be very happy with the answers.

The Major differences between Primary Key and Unique Key are:

1) Primary Key doesn't allow null values, But Unique key does all nulls(Only One per key).

2) You can have No.Of Unique Keys on a single table, but you can have atmost only one primary key.

3) Primary Keys are used to define Referential Integrity Constraints.

What is a join and explain different types of joins

Joins are used in queries to explain how different tables are related.
Joins also let you select data from a table depending upon data from another table.
Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs.
OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.