.NET Rocks Podcast: Paul Randal on SQL Server 2005 Performance and Recovery

.NET Rocks has made available their latest podcast. From the site:

"Microsoftee Paul Randal drops by for an engaging talk about his contributions
to the recoverability of SQL Server 2005, which are many (Can you say CHECKDB?).
An old friend of ours, and a fairly new friend of Paul's, busts in on the
conversation and makes a cameo appearance. "

Download the podcast here:http://dotnetrocks.com/default.aspx?showID=220

How To Make A FileGroup Read Only in SQL Server 2005

How to make a filegroup read only in SQL Server 2005? This question popped up today on tek-tips. This is how you do that: First we will create a new database named TestFilegroup. Next we will add a filegroup named Test1FG1 which contains a file named test1dat3.
The next step is to do an alter database modift filegroup readonly command
Here is the complete script

USE master
go

--Create New DB For Testing
CREATE DATABASE TestFilegroup
go


--Create FileGroup
ALTER DATABASE TestFilegroup
ADD FILEGROUP Test1FG1;

--Add file to fileGroup
ALTER DATABASE TestFilegroup
ADD FILE
(
NAME = test1dat3,
FILENAME = 'c:\t1dat3.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB

)
TO FILEGROUP Test1FG1

--Make FileGroup ReadOnly
ALTER DATABASE TestFilegroup
MODIFY FILEGROUP Test1FG1 Read_Only;

Use Read_Only not ReadOnly because the keyword READONLY will be removed in a future version of Microsoft SQL Server. Avoid using READONLY in new development work, and plan to modify applications that currently use READONLY. Use READ_ONLY instead.




Now what happens when you try to create a table on filegroup Test1FG1?
USE TestFilegroup
GO
CREATE TABLE abc (id INT) ON Test1FG1

You will see the following error message
Server: Msg 1924, Level 16, State 2, Line 1
Filegroup 'Test1FG1' is read-only.

You can use sys.filegroups and check the is_read_only column to find out if a filegroup is read only
SELECT is_read_only
FROM sys.filegroups
WHERE name = 'Test1FG1'

Here is the result
is_read_only
------------
1




Using NTFS Compression with Read-Only User-defined Filegroups and Read-Only Databases
SQL Server 2005 supports NTFS compression of read-only user-defined filegroups and read-only databases. You should consider compressing read-only data in the following situations:
You have a large volume of static or historical data that must be available for limited read-only access.
You have limited disk space.

ASP.NET Library Site Launched

A friend of mine has started the ASP.NET Library site. The idea of this site is to provide ASP.NET articles, code and tips for professional ASP.NET developers. The site right now offers Resources, Articles and a Code Bank. Under Code Bank you can find the following:
Applications
Controls
Database
Email
HTML

So go ahead and check it out: http://aspnetlibrary.com/

Arrays and Lists Article Updated For SQL Server 2005

SQL Server MVP Erland Sommarskog has updated his Arrays and Lists article and this new version deals exclusively with SQL Server 2005. This is a must read if you are a SQL Server developer. So what are you waiting for? Stop wasting your time here and start reading Arrays and Lists in SQL Server 2005

The Digg Effect

Someone posted The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools post on Digg and over a thousand people dugg the story. I got a massive amount of traffic, so much that I had to get a new counter for this blog because it stopped working when this blog got a total of 99999 hits. After all these diggs people started to add this post to del.icio.us and so far 183 people have added it to their delicious account. You can see all these people here: http://del.icio.us/url/9be2731b569601719d645d97e88254b4

Here is a graph of the pageviews



I promise that I WILL write a SQL post this weekend and it will be about partitioned functions It will show you how you can use $PARTITION to select data from a specific partition

Top 5 Post and Google Searches For Jan and Feb 2007

Usually I do this once a month but I decided to do it once every two months from now on, I guess you can call it lazyness. Here are the top 5 posts for January and February 2007 in terms of pageviews

1 The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools
2 Ten SQL Server Functions That You Hardly Use But Should
3 Clippy Is Not Dead, Clippy Is Alive.....On Linux
4 Increase Your Productivity With Query Analyzer
5 The Real Reason Why Condoleezza Rice Never Smiles

So two of these (Clippy and Condoleezza) have nothing to do with SQL Server.

Top Queries
These are searches that people typed in the Google search box on this site
I always keep track of these because it gives me some ideas of what to write
Here is the top list for the last 2 months, I left out the adult searches ;-)

None
did france pay brazil to win the cup
NotMyFault
query analyzer not view
sql login
saltrain
notmyfault
%disk read time
defrag
daylight saving SQL Express
maximum number database
openrowset
SQL queries
tree structure catalogue
rainbowfish children book
failed for login sa
jacobs
sysinternals
origin of the name jacobs

Top Ten Posts Of All Time

Here is the list of the ten most popular posts in terms of pageviews since I started collecting this info. Damn Clippy how did he/she get to number 6?

1 The Sysinternals Troubleshooting Utilities have been rolled up into a single Suite of tools
2 SQL Query Optimizations
3 Ten SQL Server Functions That You Hardly Use But Should
4 Five Ways To Return Values From Stored Procedures
5 Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server connection. SQL 2005
6 Clippy Is Not Dead, Clippy Is Alive.....On Linux
7 NULL Trouble In SQL Server Land
8 COALESCE And ISNULL Differences
9 Increase Your Productivity With Query Analyzer
10 Three Ways To Display Two Counts From a Table Side By Side

