If you have been playing with SQL Server Management Studio 2008 and are modifying tables in the designer, you may run into this error:

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created.  You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

Here is a screenshot:

In the 2008 , they have added a new option to prevent saving of changes that will require a table re-creation.  If you think about it, this is a good thing.  If you are running a production database, you want maximum performance.  If Management Studio needs to re-create a table to apply a change that means it needs to create a copy of the table, transfer the data, create the new table, move the data over, delete the old stuff, etc.  This can potentially be a major operation. 

However, if you don’t want this feature enabled or our just working with a development or test database, it is easy to turn off.  Just open up the Options window in Management Studio and go to “Designers –> "Table and Database Designers”, find the “Prevent saving changes that require table re-creation” option, and turn it off.

SQL Server 2008 was launched as part of the Heroes Happen {Here} event, but it wasn’t yet “released”.  Today, I am happy to report that Microsoft has finally released SQL Server 2008. (This was originally announced at TechEd South Africa) .  It is available now for download from TechNet and MSDN and will be available tomorrow (August 7th) for evaluation download.  You should expect to be able to purchase it within a couple of weeks.

You can check out the key new features here: http://technet.microsoft.com/en-us/magazine/cc434690.aspx. There are also numerous videos out there including:

• SQL Server 2008 Videos
• Webcasts: 24 Hours of SQL Server 2008 for IT Professionals
• Webcasts: 24 Hours of SQL Server 2008 for Developers

During my Heroes {Community} Launch talks I previewed SQL Server 2008 and introduced my 10 favorite new features for developers:

Top 10 SQL Server 2008 Features for Developers

1. LINQ
2. Intellisense for SQL Server Management Studio: T-Sql, database objects, & parameters
3. FileStream Datatype:
   • Designed to solve unstructured LOB (large object) storage, provides performance of native NTFS with transactional support. Data is stored in NTFS, and SQL Server manages relationship between the columns and files. SQL Server manages the transactional integrity such as backing up and restoring data.
4. Date & Time Datatypes: Separate data and time types. Time can now go down to hh:mm:ss[.nnnnnnn]
5. DateTime2 & DateTimeOffset Datatypes: Supports years 0001 to 9999 (much like in .Net); DateTimeOffset is timezone aware, but not daylight savings time!
6. Hierachical Datatype: Allows storage of hierarchical data such as org charts, etc.
   • Only 1 root per table though
7. Spatial Datatypes:
   • Geography: Uses a geodetic (round earth) model and stores points, lines, polygons, and collections of Latitude & Longitude coordinates.
   • Geometry: Uses a planar (flat earth) model which stores the same types of data, but is used primarily for navigation and mapping.
8. T-SQL MERGE Statement: Allows merging of rows from multiple tables. You can choose to update matched rows, insert unmatched rows, or delete unmatched rows from the primary table.
9. Table-Valued Parameters: Tables can be passed as parameters to sprocs.
10. Data Change Tracking
    • Change Data Capture (CDC): This allows you to capture all changes to data in a table into a tracking table.
    • Change Data Tracker (CDT): This similar to CDC, but it doesn’t actually capture the changes made, but captures only that some change was made.

Many companies out there won’t be able to jump on SQL Server 2008  for some time.  Some companies like to wait until new server software has been “proven” first.  However, the SQL Management Studio that is included in SQL Server 2008 has a ton of new features including intellisense.  The best part is that this version can read not only SQL Server 2008 databases, but also those for 2005 and 2000!  You can start using this today and make your life easier!

If you are able to download the software, try it out as soon as you can.  I know what I will be playing with tomorrow!

IndyPass is the Indianapolis Professional Association for SQL Server.  Tonight they celebrated their 3rd Birthday Bash. The speaker was Tom Pizzato, Data Platform Architect, at Microsoft.

