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

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.  

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

The new Who Is Active

... is here! go get it

SQL Server - Stress Testing

Came across this tool today for SQL Server stress testing. With the PFIN upgrade looming, I am gonna put this tool to use one of these days: http://www.sqlstress.com/Overview.aspx

Refresh All Views - SQL Server

Here is a stored procedure that refreshes all views -

CREATE PROCEDURE dba.RefreshAllViews
AS
DECLARE @ViewName NVARCHAR(max)
DECLARE @SQL NVARCHAR(max)

DECLARE RefreshViews CURSOR
FOR
SELECT [name] AS ViewName
FROM sys.VIEWS

OPEN RefreshViews

FETCH NEXT
FROM RefreshViews
INTO @ViewName

WHILE @@FETCH_STATUS = 0
BEGIN
 SET @SQL = 'IF EXISTS (SELECT * FROM sysobjects WHERE type = ''V'' AND name = ''' + @ViewName + ''')
   BEGIN
  exec sp_refreshview N''dbo.' + @ViewName + '''END'

 EXEC (@SQL)

 FETCH NEXT
 FROM RefreshViews
 INTO @ViewName
END

CLOSE RefreshViews

DEALLOCATE RefreshViews
GO

Reduce backup times and MSDB performance tuning

Brent Ozar's post about the backup bottlenecks here: http://www.brentozar.com/archive/2009/05/brents-backup-bottleneck-msdb/

The backup/restore history is logged in msdb database and over the time, the tables become large that they become backup bottlenecks if you don't cleanup the history.

Here is how you do it: http://weblogs.sqlteam.com/geoffh/archive/2008/01/21/MSDB-Performance-Tuning.aspx

World's best SQL Tutorial!

Learning to write SQL queries using this tool doesn't feel like you are actually learning something - I mean, the boredom part of it. It is indeed the worlds best SQL Tutorial. I wish there are more tools like this for othe technologies.

Download it from here: http://sol.gfxile.net/galaxql.html

SQL Server Login Properties

I was searching for something and I stumbled upon this script which returns a login's properties like created time, password details, whether it is active etc.,

What if your server has hundreds of logins and you need to quickly get an overview of all the logins? There may be better ways, but I shamelessly stole the above script and put it inside a cursor, just for for fun :) Enjoy!

DECLARE @name NCHAR(100)
DECLARE @LoginTable TABLE (
 LoginName NCHAR(100)
 ,PasswordLastSetTime SQL_VARIANT
 ,IsExpired SQL_VARIANT
 ,IsLocked SQL_VARIANT
 ,IsMustChange SQL_VARIANT
 ,LockoutTime SQL_VARIANT
 ,BadPasswordcount SQL_VARIANT
 ,BadPasswordTime SQL_VARIANT
 ,HistoryLength SQL_VARIANT
 ,PasswordHash SQL_VARIANT
 ,PasswordLastSet SQL_VARIANT
 )

DECLARE Login_Cursor CURSOR
FOR
SELECT NAME
FROM syslogins

OPEN Login_Cursor

FETCH NEXT
FROM Login_Cursor
INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
 INSERT INTO @LoginTable
 SELECT @name AS 'LoginName'
  ,LOGINPROPERTY(@name, 'PasswordLastSetTime') AS PasswordLastSetTime
  ,LOGINPROPERTY(@name, 'IsExpired') AS IsExpiried
  ,LOGINPROPERTY(@name, 'IsLocked') AS IsLocked
  ,LOGINPROPERTY(@name, 'IsMustChange') AS IsMustChange
  ,LOGINPROPERTY(@name, 'LockoutTime') AS LockoutTime
  ,LOGINPROPERTY(@name, 'BadPasswordCount') AS BadPasswordCount
  ,LOGINPROPERTY(@name, 'BadPasswordTime') AS BadPasswordTime
  ,LOGINPROPERTY(@name, 'HistoryLength') AS HistoryLength
  ,LOGINPROPERTY(@name, 'PasswordHash') AS PasswordHash
  ,LOGINPROPERTY(@name, 'PasswordLastSetTime') AS PasswordLastSet

 FETCH NEXT
 FROM Login_Cursor
 INTO @name
END

CLOSE Login_Cursor

DEALLOCATE Login_Cursor

SELECT *
FROM @LoginTable

Attach a database without log file (ldf fife)

I downloaded SQL Server 2012 RC0 today and the sample database to poke around from here. The download contains the data file (mdf file) of adventure works but not ldf. For a moment, I thought why would codeplex not include ldf file - it seems that it is indeed possible to attach a database without ldf file.

Head over to CREATE Database on BOL and CTRL+F for  " FOR_ATTACH_REBUILD_LOG"

Script below: