SQL Server Code,Tips and Tricks, Performance Tuning

Tuesday, November 07, 2006

SQL Server 2005 Script Repository

Saturday, November 04, 2006

How Are Dates Stored In SQL Server?

Internally dates are stored as 2 integers. The first integer is the number of dates before or after the base date (1900/01/01). The second integer stores the number of clock ticks after midnight, each tick is 1/300 of a second.

So if we run the following code for the base date (1900/01/01)

DECLARE @d DATETIME
SELECT @d = '1900-01-01 00:00:00.000'

SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),1,4)) AS DateInt,
SUBSTRING(CONVERT(VARBINARY(8),@d),1,4) AS DateBinary
SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),5,4)) AS TimeInt, SUBSTRING(CONVERT(VARBINARY(8),@d),5,4) AS TimeBinary
Go

The results are

DateInt DateBinary
----------- ----------
0 0x00000000

TimeInt TimeBinary
----------- ----------
0 0x00000000

If we use the max date 9999/12/31

DECLARE @d DATETIME
SELECT @d = '9999-12-31 23:59:59.997'

SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),1,4)) AS DateInt,
SUBSTRING(CONVERT(VARBINARY(8),@d),1,4) AS DateBinary
SELECT CONVERT(INT,SUBSTRING(CONVERT(VARBINARY(8),@d),5,4)) AS TimeInt, SUBSTRING(CONVERT(VARBINARY(8),@d),5,4) AS TimeBinary
Go

we get the following result

DateInt DateBinary
----------- ----------
2958463 0x002D247F

TimeInt TimeBinary
----------- ----------
25919999 0x018B81FF

If you take binary values and convert to datetime you get the following results

SELECT CONVERT(DATETIME,0x0000000000000001) --1 Tick 1/300 of a second
------------------------------------------------------
--1900-01-01 00:00:00.003

SELECT CONVERT(DATETIME,0x000000000000012C) -- 1 minute = 300 ticks
------------------------------------------------------
--1900-01-01 00:00:01.000

SELECT CONVERT(INT,0x12C) --= 300
SELECT CONVERT(VARBINARY(3),300) --= 0x00012C

SELECT CONVERT(DATETIME,0x0000000100000000) --add 1 day
------------------------------------------------------
--1900-01-02 00:00:00.000

For smalldatetime the time is stored as the number of minutes after midnight

Now here is some fun stuff

DECLARE @d DATETIME
SELECT @d = .0
SELECT @d
GO
------------------------------------------------------
--1900-01-01 00:00:00.000

DECLARE @d DATETIME
SELECT @d = .1
SELECT @d
GO
------------------------------------------------------
--1900-01-01 02:24:00.000

DECLARE @d DATETIME
SELECT @d = .12
SELECT @d
GO
------------------------------------------------------
--1900-01-01 02:52:48.000

DECLARE @d DATETIME
SELECT @d = '0'
SELECT @d
GO
Server: Msg 241, Level 16, State 1, Line 2
Syntax error converting datetime from character string.

DECLARE @d DATETIME
SELECT @d = 0
SELECT @d
GO
------------------------------------------------------
--1900-01-01 00:00:00.000

So there is no implicit conversion, o is fine 'o' is not

DECLARE @d DATETIME
SELECT @d = 20061030
SELECT @d
GO
Server: Msg 8115, Level 16, State 2, Line 2
Arithmetic overflow error converting expression to data type datetime.

DECLARE @d DATETIME
SELECT @d = '20061030'
SELECT @d
GO
------------------------------------------------------
--2006-10-30 00:00:00.000

Here we have the reverse, the varchar value is fine but the int is not.
This happens because the max integer value that a datetime can take is 36523
If we run the following we are okay

DECLARE @d DATETIME
SELECT @d = 2958463
SELECT @d
GO
------------------------------------------------------
--9999-12-31 00:00:00.000

Friday, November 03, 2006

ISO-11179 Naming Conventions

Straight from the man himself comes this statement posted in the microsoft.public.sqlserver.programming forum: "You need to read ISO-11179 so you use proper data element names. You
actually had "tbl-"on the table names! Sometimes "id" id a
prefix and sometimes it is a postfix."

Of course you know who I am talking about? No? Joe Celko of course. So what is ISO-11179?