John Magnabosco kicked us off with the introduction.  He gave the usual announcement events, board elections, etc . John ended by asking for job announcements, but was interrupted by the famous Buck Foley.  Buck is a “Matt Foley” impersonator (the ‘van down by the river’ character played by Chris Farley on Saturday Night Live), and the actor is a ‘Database Guy’ by profession.  He brings his own flavor of humor and applies it to SQL technologies. Buck seemed to be under the impression that he was the main speaker and not Tom Pizzato who he called “Pizza-Toe”.  He winged his talk this time, but it was still humorous and set a light hearted tone for the rest of the meeting.

Tom’s talk was broken into two sections: a detailed talk about new features followed by a series of demos.  The talk mainly went over all of the new features and changes in SQL Server 2008.

Tom’s Talk

Amazing Journey

Tom has worked with SQL Server for close to 19 years.  He started his talk by taking us a down memory lane by showing some of the original disks for the first version of SQL Server back when it was just a OS2 port of Sybase.  He also  picked fun of the fact that there was a launch event for a product that has not yet been released. A few items of interest from his introduction were:

• Microsoft is still on track for a Q3 launch of SQL Server 2008, but skill couldn’t give us a more specific date yet.  The next version of SQL Server will be in 2010 (at least that is where it is planned).
• FileStream data cannot be saved to a separate machine in SQL Server 2008.  it is still limited to the machine running SQL Server 2008.  Microsoft knows this is a limitation, but they chose to just get the feature out there first.

Protect your Information

• Secure Access
  • Strong Authentication
  • Granular Authorization
  • Transparent Data Encryption (TDE)
  • External Key Management
• Protect System
  • Secure Deployment
  • Secure by default: This ones  is going to be a pain for some people.  Secure by default means everything is turned off by default.  Microsoft is taking this mind set into all of its major projects.  I have talked about this before in terms of Wcf, but we also see it in Windows Server 2008 where almost no features are installed by default.

Ensure Business Continuity

• Ensure Availability
  • Mirror your data: Automatic repair of primary from backups
  • Failover clustering
  • Replicate your data
• Minimize Downtime
  • Online Operations: More to help you keep your system up including, hot swapping CPUs while running in production
  • Add system resources
  • Reduce Recovery time
• Concurrent Access
  • Database Snapshots: Not many changes here
  • Snapshot Isolations

Predictable Response

• Optimize Performance
  • Efficient Data Storage: Finally Compression at the data and backup levels. in SP2 for SQL Server 2005, the vardecimal datatype was introduced which helped open the doors for this feature.
  • Prescriptive Guidance
• Analyze Performance
  • Data Collection: Collect reports instead of wiping them
• Predict Performance
  • Plan Freezing: Allow you to save query execution plans to xml that can be run on other machines.
  • Resource Governor: Set up resources into pools and assigned users to them.  Such as giving 70% CPU and memory to a power users and  rest to non-power users.

Reduce Management Cost

• Flexible Administration
  • Visual Management: Management Studio improvements, Powershell integration, etc.
  • Automate Operations
• Manage centrally
  • Enterprise Policies
  • Monitor Compliance
• Monitor Health
  • Management Reports
  • Performance Tuning Tools

Simplify Development

• Easy Access
  • Model Entities: Models can be built off multiple tables such as a “User” entity that spans multiple tables.  LINQ can then be used to query over this data. This is what is formally called LINQ to Entities.
  • Integrated Query: Most of the LINQ stuff is slightly slower, but it is a trade off of getting the code done faster development vs. performance.  This is also know as LINQ to SQL.
  • Comprehensive connectivity
• Program data logic
  • Time aware applications: Introduced separate Date and Time fields.
  • Pass data results
• Sync your data
  • Local data storage
  • Offline Synchronization

Store your information

• Unstructured Information
  • File Storage
  • Search Text
  • Extensible Types
• Geospatial Information
  • Spatial Standards
  • Visualize Location