How Do I? video series focused on SQL Server 2005 Express

The http://www.asp.net/ site posted a great new "How Do I?" video series focused on SQL Server 2005 Express (which you can download and use completely for free).
So what is in this series?

#1 What is a Database?
(28 minutes, 15 seconds)

#2 Understanding Database Tables and Records
(24 minutes, 56 seconds)

#3 More about Column Data Types and Other Properties
(21 minutes, 37 seconds)

#4 Designing Relational Database Tables
(34 minutes, 10 seconds)

#5 Manipulating Database Data
(40 minutes, 20 seconds)

#6 More Structured Query Language
(23 minutes, 13 seconds)

#7 Understanding Security and Network Connectivity
(40 minutes, 59 seconds)

#8 Connecting your Web Application to SQL Server 2005 Express Edition
(1 hour, 5 minutes)

#9 Using SQL Server Management Studio
(40 minutes, 26 seconds)

#10 Getting Started with Reporting Services
(32 minutes, 51 seconds)

#11 Building and Customizing Reports in Business Intelligence Development Studio
(44 minutes, 50 seconds)

#12 Creating and Using Stored Procedures
(42 minutes, 34 seconds)

#13 Enabling Full-Text Search in your Text Data
(38 minutes, 25 seconds)

You can download all of these here: http://www.asp.net/learn/videos/default.aspx?tabid=63#sql

Enjoy

Visual Studio Code Name "Orcas" - March 2007 Community Technology Preview (CTP) Available For Download

Get it here and start messing around with LINQ:

http://www.microsoft.com/downloads/details.aspx?FamilyID=b533619a-0008-4dd6-9ed1-47d482683c78&DisplayLang=en

From the site:

Visual Studio code name “Orcas” delivers on Microsoft’s
vision of smart client applications by enabling developers to rapidly create
connected applications that deliver the highest quality rich user experiences.
This new version enables any size organization to rapidly create more secure,
manageable, and more reliable applications that take advantage of Windows Vista
and the 2007 Office System. By building these new types of applications,
organizations will find it easier than ever before to capture and analyze information
so that they can make effective business decisions.



This download is the March 2007 Community Technology Preview of Microsoft
Visual Studio Code-Named “Orcas”. This CTP is available in English only.



Note: This CTP is available as a Virtual
PC image or as a self-extracting install. If you wish to use the Virtual PC
image you will need Virtual PC or Virtual Server to run this image. If you wish
to use the self extracting install, we advise that you do not install this on a
production machine. Depending on your hardware the download files make take
between 30-60 minutes to decompress.



This CTP targets early adopters of the Microsoft technology, platform, and
tools offerings. It enables developers to experience the upcoming toolset and
underlying platform improvements. We designed this release to enable developers
to try out new technology and product changes, but not to build production
systems. This limitation is fully covered in the EULA that accompanies this
CTP.



The highlights of this CTP include:

• LINQ
  
  The LINQ Project: this CTP represents a major milestone in the LINQ
  project. For more information about LINQ click here.
  
• VB 9.0 Language
  Support: This CTP contains the following language features:
• Query Expressions:
  Basic querying, filtering, and ordering support
• Object Initializers
• Extension Methods
• Local Variable Type
  Inference
• Anonymous Types
• XML literals
• XML properties
• New Line and
  Expression IntelliSense
• C# 3.0 Language
  Support: This CTP implements all of the C#3.0 language features from the
  May LINQ CTP including:
• Query Expressions
• Object and Collection
  Initializers
• Extension Methods
• Local Variable Type
  Inference and Anonymous Types
• Lambdas bound to
  Delegates and Expression trees
• Complete design-time
  support: Intellisense, Formatting, Colorization
• LINQ to ADO.NET
• ADO.NET is fully
  integrated with LINQ and offers many options for using LINQ in various
  scenarios: LINQ to SQL provides direct access to database tables from
  the programming environment, LINQ to Entities enables developers to use
  LINQ over EDM models, and LINQ to DataSet allows the full expressivity
  of LINQ to be used over DataSets.
• LINQ to Entities
  enables developers to program against a relational database using a view
  of the data that is appropriate for the application they are building,
  independent of the structure of the underlying database. The use of the
  Entity Data Model (EDM) enables developers to design models that follow
  the concepts built into the application, instead of having to map them
  to constructs available in relational stores. LINQ to Entities is built
  on the ADO.NET Provider model and will support working against different
  back end relational stores in addition to Microsoft SQL Server. This CTP
  includes a LINQ to Entities provider for SQL Server and SQL Server
  Compact Edition.
• LINQ to SQL (previous
  name DLinq) has enhanced the functionality from the May 2006 LINQ CTP.
  You can find it in System.Data.Linq namespace in System.Data.Linq.dll.
  New in this release is that DataContext provides optimized modes for
  read-only use and serialization . Also new is that DataShape streamlines
  eager loading capabilities and adds the ability to set queries on
  relationships
• LINQ To SQL Designer
• Methods can be created
  from stored procedures and functions within the designer.
• Better handling of
  database schemas.
• Improved inheritance
  support in the designer.
• LINQ over XML (XLinq)
• System.Xml Bridge
  Classes added – There is a set of extension methods allowing XPath /
  XSLT to be used over LINQ to XML trees, allow XSLT transformations to
  produce an LINQ to XML tree, and to validate an XElement tree against an
  XML Schema.
• Event Model - This
  allows LINQ to XML trees to be efficiently synchronized with a GUI, e.g.
  a Windows Presentation Foundation application