The 11179 standard is a multipart standard that includes the following parts:

11179-1
Part 1: Framework, introduces and discusses fundamental ideas of data elements, value domains, data element concepts, conceptual domains, and classification schemes essential to the understanding of this set of standards and provides the context for associating the individual parts of ISO/IEC 11179.

11179-2
Part 2: Classification, provides a conceptual model for managing classification schemes. There are many structures used to organize classification schemes and there are many subject matter areas that classification schemes describe. So, this Part also provides a two-faceted classification for classification schemes themselves.

11179-3
Part 3: Registry Metamodel and Basic Attributes, specifies a conceptual model for a metadata registry. It is limited to a set of basic attributes for data elements, data element concepts, value domains, conceptual domains, classification schemes, and other related classes, called administered items. The basic attributes specified for data elements in ISO/IEC 11179-3:1994 are provided in this revision.

11179-4
Part 4: Formulation of Data Definitions, provides guidance on how to develop unambiguous data definitions. A number of specific rules and guidelines are presented in ISO/IEC 11179-4 that specify exactly how a data definition should be formed. A precise, well-formed definition is one of the most critical requirements for shared understanding of an administered item; well-formed definitions are imperative for the exchange of information. Only if every user has a common and exact understanding of the data item can it be exchanged trouble-free.

11179-5
Part 5: Naming and Identification Principles, provides guidance for the identification of administered items. Identification is a broad term for designating, or identifying, a particular data item. Identification can be accomplished in various ways, depending upon the use of the identifier. Identification includes the assignment of numerical identifiers that have no inherent meanings to humans; icons (graphic symbols to which meaning has been assigned); and names with embedded meaning, usually for human understanding, that are associated with the data item's definition and value domain.

11179-6
Part 6: Registration, provides instruction on how a registration applicant may register a data item with a central Registration Authority and the allocation of unique identifiers for each data item. Maintenance of administered items already registered is also specified in this document.

The one that deals with naming conventions is 11179-5 The link will point to a zip file which has a pdf file in it. The TOC of this pdf file is below

Contents
Foreword
1 Scope
2 Normative references
3 Terms and definitions
4 Data Identifiers within a registry
5 Identification
6 Names
6.1 Names in a registry
6.2 Naming conventions
7 Development of naming conventions
7.1 Introduction
7.2 Scope principle
7.3 Authority principle
7.4 Semantic principle
7.5 Syntactic principle
7.6 Lexical principle
7.7 Uniqueness principle
Annex A (informative) Example naming conventions for names within an MDR registry
Annex B (informative) Example naming conventions for Asian languages

So check it out and hopefully you and your team can adapt a common naming conventions instead of having things named like employee_address, EmployeeAddress, employeeAddress and tblEmployeeAddress.

Thursday, November 02, 2006

SQL Teaser

Try to guess what the output will be of the following SQL Select statement (don't worry, you can't get it right ;-)

First guess then run this

SELECT 123.654, 123d6, 123e4, '123'e4

Surprise!!!!!!

I lifted/stole/borrowed the select statement from Mladen Prajdic's blog

Wednesday, November 01, 2006

Tuesday, October 31, 2006

Download A FREE Trial Of Spotlight On SQL Server And Win A Xbox 360

I just received an email from Quest Software. Quest is giving away 15 Xbox 360™ game systems in 15 days! Download a free trial of Spotlight® on SQL Server Enterprise and you'll be automatically entered into the Quest "Xbox-a-Day Giveaway" drawing.

Now you have a chance to win the Xbox 360™ and detect, diagnose and resolve database performance issues with ease—just by trying Spotlight on SQL Server Enterprise.

What is Spotlight on SQL Server Enterprise?
Spotlight on SQL Server Enterprise is an award-winning database diagnostics tool that can help you ensure data availability and prevent problems before they occur.

Discover, diagnose and resolve SQL Server performance issues

Spotlight on SQL Server Enterprise provides an agent-less easy-to-use database issue discovery solution that enables you to identify and resolve SQL Server performance problems within your SQL Server environment. This powerful tool pinpoints the underlying SQL server contention issues and processes for fast and efficient database administration.

