SQL: taking a step back

Joe Celko has written a very interesting and enjoyable article at the Simple Talk site that I recommend to SQL Server database developers anywhere. We all approach our jobs with different mindsets and it’s always a good idea to step back at times and take a breather. Take a look at how your assumptions and habits could be slowing you down. To whet your appetite his 3 headers are: “Errors in complex technology”, “Getting Rid of Procedural Code” and “Getting Rid of Non-Procedural Code”.

Here’s a quote I just love from it:

People prefer static mental models, rather than a dynamic model. This was also the model for traditional programming languages – when you compile a program, you get the same executable code every time. That does not work in SQL; this is why we have query optimizers rather than procedural code in RDBMS.

SQL Server copy_only backup and restore

Previously I wrote an article listing information on SQL Server backups. I want to expand a little on the practical use of the new and useful copy_only backup available in SQL Server 2005 and 2008. You can’t get to copy_only backups/restores through the Management Studio for SQL Server 2005 (you have to use T-SQL) but you can using the new Management Studio for SQL Server 2008.

This is an ideal way to test production db’s on a development server without disrupting your normal backup schedule. In the following example I am doing a full db backup to the production backup drive:

BACKUP DATABASE [my_db]
TO DISK = N'Z:\backup\my_db_copyonly.bak'
WITH
NOFORMAT,
INIT,
copy_only,
NAME = N'my_db-Full Database CopyOnly Backup',
SKIP,
NOREWIND,
NOUNLOAD,
STATS = 10
GO


But when I restore it I would want to be sure that I’m moving the db files to the correct location on my development SQL Server, hence the WITH MOVE subcommands below.   I always want it to have a completely new name:

RESTORE DATABASE [my_db_copy]
FROM DISK = N'Z:\backup\my_db_copyonly.bak'
WITH
MOVE 'my_db_dat' TO 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_db_copydat.mdf',
MOVE 'my_db_log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\my_db_copylog.ldf',
FILE = 1,
NOUNLOAD,
REPLACE,
STATS = 10
GO


So now I have an up-to-date version of the production db on my system. Bear in mind that any logins for applications or users that are necessary would need to be recreated to be able work with this new copy. I keep a special login/user script handy and run it to recreate these.

When a decimal isn’t in TSQL

I saw a posting in SimonS Blog on SQL Server Stuff that intrigued me concerning decimals and SQL Server. I wanted to leave a comment but I don’t like having to register to do so I’ll do it here.

Simon’s point is that you should beware about making assumptions as to how code will treat expressions. An expression like 10/4 is simple for us to interpret in our heads but a strongly typed language like T-SQL sometimes needs a little help when you ’say’ integer but really mean ‘decimal’. Simon is a little unclear because he has a typo in the following statement: ‘In TSQL however you get 4′. I think he meant ‘2′.

But what he’s saying is important. I’ve fleshed out his concern in some code so we can easily see the problem:

declare @decnumb decimal(10,4);
set @decnumb = 10/4;
select 'set @decnumb = 10/4';
select @decnumb;

set @decnumb = cast ((10/4) as decimal);
select 'set @decnumb = cast ((10/4) as decimal)';
select @decnumb;

set @decnumb = (cast (10 as decimal))/(cast (4 as decimal));
select 'set @decnumb = (cast (10 as decimal))/(cast (4 as decimal))';
select @decnumb;

set @decnumb = (cast (10 as decimal))/(4);
select 'set @decnumb = (cast (10 as decimal))/(4)';
select @decnumb;

set @decnumb = (10)/(cast (4 as decimal));
select 'set @decnumb = (10)/(cast (4 as decimal))';
select @decnumb;

set @decnumb = 10/4.0;
select 'set @decnumb = 10/4.0';
select @decnumb;

set @decnumb = 10.0/4;
select 'set @decnumb = 10.0/4';
select @decnumb;

And here are the results:

set @decnumb = 10/4
2.0000

set @decnumb = cast ((10/4) as decimal)
2.0000

set @decnumb = (cast (10 as decimal))/(cast (4 as decimal))
2.5000

set @decnumb = (cast (10 as decimal))/(4)
2.5000