• Class hierarchy
  changes - XObject class added, XStreamingElement class (temporarily)
  removed
• Various
  understandability / usability improvements – There have been a number of
  relatively minor changes done in response to internal reviews, usability
  studies, and external feedback to make the API more clean and
  consistent.
• LINQ to Objects API
• The LINQ to Objects
  API supports queries over any .NET collection, such as arrays and
  Generic Lists. This API is defined in the System.Linq namespaces inside
  System.Core.dll.

• ADO.NET
• Extended, more powerful
  data APIs with the ADO.NET Entity Framework
• With the ADO.NET
  Entity Framework developers will be able to model the view of the data
  that is appropriate for each one of the applications they are building,
  independently of the structure of the data in the underlying database.
  The use of the Entity Data Model (EDM) enables developers to design
  models that follow the concepts built into the application, instead of
  having to map them to constructs available in relational stores. Once
  the model is in place, the powerful ADO.NET Entity Framework API is used
  to access and manipulate the data as .NET classes or as rows and
  columns, whatever is appropriate for each application.
• Added paging and stored
  procedures for update (“update customization”) for ADO.NET Entity
  Framework:
• Paging: the paging
  support in the ADO.NET Entity Framework allows developers to “page” over
  data in a database by indicating the start row and number of rows to be
  included in the result. Paging is available through Entity SQL (using
  the LIMIT AND SKIP keywords) and through the query-builder methods in
  the ObjectQuery &ltT> class (Top and Skip). In a future CTP the
  feature will also be enabled to be used in LINQ queries by means of the
  standard Take and Skip LINQ operators.
• Stored-procedures for
  update customization: the Entity Framework by default automatically
  generates SQL statements for insert, update and delete operations when
  processing changes to entities in memory to be sent to the database.
  With the stored-procedures update customization feature developers have
  the option to override the automatic SQL generation and instead provide
  stored-procedures that will perform the insert, update and delete
  operations, which the system will call during entity change processing.
  Among other things, this enables scenarios where direct access to tables
  is restricted in the database and the only way to make changes to the
  data is through stored-procedures.
• Microsoft
  Synchronization Services for ADO.NET
• Provides an
  application programming interface (API) to synchronize data between data
  services and a local store. The Synchronization Services API is modeled
  after the ADO.NET data access APIs and gives you an intuitive way to
  synchronize data. It makes building applications for occasionally
  connected environments a logical extension of building applications
  where you can depend on a consistent network connection. For details
  please visit http://go.microsoft.com/fwlink/?LinkId=80742
  .

• Web
• Improvements for web
  development in this CTP include:
• New ASP.NET WebForms
  design-surface with advanced XHTML and CSS features
• JScript intellisense
  for ASP.NET AJAX and browser DOM
• Multi-targetting for
  .NET Framework 2.0, 3.0, and 3.5 in websites and web applications
• LINQ to SQL designer
  integration in websites and web applications

• Client App-Level Services
• Enable client
  application developers to use the same user profile and login services as
  your Web applications. This enables customers to utilize on set of
  backend storage for user personalization and authentication regardless of
  the applications type.

• C# Workflow Rules
• Workflow Rules allows
  users to enter rules (and conditions) in a code-like manner
• Support the use of the
  new C# Extension methods features in their rules
• Enable operator
  overloading and the new operators in their rules

• XML
• XML Tools: XSLT
  Debugger
• Enables Input Data
  Breakpoints allowing the user to break the execution of the style-sheet
  whenever a certain node in input document is hit.
• XML Editor Performance
  Improvements
• Performance in the Xml
  Editor for Intellisense, schema validation etc is improved by
  implementing incremental parsing of the XML Document.
• Seamless transition
  between XML Editor and XSD Designer
• Improves the
  experience a user has when working with an XML Schema in textual and
  graphical mode at the same time.

• MSBuild
• Parallel/Multi-Processor
  Builds
• Building multiple
  projects in parallel, as much as possible based on the use of dependency
  information in projects to parallelize
• Allowing the
  developer/builder to control the parallelism by providing them the
  ability to specify the number of processors to use for build.

• UAC Manifests in the Managed Build Process
• Support for manifests
  that are embedded into the final executable via the Build process.

• IDE
• Windows Presentation
  Foundation (WPF) Designer (“Cider”) & Application Tools to deliver
  the ability to:
• Create, edit, build,
  run and debug WPF projects
• Use the WPF Designer
  to:
• Preview any XAML in
  the designer including user defined controls and types
• Design Windows, Pages
  and UserControls
• Do basic layout tasks
  in a Grid
• Do basic property
  editing using the new property browser
• Easily understand and
  navigate “document structure” using the Document Outli
• See changes in the
  designer immediately in the XAML
• Use the XAML Editor
  to:
• Edit XAML with
  intellisense
• See changes in the
  XAML immediately in the designer
• Build design time for
  WPF controls
• UAC manifests in the
  IDE for Windows Vista applications
• Enable developers on
  Windows Vista to easily include the UAC manifest as an embedded
  resource.

• CLR
• Add IRI support (RFC
  3987) to URI related classes
• This allows resource
  identifiers to be specified using a character set that supports all
  languages.
• New Async model on
  Socket class
• A new Async model is
  reduces the per I/O overhead compared to the current I/O model
• Peer Networking Classes
  
• Delivers a set of
  peer-to-peer network APIs that allow a developer to easily extend an
  application with compelling collaboration functionality.
