How Is SQL Server Books On Line Created?

Buck Woody one of the Technical Writers in the SQL Product Group has started a blog about documentation. His first post SQL Server Books Online explains how Books On Line is created. Did you know that Books Online has over 58,000 pages of content?

Use A Combination OF NULLIF and COALESCE TO Display A Custom Value

Let's you inherit a table with a column that can contain blanks, empty strings or NULL values. I am saying inherit because surely you would have a constraint on the column that wouldn't allow those values to begin with right? For all the values that are '',' ' or NULL you want to display 'N/A'.
What is the best way to do this? You can Use CASE and test for the values or you can use COALESCE with NULLIF which is much shorter. A lot of people don't know that you can stack these functions so that it makes your life so much easier.
Let's get started

CREATE TABLE Blah (SomeCol VARCHAR(33))

INSERT Blah VALUES(NULL)
INSERT Blah VALUES('')
INSERT Blah VALUES(' ')
INSERT Blah VALUES('A')
INSERT Blah VALUES('B B')

--Using COALESCE and NULLIF
SELECT COALESCE(NULLIF(RTRIM(SomeCol),' '),'N/A')
FROM Blah


--Using CASE
SELECT CASE WHEN RTRIM(SomeCol) = '' THEN 'N/A'
WHEN RTRIM(SomeCol) IS NULL THEN 'N/A'
ELSE SomeCol END SomeCol
FROM Blah


Output for both queries
-----------------------
N/A
N/A
N/A
A
B B

Give Me One Good Reason Why You Would Store Documents In The Database?

Why would you ever store PDF, Doc or Excel files in the database? What is the point? I don't see any advantages only disadvantages
Let's say you have 8 webservers and 1 big SQL monster. If you store these documents in the DB and you get hit by these 8 webservers for documents all the time your DB is going to slow down. A much better way is to have the files on the webservers itself, sending 9MB pdf files over the network is just wrong.
Also if you store all these files in the DB your backups will take much longer.
Updating BLOBs is another pain in the neck; UPDATETEXT and WRITETEXT are not my favorite SQL commands.

So here is the question:
Would you store images/documents/spreadsheets in the Database and why?

Microsoft SQL Server Database Publishing Wizard 1.0,SharePoint Server 2007 SDK And Windows SharePoint Services v3 SDK Available For Download

Microsoft SQL Server Database Publishing Wizard 1.0
SQL Server Database Publishing Wizard enables the deployment of SQL Server databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a single SQL script file which can be used to recreate a database (both schema and data) in a shared hosting environment where the only connectivity to a server is through a web-based control panel with a script execution window. If supported by the hosting service provider, the Database Publishing Wizard can also directly upload databases to servers located at the shared hosting provider.

Optionally, SQL Server Database Publishing Wizard can integrate directly into Visual Studio 2005 and/or Visual Web Developer 2005 allowing easy publishing of databases from within the development environment.



SharePoint Server 2007 SDK
The Microsoft Office SharePoint Server 2007 Software Development Kit (SDK) contains conceptual overviews, programming tasks, code samples, references, and an Enterprise Content Management (ECM) starter kit to guide you in developing solutions based on Microsoft Office SharePoint Server 2007.

Windows SharePoint Services v3 SDK
The Windows SharePoint Services 3.0 software development kit (SDK) contains conceptual overviews, programming tasks, samples, and references to guide you in developing solutions based on Microsoft Windows SharePoint Services 3.0.

Check If Auto Update Statistics Is Enabled With DATABASEPROPERTY

How do you check if auto update statistics is enabled on your database? It is pretty easy to check that, you can use the DATABASEPROPERTY function
Run the following line of code

SELECT DATABASEPROPERTY('pubs','IsAutoUpdateStatistics') AS IsAutoUpdateStatistics

If 1 is returned(true) it is enabled, if 0 is returned(false) then it is not enabled

Now to save me (and you) time I have pasted a code block below with all the properties, just change the database name from pubs to your database name and run the code


DECLARE @v VARCHAR(55)
SELECT @v = 'pubs'