set @decnumb = (10)/(cast (4 as decimal))
2.5000

set @decnumb = 10/4.0
2.5000

set @decnumb = 10.0/4
2.5000

SQL Server Data Storage

The MDF
All db’s have a primary data file: the .mdf file. But secondary data files are (.ndf) are also allowed. The default location for all of these is:
C:\Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\Data
where ‘n’ is a unique number identifying an installed instance (I’ve got \MSSQL.1\MSSQL\Data).
This changes in SQL Server 2008 (Version 10):  here’s where an install on Vista is:
c:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\
Secondary data files are used to split up extremely large databases or to spread data across multiple disks (usually for performance reasons).
Data is written in 8K pages and pages are usually organized into 8 contiguous page ‘extents’ which make up the .mdf or .ndf file (16 extents per Mb). However it is possible for small tables to share a single extent (a mixed extent).

Transaction Log and how it works
At least one transaction log (.ldf) is necessary to run SQL Server. For small databases there is only one LDF. Only one can be used at a time.
Moving transaction logs to a separate drive from the data files is considered a ‘best practice’ since it allows for faster throughput (allows disk heads to remain in place for the serial writing of the transactions and not interrupted by data writes). Typical size for an .ldf file is 25% of the entire database size.
The log is recording more than just the SQL code we put in explicit ‘transactions’ (with a begin tran and commit tran). Implicit transactions are also carried out whenever any of the following occur: alter table, create, delete, drop, fetch, grant, insert, open, revoke, select, truncate table and update. In other words, nearly everything is logged. This may seem excessive but bear in mind that the .ldf is not only there to record your explicit transactions, it is also there so you can recover from a disaster.
Here’s what happens behind the scenes (pay no attention to that curtain, Dorothy) when data modifications occur:

1. data modification is sent by an app
2. affected data pages are found in or read into the buffer cache and modified (dirty bit is set for page)
3. before and after image of the changes recorded in the .ldf
4. acknowledgement is sent to app that change is committed
5. checkpoint writes the ‘dirty’ pages to disk, causing the dirty bit to flip. Now the page in cache is ‘clean’.

So the .mdf or .ndf file is only changed after a checkpoint. So that begs the question, ‘When does a checkpoint happen?’ It depends. Usually this process is controlled by SQL Server (usually better off just letting it handle this) but it is possible to force if necessary.
Another way of thinking about a Transaction Log is to visualize a snake eating its own tail example (don’t try this at home with a pet). Assuming a Full Recovery Model, if the head reaches the tail then the log is full and the db becomes read-only (selects work but changes don’t). A log backup is required to clean up (truncate) the log. It will work as long as transactions are committed, checkpointed and backed up. The log backup frees up space by deleting committed, checkpointed and backed up log entries and leaving the rest behind.

Using SQLCMD with SQL Server 2005

Intro
You can’t always use a GUI (examples are batch scripts, DAC, etc.) and so MS provides sqlcmd. This utility replaces the older ‘osql’ utility which won’t be supported in future releases. With sqlcmd you have a host of command line parameters to allow for a line by line interface with the database engine. You can find sqlcmd in this directory for SQL Server 2005:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn\

Using with Variables
One of the more powerful features is the ability to run in ‘variable mode’ so you can make simple (or complex) boilerplate programs. An example. You are always checking the last rows of particular tables but you want to be able to automate it. Using the sql file ‘latest.sql’ with this content:

select * from $(tablename) order by $(primarykey) desc
GO

you could do this with tblmytable (which has the primary key mytableid_pk) using this on the command line:

sqlcmd -i latest.sql -v primarykey="mytableid_pk" tablename="tblmytable" -o latest.output

The table contents would be contained in reverse primary key order in the file ‘latest.output’.

DAC
DAC is something different and more important for administrators. It is a Dedicated Administrative Connection and is accessed using:

sqlcmd -A

This is a very special feature as it allows access even when the server hangs or is otherwise unavailable. It has its own private SQL Server scheduler and so the only way it can be excluded from access is if the SQL Server service has been stopped or paused. You could go into an unresponsive server and attempt to diagnose the problem, end connections or just shut down properly:

sqlcmd -A
1> shutdown with nowait
2> go

Autocompletion and Intellisense in SQL Server

I understand you can use third party tools to add autocompletion and intellisense to Management Studio 2005 (for example, Redgate offers SQL Prompt Pro) but I haven’t tried it yet. I have installed SQL Server Express 2008 to play with.  I tried the new intellisense feature and I have to say I’m impressed with how nicely it’s built in to Management Studio.

For example, if you wait long enough after entering ‘use ‘ (don’t forget the space) it’ll give you a drop down of available db’s. That’s cool.
But even more nifty is typing ‘exec usp’ and then picking one of my own stored procedures from the list. Then hit space and there’s a list of the necessary parameters.  Wow!
And if you name all your tables with a ‘tbl’ prefix you can try ’select * from tbl’ and you should see a list of the tables. And, again if you use a standard naming convention you can pick a table and then a likely prefix for a column in a where clause like ’select * from tblnappy where str’ and get all the string columns you have listed. Or functions close to that spelling.
For more information access BOL on the MS website and look at the following topics:

• Transact-SQL Syntax Supported by Intellisense
  
• Using Intellisense

It certainly makes be eager to get the new computer that’s coming to me at work so I can install the Developer edition and start working with it.

Search and Replace in SQL Server

And now a chance to wax programmatic about the replace feature in SQL Server. Opening up a table and editing thousands of cells can be a nightmare and error prone. The replace fuction can be used in an update statement to do useful searching and replacing. Quickly. I learned how to do this using BOL (Books On Line) and an article by Bill Graziano on the SQL Team site.
So say we’ve got a table called tbldata with several fields in it including an nvarchar field called ’strdata’. I want to remove the string ‘nasty ‘ completely. I can do so like this:
update tbldata set strdata = replace(strdata, 'nasty ', '')
And if I want to fix a typo:
update tbldata set strdata = replace(strdata, 'mistaek', 'mistake')
This will, of course, change all occurences so if you want to get fancy you’ll need a where clause.
Whether this find and replace search changes all cases or not will depend on your collation which can be set at the database, table and even field level. So that is something else to bear in mind if you’re concerned about exact matching. In my case I usually set my databases to be:
COLLATE SQL_Latin1_General_CP1_CI_AS
which means that the search is case insensitive (you can tell since the second last initials say ‘CI’; case sensitive collations would be ‘CS’).

Learning SQL through online videos

There is a new free service available for learning about SQL Server, all you have to do is register. This is JumpstartTV (a division of Fourdeuce Inc which is owned by Andy Warren, Brian Knight, and Steve Jones). They have a few interesting topics including Hacking SQL Server. If there’s something in their topic list that you find interesting, you may want to check them out.

Improved SQL Server Index Scripting from Excel

I covered Index Scripts using Excel in an earlier post but I was obliged to make my spreadsheet do more over the past few workdays. Basically I needed to allow multiple indexed fields and multiple included fields. The following solution works for any situation between 0 and 3 of both.
My spreadsheet contains the following columns (so ‘Table’ is in cell A1):

• Table
• Field1
• Order1
• Field2
• Order2
• Field3
• Order3
• Include1
• Include2
• Include3
• Notes (this field isn’t necessary but I like to use to remind me how I decided to use this particular index)
• MultiField
• MultiInclude
• Index Name
• Insert
• Drops
• Reorgs
• Rebuilds

Your table name, of course, goes in the Table column (cell A2) and likewise we put in up to three Fields to combine for your index and their respective orders (ASC or DESC). Any included fields (only included in the leaf portion of the index and so contribute less size to the index) go in the the three Include columns. Those fields or includes you don’t use are left blank.
To break down the work a little I have the formulae broken up into the MultiField, MultiInclude, Index Name and Inserts columns.
Here’s the MultiField formula:

=IF(ISBLANK(B2),”",IF(ISBLANK(D2),”["&B2&"] “&C2,IF(ISBLANK(F2),”["&B2&"] “&C2&”,["&D2&"] “&E2,”["&B2&"] “&C2&”,["&D2&"] “&E2&”,["&F2&"] “&G2)))

The MultiInclude:

=IF(ISBLANK(H2),”",IF(ISBLANK(I2),”["&H2&"]“, IF(ISBLANK(J2),”["&H2&"],["&I2&"]“, “["&H2&"],["&I2&"],["&J2&"]“)))

The Index Name:

=”perfindex_”&A2&”_”&IF(ISBLANK(B2),”", IF(ISBLANK(D2),B2, IF(ISBLANK(F2),B2&”_”&D2,B2&”_”&D2&”_”&F2)))&IF(ISBLANK(H2),”", IF(ISBLANK(I2),”_incl_”&H2, IF(ISBLANK(J2),”_incl_”&H2&”_”&I2,”_incl_”&H2&”_”&I2&”_”&J2)))

And the Insert:

=”create nonclustered index ["&N2&"] on [dbo].["&A2&"] (“&L2&”) “&IF(ISBLANK(H2),”",”include (“)&IF(ISBLANK(H2),”",M2)&IF(ISBLANK(H2),”",”) “)&”with (statistics_norecompute = off, sort_in_tempdb = off, ignore_dup_key = off, drop_existing = off, online = off, allow_row_locks = on, allow_page_locks = on) on [primary];”

The Drops, Reorganize and Rebuilds fields are for Index administration.
Formula for Drops:

=”drop index “&A2&”.”&N2&”;”

Formula for Reorgs:

=”alter index “&N2&” on “&A2&” reorganize;”

Formula for Rebuilds:

=”alter index “&N2&” on “&A2&” rebuild with (fillfactor = 90);”

Creating a SQL Server database by script

This is easy enough and you can find scripts to create databases in SQL Server on the Microsoft technet and MSDN sites. I include it here to show you my preference. If you want to play with this do a search and replace to change the string ‘yourdb’ with whatever name you prefer. Be sure you don’t already have a db with the same name.  Also remember to change the hyphens to – - for the comments!

use master;
go

– if yourdb exists already delete it
if db_id (N’yourdb’) is not null drop database yourdb;
go

– Create the db, you may need to change the filename path
– if you installed SQL Server without the defaults
– The sizes here are up to you; I chose these for performance reasons
– but if your planned use for the db requires more oomph, add more yourself
create database yourdb
on
( name = yourdb_dat,
filename = ‘c:\program files\microsoft sql server\mssql.1\mssql\data\yourdbdat.mdf’,
size = 25mb,
maxsize = 1500mb,
filegrowth = 10mb )
log on
( name = ‘yourdb_log’,
filename = ‘c:\program files\microsoft sql server\mssql.1\mssql\data\yourdblog.ldf’,
size = 7mb,
maxsize = 375mb,
filegrowth = 10mb )
COLLATE SQL_Latin1_General_CP1_CI_AS;
go

– verify the database files and sizes
select name, physical_name, size, size*1.0/128 as [size in mbs]
from sys.master_files
where physical_name like N’%yourdbdat.mdf’ or
physical_name like N’%yourdblog.ldf’;
go

use yourdb
go

exec sp_dboption N’yourdb’, N’autoshrink’, N’false’
GO
exec sp_dboption N’yourdb’, N’autoclose’, N’false’
GO
exec sp_dboption N’yourdb’, N’quoted identifier’, N’false’
GO
exec sp_dboption N’yourdb’, N’ANSI warnings’, N’false’
GO
exec sp_dboption N’yourdb’, N’auto create statistics’, N’true’
GO
exec sp_dboption N’yourdb’, N’auto update statistics’, N’true’
GO

set nocount on
– A build table is useful if you develop a lot of databases,
– when you make changes you can always insert another
– row and have a personalized audit trail
create table [dbo].[tblBuild] (
[strscripttype] [nvarchar] (60) not null constraint def_tblBuild_strscripttype default ‘~na~’,
[strscriptran] [nvarchar] (150) not null constraint def_tblBuild_strscriptran default ‘~na~’
) on [primary]
go
declare @build [nvarchar] (150)
set @build = N’yourdb db script 1.0′
insert into tblBuild (strscripttype, strscriptran) values (@build, (cast(getdate() as nvarchar)))
–select * from tblBuild