• WMI.NET Provider
  Extension 2.0
• WMI.NET Provider
  Extension 2.0 simplifies and enhances the development of WMI providers
  in the .Net framework to enable the management of the .NET applications
  while minimizing the impact on the development time.
• Delivers equivalent
  access to WMI features and functions available to native code
  providers.
• Exposes property
  updates and methods to managed code.
• Improved scalability
  for large collections of WMI entities.

• Office
• Enable ClickOnce deployment
  for Microsoft Office applications
• Developers now have an
  easy to use and version resilient security model for their applications
  that will exist for future versions of Visual Studio and Office. With
  full support for ClickOnce deployment of all Office 2007 customizations
  and applications, developers and administrators now have the right tools
  and framework for easy deployment and maintenance of their Office
  solutions.

• Team Architect
• Top-down service design
  
• Top-down system design
  allows an application architect/lead developer to perform the design of
  a business solution without having to be confronted with technology
  decisions. It enables the user to progressively refine a high-level
  system design, designing new sub-systems and applications in the context
  of the system in which they are to be used.
• Architectural Roles on
  System, Applications and Endpoints
• Enables an architect,
  while working on the high-level design of a system’s architecture using
  the System Designer, to introduce elements into the design that play a
  specific pre-defined architectural role(s) within architectural
  patterns.

• Team Developer
• Profiler Support for
  WCF Applications
• Enable profiling of
  WCF based applications to improve application performance
• Customize and extend
  code correctness policies
• Code Analysis Check-in
  Policy improvements to communicate to a developer why the check-in
  policy failed and to provide guidance on how to pass the policy
  requirements.
• Customize and extend
  code correctness policies
• Code Analysis Check-in
  Policy improvements to communicate to a developer why the check-in
  policy failed and to provide guidance on how to pass the policy
  requirements.
• Performance tune an
  enterprise application
• Enables developers to
  run profiling during load and test procedures for a system, to see how
  it behaves, and use integrated tools to profile, debug and tune. This
  also enables performance base-lining, so that users can save a baseline
  profile and then, if the performance degrades, compare up-to-date traces
  to identify the source of the regression

• Team Test
• Unit Test Generation
  Improvements
• Improvements to unit
  test generation provide an easy way for the user to specify what methods
  to test, and generate test methods and helper code to do unit testing,
  as well as providing unit test support for generics.
• Web Test Validation
  Rule Improvements
• Web Test rules
  improvements enable testers to create more comprehensive validation
  rules for the application being tested. These improvements include the
  following functions:
• Stop test on error
• Search request and
  response
• Add validation rule
  for title
• Redirect validation
• Provide test level
  validation rules
• Expected HTTP code
• Warning level for
  errors on dependents
• Better Web Test Data
  Binding
• This feature allows
  users to data bind .CSV and XML files, as well as databases to a web
  test, using a simple databinding wizard.
• Improved Load Test
  Results Management
• With this feature user
  can open or remove an existing load test result from the load test
  repository. User can also import and export load test results files.

• Team Foundation Server
• Team Build
• Support multi-threaded
  builds with the new MSBuild.
• Continuous Integration
  – There are many components to this, including build queuing and queue management,
  drop management (so that users can set policies for when builds should
  be automatically deleted), and build triggers that allows configuration
  of exactly how when CI builds should be triggered, for example – every
  checkin, rolling build (completion of one build starts the next), etc.
• Improved ability to
  specify what source, versions of source, etc to include in a build.
• Improved ability to
  manage multiple build machines.
• Simplified ability to
  specify what tests get run as part of a build
• Version Control support
  
• Destroy- The version
  control destroy operation provides administrators with the ability to
  remove files and folders from the version control system. The destroyed
  files and folders cannot be recovered once they are destroyed. Destroy
  allows administrators to achieve SQL server disk space usage goals
  without constantly needing to add more disks to the data tier machine.
  Destroy also facilitates removing versioned file contents that must be
  permanently removed from the system for any other reason.
• Annotate - Annotate is
  a feature that allows developers to inspect a source code file and see
  at line-by-line level of detail who last changed each section of code.
  It brings together changeset data with difference technology to enable
  developers to quickly learn change history inside a source file.
• Folder Diff - Team
  Foundation Server now supports compare operations on folders, whereby
  the contents of the folder are recursively compared to identify files
  that differ. Folder diff can compare local folders to local folders,
  local folders to server folders, and server folders to server folders.
  It’s a great way of identifying differences between branches, files that
  you’ve changed locally, and files that have changed between two points
  in time.
• Get Latest on Checkout
  - As an optional setting on a team project or on an individual basis,
  you can have Team Foundation Server always download the latest version
  of a file when you check it out. This helps ensure that you don’t have
  to merge your changes with somebody else’s when you check the file back
  in.
• Performance and Scale
• This release includes
  numerous improvements in performance and scalability of Team Foundation
  Server.

• Visual C++
• Easily add the Windows
  Vista “Look and Feel” to native C++ applications
• Developers can use
  Visual Studio to build ISV applications that exhibit the Windows Vista
  “look & feel”. A number of the Windows Vista “look & feel”
  features are available simply by recompiling an MFC application. Deeper
  integration that requires more coding or design work on the part of the
  developer is also simplified with Visual Studio’s integrated support for
  the Windows Vista native APIs.