With Spotlight, DBAs can drill down to locate in-depth information about the source of thousands of SQL Server performance problems such as: a specific user, a resource-intensive SQL transaction, an I/O bottleneck, a lock or wait. Spotlight for SQL Server Enterprise sets a baseline for normal activity for each instance, and can set thresholds, notify users and display alerts when it detects performance bottlenecks of any kind.

Spotlight not only monitors the SQL Server Environment, but the underlying operating system on which it resides. From an overview screen, DBAs can view the most active SQL Server sessions, SQL statements, Replication information , blocks, deadlocks, waits, and disk activityto pinpoint and alleviate problems before they occur before and seriously impact end users.

Through one single interface, Spotlight on SQL Server Enterprise answers many questions related to performance:

How is each SQL Server instance performing?
How is the SQL Server Environment as a whole performing?
Where and when are the performance bottlenecks occurring?
How do I catch the problems before they arise?
Who or what is causing the problem and how do I resolve it?

Go HERE for more details

Monday, October 30, 2006

Working Four Days A week Until Next Year

I will be working four days a week only until next year. I did not take a vacation this year since my wife was pregnant with twins. We couldn't go anywhere because the due date was the first week of August; twins usually arrive a couple of weeks earlier. Since we didn't know the exact due date and had to go for monitoring every week we decided not to go anywhere. the end result is that I have 8 (out of 10) vacation days left. So I will be taking wednesdays off until 2007.

I used to live in the Netherlands where everyone has five weeks vacation. So when I came to the United States I was surprised that there was not a law like that here. Besides the five weeks of vacation in Holland you also get 8% of your salary as vacation money. So when you go on vacation you can use that as spending money.

I saved the 'best' for last: if you are unemployed in Holland then you have the same rights. that's right five weeks vacation, 8% vacation money. In Holland you can be unemplyed for years and years, in the US you get 6 months unemployment and the cap is $405 a week. After 6 months you are on your own. I think they should do exactly the same in Holland (no wonder the top tax bracket was 72% in the late nineties; it is I believe 52% now). I knew a lot of people who would be unemployed and work off the books...........

Friday, October 27, 2006

Halloween SQL Teaser

Try to guess without running the code which of the following 3 queries will return a value for maxvalue that is not null
Is it A, B or C?

--A
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SELECT @maxvalue = MAX(crdate)
FROM sysobjects WHERE ID = -99
SELECT 'A' ,@maxvalue maxValue
GO

--B
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SELECT TOP 1 @maxValue = crdate
FROM sysobjects WHERE ID = -99
ORDER BY crdate DESC
SELECT 'B' ,@maxvalue maxValue
GO

