E-Learning: What's New in Microsoft SQL Server 2008

Microsoft leaning has made available a bunch of free SQL server 2008 lessons

In this 3 hour online collection, IT Professionals will learn about the new features in SQL Server 2008. Topics covered within these clinics include:
What's New in SQL Server 2008 for Enterprise Data Platform
What's New in SQL Server 2008 for Business Intelligence
What's New in SQL Server 2008 for Database Development Student

Below are the links to the three lessons:

Clinic 6188: What's New in Microsoft SQL Server 2008 for Enterprise Data Platform
Clinic 6189: What's New in Microsoft SQL Server 2008 for Business Intelligence
Clinic 6190: What's New in Microsoft SQL Server 2008 for Database Development

Return Null If A Value Is A Certain Value

You need to return NULL only if the value of your data is a certain value. How do you do this?
There are three different ways.

NULLIF
DECLARE @1 char(1)
SELECT @1 ='D'


SELECT NULLIF(@1,'D')


REPLACE
This should not really be used, I just added it here to demonstrate that you can in fact use it.

DECLARE @1 char(1)
SELECT @1 ='D'

SELECT REPLACE(@1,'D',NULL)


CASE
With case you can test for a range of values. You can test for example for values between A and D. If you reverse the logic then you also don't need to provide the ELSE part since it defaults to NULL anyway.

DECLARE @1 char(1)
SELECT @1 ='D'


SELECT CASE @1 WHEN 'D' THEN NULL ELSE @1 END

--No else needed
SELECT CASE WHEN @1 <> 'D' THEN @1 END

And this is how you test for a range.

--Null
DECLARE @1 char(1)
SELECT @1 ='D'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

--E
DECLARE @1 char(1)
SELECT @1 ='E'

SELECT CASE WHEN @1 BETWEEN 'A' AND 'D' THEN NULL ELSE @1 END

SQL Server 2008 Whitepapers

There are some good katmai whitepapers appearing on microsoft.com.
The list is available here http://www.microsoft.com/sql/2008/learning/whitepapers.mspx.

Performance and Scale http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_perfandscale.mspx

Manageability http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_manageability.mspx

Delivering location intelligence with spatial data http://www.microsoft.com/sql/techinfo/whitepapers/spatialdata.mspx

SQL Server 2008 Reporting Services http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_ssrs.mspx

What's new for XML in SQL Server 2008 http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_xml.mspx

Managing unstructured data http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_unstructured.mspx

Server consolidation http://www.microsoft.com/sql/techinfo/whitepapers/hosting_sql_srv_consol.mspx

Data warehousing
http://www.microsoft.com/sql/techinfo/whitepapers/sql-for-datawarehousing_2008.mspx

Data Programmability http://www.microsoft.com/sql/techinfo/whitepapers/sql_2008_dp.mspx

Self-paced Course: What's New in Microsoft SQL Server 2008

In this clinic, you will learn about the new and enhanced features included in SQL Server 2008. You will explore the new data types and the data management features. Additionally, you will learn about the enhanced Integration Services, Analysis Services, and Reporting Services included in SQL Server 2008. This online clinic is composed of a rich multimedia experience.

To get the most out of this clinic, it is recommended that you have:

• Knowledge of general SQL database concepts that are largely independent of a specific version of SQL Server
  
• Knowledge of Microsoft SQL Server 2005 features
  
• Knowledge of deploying and upgrading database solutions
  
• Knowledge of how to solve performance issues related to SQL Server hardware
  
• Performed the job role of a SQL Server 2005 database administrator or database developer
  
• Product or technology experience in Microsoft SQL Server 2000 or SQL Server 2005

Objectives:
At the end of the course, students will be able to:

• Describe the features of SQL Server 2008 and their benefits.
  
• Describe the features of enterprise data platform that help you to secure data in applications.
  
• Describe the dynamic development features that facilitate the development of database applications.
  
• Describe the features of SQL Server 2008 that provide data storage solutions beyond relational databases.
  
• Describe the enhanced features in SSIS that help you to integrate data effectively.
  
• Describe the enhanced features in SSAS that help you to improve the BI infrastructure.
  
• Describe the enhanced features in SSRS that help you to improve the scalability of the reporting engine.

Modules & Lessons

Clinic Overview
Module Introduction
Navigation Overview
Clinic Information

What’s New in SQL Server 2008
Module Introduction

Overview of SQL Server 2008
Lesson Introduction
Enhancements in SQL Server 2008
New Features of SQL Server 2008
Self Test

Enterprise Data Platform
Lesson Introduction
Methods for Data Encryption
Security Auditing for Data Protection
Resource Management Features
Benefits of the Resource Governor Tool
System Analysis Features
Server Management Features
Self Test

Dynamic Development
Lesson Introduction
Data Entities in SQL Server 2008
Data Synchronizing Features
Self Test

Beyond Relational Databases
Lesson Introduction
New Data Types
Spatial Data Type
Table-Value Parameters in SQL Server 2008
Features of Integrated Full-Text Search in SQL Server 2008
Self Test

Pervasive Insight in Integration Services
Lesson Introduction
Data Integration Features
The MERGE Statement in SQL Server 2008
Self Test

Pervasive Insight in Analysis Services
Lesson Introduction
Enhanced Features in Analysis Services
How the Block Computation Feature Works
Self Test

Pervasive Insight in Reporting Services
Lesson Introduction
How Reporting Services Work
Features for Microsoft Office 2007 Integration
The Report Authoring Tool
Report Builder Enhancements
What Is Tablix?
Self Test
Module Summary

Glossary
Module Introduction


Start here: https://www.microsoftelearning.com/eLearning/offerDetail.aspx?offerPriceId=128041

How to find out the recovery model for all databases on SQL Server 2000, 2005 and 2008

How do you find out the recovery model for all the databases on your SQL Server box?
On a SQL Server 2005/2008 box you can use the sys.databases view, the sys.databases view returns a column named recovery_model_desc.
On a SQL server 2000 box you will have to use the DATABASEPROPERTYEX function. The 2000 version will also work on 2000 and 2008 (I tested this with the July CTP)


--2005/2008 version
SELECT [name],
recovery_model_desc
FROM sys.databases


--2000/2005/2008 version
SELECT [name],
DATABASEPROPERTYEX([name],'Recovery') AS recovery_model_desc
FROM master..sysdatabases

SQL Teaser: Guess the output

Here is a small teaser, can you guess the output?


SELECT d.c-d.b/d.a
FROM(SELECT 1,2,5)d(a,b,c)

Microsoft SQL Server 2008 CTP 4 Released

Microsoft SQL Server 2008 CTP 4 has been released 20070831 (yes that is ISO format)
So I have been sleeping for the last couple of days and missed this. Connect didn't email me either ;-(

This download comes as a pre-configured VHD. This allows you to trial SQL Server 2008 CTP 4 in a virtual environment.

Get it here: http://www.microsoft.com/downloads/details.aspx?familyid=6a39affa-db6e-48a9-82e4-4efd6705f4a6&displaylang=en&tm

Microsoft Gains Momentum in Business Intelligence Market as It Prepares to Launch Major BI Offerings

Microsoft Corp. today announced key milestones achieved within the business intelligence (BI) marketplace, including IDC’s recognizing Microsoft as one of the fastest-growing BI vendors in 2006. In IDC’s report, “Worldwide Business Intelligence Tools 2006 Vendor Shares,”* analysts found that Microsoft had a growth rate of 28 percent, the highest among the top 10 industry vendors. In addition, Microsoft® SQL Server™ 2005 was acknowledged by The OLAP Report as the No. 1 online analytical processing (OLAP) server on the market.

Microsoft’s continued investment in delivering high-performing, low-cost BI solutions for all business users via the Microsoft Office experience and SQL Server 2005 has customers taking notice.

“Like most companies, we experience a constant flow of change. Having the ability to monitor my business, analyze key business data, act on it and see real-time results gives me the competitive edge my business needs to survive,” said Michael Saunders, corporate vice president and chief information officer for Kellwood Co. “Microsoft has given us the ability and confidence to allow our employees at all levels to make informed decisions.”

SQL Server has established itself as an enterprise-class data platform. A recent BZ Research study found that 74.7 percent of enterprises use SQL Server, compared with 54.5 percent for the nearest competitor.

The Rise in Demand for Pervasive Business Intelligence

In its analysis, IDC states that the BI market grew by more than 11 percent in 2006. The BI tools market continues to be driven by the need for improved performance management and, to a lesser extent, compliance. “Performance management can take on the form of various decision-support and reporting functions to improve revenue, profit and operational efficiency; decrease costs; uncover new opportunities; or mitigate risk,” said Dan Vesset, an analyst with IDC. “Microsoft had another strong year in this market.”

To give businesses a step in the right direction, Microsoft will launch Microsoft Office PerformancePoint™ Server 2007 on Sept. 19, and SQL Server 2008 is scheduled to ship in the second quarter of 2008. Customers are invited to attend an event on Sept. 20 to learn more about Office PerformancePoint Server 2007. More information about the event is available at http://www.windowsitpro.com/roadshows/performancepoint.

“We are focused on helping our customers achieve higher levels of business performance by allowing them to take advantage of the rich BI capabilities offered through Microsoft business intelligence,” said Chris Caren, general manager of Office Business Applications at Microsoft. “We are excited to help companies integrate efficiently with one another to ensure all employees are held accountable for their actions across the entire business.”

In preparation for the upcoming releases, Microsoft issued its fourth and final community technology preview (CTP) for Microsoft Office PerformancePoint Server 2007 as well as the July CTP for SQL Server 2008.

The CTP program has over 10,000 active members. The latest preview of PerformancePoint Server 2007 is available to the public at https://connect.microsoft.com/site/sitehome.aspx?SiteID=181&wa=wsignin1.0, and enables customers and partners to preview and provide feedback on the latest pre-release versions of both products. The SQL Server 2008 July CTP can be downloaded at http://connect.microsoft.com/sqlserver. Feedback received from customers and partners during previous CTP programs has led to significant improvements in both products and closely aligned them with the needs of customers.

Bridging the BI Divide

Both releases promise significant enhancements to the BI landscape. PerformancePoint Server 2007 helps organizations align their processes by streamlining into a single application the monitoring, analysis and planning activities needed to improve business performance. By deploying PerformancePoint Server 2007, businesses can achieve better results by allowing individuals across the organization to improve performance. SQL Server 2008 will help organizations deliver a more secure, reliable data platform for storing business-critical information and delivering the right information to all users, while reducing the time and cost of managing data.

PerformancePoint Server is tightly integrated with the familiar and easy-to-use Microsoft Office system, allowing organizations to better align employees across divisions and make them accountable for their actions. In addition, PerformancePoint Server 2007 takes advantage of the enterprise-grade reliability, high performance, security technology and scalability of SQL Server 2005, enabling more people at all levels of the organization to transform disparate enterprise data into shared information they can use to make decisions and take actions that improve business outcomes.

“TCS and Microsoft are working together to invest in developing industry-leading, world-class BI solutions that enable our customers to experience certainty,” said Santosh Mohanty, global director and head of the Business Intelligence and Performance Management practice at Tata Consultancy Services Ltd. “We are seeing significant market momentum and double-digit growth for BI solutions built on the industry-leading Microsoft technology stack. This growth, being fueled by demand from some of the largest corporations in the world, has enabled us to aggressively push our strategic objective of building a $100 million BI practice on the Microsoft technology stack.”


* Source: IDC, Doc #207422, June 2007

SQL Server 2008 Live Meeting Event on August 21st on DateTime function

Don't forget, today is the day of the SQL Server 2008 Live Meeting Event. Here is what I got in my inbox:

We wanted to remind you that on August 21st at 11am Pacific, that we will holding our 3rd Live Meeting event of the month of July. Join Michael Wang as he explains all the interesting features the new function "DateTime" will include. We will some examples of how "DateTime" will work in SQL Server 2008, and will be fielding all your questions as well. So make sure you get there early for all the fun!


New Datetime Data Type
08/21/07 @ 11am Pacific

Do you know how NULLIF and non-deterministic functions work?

Run this first

CREATE TABLE #j (n varchar(15))

DECLARE @a int
SET @a = 1
WHILE @a <= 1000 BEGIN
INSERT #j
SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')
SET @a = @a + 1
END
Go

After that is done run this query

SELECT * FROM #j WHERE n = ' '

You will get back between 200 and 300 rows. What just happened? In our insert we use this NULLIF(REPLICATE('1', RAND()*2) , ' ')
What this does is the following: if REPLICATE('1', RAND()*2) equals ' ' then it will insert a NULL, so where do the blanks come from? Well let's find out.

Run this

SET SHOWPLAN_TEXT ON

SELECT NULLIF(REPLICATE('1', RAND()*2) , ' ')

and we see the following
--Compute Scalar(DEFINE:([Expr1000]=If (replicate('1', Convert(rand(NULL)*2))=' ') then NULL else replicate('1', Convert(rand(NULL)*2))))

This can also be written like this

SELECT CASE WHEN REPLICATE('1', RAND()*2) =' '
THEN NULL ELSE REPLICATE('1', RAND()*2) END

See what happens? First SQL evaluates if REPLICATE('1', RAND()*2) is ' ' if that is not ' ' then the same code gets executed again and thus could be a blank
If you use a variable this can never happen.

Here is an example, keep hitting F5 and you will see it will never be blank

DECLARE @val float
SET @val = RAND()

SELECT NULLIF(REPLICATE('1', CONVERT(int, @val*2)) , ' ')

Article: LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to model relational databases using .NET classes. You can use LINQ expressions to query the database with them, as well as update/insert/delete data.

This is the 6th article that Scott Guthrie has written about LINQ. The first 5 can be found below.

Part 1: Introduction to LINQ to SQL
Part 2: Defining our Data Model Classes
Part 3: Querying our Database
Part 4: Updating our Database
Part 5: Binding UI using the ASP:LinqDataSource Control

What is covered in the 6th article?

To SPROC or not to SPROC? That is the question....
The Steps to Map and Call a SPROC using LINQ to SQL
How to Map a SPROC to a LINQ to SQL DataContext
How to Call our Newly Mapped SPROC
Mapping the Return Type of SPROC Methods to Data Model Classes
Handling SPROC Output Parameters
Handling Multiple Result Shapes from SPROCs
Supporting User Defined Functions (UDFs)
Summary


Read the article here: http://weblogs.asp.net/scottgu/archive/2007/08/16/linq-to-sql-part-6-retrieving-data-using-stored-procedures.aspx

SQL Server 2008 Webchat and Live Meeting Series

SQL Server 2008 July CTP Web Chat
July CTP Public Web Chat
08/13/07
Add to my Calendar


SQL Server 2008 July CTP Live Meeting Schedule
Enterprise Scale Reporting Engine
08/15/07

AS Time Series Stability
08/17/07

New Datetime Data Type
08/21/07

Ordpath For SQL Server
08/28/07

Becoming A Better Programmer In 6 Months: The First 20 days

Here is an update of what I accomplished in the first 20 days

Read the book lifehacker
Read the book Microsoft SQL Server 2005 Integration Services
Read the book Extending SSIS 2005 with Script
Read 1 chapter of Learning Python, Second Edition
Played around with the July CTP of SQL Server 2008

So in the first 20 days I have read 3 books however two books are very thin. I will need that time later when I start on much thicker books like Code Complete and Inside Microsoft SQL Server 2005: T-SQL Querying

I will also make a small change to the list instead of Expert SQL Server 2005 Integration Services I will read Core Python Programming

I also started tinkering with Python, those guys are a bunch of jokers. if you type "import this" in a Python command line window you get this output

win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import this
The Zen of Python, by Tim Peters

Beautiful is better than ugly.
Explicit is better than implicit.
Simple is better than complex.
Complex is better than complicated.
Flat is better than nested.
Sparse is better than dense.
Readability counts.
Special cases aren't special enough to break the rules.
Although practicality beats purity.
Errors should never pass silently.
Unless explicitly silenced.
In the face of ambiguity, refuse the temptation to guess.
There should be one-- and preferably only one --obvious way to do it.
Although that way may not be obvious at first unless you're Dutch.
Now is better than never.
Although never is often better than *right* now.
If the implementation is hard to explain, it's a bad idea.
If the implementation is easy to explain, it may be a good idea.
Namespaces are one honking great idea -- let's do more of those!
>>>