Existing CTPs: As Visual Studio code name “Orcas” CTPs are released on a predefined cadence, existing CTPs (such as the LINQ May 2006 CTP) may not yet have been integrated into a given “Orcas” CTP release (This should not be taken as a change in commitment to any existing technology that has been made available as a CTP but instead is just a real world example of how large applications, with many technology areas, are built. We will be integrating this existing functionality into future CTP builds.

Developers using a VPC image can run the CTP on a machine without impacting any existing software installations. The CTP can be removed by deleting the folder and using the Virtual PC application to remove the configuration information.

This image ships with networking set to “local”. This setting enables the virtual machine to think it is connected to a network without actually connecting and exposing the machine to the Internet. We recommend that customers do not modify the networking settings. Customers who wish to turn networking “on” to connect the image to a physical network are advised that they will need to ensure the security of the virtual machine as well as apply any security updates that may have become available since the release of this image.

Using ALTER INDEX…REBUILD To Rebuild A Clustered Index Does Not Rebuild Its Nonclustered Indexes By Default On SQL Server 2005

How many times have you asked/been asked the following question: Does rebuilding a clustered index rebuild nonclustered indexes?

Well Ken Henderson has the answer for you on his blog.

On SQL Server 2005, using ALTER INDEX…REBUILD to rebuild a clustered index
does not rebuild its nonclustered indexes by default

Read the rest here: http://blogs.msdn.com/khen1234/archive/2007/02/27/does-rebuilding-a-clustered-index-rebuild-nonclustered-indexes.aspx

Failure Trends in a Large Disk Drive Population

The Google engineers published a paper on Failure Trends in a Large Disk Drive Population. Based on a study of 100,000 disk drives over 5 years they found some interesting stuff:

While drive manufacturers often quote yearly failure rates below 2%,
user studies have seen rates as high as 6%.

We find, for example, that after their first scan error, drives are 39
times more likely to fail within 60 days than drives with no such errors. First
errors in reallocations,offline reallocations, and probational counts are also
strongly correlated to higher failure probabilities.

Six percent, that is higher than I expected. I must say (and I am knocking on wood as I write this) that I only saw a drive die once (within a month of deploying) on a blade server. The only major problem I had was when consulting for a client in NYC. They had a SQL Server box which was running for 2 years without a problem. We upgraded the machine to an active/passive cluster and a week later the motherboard died (downtime 20 seconds ;-) ), talking about good timing.....

So what failure rates do you see? Does stuff break down a lot?

The Real Reason Why Condoleezza Rice Never Smiles

I always wondered why Condoleezza Rice never smiles, now I know ;-)

Math Geniuses?

What do you think? Can you answer these very difficult questions?

How To Find Out Which Columns Have Defaults And What Those Default Values Are

Okay so many many moons ago you created a bunch of tables and those tables have columns of course. You want to know how to find the columns that have defaults.
There are a couple of ways to do this
Below is a list:

1 INFORMATION_SCHEMA.COLUMNS view (2000 and 2005)
2 sysobjects,syscolumns and syscomments (2000 only)
3 sys.default_constraints and sys.sysobjects (2005 only)
4 sp_help (2000 only)


So let's get started with some code
CREATE TABLE blah(id INT DEFAULT 0,
SomeDate DATETIME DEFAULT CURRENT_TIMESTAMP)

INSERT blah DEFAULT VALUES

SELECT * FROM blah


--SQL 2000/2005
SELECT COLUMN_DEFAULT,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='blah'

--SQL 2000
SELECT Text,*
FROM sysobjects o
INNER JOIN syscolumns c
ON o.parent_obj = c.id
AND o.info = c.colid
INNER JOIN syscomments s
ON o.id = s.id
WHERE o.xtype = 'D'
AND OBJECT_NAME(parent_obj) = 'blah'

--SQL 2005
SELECT * FROM
sys.default_constraints d
JOIN sys.sysobjects o ON d.parent_object_id = o.id
WHERE o.name = 'blah'


See what happens when you don't specify a name (we will do this later)? You will get wacky names like these: DF__blah__id__15A53433 and DF__blah__SomeDate__1699586C
Instead of specifying the default when creating the table use an alter table add constraint statement.Let's see this in action.

DROP TABLE blah
CREATE TABLE blah(id INT,SomeDate DATETIME)

ALTER TABLE blah
ADD CONSTRAINT DF_Blah_ID_0
DEFAULT 0 FOR id

ALTER TABLE blah
ADD CONSTRAINT DF_Blah_SomeDate_Now
DEFAULT CURRENT_TIMESTAMP FOR SomeDate


INSERT blah DEFAULT VALUES

SELECT * FROM blah


--SQL 2000/2005
SELECT COLUMN_DEFAULT,*
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ='blah'

--SQL 2000
SELECT Text,*
FROM sysobjects o
INNER JOIN syscolumns c
ON o.parent_obj = c.id
AND o.info = c.colid
INNER JOIN syscomments s
ON o.id = s.id
WHERE o.xtype = 'D'
AND OBJECT_NAME(parent_obj) = 'blah'

--SQL 2005
SELECT * FROM
sys.default_constraints d
JOIN sys.sysobjects o ON d.parent_object_id = o.id
WHERE o.name = 'blah'