• Structured information
  • Model complex hierarchies: No more need to use CTEs to recursively query data to build hierarchy trees.  The new Hierarchy data type can store these values
  • Enhanced Partitioning

Integrate and Manage data

• Build Faster
  • Connect to data
  • Cleanse Data
  • Integrate data
• Manage Efficiently
  • Efficient Data Storage
  • Mange mixed workloads
  • Streamline Aggregates
• Scalable Performance
  • DW Query Optimizations: Lots of optimizations in performance of Star joins
  • Enhanced Partitioning: Easier to align partitions with their indices.

Deliver Relevant Reports

• Author reports
  • Enhanced Designers: Licensed technology from Dundas to create better and more professional looking reports.
  • Flexible Layout: Introduced Tablixs.
• Manage reports
  • Enterprise Platform: SSRS was rewritten to not require IIS and scale better in the enterprise
  • Server-based Deployment
• Deliver reports
  • Rich Visualizations
  • Render in office
  • Share reports

Drive Actionable Insight (Analysis Services)

• Build Analytical Solutions
  • Scalable Platform
  • Prescriptive Guidance
• Extend Reach
  • Manage KPIs: How many Microsoft tools report KPIs?
  • Predictive Analysis
• Deliver Insight
  • Work in Office: Data mining add-ins for office allow you to mine and report on data directly from Office tools
  • Business view of data

Tom’s Demos

Demos were done using SQL Server 2008 CTP 6.  Tom had 9 demos to show but only got to 6 of them.  The demos were run from a custom application that had the queries and results all displayed in a custom window.  This was obviously a pre-canned set of demos or at least a pre-canned tool, but it was a great way to display these features.  I know it took a lot of time to put together,

Auditing

• Auditing performance can eat up to 10%
• Audit logs are stored in a separate file on the disk.  These files are not xml or directly human readable, but at least they can’t be queried like any other table.
• Audit logs can be maintained by setting up rollover files and file sizes

Compression

• Compression types can be set at the row or page levels

Powershell

• Only works for the relational engine
• Can right click on teh database in management studio and say “Open Powershell”
• Once in powershell you can say “dir”and get the list of database objects including being able to drill into tables, users, etc. 
• You could even run more complex Powershell queries such as the ability to get tables where row count is > 1000 like:
  • dir | where < $._RowCount -gt 1000
• You can pipe results out to HTML or other mediums

Freezing Plans

• Gives you the ability to take a query execution plan and save it to XML
• Can then specify when plan to use when executing a query

New DateTime Datatypes

• Separate Date and Time datatypes
• A new DateTimeOffset datatype supports years 0001 to 9999 as well as storing time zone information
• A new DateTime2 datatype supports years 0001 to 9999

Table Value Parameters and Merge

• This was the last feature dropped from SQL Server 2005
• A new MERGE statement allows merging of rows from multiple tables. You can choose to update matched rows, insert unmatched rows, or delete unmatched rows from the primary table.

Prizes

I know a lot of people probably only came here for the prizes, and there were some great giveaways again this year.  There was an Xbox, a 30 gig Zune, two 8 gig Zunes, and a slew of books and other swag.  As usual I didn’t win anything, even though the odds were pretty good.  But I came here for the event more than the prizes

Final Thoughts

I do miss having Bill Baker at the Birthday Bashes, but Tom has been filling in nicely. He is a good speaker, is able to answer most questions, and is very competent in his skills. He was asked some detailed questions about LINQ to SQL and LINQ to Entities that he couldn’t answer, but he wasn’t afraid to punt on them and commit to getting Joe Healy to come to talk about LINQ at a future meeting.  I am looking forward to future events at IndyPASS.

We prefix all of our database objects with a namespace scope.  Objects begin with "ININ_" followed by an application prefix.  So all database objects relating to to an application called "ABC" will begin with the prefix "ININ_ABC_".  This namespacing really helps out when multiple applications share the same database.

Advantages of this approach include:

1. Identifying what application an object belongs to
2. Finding all objects for an application in the sysobject tables

But, this post isn’t about how to name database objects…

To test our database scripts, we normally drop a database and recreate through our build process. However, when multiple applications share the same database, we need to only drop the database objects relating to that application. 

The stored procedure below takes in an application prefix and drops all database objects whose name starts with that prefix.  This script will work for any prefix you might use to segregate your objects. 

This was written for SQL Server 2005, but it could be easily modified to work with older versions.

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'DropApplication') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    DROP PROCEDURE [dbo].[DropApplication]
GO

CREATE PROCEDURE [dbo].[DropApplication]
(
    @ApplicationPrefix varchar(64)
)
/************************************************************/
-- DropApplication
-- 
-- Date Created: 8/19/2007
-- Created By:     Dan Rigsby (www.danrigsby.com)
-- 
-- Purpose:         Deletes all of the sysobjects in the database
--                 for an application based on sysobject name prefix
--
-- Example:         To delete all sysobjects for an application called
--                 ISupport whose object are prefixed with 'ININ_ABC' call: 
--                 EXEC DropApplication 'ININ_ABC'
/************************************************************/

AS
BEGIN
    DECLARE @Statement AS varchar(4000)
    DECLARE @ParentID AS int
    DECLARE @ReferenceID AS int
    DECLARE @ParentName AS varchar(256)
    DECLARE @ObjectName AS varchar(256)
    DECLARE @ObjectType AS varchar(5)

    -- Step 1: Delete procedures
    DECLARE cr1 CURSOR FOR
    (
        SELECT [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + '%' AND [type] in (N'P', N'PC', N'X', N'RF')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        EXEC ('DROP PROCEDURE ' + @ParentName)
        FETCH NEXT FROM cr1 INTO @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    -- Step 2: Delete functions
    DECLARE cr1 CURSOR FOR
    (
        SELECT [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + '%' AND [type] in (N'IF', N'FN', N'TF')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        EXEC ('DROP FUNCTION ' + @ParentName)
        FETCH NEXT FROM cr1 INTO @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    -- Step 3: Delete views
    DECLARE cr1 CURSOR FOR
    (
        SELECT [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + '%' AND [type] in (N'V')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        EXEC ('DROP VIEW ' + @ParentName)
        FETCH NEXT FROM cr1 INTO @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    -- Step 4: Delete FKs
    DECLARE cr1 CURSOR FOR
    (
        SELECT [id], [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + '%' AND [type] in (N'U')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentID, @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        DECLARE cr2 CURSOR FOR
        (
            SELECT [name]
            FROM sysobjects
            WHERE [parent_obj] = @ParentID AND [type] IN (N'F')
        )

        OPEN cr2
        FETCH FROM cr2 INTO @ObjectName
        WHILE (@@fetch_status = 0)
        BEGIN
            EXEC (' ALTER TABLE ' + @ParentName +
                ' DROP CONSTRAINT ' + @ObjectName)

            FETCH NEXT FROM cr2 INTO @ObjectName
        END
        CLOSE cr2
        DEALLOCATE cr2

        FETCH NEXT FROM cr1 INTO @ParentID, @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    -- Step 5: Delete any FK constraints in other tables that reference our tables
    DECLARE cr1 CURSOR FOR
    (
        SELECT [id]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + '%' AND [type] in (N'U')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ReferenceID
    WHILE (@@fetch_status = 0)
    BEGIN
        DECLARE cr2 CURSOR FOR
        (
            SELECT [name], [type], [parent_object_id]
            FROM sys.foreign_keys
            WHERE [referenced_object_id] = @ReferenceID
        )

        OPEN cr2
        FETCH FROM cr2 INTO @ObjectName, @ObjectType, @ParentID
        WHILE (@@fetch_status = 0)
        BEGIN
            -- Get parent name
            SELECT @ParentName = [name]
            FROM sysobjects
            WHERE [id] = @ParentID

            SELECT @Statement =
                CASE (@ObjectType)
                    WHEN 'F' THEN
                        ' ALTER TABLE ' + @ParentName +
                        ' DROP CONSTRAINT ' + @ObjectName
                END
            EXEC (@Statement)

            FETCH NEXT FROM cr2 INTO @ObjectName, @ObjectType, @ParentID
        END
        CLOSE cr2
        DEALLOCATE cr2

        FETCH NEXT FROM cr1 INTO @ReferenceID
    END
    CLOSE cr1
    DEALLOCATE cr1

    -- Step 6: Delete tables, triggers, FKs, and constraints
    DECLARE cr1 CURSOR FOR
    (
        SELECT [id], [name]
        FROM sysobjects
        WHERE [name] LIKE @ApplicationPrefix + '%' AND [type] in (N'U')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentID, @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        DECLARE cr2 CURSOR FOR
        (
            SELECT [name], [type]
            FROM sysobjects
            WHERE [parent_obj] = @ParentID
        )

        OPEN cr2
        FETCH FROM cr2 INTO @ObjectName, @ObjectType
        WHILE (@@fetch_status = 0)
        BEGIN
            SELECT @Statement =
                CASE (@ObjectType)
                    WHEN 'TR' THEN
                        ' DROP TRIGGER ' + @ObjectName
                    ELSE
                        ' ALTER TABLE ' + @ParentName +
                        ' DROP CONSTRAINT ' + @ObjectName
                END
            EXEC (@Statement)

            FETCH NEXT FROM cr2 INTO @ObjectName, @ObjectType
        END
        CLOSE cr2
        DEALLOCATE cr2

        EXEC (' DROP TABLE ' + @ParentName)

        FETCH NEXT FROM cr1 INTO @ParentID, @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1

    -- Step 7: Drop users
    DECLARE cr1 CURSOR FOR
    (
        SELECT [name]
        FROM sys.database_principals
        WHERE [name] LIKE @ApplicationPrefix + '%'
            AND [type] IN (N'S')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ObjectName
    WHILE (@@fetch_status = 0)
    BEGIN
        EXEC ('DROP USER ' + @ObjectName)
        FETCH NEXT FROM cr1 INTO @ObjectName
    END
    CLOSE cr1
    DEALLOCATE cr1

    -- Step 8: Drop roles
    DECLARE cr1 CURSOR FOR
    (
        SELECT [principal_id], [name]
        FROM sys.database_principals
        WHERE [name] LIKE @ApplicationPrefix + '%'
            AND [type] in (N'R')
    )

    OPEN cr1
    FETCH FROM cr1 INTO @ParentID, @ParentName
    WHILE (@@fetch_status = 0)
    BEGIN
        DECLARE cr2 CURSOR FOR
        (
            SELECT [name]
            FROM sys.database_role_members DRM
                INNER JOIN sys.database_principals DP
                    ON DRM.[member_principal_id] = DP.[principal_id]
            WHERE [role_principal_id] = @ParentID
        )

        OPEN cr2
        FETCH FROM cr2 INTO @ObjectName
        WHILE (@@fetch_status = 0)
        BEGIN
            EXEC
            ('
                EXEC dbo.sp_droprolemember ''' + @ParentName + ''', ''' + @ObjectName + '''
            ')
            FETCH NEXT FROM cr2 INTO @ObjectName
        END
        CLOSE cr2
        DEALLOCATE cr2

        EXEC ('DROP ROLE ' + @ParentName)

        FETCH NEXT FROM cr1 INTO @ParentID, @ParentName
    END
    CLOSE cr1
    DEALLOCATE cr1
END
GO

Database objects are dropped in the following order:

1. Procedures
2. Functions
3. Views
4. Foreign Keys
5. Foreign Key constraints in other tables
6. Triggers and Constraints
7. Tables
8. Users
9. Roles