So that is one of the easter eggs hidden in Python.

In the past week I also played around with the new date data types in SQl server 2008, I have filed a bug/typo which I found in Books On Line.This week I will concentrate on the book Learning Python, Second Edition during weekends and lunch hours, in the evening I will read Practices of an Agile Developer

This is it for the update. The original post can be found here: http://sqlservercode.blogspot.com/2007/07/become-better-developer-in-6-months.html

A more detailed post about the first 10 days can be found here: http://sqlservercode.blogspot.com/2007/07/becoming-better-programmer-in-6-months.html

I am also glad to say that most of the people I tagged in the original post have responded

SQL Server Notification Services Removed from SQL Server 2008

From the last section (5.0 Deprecated Features) in the read me file

5.0 Deprecated Features
This section covers SQL Server 2005 features that are no longer included with SQL Server 2008.

5.1 SQL Server Notification Services Removed from SQL Server 2008
SQL Server Notification Services will not be included as a component of SQL Server 2008, but will continue to be supported as part of the SQL Server 2005 product support life-cycle. Moving forward, support for key notification scenarios will be incorporated into SQL Server Reporting Services. Existing Reporting Services functionality, such as data driven subscriptions, addresses some of the notification requirements. Features to support additional notification scenarios may be expected in future releases.

There you have it, no more Notification Services

SQL Server 2008 July CTP Has Been Released, The 10 New Features

Download it here: https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5395

What is new?

Enterprise Reporting Engine
Improvements represent the two major infrastructure changes for Reporting Services. Reporting Services enhances the processing engine and rendering extensions to enable new functionality, such as Tablix support, and scalability as well as remove the dependency on IIS. Additionally, new report designer and configuration tool are provided that improve usability and workflow for RS customers.

Analysis Services Time Series
This improvement adds a new time series forecasting algorithm (ARIMA: Auto Regressive Integrated Moving Average) to the data mining algorithm suite that provides more stable long term predictions.

T-SQL Improvements
Object Dependencies: The object dependencies improvement provides reliable discovery of dependencies between objects through newly introduced catalog view and dynamic management functions. Dependency information is always up-to-date for both schema-bound and non-schema-bound objects. Dependencies are tracked for stored procedures, tables, views, functions, triggers, user-defined types, XML schema-collections, and more.


Performance Data Collection
Collect data from various sources in SQL Server and OS to help with performance troubleshooting and server maintenance. With this improvement, organizations improve their analysis of common performance issues:
· Define what data is collected and organize the collection into collection sets
· Start/stop/manipulate collection sets programmatically (T-SQL and .NET API)
· Define where data is stored (relational database)
· View data through reports in SQL Server Management Studio.
· Provide platform to plug in more data collectors in the future.


Extended Events
SQL Server Extended Events is a general event-handling system for server systems. The Extended Events infrastructure supports the correlation of data from SQL Server, and under certain conditions, the correlation of data from the operating system and database applications. In the latter case, Extended Events output must be directed to Event Tracing for Windows (ETW) in order to correlate the event data with operating system or application event data.


Database Mirroring Enhancements
SQL Server 2008 builds upon the momentum of SQL Server 2005 by providing a more reliable platform with enhanced database mirroring:

Automatic bad page repair – allows the principal and mirror machines to transparently recover from 823/824 types of data page errors by requesting a fresh copy of the corrupted page from the mirroring partner.

Log stream compression – compression of the outgoing log stream in order to minimize the network bandwidth used by database mirroring.

Miscellaneous performance enhancements:
using asynchronous log write requests on the mirror in order to shorten the log write time and thus speed-up the commit acknowledgement.

better utilization of the mirroring log send buffers in order to pack multiple smaller log blocks into a single network send.

Supportability and diagnosability improvements:
additional performance counters to allow for more granular accounting of the time spent across the different stages of the DBM log processing.

new DMVs and extensions of existing views in order to expose additional information about the mirroring sessions.


ORDPATH Improvement
ORDPATH improvement provides an important new functionality to our customers who use hierarchical data. It provides a superior way of modeling hierarchies in SQL Server by introducing the HierarchyID system data type and corresponding built-in methods which are designed to make it easier to store, query and operate hierarchical data. HierarchyID is also optimized for representing trees, the most common type of hierarchical data.


Large User-Defined Types Improvement
Large user-defined types allows users to expand the size of defined data types by eliminating the 8‑KB limit.

DATE/TIME Data Types
SQL Server 2008 introduces new date and time data types. The new data types enable applications to have separate date and time types, larger year ranges for date value, larger fractional seconds precision for time value, time-zone offset aware datetime type that containing date, time and time zone offset portion, user defined option on fractional seconds precision of time related types and datetime2 and datetimeoffset provide standards conformant semantics. Along with the T-SQL support on the new types, both native (ODBC, OLEDB) and managed (SqlClient) providers also provide the full support through the client driver APIs.


Improved XML Support
To leverages the new date and time types, SQL Server’s XML Schema collection now provides full support for the xs:date, xs:time and xs:dateTime data types. Support for union types is also enhanced by returning correct results for “instance of” queries when union types are involved, and adding support for lists of unions and unions of lists constructs in XML Schemas.

SQL Server 2008 will launch on Feb. 27, 2008

Turner announced that Windows Server® 2008, Visual Studio® 2008 and Microsoft SQL Server™ 2008 will launch together at an event in Los Angeles on Feb. 27, 2008, kicking off hundreds of launch events around the world. As the next wave of innovation from Microsoft’s Server and Tools Business, these three products will provide a reliable and security-enhanced enterprise platform, serve as the foundation for the next generation of Web-based service applications, and broadly support virtualization and business intelligence. Windows Server 2008, SQL Server 2008 and Visual Studio 2008 represent tremendous opportunities for partners and customers, and as part of the launch wave throughout 2008, Microsoft is planning extensive and far-reaching IT pro, developer and partner outreach, including worldwide training, online and virtual events, as well as myriad resources that will be made available in the coming months to help ensure partners and customers are ready to capitalize on the new benefits offered by these products.

Read the rest here: http://www.microsoft.com/presspass/press/2007/jul07/07-10WPCDay1PartnersPR.mspx

SSIS Script Task In SQL Server 2008 Can Use VB Or C#

Where do I send a thank you letter? Finally we are allowed to use C# in the SQL Server Integration Services Script Task. I always wondered why SQL Server 2005 only uses VB and not C#, you can use C# in the SQLCLR but not in a Script Task. It turns out that SSIS in SQL Server 2005 uses VSA (Visual Studio for Applications) but SQL Server 2008 will use VSTA (Visual Studio Tools for Applications). Lets put these 2 right under each other.

Visual Studio for Applications
Visual Studio Tools for Applications

See the only (confusing) difference is the word Tools. So VSTA does support C#. I guess that if you come from a heavy DTS ActiveX usage background VB would be natural to you. I never felt at home with VB.NET, I switched to C# because I was also using Java and it was easier to make the switch to C#.

Enough whining from me, here are 2 screenshots that I took from the latest SQL Server 2008 June CTP. Have a nice holiday, don't overeat

SQL Myth: Truncate Cannot Be Rolled Back Because It Is Not Logged

I am still amazed at how many people still think that TRUNCATE TABLE is not logged. There is some logging going on but it is minimal, here is what Books On Line says:

TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.

Let’s prove that we can rollback a truncate

Create this table and do the select

