October | 2010 | SQL with Manoj

SQL Logical Query Processing Order

October 28, 2010 2 comments

–>Logical Query-processing step numbers:

(5) SELECT (5-2) DISTINCT (7) TOP(<top_specification>) (5-1) <select_list>
(1) FROM (1-J) <left_table> <join_type> JOIN <right_table> ON <on_predicate>
| (1-A) <left_table> <apply_type> APPLY <right_input_table> AS <alias>
| (1-P) <left_table> PIVOT(<pivot_specification>) AS <alias>
| (1-U) <left_table> UNPIVOT(<unpivot_specification>) AS <alias>
(2) WHERE <where_predicate>
(3) GROUP BY <group_by_specification>
(4) HAVING <having_predicate>
(6) ORDER BY <order_by_list>
(7) OFFSET <offset_specs> ROWS FETCH NEXT <fetch_specs> ROWS ONLY;

–> Logical step sequence of a Query:
1. FROM / JOIN/ APPLY/ PIVOT/ UNPIVOT
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT list/ DISTINCT
6. ORDER BY
7. TOP/ OFFSET-FETCH

–> Flow diagram representing logical query-processing:

SQL Logical Query Processing Order

Categories: DB Concepts Tags: Query Order, SQL Logical Query Processing

SQL Server CURSOR and it’s Life Cycle

October 24, 2010 1 comment

A CURSOR in SQL is a database object that contains a set of records that you can traverse one-by-one, rather than the SET as a whole.

SQL is a set-based language and produces a complete result set, and the SQL queries works on this complete set only, and not on individual rows. But there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type.

CURSOR in SQL language gives you the flexibility to traverse records like the way you do in other programming languages with iterators and for-loop.

–> A simple Cursor life cycle with minimum definition:

USE [AdventureWorks]
GO

-- Returns -3
SELECT CURSOR_STATUS('global','myCursor') AS 'Before Declare'

DECLARE myCursor CURSOR
FOR SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID BETWEEN 100 and 102

-- Returns -1
SELECT CURSOR_STATUS('global','myCursor') AS 'After Declare'

DECLARE @ContactID INT, @FirstName VARCHAR(50), @LastName VARCHAR(50)

OPEN  myCursor

-- Returns 1
SELECT CURSOR_STATUS('global','myCursor') AS 'Open Cusrsor'

FETCH NEXT FROM myCursor INTO @ContactID, @FirstName, @LastName
WHILE @@fetch_status=0
BEGIN

	-- SQL Statements with logic inside
	SELECT @ContactID, @FirstName, @LastName

	FETCH NEXT FROM myCursor INTO @ContactID, @FirstName, @LastName
END

-- Returns 1
SELECT CURSOR_STATUS('global','myCursor') AS 'While loop exited, all rows iterated'

CLOSE myCursor

-- Returns -1
SELECT CURSOR_STATUS('global','myCursor') AS 'Cursor is Closed'

DEALLOCATE myCursor

-- Returns -3
SELECT CURSOR_STATUS('global','myCursor') AS 'Cursor Deallocated'

Do check & Like my FB Page.

Categories: Datatypes, SQL Tips Tags: @@FETCH_STATUS, Cursor, CURSOR_STATUS, DEALLOCATE, DECLARE CURSOR

SQL Basics – IDENTITY property of a Column (in a table)

October 19, 2010 1 comment

With IDENTITY property you can:

1. Creates an identity column in a table.

2. Used for generating key values, based on the current seed & increment.

3. Each new value for a particular transaction is different from other concurrent transactions on the table.

–> You can check the demo about IDENTITY property here:

–> IDENTITY property on a column does not guarantee:

1. Uniqueness of the value,

2. Consecutive values within a transaction,

3. Consecutive values after server restart or other failures,

4. Reuse of values,

–> SQL Script used in Demo:

-- IDENTITY property of a Column

CREATE TABLE [dbo].[Employee](
	[EmployeeID]	int				NOT NULL IDENTITY (100, 1),
	[EmployeeName]	nvarchar(100)	NOT NULL,
	[Gender]		nchar(1)		NULL,
	[DOB]			datetime		NULL,
	[DOJ]			datetime		NULL,
	[DeptID]		int				NULL
)

INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID)
VALUES ('MANOJ PANDEY', 'M', '1990-01-01', '2010-01-01', 101)

INSERT INTO [dbo].[Employee] (EmployeeName, Gender, DOB, DOJ, DeptID)
VALUES ('JHON K', 'M', NULL, '2010-01-01', NULL)

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Brock H')
GO

SELECT * FROM [dbo].[Employee]
GO


-- Inserting Explicit value in IDENTITY column:

SET IDENTITY_INSERT [dbo].[Employee] ON

INSERT INTO [dbo].[Employee] ([EmployeeID],[EmployeeName])
VALUES (1000, 'Brock H')

SET IDENTITY_INSERT [dbo].[Employee] OFF
GO


SELECT * FROM [dbo].[Employee]
GO

INSERT INTO [dbo].[Employee] ([EmployeeName])
VALUES ('Jhon B')
GO

SELECT * FROM [dbo].[Employee]
GO

–> Check more articles on IDENTITY property:

– RE-SEED an IDENTITY value of a Column

– Using IDENTITY() function with SELECT into statement

– All about IDENTITY columns, some more secrets

– IDENTITY property behavior with SQL Server 2012 and above

Categories: SQL Basics Tags: IDENTITY, IDENTITY Columns

Multiple ways to INSERT records in a table

October 15, 2010 4 comments

The following exercise shows multiple ways to INSERT records in a table, as the post title says.

USE [tempdb]
GO

CREATE TABLE sometable(a INT, b VARCHAR(20), c INT)
GO

-- Method #1 - Simple INSERT statement
INSERT sometable (a, b, c)
VALUES(1, 'New York', 123)
GO

DROP TABLE sometable

-- Method #2 - CREATE the table and INSERT records. This is minimally logged operation and faster than explicitly creating table and inserting records.
SELECT 1 a, 'New York' b, 334 c
INTO sometable
UNION
SELECT 2, 'London', 823
UNION
SELECT 3, 'Paris', 1124
UNION
SELECT 4, 'Munich', 2080
GO

-- Method #3
INSERT sometable (a, b, c)
 EXEC('SELECT 5, ''New York'', 234
  SELECT 6, ''London'', 923
  SELECT 7, ''Paris'', 1024
  SELECT 8, ''Munich'', 1980')
GO

-- Method #4
INSERT sometable (a, b, c)
 SELECT 9, 'New York', 334 UNION
 SELECT 10, 'London', 823 UNION
 SELECT 11, 'Paris', 1124 UNION
 SELECT 12, 'Munich', 2080
GO

-- Method #5 - More options in SQL Server 2008, by using the VALUES() constructor
INSERT sometable (a, b, c)
VALUES	(13, 'New York', 334),
		(14, 'London', 823),
		(15, 'Paris', 1124),
		(16, 'Munich', 2080))
GO

-- Method #6 - Yes you can also use SQL statements at column level inside the VALUES constructor
INSERT sometable (a, b, c)
VALUES	(18, 'New York', 334),
		(19, 'London', 823),
		((SELECT MAX(a)+1 FROM sometable), (SELECT b FROM sometable WHERE a=15), SELECT SUM(c) FROM sometable),
		(20, 'Munich', 2080))
GO

-- Now check the resultset
SELECT * FROM sometable

-- Final Cleanup
DROP TABLE sometable

More on VALUES constructor on MS BOL: http://technet.microsoft.com/en-us/library/dd776382.aspx

Categories: SQL Tips Tags: INSERT

Physical Join vs Logical Join in SQL Server

October 6, 2010 5 comments

Most of us know about JOINS in SQL Server and their types. But do we really know how they are interpreted in SQL Server internally. Today I found lot of informative, interesting and important sources regarding Logical and Physical joins in SQL Server (links below).

–> Classifying JOINS mainly into 2 types:

1. Logical Joins: These joins are simple joins that we apply in our SQL queries, like INNER JOIN, RIGHT/LEFT OUTER JOIN, CROSS JOIN, OUTER APPLY, etc.

2. Physical Joins: These are the joins that users don’t use/write in their SQL queries. Instead these are implemented inside SQL Server engine as operators or algorithms to implement the Logical Joins. Their types are Nested Loop, Merge and Hash.

For a particular SQL query when you try to view an Estimated Execution Plan or execute a query by selecting Actual Execution Plan, you can clearly see these Physical Joins under the Execution Plan tab in SSMS.

Reference for Logical Joins from one of my old posts: https://sqlwithmanoj.wordpress.com/2009/03/12/sql-server-joins-and-types

–> Logical Joins:

– Inner/Outer/Cross: https://blogs.msdn.microsoft.com/craigfr/2006/07/19/introduction-to-joins/

–> Physical Joins:

– Nested Loop Joins: https://blogs.msdn.microsoft.com/craigfr/2006/07/26/nested-loops-join/

– Merge Joins: https://blogs.msdn.microsoft.com/craigfr/2006/08/03/merge-join/

– Hash Joins: https://blogs.msdn.microsoft.com/craigfr/2006/08/10/hash-join/