And last we have sp_help
You can use sp_help in SQL Server 2000(you can also use it in SQL server 2005 but it doesn't return the defaults )
Execute the following
sp_help 'blah'

The defaults will be in the last resultset (the one where the first column name = constraint_type)


And last but not least did you notice that we had CURRENT_TIMESTAMP but when we queried the table we saw GETDATE() This is kind of strange since CURRENT_TIMESTAMP is ANSI complaint but GETDATE() is not

SQL Server 2005 Best Practices Analyzer (February 2007 CTP) Available For Download

As if you did not download enough in the last 3 days here is one more: SQL Server 2005 Best Practices Analyzer (February 2007 CTP)

The SQL Server 2005 Best Practices Analyzer (BPA) gathers data from Microsoft Windows and SQL Server configuration settings. BPA uses a predefined list of SQL Server 2005 recommendations and best practices to determine if there are potential issues in the database environment.

This download is the February 2007 Community Technology Preview of SQL Server 2005 Best Practices Analyzer.

SQL Server 2005 Books Online Is Not Part Of The Service Pack 2 Download

If you have downloaded SQL Server 2005 Service Pack 2 you probably have noticed that it does not come with Books On Line. You can download that here: SQL 2005 Books Online Feb 2007 (SP2)

Here are two more downloads:
SQL Server 2005 Reporting Services Add-in for SharePoint Technologies
SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007

February 2007 Feature Pack for Microsoft SQL Server 2005

Yesterday I told you that SQL server Service Pack 2 has been released. Microsoft also released the February 2007 Feature Pack for Microsoft SQL Server 2005


The February 2007 Feature Pack for Microsoft SQL Server 2005 is a collection of standalone install packages that provide additional value for SQL Server 2005.

Here is what is available:

Microsoft ADOMD.NET
ADOMD.NET is a Microsoft .NET Framework object model that enables software developers to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2005 Analysis Services. ADOMD.NET is a Microsoft ADO.NET provider with enhancements for online analytical processing (OLAP) and data mining.


Microsoft Core XML Services (MSXML) 6.0
Microsoft Core XML Services (MSXML) 6.0 is the latest version of the native XML processing stack. MSXML 6.0 provides standards-conformant implementations of XML 1.0, XML Schema (XSD) 1.0, XPath 1.0, and XSLT 1.0. In addition, it offers 64-bit support, increased security for working with untrusted XML data, and improved reliability over previous versions of MSXML.


Microsoft OLEDB Provider for DB2
The Microsoft OLE DB Provider for DB2 is a COM component for integrating vital data stored in IBM DB2 databases with new solutions based on Microsoft SQL Server 2005 Enterprise Edition and Developer Edition. SQL Server developers and administrators can use the provider with Integration Services, Analysis Services, Replication, Reporting Services, and Distributed Query Processor. Run the self-extracting download package to create an installation folder. The single setup program will install the provider and tools on x86, x64, and IA64 computers. The package includes product updates in the form of an integrated Service Pack 1. Read the installation guide and Readme for more information.


Microsoft SQL Server Management Pack for MOM 2005
The Microsoft SQL Server Management Pack enables you to monitor SQL Server 2005 and SQL Server 2000 in an enterprise environment. Included are enterprise-level capabilities to monitor resource availability and configuration, collect performance data, and test default thresholds. Local and remote connectivity checks help ensure database availability.
With the embedded expertise in the SQL Server Management Pack, you can identify issues and manage issues before they become critical. This Management Pack increases the security, availability, and performance of your SQL Server infrastructure.
The Microsoft SQL Server Management Pack Guide that is included describes the content of the management pack and how to deploy it.


Microsoft SQL Server 2000 PivotTable Services
PivotTable Services 8.0 is the OLE DB provider for SQL Server 2000 Analysis Services and is used to connect with an Analysis Services 2000 server. PivotTable Services does not work with SQL Server 2005 Analysis Services. Therefore, client applications that need connect to Analysis Services in both SQL Server 2000 and SQL Sever 2005 will need to install both PivotTable Services 8.0 and Analysis Services 9.0 OLE DB Provider in a side-by-side configuration.


Microsoft SQL Server 2000 DTS Designer Components
The Microsoft SQL Server 2000 Data Transformation Services (DTS) package designer is a design tool used by developers and administrators of SQL Server 2005 servers to edit and maintain existing DTS packages until they can be upgraded or recreated in the SQL Server 2005 Integration Services package format. After installing this download, SQL Server 2005 users can continue to edit and maintain existing DTS packages from the Object Explorer in SQL Server 2005 Management Studio and from the Execute DTS 2000 Package Task Editor in Business Intelligence Development Studio, without needing to reinstall the SQL Server 2000 tools. The DTS package designer in this download was formerly accessed from the Data Transformation Services node in SQL Server 2000 Enterprise Manager.


Microsoft SQL Server Native Client
Microsoft SQL Server Native Client (SQL Native Client) is a single dynamic-link library (DLL) containing both the SQL OLE DB provider and SQL ODBC driver. It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 7.0, 2000 or 2005. SQL Native Client should be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features. This redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2005 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API.


Microsoft SQL Server 2005 Analysis Services 9.0 OLE DB Provider
The Analysis Services 9.0 OLE DB Provider is a COM component that software developers can use to create client-side applications that browse metadata and query data stored in Microsoft SQL Server 2005 Analysis Services. This provider implements both the OLE DB specification and the specification’s extensions for online analytical processing (OLAP) and data mining.


Microsoft SQL Server 2005 Backward Compatibility Components
The SQL Server Backward Compatibility package includes the latest versions of Data Transformation Services 2000 runtime (DTS), SQL Distributed Management Objects (SQL-DMO), Decision Support Objects (DSO), and SQL Virtual Device Interface (SQLVDI). These versions have been updated for compatibility with SQL Server 2005 and include all fixes shipped through SQL Server 2000 SP4.


Microsoft SQL Server 2005 Command Line Query Utility
The SQLCMD utility allows users to connect, send Transact-SQL batches, and output rowset information from SQL Server 7.0, SQL Server 2000, and SQL Server 2005 instances. SQLCMD is a replacement for ISQL and OSQL, but can coexist with installations that have ISQL or OSQL installed.


Microsoft SQL Server 2005 Datamining Viewer Controls
The Data Mining Web Controls Library is a set of Microsoft Windows Forms controls that enable software developers to display data mining models created using Microsoft SQL Server 2005 Analysis Services in their client-side applications. The controls in this library display the patterns that are contained in Analysis Services mining models.


Microsoft SQL Server 2005 JDBC Driver
In its continued commitment to interoperability, Microsoft has released and supports a new Java Database Connectivity (JDBC) driver for SQL Server 2005. The SQL Server 2005 JDBC Driver download is available to all SQL Server users at no additional charge and provides access to SQL Server 2000 and SQL Server 2005 from any Java application, application server, or Java-enabled applet. This is a Type 4 JDBC driver that provides database connectivity through the standard JDBC application programming interfaces (APIs) available in J2EE (Java2 Enterprise Edition).


Microsoft SQL Server 2005 Management Objects Collection
The Management Objects Collection package includes several key elements of the SQL Server 2005 management API, including Analysis Management Objects (AMO), Replication Management Objects (RMO), and SQL Server Management Objects (SMO). Developers and DBAs can use these components to programmatically manage SQL Server 2005.


Microsoft SQL Server 2005 Compact Edition
Microsoft SQL Server 2005 Compact Edition is the next version of SQL Server Mobile adding the desktop platform. SQL Server Compact extends the SQL Server Mobile technology by offering a low maintenance, compact embedded database for single-user client applications for all Windows platforms including tablet PCs, pocket PCs, smart phones and desktops. Just as with SQL Server Mobile, SQL Server Compact is a free, easy-to-use, lightweight, and embeddable version of SQL Server 2005 for developing desktop and mobile applications.


Microsoft SQL Server 2005 Notification Services Client Components
The SQL Server 2005 Notification Services Client Components package provides client APIs that enable subscription management and event submission within custom applications that include SQL Server 2005 Notification Services functionality. The subscription management APIs allow developers to create subscriptions and subscribers, and manage subscriber devices. The event submission APIs allow users to specify events using the event APIs or stored procedures.


Microsoft SQL Server 2005 Upgrade Advisor
Upgrade Advisor analyzes instances of SQL Server 7.0 and SQL Server 2000 in preparation for upgrading to SQL Server 2005. Upgrade Advisor identifies deprecated features and configuration changes that might affect your upgrade, and it provides links to documentation that describes each identified issue and how to resolve it.


Microsoft .NET Data Provider for mySAP Business Suite, Preview Version
SQL Server 2005 includes support for accessing SAP data by using the Microsoft .NET Data Provider for mySAP Business Suite. This provider lets you create an Integration Services package that can connect to a mySAP Business Suite solution and then execute commands to access data via supported interfaces. You can also create Reporting Services reports against a server running the mySAP Business Suite.
You can use the Microsoft .NET Data Provider for mySAP Business Suite in the SQL Server Import and Export Wizard and in various Integration Services features (including the Script task, the DataReader Source component, and the Script component), as well as the data processing extensions in Reporting Services.
The Microsoft .NET Data Provider for mySAP Business Suite is not included in SQL Server 2005. The preview version is licensed as pre-release software as outlined in the License Terms. See the Readme included with the download for information on the prerequisites for using the Microsoft .NET Data Provider for mySAP Business Suite.


Reporting Add-In for Microsoft Visual Web Developer 2005 Express
The Reporting Add-In for Microsoft Visual Web Developer 2005 Express includes a ReportViewer control, an integrated report designer, and a comprehensive API that lets you customize run-time functionality. You can use the control to add interactive real-time data reports to your ASP.NET Web applications. Reports can use data from any ADO.NET DataTable or custom Business object. You can create reports that combine tabular, matrix, and visual data in free-form or traditional report layouts. An integrated report designer is included so that you can create the custom reports that bind to the control.
The Reporting Add-In is the same reporting component that is included with other editions of Visual Studio, but without support for Windows Forms applications. For more information including product documentation and samples, see the ReportViewer Controls (Visual Studio) topic on MSDN.


Microsoft Exception Message Box
The exception message box is a programmatic interface that you can use in your applications for any tasks for which MessageBox may be used. The exception message box is a supported managed assembly designed to elegantly handle managed code exceptions. It provides significantly more control over the messaging experience and gives your users the options to save error message content for later reference and to get help on messages.


Data Mining Managed Plug-in Algorithm API for SQL Server 2005
The Managed Plug-in API is a Microsoft .NET object model that enables software developers to create plug-in data mining algorithms for SQL Server 2005 by using CLI-compliant languages, such as Visual C# 2.0. The object model is available as source code and it needs to be built on the developer’s computer. The package includes a step-by-step tutorial, a compiled HTML help file (.chm), as well as a sample plug-in algorithm developed in C#.


Microsoft SQL Server 2005 Reporting Services Add-in for Microsoft SharePoint Technologies
Microsoft SQL Server 2005 Reporting Services Add-in for SharePoint Technologies allows you to take advantage of SQL Server 2005 Service Pack 2 (SP2) report processing and management capabilities in SharePoint. The download provides a Report Viewer web part, web application pages, and support for using standard Windows SharePoint Services.


Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007
Microsoft SQL Server 2005 Data Mining Add-ins for Microsoft Office 2007 allow you take advantage of SQL Server 2005 predictive analytics in Office Excel 2007 and Office Visio 2007. The download includes Table Analysis Tools for Excel, Data Mining Client for Excel, and Data Mining Templates for Visio.


You can download all the packages here: http://www.microsoft.com/downloads/details.aspx?FamilyID=50B97994-8453-4998-8226-FA42EC403D17&displaylang=en#filelist

Podcast With Dr. Peter Chen on the Entity Relationship Model and ADO.NET Entity Framework On Channel 9

If you are a SQL developer then you have probably heard of Dr. Peter Chen, he is the person who wrote the original paper on Entity-Relationship model (ER model).


From the site:
Dr. Peter Chen is a Fellow of ACM, IEEE, and AAAS and has received many prestigious awards including ACM/AAAI Allen Newell Award and IEEE Harry Goode Award. He has been listed in Who’s Who in America and Who’s Who in the World for more than 15 years.

Dr. Chen's original paper on the
Entity-Relationship model (ER model), published in 1976 is one of
the most cited papers in the computer software field. Based on one particular
citation database, Chen's paper is the 35th most cited
article in Computer Science. It is the 4th most downloaded
paper from the ACM Digital Library in January 2005 (Communications of ACM, March
2005) even though the paper was published 30 years ago.

Dr. Chen’s work is a cornerstone of software engineering, in particular Computer-Aided Software Engineering (CASE). In the late 80’s and early 90’s, IBM’s Application Development Cycle (AD/Cycle) framework and DB2 repository (RM/MVS) were based on the ER model. Other vendors’ repository systems such as Digital’s CDD+ were also based on the ER model. Prof. Chen has made significant impact on the CASE industry by his research work and by his lecturing around the world on structured system development methodologies. Most of the major CASE tools including Computer Associates’ ERWIN, Oracle’s Designer/2000, and Sybase’s PowerDesigner (and even a general drawing tool like Microsoft’s VISIO) are influenced by the ER model.


In this podcast, Brian Beckman interviews Dr. Chen along with Jose Blakeley, Software Architect, SQL Server, and Britt Johnston, Director of Program Management, Data Programmability



The podcast is 51 minutes and 24 seconds

Visit Channel 9 to download the Podcast

Microsoft Releases SQL Server 2005 Service Pack 2

Microsoft Corp. today released Microsoft® SQL Server™ 2005 SP2, an update to its award-winning data management and analysis platform. Customers can now take advantage of enhancements in the familiar and easy-to-use Windows Vista™ operating system and 2007 Microsoft Office system to easily connect and integrate with the power, security and reliability of SQL Server 2005. In addition, Microsoft announced that it is expanding virtualization use rights to allow unlimited virtual instances on servers that are fully licensed for SQL Server 2005 Enterprise Edition. For customers who want maximum flexibility in their use of virtualization technology, now or in the future, SQL Server 2005 Enterprise Edition is the ideal choice.

“SP2 delivers customer-driven improvements and new features that align with our data platform vision,” said Ted Kummert, corporate vice president of the Data and Storage Platform Division at Microsoft. “SP2 realizes a step forward in enabling organizations to bring the business intelligence capabilities of SQL Server 2005 directly to end users in the tools they use every day.”

Key enhancements to SQL Server SP2 include the following:

• Data Mining Add-ins for the 2007 Microsoft Office system enable data mining functionality from SQL Server Analysis Services (SSAS) to be used directly within Excel® 2007 and Visio® 2007.

• SQL Server Reporting Services (SSRS) compatibility with Microsoft Office SharePoint® Server 2007 provides integration with the Report Center in SharePoint, enabling the seamless consumption and management of SSRS reports within SharePoint.

• SQL Server Analysis Services improvements for Excel 2007 and Excel Services relate to performance and functionality.

• Data compression (varDecimal) is an important feature for data warehousing scenarios, requiring less disk storage of decimal data and increasing overall performance.

• Manageability enhancements, based on customer feedback, provide management capabilities for database administrators such as improvements in database maintenance plans, enhanced management reports and a new copy database wizard.

• Management reports added to SQL Server Express Edition enable customers to get insights into the performance of their Express Edition and SQL Server Compact Edition databases.

• Interoperability improvements including Oracle support in the Report Builder feature enable customers to use its functionality on top of Oracle data sources. Customers also have access to SQL Server Reporting Services to build reports on top of Hyperion’s Essbase cubes.


Customers can download SQL Server 2005 Service Pack 2 immediately from http://www.microsoft.com/sql/sp2.mspx.

Chuck Boyce Has Some Joe Celko Videos On YouTube

Chuck Boyce left me a comment on the Celko post yesterday. I decided to make it a separate post since most people who use a RSS reader don't see the comments

Here are some videos of Adam Machanic interviewing Joe Celko at the 2006 PASS Summit
that I just published today to our new
Solid Quality Learning YouTube channel.


Joe Celko on the evil of cursors

Joe Celko discusses the problem with the median puzzle

Joe Celko recalls an old-school pre CASE statement trick

Thanks Chuck

Link to Chuck's post: http://chuckboyce.blogspot.com/2007/02/joe-celko-on-youtube.html