Skip to main content

Tag: SQL Server

Explore the SQL Server Cursors basics

Tarun Dudhatra

Cursor is the database object in MS SQL Server to retrieve the data from the result set one row at a time either in backward direction or forward direction. Generally in T-SQL command it will direct operates on all the rows of result at one time, so when ever we want to process based on the condition 1 by 1 for our process or data is dependent on the previous actions cursor is the suitable feature to use.

Now lets see the Life cycle of the cursor as below

Declare the cursor

Like any other object we need to declare the cursor first, we can declare the cursor by defininf SQL statement which returs a result set.

Open the cursor

Once cursor is declared we can open and populate by executing the SQL statement defined in the cursors.

Fetch records one by one from the Cursor

One cursor is opened we can fetch rows one by one or in a block for the data manipulation.

Close Cursor

One we have fetched all the rows and manipulated data as required we need to close the cursor explicitly.

Deallocate Cursor

One we have close the cursor at the end, we need to delete the cursor definition and free all the system resources associated with the cursor.

Cursor Syntax

Declare Cursor SQL Command is used to declare the cursor with provided options. There are some options we need to use while declaring the curosr which impact on the performance of cursor.

The complete syntax of declaring a cursor is as below


DECLARE name_of_cursor CURSOR
[LOCAL | GLOBAL] -- Local or global define the scope of the cursor, default it is local only
[FORWARD_ONLY | SCROLL] -- It defines the direction of the cursor either forward or backward
[STATIC | KEYSET | DYNAMIC | FAST_FORWARD] -- It define the basic type of cursor
[READ_ONLY | SCROLL_LOCKS | OPTIMISTIC] -- It define the locks
FOR Select_Query -- Here you can define select or update statement as required
FOR UPDATE [field1,field2,field3 ....., fieldn] -- Here provide the list of fields/columns to updated

Syntax to Open a Cursor

A SQL cursor can be either opened locally or globally. By default it is opened locally.

OPEN [GLOBAL] name_of_cursor

Syntax to Fetch rows from Cursor

Fetch statement provides different different options to retrieve the rows from the cursor. Default NEXT option is applied.


FETCH [NEXT|PRIOR|FIRST|LAST|ABSOLUTE n|RELATIVE n]
FROM [GLOBAL] name_of_cursor
INTO @Name_Of_Variable[1,2,3,4, .....n]

Syntax to Close the Cursor

Close statement closed the cursor explicitly.


CLOSE name_of_cursor -- Once it is closed then it can be reopen

Syntax to Deallocate Cursor at the end

Deallocate statement delete the cursor definition and free all the system resources associated with the cursor.

DEALLOCATE name_of_cursor -- Once cursor is deallocated it can not be open again.

See the sample data as below from which we will retrieve the data using cursor.

The full cursor example

SET NOCOUNT ON

DECLARE @ID int
DECLARE @Title varchar(100)
DECLARE @ISBN varchar(100)
Declare @Price Decimal

DECLARE all_book CURSOR
STATIC FOR
SELECT Id,Title,Isbn,Price from Books

OPEN all_book
IF @@CURSOR_ROWS > 0
BEGIN
FETCH NEXT FROM all_book INTO @ID,@Title,@ISBN,@Price

WHILE @@Fetch_status = 0
BEGIN
Print 'Id :'+Convert(VarChar(20),@ID) + ', Price :'+Convert(VarChar(20),@Price)+ ', Title :'+@Title + ', ISBN :'+@ISBN

FETCH NEXT FROM all_book INTO @ID,@Title,@ISBN,@Price
END
END

CLOSE all_book
DEALLOCATE all_book
SET NOCOUNT OFF

When you run the cursor you will see the below out put.

Summary

in this article we have see the basic concept and syntax of the SQL Server Cursor. I hope it will help you to understand the cursors. i would like to have feedback from my blog readers

Easiest way to find row count of each sql tables using queries

Tarun Dudhatra

Introduction
I was working in a project where I need to compare my Production database and Staging database each time I upload code to the production server.
Being developer it is tedious task to check manually for each table in source and target database as it is taking more time. Using script/query which can get the number of rows from all the tables in selected database can be really effective.

So I was thinkg to check it using query where we can run the query again each database and it returns name of the table and number of rows for each table.
I have prepared a script for that and now let me share that query with my blog readers in this article.

In SQL Server there are tables called sys.tables, sys.sysindexes and many others like this, which stores table related data and all the db object related data for the internal use. So we can write the query on that object to retrieve table rows as below.

MS-SQL Query

SELECT Tables.name AS [TABLE_NAME],
objects.rows AS [ROW_COUNT]
FROM sys.tables AS Tables
INNER JOIN sys.sysindexes AS objects
ON Tables.object_id = objects.id
AND objects.indid < 2
ORDER BY objects.rows DESC

Output
Please check the below image, it is sample output from AdventureWorks2014 database.