CREATE TABLE dbo.Enfarkulator (ID int IDENTITY PRIMARY KEY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator VALUES(1)



SELECT * FROM dbo.Enfarkulator

ID SomeOtherCol
1 1
2 1


Now run this part

BEGIN TRAN
TRUNCATE TABLE dbo.Enfarkulator
SELECT * FROM dbo.Enfarkulator
ROLLBACK TRAN


ID SomeOtherCol
(0 row(s) affected)

As you can see the table was truncated, now select from the table again


SELECT * FROM dbo.Enfarkulator

ID SomeOtherCol
1 1
2 1


Yep, the data is there, proving that you can rollback a truncate and all the data will be there. There are two other major difference between truncate and delete which I will explain below.

Truncate doesn’t preserve the identity value but delete does

This is another difference between truncate and delete, truncate will reset the identity value but delete does not. Run the following code to see how that works


CREATE TABLE dbo.Enfarkulator2 (ID int IDENTITY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator2 VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)


SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator


DELETE dbo.Enfarkulator2
TRUNCATE TABLE dbo.Enfarkulator

INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)

SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator

The Enfarkulator id was reset and the Enfarkulator2 id was not. In order to do the same with delete you will need to run a dbcc checkident reseed command. Here is the code for that.

DELETE dbo.Enfarkulator2
TRUNCATE TABLE dbo.Enfarkulator

DBCC CHECKIDENT (Enfarkulator2, RESEED, 0)

Now insert again and you will see that the values are the same.

INSERT dbo.Enfarkulator VALUES(1)
INSERT dbo.Enfarkulator2 VALUES(1)

SELECT * FROM dbo.Enfarkulator2
SELECT * FROM dbo.Enfarkulator



You can’t truncate tables that are referenced by a foreign key constraint.

If you have a table which is referenced by another table with a foreign key constraint then you cannot truncate that table. Here is the code for that

CREATE TABLE dbo.Enfarkulator3 (ID int IDENTITY, SomeOtherCol varchar(49))
GO
INSERT dbo.Enfarkulator3 VALUES(1)



Now let’s add the foreign key

ALTER TABLE dbo.Enfarkulator3 ADD CONSTRAINT [FK_Fark3_Fark]
FOREIGN KEY ([ID]) REFERENCES [dbo].[Enfarkulator] ([ID])


Now try to truncate.

TRUNCATE TABLE Enfarkulator

Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'Enfarkulator' because it is being referenced by a FOREIGN KEY constraint.

See? You cannot do that

--Clean up time ;-)
DROP TABLE dbo.Enfarkulator3,dbo.Enfarkulator2,dbo.Enfarkulator


Cross-posted from SQLBlog! - http://www.sqlblog.com/

SQL Server 2008 Is RTM (According to SERVERPROPERTY('productlevel'))

SELECT @@VERSION,
SERVERPROPERTY('productversion'),
SERVERPROPERTY('productlevel')

Microsoft SQL Server code name "Katmai" - 10.0.1019.17 (Intel X86)
May 24 2007 15:26:55 Copyright (c) 1988-2007 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

10.0.1019.17
RTM (???)

Also interesting is that the tools (SSMS) are the same as with SQL Server 2005, If you have SQL Server 2005 already installed it will skip installing those.

Okay, this is the last Katmai post....for today.....I promise.....


Cross-posted from SQLBlog! - http://www.sqlblog.com/

SQL Server 2008 (Katmai) Cannot Be Installed On A PC With SQL Server 2000 On It

I tried installing the SQL Server 2008 – June CTP on one of my machines which had SQL Server 2000 installed; it does not let you do that. I guess this is the time to uninstall SQL Server 2000. That is just what I did. First thing I noticed is that MERGE statement is back. IIRC the MERGE statement was also in SQL Server 2005 Beta2 but got pull out of the RTM

Here is a small example of using MERGE from the Katmai Books On Line

MERGE FactBuyingHabits AS fbh
USING (SELECT CustomerID, ProductID, PurchaseDate FROM PurchaseRecords) AS src
ON (fbh.ProductID = src.ProductID AND fbh.CustomerID = src.CustomerID)
WHEN MATCHED THEN
    UPDATE SET fbh.LastPurchaseDate = src.PurchaseDate
WHEN NOT MATCHED THEN
    INSERT (CustomerID, ProductID, LastPurchaseDate)
        VALUES (src.CustomerID, src.ProductID, src.PurchaseDate);

Cross-posted from SQLBlog! - http://www.sqlblog.com/