Change DataTable ColumnType after loading data

Giving Dataset as input

private DataSet ChangeDataTableColumnType(DataSet ds,string tablename,string columnName,Type dataType)
{
DataTable dt = ChangeDataTableColumnType(ds.Tables[tablename], columnName, dataType);
ds.Tables.Remove(tablename);
ds.Tables.Add(dt);
return ds;
}

Giving DataTable as input

private DataTable ChangeDataTableColumnType(DataTable dt,string columnName,Type dataType)
{
DataTable newDt = dt.Clone();
newDt.Columns[columnName].DataType = dataType;
newDt.Merge(dt, true, MissingSchemaAction.Ignore);
return newDt;
}

SQL : Search text in store procedure

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%search text%'
AND ROUTINE_TYPE='PROCEDURE'

DB structure compare query

select distinct s1.type,s1.type_desc,count(s1.type)
FROM sys.objects s1 with(nolock)
left join sys.objects s2 with(nolock) on s2.object_id = s1.parent_object_id
where s1.name not like '%__SQLXMLB%' and s1.type not in ('IT','SQ')
group by s1.type,s1.type_desc

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

SELECT distinct s1.name,s1.type,s1.type_desc,s2.name as parentname
FROM sys.objects s1 with(nolock)
left join sys.objects s2 with(nolock) on s2.object_id = s1.parent_object_id
where s1.name not like '%__SQLXMLB%' and s1.type not in ('IT','SQ')
order by s1.type,s1.name

SQl : Grouping Sets

Grouping Sets is an extension to the GROUP BY clause that lets users define multiple grouping in the same query. Grouping Sets produce a single result set that is equivalent to a UNION ALL of differently grouped rows, making aggregation querying and reporting easier and faster.

Example:

SELECT year (order_date) AS Year, quarter (order_date) AS Quarter, COUNT (*) AS Orders FROM sales_order GROUP BY GROUPING SETS ((Year, Quarter), (Year))

ORDER BY Year, Quarter

SQl : Insert Multiple Rows in a single statement

This new feature enables the developer to insert multiple rows in a single SQL Statement.

Example:

--SQL Server 2005

INSERT dbo.EMPLOYEE(SALARY) VALUES (1000)

INSERT dbo.EMPLOYEE(SALARY) VALUES (2000)

INSERT dbo.EMPLOYEE(SALARY) VALUES (3000)

INSERT dbo.EMPLOYEE(SALARY) VALUES (4000)

--SQL Server 2008

INSERT dbo. EMPLOYEE(SALARY) VALUES (1000),(2000),(3000),(4000)

SQL SERVER - Stored Procedure - Clean Cache and Clean Buffer

Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS

SQL concate top N rows of a column

declare @emp varchar(max)
SET @emp=''
select top(4) @emp=@emp+ cast(empid as varchar) + ',' from employees
select @emp
select SUBSTRING(@emp,0,len(@emp)-1)