A word on the decimal data type

Was inspired to write up a few notes on the SQL Server data type.  This is pretty basic stuff, but everyone should know it.

If you declare a column as DECIMAL, it will default to the precision and scale 18, 0.  Not very useful, is it?  You might as well have used a bigint column to store integer values like that.  So first off, when you declare a decimal column, always give it precision and scale.

Review: SQL Pocket Guide By Jonathan Gennick

The SQL Pocket Guide is a cross-platform reference guide for SQL standard syntax, covering Oracle, DB2, SQL Server, MySQL, and PostgreSQL.

Surely this book was quite an undertaking for the author.  The book provides a good amount of platform-specific detail when necessary.  For example, commendable is the inclusion of the importance of SET XACT_ABORT {ON|OFF} on SQL Server transactions, or caveats about InnoDB vs non-transactional tables in MySQL.  This is good detail that shows the author clearly has experience and insight which provide value to the reader.

Regularly, the author breaks a topic down by platform, which is helpful in navigation.  While the book provides syntax and examples on standard ANSI SQL statements in each platform, it avoids comparing and contrasting usage except when necessary.  This is a good thing, I can't see why the author need to include full cross references of simple syntax.

Sometimes though, detail is lacking. For example, the LAG and LEAD functions are implicitly described as supported by SQL Server when they are not.  This isn't necessarily errata, but is potentially confusing.

What would be considered errata is the incorrect note that SQL Server supports the TRIM string function. This wasn't the only errata I found and submitted in a few hours of skipping back and forth through the book, as one would typically make use of it.



In the interest of full disclosure, this blog was provided a free electronic (PDF) copy of the book through O'Reilly media's Blogger Review Program.  This review was not otherwise solicited or compensated from O'Reilly, and the opinions of the review are the opinion of its author.

Script TSQL Server Level security

Here's a handy script that's part of my toolbox everywhere I go.  Scripts out and identifies basic server level security objects, and generates a tsql statement to recreate the objects.

Note that this script only works on SQL 2005 or above.  This is far from an official script, so caveat emptor.

I welcome to any and all feedback on these scripts.  I also have a database-level security script here.

--Script Server Level security

Script TSQL Database-level security

Here's a handy script that's part of my toolbox everywhere I go.  Scripts out and identifies basic database level security objects, and generates a tsql statement to recreate the objects. 

Note that this script only works on SQL 2005 or above.  This is far from an official script, so caveat emptor.

I welcome to any and all feedback on these scripts.  I also have a server-level security script here.

--Run the below on each database for database-level security.