It is as simple as above query. In this system related tables there are more useful information stored so we will see those tips and tricks soon.

Please share you feedback using comment.

SQL Server Interview Question and answers

Milan Dudhatra

If you are going for an SQL Server Interview or .Net interview then you need to refer below list of questions. Below question and answer will give you a break through. Here I have prepare some simple and tricky questions for SQL Server which will really help you to go through the interview.
1. What is the difference between clustered and a non-clustered index?
– Clustered index is the physical index, It reorders the records physically in the table while storing the data.
– Non-Clustered index is the logical index, It does not reorders the data into table, it’s just set the logical order of the index.

2. What is the difference between UNION and UNIONALL in SQL Server?
– Union and Union All both are used in select statement, Union will remove the duplicate rows from the final result set of the select statement while UNIONALL will not remove duplicate row from the result set and will return all the row. So you can use UNION clause if you want to avoid data redundancy.

3. What’s the difference between a primary key and a unique key?
– Unique key and Primary key both are constraints in SQL Server but has little bit difference, Both keys are not allow to store duplicate value in the database, but Primary key is a cluster index and Unique key is a non clustered index. Another difference is Primary key can not contains any null value while Unique key contains only 1 null value.

4. Alter command to change the data type of a column
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
Below is the query to change column datataype

[SQL]
ALTER TABLE tablename ALTER COLUMN columnname data type
[/SQL]

Let’s say we want to change the datatype from int to string of phone number column of employee table then below is query for it.

[SQL]
ALTER TABLE employee ALTER COLUMN phonenumber varchar(15)

[/SQL]

5. In which Files does SQL Server Actually Store Data?
On the disk SQL server has 2 data files associated with each database
1. LDF : Which stores generally transaction log
2. MDF : Which stores actual data

6. What is CHECK Constraint?
– CHECK Constraint used to limit the values that can be inserted in a table column

7. Do you know How many locks are exist in SQL Server ?
– We have these many locks exist here, see below
1. Intent
2. Shared
3. Update
4. Exclusive
5. Schema
6. Bulk Update

8. Can I insert value into a table which having just one IDENTITY column?
– Yes we can, There is a very simple query for that as below

[SQL]
INSERT INTO TABLE1 DEFAULT VALUES;
[/SQL]

9. How to drop primary key from a column using Query
– It’s very simple you can use Alter table command for that, see the below query to drop primary key constraint
ALTER TABLE tablename DROP CONSTRAINT PK_tablename_columnname
in above query PK_tablename_columnname is the name of primary key constraint.

10. What is a DDL trigger and a DML trigger?
– DDL commands are used to create, modify or delete the structre of the table, like creating table, alter table or drop table, so we can easily say those are the command which deal with the table structure. Those commands are CREATE, ALTER and DROP
– DMS commands are used to insert, update or delete the data of in to the table, like insert into, update and delete from, so those are the commands which deal with the table data, Those commands are INSERT, UPDATE and DELETE.

11. What is the difference between Trigger and Stored Procedure?

– Triggers and Stored procedure are generally same but the only single difference is that trigger cannot be called directly, it can be called on either insert,update or delete statement of the table based on trigger we define.

12. How can you delete duplicate records in a table where there is no primary key?
– You can take Use of the SET ROWCOUNT command. Here if you had 2 duplicate rows you would issue SET ROWCOUNT 1, then your DELETE command then SET ROWCOUNT 0.

13. How to check the version of SQL server and operating system?
– With the help of the following query we can get it

[SQL]
SELECT SERVERPROPERTY (‘productversion’) as Version, SERVERPROPERTY (‘productlevel’) As Level, SERVERPROPERTY (‘edition’) as Edition

[/SQL]

14. What is NOT NULL Constraint?
– NOT NULL is the constraint in the SQL Server. When ever we want to force someone to enter data into particular table column then we need to set it NOT NULL column so it will not accept NULL value.
15. Find the 3rd MAX Age of the employee in the emp table
– With the help of the following query we can get 3rd Max Age
Select distinct age from emp e1 where 3 = (select count(distinct age) from emp e2 where e1.age <= e2.age);

16. How to copy data from one table to another table?
– With the help of ‘INSERT INTO SELECT’ or ‘SELECT INTO’ queries you can copy data from a data-table to another

17.What is the Sixth normal form in SQL server?
– Actually Sixth normal form exist but use it when you want a relational system in conjunction with time. At this moment SQL Server does not support it directly.

18.What is SQL Profiler?
– It is SQL Server tool that facilitate administrator to monitor different events and transaction of particular SQL server instance, You can capture and save data about each event to a file or SQL Server table to analyze later.
It is not available with EXPRESS edition

19. What is PIVOT in SQL Server
– To sort, total and count the data stored in 1 table automatically we use PIVOT. It will also rotate table as a columns to rows and rows to column.
– To automatically sort, count, and total the data stored in one table we use PIVOT. It will also rotate table as rows to columns and vice versa

Please let us know your suggestion/queries