Friday, June 22, 2012

select top x % from a table

I didn't know you could do this. Thanks to @shark at dba.stackexchange, now I do:

Source


declare @top_val int = 30
select top (@top_val) percent
    col1,
    col2,
    col3from yourTable

Wednesday, June 20, 2012

SQL Server Internals Viewer - CodePlex

Internals Viewer is a tool for looking into the SQL Server storage engine and seeing how data is physically allocated, organised and stored.

All sorts of tasks performed by a DBA or developer can benefit greatly from knowledge of what the storage engine is doing and how it works
I found it very useful while troubleshooting. You can download it from here

Btw, Codeplex is looking good after Metro UI makeover.  

Tuesday, June 12, 2012

Exporting table to excel - TSQL

There are many ways to export a table to excel - using SSIS/DTS package, bcp or simply right click on the grid results, copy and paste results to a new excel file - but most of them are cumbersome.

Here is an easier method which uses sp_makewebtask stored procedure. It needs to be enabled first using sp_configure.


sp_configure 'show advanced options' ,1;
GO
RECONFIGURE;
GO
sp_configure 'Web Assistant Procedures',1;
GO
RECONFIGURE
GO
Then, execute the below:
EXEC sp_makewebtask @outputfile = 'E:\testing.xls'
 ,@query = 'Select * from [RSBY-FINAL]..Tbl_policyRenewalDetails'
 ,@colheaders = 1
 ,@FixedFont = 0
 ,@lastupdated = 0
 ,@resultstitle = 'Testing details'
GO