--C
DECLARE @maxValue DATETIME
SET @maxValue = '1/1/1900'
SET @maxvalue = (SELECT TOP 1 crdate
FROM sysobjects WHERE ID = -99
ORDER BY crdate DESC
SELECT 'C' ,@maxvalue maxValue
GO

Let me know if you were correct. I must admit I saw this code on Simon Sabin's blog
To understand why this happens read this Tony Rogerson article: T-SQL Value assignment SET vs SELECT

So what does this have to do with Halloween? Nothing really.

One Of The Reasons I Participate In Forums And Newsgroups

This is one of the reasons I participate in SQL Server newsgroups and forums

A person had the following update statement


Update Object_data
set External_Claim_Number = BWCClaimNum
From yson_Claims_eDocs_10_27_2006
where LastName = INJ_lname
and FirstName = INJ_fname
and SSN = inj_ssn
and convert(varchar,injurydate,101) = convert(varchar,claim_injury_date,101)
and convert(varchar,pkclaim) = internal_claim_Number
and BWCClaimNum is not null
and BWCClaimNum <> External_Claim_Number

On the table that got updated (Object_data) the following trigger was created


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER   TRIGGER [tr_Object_Data_IU] ON [dbo].[OBJECT_DATA]
FOR UPDATE,Insert
AS

declare @rows int
declare @object_Data_ID int
set @rows = @@ROWCOUNT
if @rows = 0
    return
if @rows = 1
    begin
    select @object_Data_Id = object_data_id
    from inserted
    goto singlerecord
    end
declare curObject insensitive cursor for
    select object_data_id
    from inserted
open     curObject
fetch next from curObject into @object_data_id
while (@@fetch_status <> -1)
    begin
    singlerecord:
    insert object_datahist (OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,
        INTERNAL_CLAIM_NUMBER,CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,
        DOCUMENT_SUBTYPE,DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,
        YEAR_NUM,DateChanged,ChangedBy,Change
        ,PROV_FAX_NUM)   
        select OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,INTERNAL_CLAIM_NUMBER,
            CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,DOCUMENT_SUBTYPE,
            DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,YEAR_NUM,
            getdate(),user_name(),'U'
            ,PROV_FAX_NUM   
        from    inserted where object_data_id = @object_data_id
    if @rows = 1
        return
   
    fetch next from curObject into @object_data_id
    end
close curObject
deallocate curObject

I suggested that the person did not need the cursor since he is inserting everything anyway into the object_datahist table
I decided to modify the trigger as follows


SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
ALTER   TRIGGER [tr_Object_Data_IU] ON [dbo].[OBJECT_DATA]
FOR UPDATE,Insert
AS
IF @@ROWCOUNT =0
RETURN

    insert object_datahist (OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,
        INTERNAL_CLAIM_NUMBER,CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,
        DOCUMENT_SUBTYPE,DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,
        YEAR_NUM,DateChanged,ChangedBy,Change
        ,PROV_FAX_NUM)   
        select OBJECT_DATA_ID,OBJECT_ID,INJ_SSN,INJ_LNAME,INJ_FNAME,INJ_SEX,EXTERNAL_CLAIM_NUMBER,INTERNAL_CLAIM_NUMBER,
            CLAIM_INJURY_DATE,CLAIM_DOCUMENT_DOS,CLAIM_RISK_NUMBER,DOCUMENT_TYPE,DOCUMENT_SUBTYPE,
            DOCUMENT_SUBTYPE2,EMPLOYER,BILL_NUMBER,PROVIDER_NUMBER,OTHER,GROUP_ID,GROUP_NAME,YEAR_NUM,
            getdate(),user_name(),'U'
            ,PROV_FAX_NUM   
        from    inserted

I also suggested modifying the following line from the update query
and convert(varchar,injurydate,101) = convert(varchar,claim_injury_date,101)
to
and injurydate = claim_injury_date
since the dates are the same anyway

This is the response I got

"I tested removing the cursor driven trigger from the Object_Data table in development and replaced it with the set based up date you provided. Here is what happened:

20,784 records in the test.

1. Using the current trigger I canceled the update after an hour and eleven minutes of running. It had updated 10,218 rows.
2. Using the set based trigger it ran in eleven seconds and generated the correct amount of history.

That's really something to me! Thanks again!"

You see, this is one of the reasons I participate in forums/newsgroups because it feels good to help other people. Another reason is that you can look at the responses from the SQL MVP's and see how they would handle certain situations. I remember running into problems once and I also remembered that I saw this same problem in a newsgroup. after that it's a quick Google and you find your answer

What Are You Watching On Halloween?

I have difficulty picking a movie to watch on Halloween.
Did any of you (my readers) see any of these movies below? Which movie would you pick?

Omen (2006 Edition)
The Gingerdead Man
Saw I
Saw II
Exorcist (Begining)
Seed of Chucky
Ringu
Texas Chainsaw
Silent hill
Hills have eyes
Underworld (evolutions)

Leave me a comment or send me an email by using the Contact Me link.

Thursday, October 26, 2006

SQL Server experienced revenue growth of over 30%

Microsoft Corp. today announced first quarter revenue of $10.81 billion for the period ended September 30, 2006, an 11% increase over the same quarter of the prior year. Operating income for the quarter was $4.47 billion, an 11% increase compared with $4.05 billion in the prior year period. Net income and diluted earnings per share for the first quarter were $3.48 billion and $0.35 per share. For the same quarter of the previous year, net income and diluted earnings per share were $3.14 billion and $0.29, including a $0.02 per share charge for certain legal charges.

Server & Tools revenue increased 17% over the comparable quarter in the prior year, reflecting healthy performance for offerings such as SQL Server™ 2005, Windows Server®, Visual Studio® 2005 and BizTalk® Server. SQL Server experienced revenue growth of over 30%, as customers are increasingly deploying SQL Server for their mission critical, transaction-oriented databases.

Good news if you are a SQL Server developer

Wednesday, October 25, 2006

Enable xp_cmdshell In SQL Server 2005 With The Surface Configuration Tool Or With A Script

This question popped up in a newsgroup today. How do I enable xp_cmdshell in SQL Server 2005? Well you can do it two ways; one is with a script and the other with the Surface Configuration Tool
Let's start with the Surface Configuration Tool

Surface Configuration Tool
You have to navigate to the tool from the start button, the path is below
Programs-->Microsoft SQL server 2005-->Configuration Tools-->Surface Configuration Tool

Then select Surface Area Configuration for Features (bottom one)
Expand Database Engine go all the way down to xp_cmdshell and click enable xp_cmdshell and hit apply

SQL Script
To do it with a script use the one below

EXECUTE sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'xp_cmdshell', '1'
RECONFIGURE WITH OVERRIDE
GO

EXECUTE sp_configure 'show advanced options', 0
RECONFIGURE WITH OVERRIDE
GO

http://sqlservercode.blogspot.com In Top 100000 On Technorati

This blog is currently ranked 90,981 on Technorati
Rank: 90,981 (65 links from 30 blogs)

Not bad I guess, I wonder how long it will take to reach 50000?
The rank was 150000 just a couple of months ago so it's going towards the top (but will never reach it ;-( )

You can check the ranking here http://www.technorati.com/search/sqlservercode.blogspot.com and see who is linking here

Microsoft Changing Live Search Back To MSN Search

Just saw The Island the other day and I must say I liked it a lot. I don't know why the movie didn't do as well as expected. The stars of the movie are Scarlett Johansson (Lost in Translation, Girl with a Pearl Earring) and Ewan McGregor ( Star Wars: Episode III - Revenge of the Sith , Trainspotting , Moulin Rouge!) other prominent roles are for Sean Bean, Steve Buscemi and Michael Clarke Duncan.. The movie is directed by Michael Bay who did Pearl Harbor and Armageddon. Michael Bay is currently working on The Hitcher and Transformers The links are all to IMDB in case you want more details about any of the movies.

The best part of the movie: the car chase.
The car chase is probably the best ever; it even beats the one from the second Matrix movie.

The movie takes place in the year 2050 and people have clones made as insurance; if their liver gives up then they just take the liver from the clone. These clones live isolated underground and have no idea what's going on; they think that they survived some contamination. Every now and then a lottery takes place and the winner goes to the Island. In reality the winner goes to the operating table. I won't give away the whole story you watch that yourself

There is some major product placement in this movie. The Xbox is featured in the movie alongside MSN Search; that's right not Windows Live Search. I guess they can always release a director's cut and 'fix' that. Cadillac has some top of the line car that costs half a million dollars; How much is half a million in the year 2050? To me it seems that a Porsche Carrera would cost about $500000 in about 50 years. Anyway the 'real' Ewan in the movie, who is very rich complains when his 'very' expensive car gets trashed. Would he really get that upset over the car; his clone is more expensive and he tries to kill the clone.

If you can catch the movie on HBO/On Demand then watch this movie. If you don’t want to watch the whole movie then at least watch the car scene; you won’t regret it

Monday, October 23, 2006

Do You Know How Between Works With Dates?

Do you use between to return data that has dates? Do you know that between will get everything since midnight from the first criteria and up to midnight exactly from the second criteria. If you do BETWEEN 2006-10-01 AND 2006-10-02 then all the values that are greater or equal than 2006-10-01 and less or equal to 2006-10-02 will be returned. So no values after 2006-10-02 midnight will be returned.

Let's test this out, first let's create this table

CREATE TABLE SomeDates (DateColumn DATETIME)

Insert 2 values
INSERT INTO SomeDates VALUES('2006-10-02 00:00:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:00:00.000')

Return everything between '2006-10-01' and '2006-10-02'
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061002'
ORDER BY DateColumn
This works without a problem

Let's add some more dates including the time portion
INSERT INTO SomeDates VALUES('2006-10-02 00:01:00.000')
INSERT INTO SomeDates VALUES('2006-10-02 00:00:59.000')
INSERT INTO SomeDates VALUES('2006-10-02 00:00:01.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:01:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 00:12:00.000')
INSERT INTO SomeDates VALUES('2006-10-01 23:00:00.000')

Return everything between '2006-10-01' and '2006-10-02'
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061002'
ORDER BY DateColumn
Here is where it goes wrong; for 2006-10-02 only the midnight value is returned the other ones are ignored

Now if we change 2006-10-02 to 2006-10-03 we get what we want
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061003'
ORDER BY DateColumn

Now insert a value for 2006-10-03 (midnight)

INSERT INTO SomeDates VALUES('2006-10-03 00:00:00.000')

Run the query again
SELECT *
FROM SomeDates
WHERE DateColumn BETWEEN '20061001' AND '20061003'
ORDER BY DateColumn

We get back 2006-10-03 00:00:00.000; between will return the date if it is exactly midnight

If you use >= and < then you get exactly what you need
SELECT *
FROM SomeDates
WHERE DateColumn >= '20061001' AND DateColumn < '20061003'
ORDER BY DateColumn

--Clean up
DROP TABLE SomeDates

So be careful when using between because you might get back rows that you did not expect to get back and it might mess up your reporting if you do counts or sums

Sunday, October 22, 2006

SQL Server 2005 Practical Troubleshooting: The Database Engine Sample Chapter Available

Ken Henderson has posted a sample chapter from the forthcoming book SQL Server 2005 Practical Troubleshooting: The Database Engine on his blog. The sample chapter is: Chapter 4 Procedure Cache Issues. This is a must read for anyone who is troubleshooting SQL Server queries and procedures. Some of the things covered are: Cached Object Leaks, Cursor Leaks, Parameter Sniffing, Excessive Compilation, Poor Plan Reuse and How Cache Lookups Work. I think that this is the first time I have seen parameter sniffing described in a book. Check it out and let me know what you think.

SQLQueryStress Beta 1 Available For Download

Adam Machanic has made available beta 1 of the SQLQueryStress tool. It provides some support for query parameterization and options for collection of I/O and CPU metrics. It is not intended to replace tools such as Visual Studio Team System's load tests, but rather to be a simple and easy-to-use tool in the DBA or database developer's kit.

To download the tool and read the documentation go here: http://www.datamanipulation.net/SQLQueryStress/

Wednesday, October 18, 2006

Visual Studio Team Edition for Database Professionals CTP 6 Is Available For Download

Gert Drapers has announced that CTP6 of Visual Studio Team Edition for Database Professionals is available for download

This is an overview of the highlights in CTP6

Full support for SQL Server 2000 & 2005 objects, the parser work has been completed
Extended Properties support, we know import and deploy all your extended properties
Inline constraint support, if you do not want to separate them out, we allow them inline as well
Pre- and post deployment scripts population during Import Script
Full support for command line build & deploy and Team Build
A new Import Schema Wizard which is also integrated with the New Project Wizard to make project creation and import a single stop shop
Synchronize your database project from Schema Compare, compare your project with a database and pull the differences in to the database project
Schema refactoring is now allowed even if you have files in a warning state
Resolve 3 and 4 part name usage when the referenced database is locally present, same for linked servers
The product no longer installs SQL Express; you can pick your own SQL Server 2005 Developer Edition or SQL Server 2005 Enterprise Edition instance on the local box. When you first start the product for the first time we will ask you to choose an local instance to use
Display detailed Schema Object properties in the VS Property Window for selected objects in the Schema View
Separation of user target database settings through user project files, this allows users to work against a different target instance without changing the main project file.
We made great progress on the overall stability and performance of the product across the board, project creation, importing your schema, reloading project and making changes to your schema
And last but not least we fixed many reported customer problems!

Visit The Data Dude blog for more info

Monday, October 16, 2006

SQL Server Database Publishing Wizard CTP 1 released

SQL Server Hosting Toolkit Released
The goal of the SQL Server Hosting Toolkit is to enable a great experience around SQL Server in shared hosting environments.
The toolkit will eventually consist of a suite of tools and services that hosters can deploy for use by their customers. It will also serve as an incubation vehicle for tools that hosting customers can download and use directly, regardless of whether their hoster has deployed the toolkit

Database Publishing Wizard Community Technology Preview 1
The Database Publishing Wizard enables the deployment of SQL Server 2005 databases into a hosted environment on either a SQL Server 2000 or 2005 server. It generates a SQL script file which can be used to recreate the database in shared hosting environments where the only connectivity to a server is through a web-based control panel with a scripting window.