Log in | Register

Transact-SQL Archives - SQL Server Blogs, Events, Webcasts, Videos, SQL Server learning & education Transact-SQL Archives - SQL Server Blogs, Events, Webcasts, Videos, SQL Server learning & education



Sub Query behaviour looks like a bug

While debugging one procedure I came across below Scenario where a sub-query was returning all records from outer query.

Initially I thought it is a bug in SQL server. As COL1 column is not part of Table2 , it should fail binding stage and throw error. But it returned all 20 records from Table1. Though […]

SQL Server – Join Operation

In this blog we’ll find how SQL Server works when we do join operation. SQL Server optimizer chooses one of the below physical operator to perform logical join operation.

Hash Match
Merge Join
Nested Loop Join

As far as performance is concern let me tell you, we can’t say which one will best.Each operator has its own advantages […]

Don’t use ISDATE() function to check valid Date

I got the below email from one of my teammate on ISDATE() function.

Thanks to him for asking me the question. I do not have any idea on this behaviour of  sql server. I gone through the MSDN documentation and  got to  know, why SQL Server behaves like this.

select ISDATE(’08/16/013′) returns true because here it […]

By |October 11th, 2016|Categories: SQL Server, Transact-SQL|Tags: , , , |0 Comments

How to redefine the returning result sets of a stored procedure?

This feature was introduced in SQL Server 2012. Using With Result Sets option with Execute Command we can change the column name and data type of returning result set.

(2 row(s) affected) 
ID          Name       DOB 
———– ———- ———————– 
1           A          1980-04-10 06:09:00.100 
1           B          1982-03-08 03:42:41.003
(2 row(s) affected)
MyID                 MyName  DateOfBirth 
——————– ——- ———– 
1                    A       1980-04-10 
1                    B       1982-03-08
(2 row(s) affected)
The second result set […]

By |October 4th, 2016|Categories: Transact-SQL|Tags: , |2 Comments

SQL SERVER – Trace Flags

To know about a topic we usually ask questions. Here I used the same method to explain about trace flags of SQL Server.

Below are some basic questions and its answers on this.

What is the use of Trace Flag?

Trace Flags are used to enable or disable certain functionality of SQL server temporarily. It is used to […]

By |September 29th, 2016|Categories: SQL Server, Transact-SQL|Tags: , , |0 Comments

SQL Server – Customize Database object Script

SSMS provides functionality to generate script for database objects. I’ve seen developer use this functionality to generate script for various objects like Tables, Procedures, Triggers and other database objects but after generating it, they manually modify the generated scripts. When I asked one of the developers why you need to modify the generated scripts? He […]

By |September 24th, 2016|Categories: Transact-SQL|Tags: , |0 Comments

SQL Server– Unique Constraint VS Unique Index

Both  enforces uniqueness of the column. When we define an Unique constraint, SQL server creates an unique index (Unique non clustered index) on the column where Unique constraint is defined. Even though both of them can be used for same purpose but consider below points before you decide which one to use.
1 –  From SQL server […]

SQL Server– Template Explorer

Yesterday when I was about to leave office , I got a call from one of my friend. He wanted to know how to write a recursive query?I said use Template Explorer, Where you can get examples for recursive query but He was unaware of this feature of SQL server. So I thought to […]

Analytical functions – Lead and Lag

Using this function we can fetch the nth row before (Lag) the Current row without using self-join.
It is useful when we need to compare the current row values with values in a previous row.
LAG (scalar_expression [,offset] [,default])
    OVER ( [ partition_by_clause ] order_by_clause )
Lets explore it. Before exploring let us first create a table […]

Identity Property Part – 3

This is the third part of this blog series. You can read the 2nd part here Here. In this blog post we’ll learn how to retrieve last generated identity value. There are scenarios where we need to fetch the last inserted identity value, for example in a transaction we may first generate OrderID for order header table […]