July 2014 - SQL SERVER TUTORIAL IN DEPTH

Tuesday, 29 July 2014

July 29, 2014

One tricky question asked in interview on Having and group by Clause

select * from EmployeeSalary order by id

--o/p [Need employee id and the count that crossed more than 5000]
--employeeid and count(> 5000)
--1 1
--2 1
--3 2

select id,COUNT(EmployeeSalary) from EmployeeSalary
where EmployeeSalary > 5000
group by id

--Now i want those employees id who has more than 1 count [> 5000] salary.

select id,COUNT(EmployeeSalary) from EmployeeSalary
where EmployeeSalary > 5000
group by id
having COUNT(EmployeeSalary) >1

--> After Group by No Where clause we know that.

A nice way to truncate all the tables

July 29, 2014

A nice way to truncate all the tables

I wanted to truncate all tables in a cursor. But I was unable to do it as some table were being reference by foreign key constraint.

This is the way I got it.

1)Alter table with no check constraint

2)Delete

3)Alter table with Check Constraint

4) Reseed it

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

EXEC sp_MSForEachTable "DELETE FROM ?"

EXEC sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

declare @table varchar(100)

declare NfrTable_cursor cursor

for

select TABLE_NAME

from INFORMATION_SCHEMA.COLUMNS

where TABLE_SCHEMA = 'dbo'

and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1

order by TABLE_NAME

open NfrTable_cursor

fetch next from NfrTable_cursor into @table

WHILE @@FETCH_STATUS = 0

BEGIN

DBCC CHECKIDENT (@table, reseed, 0);

fetch next from NfrTable_cursor into @table

end

close NfrTable_cursor

deallocate NfrTable_cursor

Created By Web Development Company | Distributed By MyBloggerThemes