Posted by Dan Rigsby on 26th September 2008
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.

Posted in MSSQL, Tips | 11 Comments »
Posted by Dan Rigsby on 6th August 2008
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:
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
- LINQ
- Intellisense for SQL Server Management Studio: T-Sql, database objects, & parameters
- 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.
- Date & Time Datatypes: Separate data and time types. Time can now go down to hh:mm:ss[.nnnnnnn]
- DateTime2 & DateTimeOffset Datatypes: Supports years 0001 to 9999 (much like in .Net); DateTimeOffset is timezone aware, but not daylight savings time!
- Hierachical Datatype: Allows storage of hierarchical data such as org charts, etc.
- Only 1 root per table though
- 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.
- 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.
- Table-Valued Parameters: Tables can be passed as parameters to sprocs.
- 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!
Posted in MSSQL | 3 Comments »
Posted by Dan Rigsby on 18th June 2008
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.
Posted in Community, MSSQL | No Comments »
Posted by Dan Rigsby on 8th January 2008
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:
- Identifying what application an object belongs to
- 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:
- Procedures
- Functions
- Views
- Foreign Keys
- Foreign Key constraints in other tables
- Triggers and Constraints
- Tables
- Users
- Roles
Posted in Database, MSSQL | 1 Comment »