SELECT
DATABASEPROPERTY(@v,'IsAnsiNullDefault') AS IsAnsiNullDefault,
DATABASEPROPERTY(@v,'IsAnsiNullsEnabled') AS IsAnsiNullsEnabled,
DATABASEPROPERTY(@v,'IsAnsiWarningsEnabled') AS IsAnsiWarningsEnabled,
DATABASEPROPERTY(@v,'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTY(@v,'IsAutoCreateStatistics') AS IsAutoCreateStatistics,
DATABASEPROPERTY(@v,'IsAutoShrink') AS IsAutoShrink,
DATABASEPROPERTY(@v,'IsAutoUpdateStatistics') AS IsAutoUpdateStatistics,
DATABASEPROPERTY(@v,'IsBulkCopy') AS IsBulkCopy,
DATABASEPROPERTY(@v,'IsDboOnly') AS IsDboOnly,
DATABASEPROPERTY(@v,'IsDetached') AS IsDetached,
DATABASEPROPERTY(@v,'IsEmergencyMode') AS IsEmergencyMode,
DATABASEPROPERTY(@v,'IsInLoad') AS IsInLoad,
DATABASEPROPERTY(@v,'IsInRecovery') AS IsInRecovery,
DATABASEPROPERTY(@v,'IsAutoClose') AS IsAutoClose,
DATABASEPROPERTY(@v,'IsInStandBy') AS IsInStandBy,
DATABASEPROPERTY(@v,'IsLocalCursorsDefault') AS IsLocalCursorsDefault,
DATABASEPROPERTY(@v,'IsNotRecovered') AS IsNotRecovered,
DATABASEPROPERTY(@v,'IsNullConcat') AS IsNullConcat,
DATABASEPROPERTY(@v,'IsOffline') AS IsOffline,
DATABASEPROPERTY(@v,'IsQuotedIdentifiersEnabled') AS IsQuotedIdentifiersEnabled,
DATABASEPROPERTY(@v,'IsReadOnly') AS IsReadOnly,
DATABASEPROPERTY(@v,'IsRecursiveTriggersEnabled') AS IsRecursiveTriggersEnabled,
DATABASEPROPERTY(@v,'IsShutDown') AS IsShutDown,
DATABASEPROPERTY(@v,'IsSingleUser') AS IsSingleUser,
DATABASEPROPERTY(@v,'IsSuspect') AS IsSuspect,
DATABASEPROPERTY(@v,'IsTruncLog') AS IsTruncLog,
DATABASEPROPERTY(@v,'Version') AS Version

So what do all these values mean? Here is a list of all the properties

IsAnsiNullDefault
Database follows SQL-92 rules for allowing null values.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAnsiNullsEnabled
All comparisons to a null evaluate to unknown.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAnsiWarningsEnabled
Error or warning messages are issued when standard error conditions occur.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoClose
Database shuts down cleanly and frees resources after the last user exits.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoCreateStatistics
Existing statistics are automatically updated when the statistics become out-of-date because the data in the tables has changed.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoShrink
Database files are candidates for automatic periodic shrinking.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsAutoUpdateStatistics
Auto update statistics database option is enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsBulkCopy
Database allows nonlogged operations.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsCloseCursorsOnCommitEnabled
Cursors that are open when a transaction is committed are closed.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsDboOnly
Database is in DBO-only access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsDetached
Database was detached by a detach operation.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsEmergencyMode
Emergency mode is enabled to allow suspect database to be usable.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsFulltextEnabled
Database is full-text enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsInLoad
Database is loading.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsInRecovery
Database is recovering.
1 = TRUE
0 = FALSE
NULL1 = Invalid input

IsInStandBy
Database is online as read-only, with restore log allowed.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsLocalCursorsDefault
Cursor declarations default to LOCAL.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsNotRecovered
Database failed to recover.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsNullConcat
Null concatenation operand yields NULL.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsOffline
Database is offline.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsQuotedIdentifiersEnabled
Double quotation marks can be used on identifiers.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsReadOnly
Database is in a read-only access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsRecursiveTriggersEnabled
Recursive firing of triggers is enabled.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsShutDown
Database encountered a problem at startup.
1 = TRUE
0 = FALSE
NULL1 = Invalid input

IsSingleUser
Database is in single-user access mode.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsSuspect
Database is suspect.
1 = TRUE
0 = FALSE
NULL = Invalid input

IsTruncLog
Database truncates its logon checkpoints.
1 = TRUE
0 = FALSE
NULL = Invalid input

Version
Internal version number of the Microsoft® SQL Server™ code

A Witchbrew Of SQL Server News And Interesting Links

Since I have nothing interesting or fascinating to say I decided to put some links up to some posts of people who do have something interesting to say.
We will start with Hugo Kornelis who has two posts about why clustered index ordering is not guaranteed in SQL Server. The first article can be found here: http://sqlblog.com/blogs/hugo_kornelis/archive/2006/12/31/Beatles-vs-Stones.aspx
And the second article that explains the behaviour can be found here: Beatles vs Stones Explanation

Developer.com has announced their Database Tool or Add-in winner and the winner is......SQL Server 2005. You can read the whole article here http://www.developer.com/db/article.php/10920_3653956_1

Euan Garden has a post telling us that SQL Server Compact Edition launches (finally)

Tim O'Reilly posted his latest State of the Computer Book Market article and SQL server is still going strong. You can fnd that article here: State of the Computer Book Market, Q4 06, Part 1, Overall Market Trend

And we will end with Internet Maverick Marc Cuban who wrote an interesting article titled Why I Don't Wear a Suit and Can't Figure Out Why Anyone Does !
I don't wear a suit either, I actually wear jeans and sneakers most of the time. When i used to work in Silicon Alley (Broadway and 21st street in New York City) I actually came to work in shorts in the summer but then again what do you expect when you have 2 lizards and a cat roaming around the office?