Drop tables

I was reading Itzik Ben-Gan's T-SQL Fundamentals book and I learned something that I didn't know so far. You can drop multiple tables in a single drop query. I know, its stupid. I am monument of stupidity right now!

drop table dbo.Orders, dbo.Employees

NULL

For relational databases, the NULL is the unknown or it can simply mean that the value is absent. Just like in real world you can't add something to an unknown quantity and make it a known one in the relational world. So NULL is like the evil queen from fairy tales that turns every known value to unknown if not properly handled. Lets see

SELECT 'binyoga' + NULL;
GO 

Results a NULL. It seems this is the reason that some developers don't like allowing NULLs in their databases. Anyways, we all know that an unknown is not equal to another unknown, right? Even if it is, it is still unknown. SQL Server knows that too.

IF NULL = NULL
 SELECT 'Whoa, I cracked the unknown'
ELSE
 SELECT 'Nope. Unknown is not equal to unknown :(' 

So how do you deal with this? Enter IS NULL.

IF NULL IS NULL
 SELECT 'Yes. It is NULL. You don''t need my help to figure'
ELSE
 SELECT 'No, the world is ending. NULL is NOT NULL anymore'

You can use this guy to find out all the people that doesn't have Mr/Mrs titles in AdventureWorks database.

SELECT LastName + ',' + FirstName AS Name
FROM Person.Person
WHERE Title IS NULL
-- I wonder why 18K people have no titles!

Okay, so you push data to reports people and they don't like NULLs. How do you handle that? Enter ISNULL() function. More about that later. Trivial and basic stuff - yes. But blogging about it is a way to think again about it. Where did I get this info from? Like I said, I have been reading SQL Server Bible by Paul Nielsen and my next few posts are likely to be influenced by the book. Its an aswesome book, btw!

Why can't I drop this table?

Cannot DROP TABLE 'dbo.PleaseDropMe' because it is being referenced by object '_dta_mv_25'.

Looking at the _dta_ prefix, it looked like a view created from database engine tuning advisor. The view was created with SCHEMABINDING . When a view is created with SCHEMABINDING specified, the underlying tables can not be modified in a way that would break the view. Explanation here. So in this case I first had to drop the view and then the table

 This is a nice feature if you would like to restrict some of your important tables from being 'accidentally' dropped. Lets test it:

USE BINYOGA
GO
IF OBJECT_ID('dbo.DropTest', 'U') IS NOT NULL
DROP TABLE dbo.DropTest;

CREATE TABLE dbo.DropTest (
 c1 int primary key,
 c2 char(2)
)
GO

-- Lets create a view

CREATE VIEW DropTestView WITH SCHEMABINDING
AS
SELECT c1, c2 FROM dbo.DropTest
GO

--Lets try and drop the table

DROP TABLE dbo.DropTest
GO

You'll see the error

Msg 3729, Level 16, State 1, Line 1 Cannot DROP TABLE 'dbo.DropTest' because it is being referenced by object 'DropTestView'.

SQL Server Unattended installations

I was aware of the configuration.ini file that gets created during the normal GUI installation of SQL Server. This configuration file can be changed and reused to perform command line/remote installations. But in order to generate that initial file, you have to go through the GUI steps initially. Thats what I used to think. I have been reading SQL Server Bible by Paul Nielsen (Its a great book, btw. I wish I read it a couple of years ago) and I came across an interesting piece of info about unattended installations - Microsoft ships a template.ini file that you can use to kick off remote installations in the installation dvd. 

There is a long (very long!) article on BOL about command line and unattended installs:

There are two ways to go about it

You run the setup.exe file from command prompt specifying the components, passwords you want to install, passwords etc:

Start /wait <CD or DVD Drive>\servers\setup.exe /qb INSTANCENAME=<InstanceName> ADDLOCAL=All PIDKEY=<pidkey value with no "-"> SAPWD=<StrongPassword> SQLACCOUNT=<domain\user> SQLPASSWORD=<DomainUserPassword> AGTACCOUNT=<domain\user> AGTPASSWORD=<DomainUserPassword> SQLBROWSERACCOUNT=<domain\user> SQLBROWSERPASSWORD=<DomainUserPassword>

Use the template.ini file to create a configuration.ini file and kick off the install

setup.exe /settings <full path to your .ini file>

Example:

setup.exe /settings C:\binyoga\sqlinstall.ini

You may use \qn switch for a silent installation with no dialogs and \qb switch if you can live with progress dialogs.

Click here for a sample template.ini file

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