Browse by Tags

SQL Saturday Jax May 3 2008

Tomorrow will be the long awaited third installment of SQL Saturday in Jacksonville FL. I want to thank Brian, Andy and the team for all their hard work putting the event together. If you attended my talk or just want to see some cool stuff about using the CLR in SQL Server check out my presentation...

Posted to Shawn Weisfeld [MVP] (Weblog) by sweisfeld on 05-02-2008
Need to delete lots of data, do it in small chunks

By adding a top clause to your delete statement you can delete a chunk of records at a time. By combining it with a while you can put it in a loop and wipe the entire table. WHILE EXISTS ( SELECT * FROM Foo ) BEGIN DELETE TOP ( 100 ) FROM Foo END Had someone send me an email telling me that it might...

Posted to Shawn Weisfeld [MVP] (Weblog) by sweisfeld on 04-25-2008
Drop Me!

I was trying to get rid of all the objects (tables, procedures, views) in my database and whipped up this little script and while it is far from perfect it is well worth sharing. . . SELECT CASE WHEN type = 'P' THEN 'DROP PROCEDURE ' + name WHEN type = 'U' THEN 'DROP TABLE...

Posted to Shawn Weisfeld [MVP] (Weblog) by sweisfeld on 04-12-2008
how can i get some performance statistics on my stored procedures in sql2005?

select case when dbid = 32767 then 'resource' else db_name(dbid) end [db_name] , object_schema_name(objectid,dbid) [schema_name] , object_name(objectid,dbid) [object_name] , sum(usecounts) [executions] , max(max_execution_time) [last_execution_time] , sum(total_logical_reads) [total_logical_reads...

Posted to roy ashbrook (Weblog) by royashbrook on 03-21-2008
SQL Server 2005 Emergency Diagnostic and Performance Queries

Great useful base diag scripts! Snipped from http://glennberrysqlperformance.spaces.live.com/blog/cns!45041418ECCAA960!893.entry -- SQL Server 2005 Emergency Diagnostic and Performance Queries -- Glenn Berry 3-17-2008 -- Step 1 - Check Task Manager. Are all CPUs above 90-95% for an extended period of...

Posted to roy ashbrook (Weblog) by royashbrook on 03-21-2008
how can i find when a stored procedure was last executed in sql 2005?

select top 10 t.text , s.last_execution_time , * from sys.dm_exec_query_stats s cross apply sys.dm_exec_sql_text(s.sql_handle) t where t.objectid is not null and text like '%procname%' order by s.last_execution_time desc obviously replace procname with the procname you want to find. you might...

Posted to roy ashbrook (Weblog) by royashbrook on 03-21-2008
enable clr procs on sql2005

EXEC sp_configure @configname = 'Show Advanced Options', @configvalue = 1 RECONFIGURE WITH OVERRIDE GO EXEC sp_configure @configname = 'clr enabled', @configvalue = 1 RECONFIGURE WITH OVERRIDE GO EXEC sp_configure go

Posted to roy ashbrook (Weblog) by royashbrook on 03-19-2008
2008 SQL Saturday Tampa

SQL Saturday Tampa 2008 was great, a big thank you to the organizers Pam, Wes, and team, great job guys! To those that attended my session on the SQL CLR I have uploaded my presentation here ( http://cid-afc22ba66ea68f7d.skydrive.live.com/self.aspx/Public/2008_02_16_SQL_CLR.zip )

Posted to Shawn Weisfeld [MVP] (Weblog) by sweisfeld on 02-17-2008
Pivot/Cross Tab Queries in SQL Server

There are many ways to turn rows of data into columns in SQL Server, I figured I would outline some of them and talk about some of the pro/cons of each. Sample 1: Using the SQL Server 2005 PIVOT operator. This is a great feature of SQL Server 2005 but has 2 obvious limitations. First it requires you...

Posted to Shawn Weisfeld [MVP] (Weblog) by sweisfeld on 01-21-2008
sql2005 recursion, common table expressions (CTEs), hierarchical data

A colleague of mine that is more familiar with Oracle than MSSql asked me how to create a CONNECT BY query in TSQL. CONNECT BY queries provide for recursive results or views on hierarchical data . The answer is to use a Common Table Expression or CTE . Here is a very simple example that is actually pretty...

Posted to roy ashbrook (Weblog) by royashbrook on 01-04-2008

Page 1 of 8 (76 items) 1 2 3 4 5 Next > ... Last »