Is it possible to create a STORED PROCEDURE or a FUNCTION or TRIGGER with the same name as TABLE Name?

Answer is NO; let’s analyze answer by taking a temporary table ‘PARENT_TAB’ having the following records:

select * from parent_tab

first_id    name

----------- --------------------------------------------------

1           Aadarshini

2           Taksha

3           Tanika

4           Vaikunth

5           Ekantika

6           Gargi

7           Sachiv

(7 row(s) affected)

Procedure: Now we try to create a PROCEDURE with the same name as table name ie. ‘parent_tab’

create procedure parent_tab

as

begin

      select * from parent_tab

end

Msg 2714, Level 16, State 3, Procedure parent_tab, Line 4

There is already an object named 'parent_tab' in the database.

Urr.. Got error

Function:  Let’s try our luck with function with the same name as table name ie. ‘parent_tab’

create function parent_tab()

returns int

AS

BEGIN

      return (0)

end

Msg 2714, Level 16, State 3, Procedure parent_tab, Line 5

There is already an object named 'parent_tab' in the database.

Again Fail!!

Trigger: Let’s try for trigger

create trigger parent_tab ON parent_tab

instead of delete

AS

    PRINT 'Sorry - you cannot delete this data'

GO

Msg 2714, Level 16, State 2, Procedure parent_tab, Line 4

There is already an object named 'parent_tab' in the database.

Constraint: Will we be allowed to make constraint with the same name as table name, lets see

alter table parent_tab

add constraint parent_tab primary key(first_id)

Msg 2714, Level 16, State 4, Line 1

There is already an object named 'parent_tab' in the database.

Msg 1750, Level 16, State 0, Line 1

Could not create constraint. See previous errors.

So we fail in all our attempt.

CONCLUSION: We cannot make procedure or function or trigger or constraint with the name as table name.

REASON: Since SQL Server treat all these as objects of database and this object are uniquely defined by the object name and object id that is the reason why we cannot have procedure or function etc with the same name as table name.

Post Reference: Vikram Aristocratic Elfin Share

Different ways to find TOP N records.

For our explanation we have taken a table ‘parent_tab ‘ having the following seven records data.

select * from parent_tab

first_id    name

----------- --------------------------------------------------

1           Aadarshini

2           Taksha

3           Tanika

4           Vaikunth

5           Ekantika

6           Gargi

7           Sachiv

(7 row(s) affected)

Various ways to achieve this : Our objective is to find first 4 records from the above table

1. Using ROWCOUNT

set rowcount 4

select name from parent_tab

set rowcount 0

name

--------------------------------------------------

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

2. Using ROW_NUMBER

select name from

(

select row_number() over (order by first_id) as rno, name from parent_tab

) as tab where rno <= 4

name

--------------------------------------------------

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

3. Using Top N

select top 4 name from parent_tab

name

--------------------------------------------------

Aadarshini

Taksha

Tanika

Vaikunth

(4 row(s) affected)

If you know any other way to achieve this, please light up your suggestion to me.

Post Reference: Vikram Aristocratic Elfin Share

TOP, WITH TIES and ORDER BY? What is WITH TIES clause in SQL Server?

WITH TIES can be used only when TOP and ORDER BY clauses are present in SELECT statement, both the clauses are required in order to use WITH TIES.

Let understand this by taking a small example: Here we are having a table parent_tab with following records:

select * from parent_tab

first_id    name

----------- --------------------------------------------------

1           sneha

3           pratik

3           pratik

1           sneha

4           chitrangada

5           chitrangada

(6 row(s) affected)

Now I am firing a select query with Top clause to retrieve the topmost record from the table [parent_tab] where the name is equal to ‘chitrangada’.

select top 1  * from parent_tab where name like 'chitrangada'

first_id    name

----------- --------------------------------------------------

4           chitrangada

(1 row(s) affected)

Here we can see the top most record has been arrived by the above query.

Now If I want to retrieve the top most record and all the records in the table where the name is equal to ‘chitrangada’?

If  such is my requirement then ‘WITH TIES’ will help you out, let see how:

select top 1 with ties  * from parent_tab where name like 'chitrangada' order by name

first_id    name

----------- --------------------------------------------------

4           chitrangada

5           chitrangada

(2 row(s) affected)

What it does it when you use TOP 1 rows, it will return you only 1 rows, but when used TOP 1 WITH TIES, it will return you all the rows that have same value as that of the last record of TOP 1.

The expected result is based on the column that is specified in ORDER BY. That is it will look for the column used in the ORDER BY to compare its equivalent in rest of the table.

NOTE: WITH TIES Clause can be used only with TOP and ORDER BY, both the clauses are required.

Post Reference: Vikram Aristocratic Elfin Share

How to find Distinct record using GROUP BY?

How to find distinct records using group by? Then what is the difference between group by and Distinct?

Taking a scenario where we are having a table with duplicate records like the one mentioned below:

select first_id,name from vw_parent_tab

first_id    name

----------- -----------------------------------------

1           sneha

3           pratik

3           pratik

1           sneha

(4 row(s) affected)

Now if we want to find distinct record we can easily do it with the help of distinct clause

select distinct first_id,name from vw_parent_tab

first_id    name

----------- ------------------------------------------

1           sneha

3           pratik

(2 row(s) affected)

But the question is how to find the same distinct result using GROUP BY Clause; Answer is simple add a group by clause and put all the field you want to display using select query with the Group By.

select first_id,name from vw_parent_tab group by first_id,name

first_id    name

----------- -----------------------------------------

1           sneha

3           pratik

(2 row(s) affected)

If you are using a group by without any aggregate function then internally it will be treated as Distinct so in this case there is no difference between group by and Distinct...
But when you are provided with Distinct Clause better to use it for finding your unique records because objective of group by is to achieve aggregation not distinct.

Use of Group by:

select first_id,name,count(*) as 'duplicate_count' from vw_parent_tab group by first_id,name

first_id    name                       duplicate_count

----------- -------------------------- ---------------

3           pratik                     2

1           sneha                      2

(2 row(s) affected)

A hammer can work to drive in a screw sometimes, but if you have got a screwdriver handy, why bother?

Conclusion:  Use Distinct when you want distinct records whereas use group by when you want your records to be aggregated.

Post Reference: Vikram Aristocratic Elfin Share

How DATETIME2 differs with DATETIME?

If you are having a table with two different columns of datetime datatype as FirstModified and LastModified. And you have datatype for FirstModified as Datetime and LastModified as Datetime2.

create table testDemoForDateDatatype

(

firstName varchar(50),

firstModified datetime,

lastModified datetime2,

)

Now when you are populating each of them with SYSDATETIME, you assume that the value inserted in the table will be the same, you have done on insert operation on it, no update operation has yet done on the table.

insert into testDemoForDateDatatype values('abc', sysdatetime() ,sysdatetime ())

insert into testDemoForDateDatatype values('xyz', sysdatetime (),sysdatetime ())

insert into testDemoForDateDatatype values('def', sysdatetime (),sysdatetime ())

But when you querying the table with distinct clause on both these columns and you will be surprised by the result because your perception will be like both the column will have the same data, but in fact, they had very different data.

The date value in the DATETIME field gets rounded up whereas the value didn’t round up in the field having DATETIME2 datatype.

*The best way is to use GETDATE () if you are using DATETIME datatype.

*And SYSDATETIME () if you are using DATETIME2

 select getdate() -- 2011-12-28 11:23:39.727

select sysdatetime () --2011-12-28 11:23:39.9062500

NOTE: DATETIME2 and Sysdatetime() was introduced in SQL Server 2008. So try this in 2008 SQL Server.

Post Reference: Vikram Aristocratic Elfin Share