SQL Server
We’ve all written a CASE expression (yes, it’s an expression and not a statement) or two every now and then. But did you know there are actually 2 formats you can write the CASE expression in? This actually bit me when I was trying to add some new functionality to an old stored procedure. In some rare cases the stored procedure just didn’t work correctly. After a quick look it turned out to be a CASE expression problem when dealing with NULLS. In the first format we make simple “equals to” comparisons to a value: ...
2013 has started great and the SQL community is already brimming with events. At some of these events you can come say hi. I’ll be glad you do! These are the events with dates and locations that I know I’ll be speaking at so far. February 16th: SQL Saturday #198 - Vancouver, Canada The session I’ll present in Vancouver is SQL Impossible: Restoring/Undeleting a table Yes, you read the title right. No, it's not about the usual "one table per partition" and "restore full backup then copy...
This version adds support for SQL Server 2012 RC0 and fixes a few bugs with SQL History. Because of the support for regions in SSMS 2012 the regions and debug sections feature has been removed from SSMS Tools Pack for SQL Server 2012. The feature is still available for previous SSMS versions. In other news SSMS Tools Pack has won the SQL Magazine bronze award for best free tool of 2011. You can view all the details at the SQL Server Magazine Award page. Thanx to all the people who...
Two years ago 59 SQL Server MVP's came together and helped make one of the best book on SQL Server out there. Each chapter was written by an MVP about a part of SQL Server they loved working with. This resulted in superb quality content and excellent ratings from the readers. To top it off all earnings went to a good cause, the War Child International organization. That book was SQL Server MVP Deep Dives.
This year 63 SQL Server MVPs, me included, decided it was time do repeat the success of the first book.
Let me introduce you the:
SQL...
What better way to end the summer and start those productive autumn days ahead than with a fresh new version of the SSMS Tools Pack.
This is a big release with two new features that are huge productivity boosters.
First new feature are Tab Sessions. Every SQL tab you open is saved every N (default 2) minutes and is stored in a session. This works similar to internet browser sessions. Once you reopen SSMS you can restores your last session with a click of a button. You even get every window connected to the server it was previously connected to. The Tab...
My MVP rotation came up today and I'm happy to say that I've been renewed for one more year as a SQL Server MVP. Hm…. looks like I'll have to start blogging some more. :)
SQLBits: April 7th - April 9th 2011 in Brighton, UK
Free community event on Saturday (April 9th) with a paid conference day on Friday (April 8th) and a Pre Conference day full of day long seminars (April 7th).
It'll be a huge event with over 800 attendees and over 20 MVPs. I'll be presenting on Saturday April 9th.
SQL in the City: July 15th 2011 in London, UK
One day of free SQL Server training sponsored by Redgate. Other MVP's that'll be presenting there are Steve Jones (website|twitter), Brad McGehee (blog|twitter) and Grant Fritchey (blog|twitter)
At both conferences I'll be presenting about database testing.
In...
SQL injection is a method by which a hacker gains access to the database server by injecting specially formatted data through the user interface input fields. In the last few years we have witnessed a huge increase in the number of reported SQL injection attacks, many of which caused a great deal of damage.
A SQL injection attack takes many guises, but the underlying method is always the same. The specially formatted data starts with an apostrophe (') to end the string column (usually username) check, continues with malicious SQL, and then ends with the SQL comment mark (--) in order...
This release adds a great new feature and fixes a few bugs.
The new feature called Window Content History saves the whole text in all all opened SQL windows every N minutes with the default being 30 minutes. This feature fixes the shortcoming of the Query Execution History which is saved only when the query is run. If you're working on a large script and never execute it, the existing Query Execution History wouldn't save it. By contrast the Window Content History saves everything in a .sql file so you can even open it in your SSMS. The Query Execution History...
We’re all familiar with the ability to restore a database to point in time using the RESTORE WITH STOPAT statement.
But what if we have multiple databases that are accessed from one application or are modifying each other? And over multiple instances? And all databases have different workloads? And we want to restore all of the databases to some known common relative point? The catch here is that this common relative point isn’t the same point in time for all databases. This common relative point in time might be now in DB1, now-1 hour in DB2 and yesterday in DB3....
I was wondering what i should write about for my 200th blog post. And I’m in luck because Karma’s got my back and that’s why yesterday we had an interesting case of plagiarizing. So let’s get optimistic! A relatively new blogger called SQLDigs copied this post of mine about new version of SSMS Tools Pack. Because there was no trackback or any other link back to my blog I had no idea until David Levy (Blog|Twitter) told me about it. As I’ve looked over the other content of the blog I’ve noticed quite a few familiar paragraphs of...
This is a release that fixes all known bugs. If you encounter any new ones don’t hesitate to report them. :)
The main feature list hasn’t changed.
A few improvements have been made though:
Save SQL Snippets to HTML and/or print them directly from SSMS.
In Window Connection Coloring the server names can now be regular expressions giving you the ability to color multiple servers with the same color.
Here’s a blog from David Levy (Blog|Twitter) about the new window connection coloring using regular expression and why is it really useful to him. Thanx for kind words Dave.
You can download the new version 1.9 here.
Enjoy...
As of today I’m the third Slovenian SQL Server MVP.
Thanx to all who nominated me!
Let’s see how this year goes and i’m sure it’ll be a blast, but most importantly:
See you all at the MVP Summit next year! :))
This is a post for a great idea called SQL University started by Jorge Segarra also famously known as SqlChicken on Twitter. It’s a collection of blog posts on different database related topics contributed by several smart people all over the world. So this week is mine and we’ll be talking about database testing and refactoring. In 3 posts we’ll cover: SQLU part 1 - What and why of database testing SQLU part 2 - What and why of database refactoring SQLU part 3 - Database testing and refactoring tools and examples This is the third...
This is a post for a great idea called SQL University started by Jorge Segarra also famously known as SqlChicken on Twitter. It’s a collection of blog posts on different database related topics contributed by several smart people all over the world. So this week is mine and we’ll be talking about database testing and refactoring. In 3 posts we’ll cover: SQLU part 1 - What and why of database testing SQLU part 2 - What and why of database refactoring SQLU part 3 - Database testing and refactoring tools and examples This is a second...
This is a post for a great idea called SQL University started by Jorge Segarra also famously known as SqlChicken on Twitter. It’s a collection of blog posts on different database related topics contributed by several smart people all over the world. So this week is mine and we’ll be talking about database testing and refactoring. In 3 posts we’ll cover: SQLU part 1 - What and why of database testing SQLU part 2 - What and why of database refactoring SQLU part 3 - Database testing and refactoring tools and examples With that out of...
This is a release that fixes all known major bugs and most of the minor ones.
The main feature list hasn’t changed.
The only addition is the ability to export and import only SQL snippets.
Before you could only export/import all settings which included the snippets.
You can download the new version here.
Enjoy it!
Execution plans! Don’t you just love them? They’re the first thing you look at when tuning a query or a stored procedure. But what do you do if you have a gigantic query play with 10’s of nodes? how do you find the most complex one? Where do you start?
What I’ve usually done in situations like that is to first find the node/statement with the highest cost and work from there. Now the highest cost can be IO, CPU, Row number or the good old SubTree cost which gives us a number based on all those counters. Let’s see how.
With...
Adam Machanic launced his second TSQL tuesday: Invitation for T-SQL Tuesday #002: A Puzzling Situation. The theme is to show something interesting and not expected by the first look. You can follow posts for this theme on Twitter by looking at #TSQL2sDay hashtag.
Ok here we go. Let me ask you this: How many times will the subquery in the below SQL statement be executed?
SELECT (SELECT COUNT(*) FROM Sales.SalesOrderDetail WHERE SalesOrderID = SOH.SalesOrderID) AS c, *FROM Sales.SalesOrderHeader SOHORDER BY c DESC
How about the subquery in this in the below SQL statement be executed? It’s the same query...
A few days ago Adam Machanic proposed a great idea about a T-SQL Tuesday. Every Tuesday there would be a topic bloggers all around would post about. Chosen as the first topic was the date and time stuff in SQL Server.
Because there’s already all this great content out there I’m not going to repeat it but I’m going to put together a ton of datetime resources from SQLTeam forums and blogs as a resource you can use in the future.
Michael Valentine Jones
MVJ as we like to call him in the forums has a thing for datetime manipulation. He has...
A while back I was thinking about SQL Server’s capabilities of returning data as XML with FOR XML and it occurred to me that maybe returning 10.000 results as xml would be faster that the way SQL server returns them now in Tabular Data Stream (TDS). After 10 seconds I dismissed it as nonsense because if that were so the xml would be used by default. But it piqued my curiosity about what would be the actual size overhead of returning XML compared to TDS. I knew there was overhead but i didn’t think it would be so high.
So i...
Due to a bug i haven’t anticipated, a whole load of users with non English SSMS couldn’t install the latest SSMS Tools Pack 1.7.
The SSMS Tools Pack 1.7.5.1 fixes this as well as 1 other major bug that was reported and 3 smaller ones.
The feature list hasn’t changed.
You can simply reinstall 1.7.5.1 over the 1.7.0.0.
If you’re still on 1.5 or less then you’ll have to manually uninstall it and install 1.7.5.1.
You can download the new version here.
Hopefully this will bring back joy to everyone :)
Ladies and gentlemen, boys and girls, the STP is back to rock your world!
The new feature is SQL Snippets to speed up your development.
This new version brings one completely new option, full GUI redesign, completely rewritten installer, improved error handling and reporting, bug fixes and old features improvements.
Type in the assigned shortcut, press enter or tab and get the snippet you need.
It's quick, clean and saves time!
Some of more visible improved features include:
Completely new installer using the powerful Windows Installer XML (WiX) toolset. Now there’s only one MSI installer file for...
We all know how to find only duplicated rows in a table. Since SQL Server 2005 this became really simple using a ROW_NUMBER() window function like this: USE AdventureWorks
GO
;WITH cteDupes AS
(
-- find all rows that have the same AddressLine1 and City.
-- we consider those rows are duplicates so we partition on them
SELECT ROW_NUMBER() OVER(PARTITION BY AddressLine1, City ORDER BY AddressID) AS RN,
*
FROM ...
Simply put:
I prefer using surrogate keys because natural keys are by default a subject to change which is a bad behavior for a row identifier.
But let’s dig a bit deeper into each key type to see why this is. Here’s a little table with column names that tell us what kind of a key each column is.
Surrogate keys
A surrogate key is a row identifier that has no connection to the data attributes in the row but simply makes the whole row unique. And that property is also the downside of it. Because it has no connection to the data...
On September 2nd this years biggest online SQL Server related event will begin. It’s the 24 hours of PASS. The 24 one-hour presentations will begin at 00:00 GMT (UTC) on September 2, 2009 and it will last full 24 hours. The speaker list is impressive and topics are something to be desired. So on September 2nd prepare to have a sleepless day. Gentlemen, start your SQL engines!
The Running Totals problem is as old as accounting. In SQL Server there are different ways of calculating it and the general consensus is that it is one of the few problems best handled with a cursor. I still say it’s best handled in the presentation layer though. Being the SQL geek I am I can’t accept a problem in SQL Server which has a cursor for a solution (just kidding). Note that I didn’t put any indexes on the tables so we can’t rely on them for any kind of ordering. The base table...
Today on twitter Lori Edwards (@loriedwards) asked how can you check when was SQL Server installed with a T-SQL query. Otherwise this is pretty simple by looking at the creation time of master database (provided you never had to restore it). But i wanted to find a nice way of doing this without resorting to any xp_ stored procedures. Of course this is possible by looking into the sys.syslogins compatibility view: SELECT createdate as Sql_Server_Install_Date
FROM sys.syslogins
where sid = 0x010100000000000512000000 -- language neutral
...
I’ll be talking about the following topics: What should developers know about database design so they don't have performance and logical problems? What's new in SQL Server 2008 that helps solve some business problems that sometime required "hacking" before. Concurrency design models and isolation levels. The presentation will start on Tuesday June 16th at 2:00 PM EST / 6:00 PM UTC / 8:00 PM CET More info at PASS Application Development SIG UPDATE: you can view the presentation here.
I’ve written two articles on SQLTeam.com about a great new feature in SQL Server 2008 called Extended Events. They are the new low level, high performance eventing system in SQL Server. They use less system resources and provide better tracking of SQL Server performance than previous methods like Perfmon and SQL Trace/Profiler events. 1. Introduction to SQL Server 2008 Extended Events This is an introductory article where we take a look at performance troubleshooting and system monitoring and what they lack in previous SQL Server versions. After that we get to know the Extended Events architecture,...
Recently i was looking for a SQL Server 2008 based template that would look cool. Since I haven’t found anything I’ve made my own. It’s a dark background template that you can use freely.
Suggestions for improvements are welcome in the comments. Also if you have an idea for a light background based template, I’d love to hear it.
The title look
...
A while back we had an interesting problem at work. We were calculating MD5 hashes for some values in both .Net and SQL Server and although the input values were all the same our MD5 hashes were different. After some time spent looking dumbfounded at the code I’ve realized what the bug was and started laughing. I saw that HashBytes function was the “culprit”. HashBytes function was introduced in SQL server 2005 to simplify creating hashes in the database. It can convert values to MD2, MD4, MD5, SHA, or SHA1 formats. HashBytes' result depends on the input text’s data type...
Indexes are one of the most important database features. Without them your database will crawl under a table in fear of simple queries on large tables or complex queries on small tables. That’s why one of the most important things a DBA or a database developer should know is basic index maintenance.
Performance problems are usually result of missing indexes. Index tuning is more of an art than it is a science since possible workloads are so many that there is no one rule to rule them all. It’s a mixture of testing, implementing, retesting and reimplementing. For this purpose...
This new version brings one completely new feature and a few improved old ones.
The new feature is Window Connection Coloring.
It includes a colored strip indicator that can be docked to any side of the window.
Improved features include:
Search Table or Database Data
Uppercase/Lowercase keywords and Proper Case Database Object Names
Sending feedback directly from SSMS.
Import and Export options.
Delete Query...
Comparing result sets
There are times, although not often that you have to compare 2 result sets. This usually happens when you're analyzing data for whatever reason or unit testing a database.
Usual methods include
- using the UNION of both queries because it filters duplicate data and checking if the row counts are the same
- using the EXCEPT if you have SQL Server 2005+
- using NOT EXISTS which is very ugly and not worth it
- using a FULL OUTER JOIN and looking at nulls or some other method I haven't mentioned
- returning result sets to client and comparing...
This is something I haven't noticed before but I think it's mighty cool! When you choose to Include the Actual Execution Plan in SSMS 2008 it suggest any missing indexes it thinks that are needed. Plus it also calculates the performance benefit from adding them. I'm guessing the number (green 84.7361 in the picture) is in percent... So it's like a Database Tuning Advisor "Lite" :)) And best of all this also works when you're connected to a SQL Server 2000 or 2005. This is what it looks like: I'm starting to like this new SSMS...
I've written a second article here on SQL Team on how to schedule jobs in SQL Server 2005 Express.
Intro
In my previous article we saw how to make simple job scheduling in SQL Server 2005 Express work. We limited the scheduling to one time or daily repeats. Sometimes this isn't enough. In this article we'll take a look at how to make a scheduling solution based on Service Broker worthy of the SQL Server Agent itself including run once, daily, weekly, monthly absolute and relative scheduling:
Scheduling Jobs in SQL Server Express - Part 2
And part 1 of this series:
Scheduling...
SQL Server Profiler is a well known tool for tracing different activity that happens between your server and the clients connected to it.
But very few people know that you can have custom events and trace them in the SQL Server Profiler. They can be found under User configurable event group:
We can have up to 10 custom events.
Firing these custom events is done by executing sp_trace_generateevent stored procedure which takes event id as the first input parameter. These event ID's span from 82 (UserConfigurable:0) to 91 (UserConfigurable:9)
A simple code example to demonstrate this:
USE AdventureWorks
GO
CREATE PROCEDURE spDoStuff
...
With this new version you can use it for SQL Server Management Studio 2008 and SQL Server Management Studio 2008 Express.
I've added a new feature to Search through the Database data. There are times when you'd like to find some value but can't remember in which table it is.
Also the SQL Query History Log Viewer has been remodeled. I'm open to suggestions on how to improve it further.
You can also have the SSMS Tools Pack installed for both SSMS 2005 and SSMS 2008 on the same machine, however they don't share the same settings.
I would also like to...
Let us start with a simple question: What is the goal of software development, be it database or .Net (or any other language)? The first answer would be: Customer satisfaction! And you'd be right. However there's more to customer satisfaction then the immediate product delivery effect. We have to think about future change requests, maintenance periods, etc... Almost every business application out there consists of 2 basic parts: database back end and some kind of front end that consumes the data. In our case the front end is anything with access to the database. In regard to future code...
In my opinion these 2 batches should behave the same but they don't. the first fails and the second runs ok. I've searched through Books Online for any clue but i haven't really found anything useful. Does anyone have any clue about this? Permissions and transaction isolation levels are not an issue here. -- just to make sure it doesn't already exist
IF OBJECT_ID('tempdb..#tempTable') IS NOT NULL
BEGIN
SELECT 'DROP Temporary table'
DROP TABLE #tempTable
END
IF OBJECT_ID('normalTable') IS NOT NULL
BEGIN
SELECT 'DROP Normal table'
DROP TABLE...
In my previous post about immediate deadlock notifications in SQL Server 2005 I've shown a way to use a try catch block to get the deadlock error. The con of this method, although it uses best practice for error handling in SQL Server 2005, is that you have to change existing code and it doesn't work for non stored procedure code. And that IS a pretty BIG con! As is customary in this blog there is a solution to this. :) SQL Server 2005 Event notifications Event notifications are a special kind of database object that send information...
Deadlocks... huh??
Deadlocks can be a pain to debug since they're so rare and unpredictable. The problem lies in repeating them in your dev environment. That's why it's crucial to have as much information about them from the production environment as possible.
There are two ways to monitor deadlocks, about which I'll talk about in the future posts. Those are SQL Server tracing and Error log checking. Unfortunately both of them suffer from the same thing: you don't know immediately when a deadlock occurs. Getting this info as soon as possible is sometimes crucial in production environments. Sure you can always set...
After a long while I've finally managed to create a release version. I've also added some cool new features.
It is completely free and has no expiration date like the prior beta versions.
I've also changed the RSS feed to Feedburner to which you can subscribe to at: http://feeds.feedburner.com/SsmsToolsPack
If you're subscribed to the old feed please change it.
In SSMS Tools Pack 1.0 you can find these features:
- Uppercase/Lowercase keywords:
Set all keywords to uppercase or lowercase letters. Custom keywords can be added.
- Run one script on multiple databases:
Run selected or full window text on selected databases on the...
Just as the title says :)
You can read the article here.
Thanx to Kevin Kline for publishing it!
Sometimes we wish to know what locks are being held by the transaction. Also it would be great to know what SPID owns the transaction, on which objects the locks are being held, what SQL statement caused the locks, etc...
With the introduction of DMV's in SQL Server 2005 getting this information is quite easy with this query:
SELECT L.request_session_id AS SPID,
DB_NAME(L.resource_database_id) AS DatabaseName,
O.Name AS LockedObjectName,
P.object_id AS LockedObjectId,
...
It does when it feels like it, but not in the way you immediately think of.
A few days ago Jeff posted about short-circuiting wondering how it works and since I've played with this a long while ago I thought I'd share my results. Hopefully it will make it clearer how SQL Server behaves when evaluating conditions in its WHERE clause. So let's see how it's done in greater detail.
First we must create our test table with some sample data which will be defined like this:
IF OBJECT_ID('t1') IS NOT NULL
DROP TABLE t1
CREATE TABLE t1(id INT PRIMARY...
I've written an article here on SQL Team about Integrating Profiler and PerfMon Log Files.
Troubleshooting SQL Server requires the use of both PerfMon (Performance/System Monitor) and SQL Trace files created by SQL Profiler or directly by SQL Server trace. Analysis of the gathered data is much easier if you can correlate your trace file with the PerfMon counters. In this article I'll show how to create a PerfMon counters log file and SQL Profiler Trace file, how to read them both and how to correlate the two files in SQL Profiler.
Integrating Profiler and PerfMon Log Files
Probably everyone is familiar with the Count(*) function in SQL Server.
But there seems to be a great deal of confusion amongst youngsters (SQL wise) about how all its possible options work.
Let us banish the confusion back to the dark realms where in belongs to:
DECLARE @t TABLE (val INT)
INSERT INTO @t
SELECT 1 UNION ALL
SELECT 1 UNION ALL
SELECT NULL UNION ALL
SELECT NULL UNION ALL
SELECT 4 UNION ALL
SELECT 4 UNION ALL
SELECT 5
SELECT COUNT(*) AS CountAll, -- counts all rows
COUNT(val) AS CountAllNoNull, -- counts rows that don't contain NULL
COUNT(DISTINCT val)...
I've written an article here on SQL Team about locking in SQL Server.
It's an introductory article that shows lock modes, lock granularity and lock compatibility matrix
and it will be followed by a few more advanced ones on the topic of locking.
Part 1: Introduction to locking in SQL Server 2005
I've released a new version of the add-in.
Most of the reported bugs are fixed and I've added two new main functionalities:
- Search Results in Grid Mode and through Execution Plans
- Generate Insert statements for a single table, the whole database or current resultsets (yes all of them) in grids
Insert statement support scripting binary data up to 5 Mb from image and varbinary(max) columns
Minor changes include customizable shortcuts and the ability to move the menu between tools submenu and main menu bar.
You can find it on www.SsmsToolsPack.com or subscribe to the RSS feed to get notified of the new changes...
If you're a .Net developer aliases should be very familiar to you. if they're not here is a reminder:using MyAlias = This.Is.My.Full.Namespace.MyClass;
MyAlias m = new MyAlias(); // MyAlias is of course of type MyClass
Well it seems SQL Server 2005 added a similar functionality to T-SQL. It's called Synonyms.
You can use them in all CRUD operations as well as in sub-selects and dynamic SQL
What's interesting is that synonyms are evaluated at run time and the binding between the synonym and an object is by name only.
This means that you can drop the object referenced by the synonym at any time, but...
Update: New Stuff from the latest update will be in RED. This list will grow as I find new tools. So if you know of some not on this list do post them in the comments. SQL Server Management Studio Add-in's SSMS Tools Pack - an add-in with lots of IDE options (Query Execution history, regions, debug sections, CRUD stored procedures creation, new query templates, running custom scripts from Object explorer's context menu) for SQL Server Management Studio and SQL Server Management Studio Express Data Scripter - generates insert statements for a table Create Database Snapshot - simple GUI...
Today I feel a bit evil minded so I thought I'd share this little prank-friendly function.
If you put this into a .Net Assembly you import to SQL Server as a function or a stored procedure
you can wreck some real havoc :)
private static void SQLPrank()
{
Random r1 = new Random(DateTime.Now.Millisecond);
int lowerBound = r1.Next(1000, 2000);
int upperBound = r1.Next(3000, 4000);
int randomNumber = r1.Next(1000, 4000);
/*
1000 2000 ...
If you've ever worked with SQL Server profiler then you're familiar with the EventClass column in the trace.
It tells you what event is being monitored for each row. When you save this trace to a table you can see
that the EventClass no longer has a description but an ID. So how do you map this to a description?
If you know where to look it's amazingly simple but if you don't here's a hint: sys.trace_events
Here's a script to help you out:
SELECT TE.name, T.*
FROM dbo.Trace T -- table that contains...
We're having a contest over at SqlTeam.com. Go take a look it might be worth your while.
All you have to do is have a post in the forums.
The more posts you have the better the chance you have of winning.
What can you win?
Well the title says it all. See what that is on the ApexSQL Developer studio page. It's a whole lot of goodies!
Read contest details here.
Until recently i've been sure that there are 2 date formats that are completly locale insensitive. Those 2 i thought were locale insensitive are yyyy-mm-dd HH:mm:ss.fff and yyyymmdd HH:mm:ss.fff. And i was proven wrong a few days ago! The only locale insensitive date format is yyyymmdd HH:mm:ss.fff you can easily test this with this script: DECLARE @t1 table (date DATETIME)
SET DATEFORMAT ymd
INSERT INTO @t1 ( date )
VALUES ( '2007-10-22 10:15:3.83' )
-- this fails
SET DATEFORMAT ymd
INSERT INTO @t1 ( date )
VALUES ( '2007-22-10 10:15:3.83' )
SET DATEFORMAT ymd
INSERT INTO @t1 ( date )
VALUES ( '20071022 10:15:3.83' )
SET...
Often you can see a request made by people to help them separate data in thier character based columns to numeric and non-numeric part.
Most often the answer they get is very simple: Use IsNumeric built in function.
But there's a very little known fact about this function though:
It DOES NOT behave the way you think it does!
You don't believe me? Let's see with an example. How many rows do you think this will return:
DECLARE @t1 TABLE (title varchar(20))
INSERT INTO @t1
SELECT '123d45' UNION ALL
SELECT '123e45' UNION ALL
SELECT '12 3456' UNION ALL
SELECT '123456' UNION ALL
SELECT '5532.673'
SELECT *
FROM ...
First let's look at the difference assuming we want to delete the whole table like truncate does.
Truncate:
- deallocates the data pages in a table and only this deallocation is stored in transaction log
- aquires only table and page locks for the whole table. since no row locks are used less memory is required (lock is a pure memory object)
- resets identity column if there is one
- removes ALL pages. NO empty pages are left behind in a table
- fast(er)
- doesn't fire delete triggers
Delete:
- removes one row at the time and every deleted row is stored in the transaction log
- aquires table and/or page and...
In long running scripts it's usefull to notify the client of the progress. Here's a simple solution.
Let's create a simple "long running" script:
-- this will return all 3 results to the client after 20 seconds (full execution time)
SELECT 1
WAITFOR DELAY '00:00:10'
SELECT 2
WAITFOR DELAY '00:00:10'
SELECT 3
But what if you want to notify the client when each select happened?
RAISERROR to the rescue:
SELECT 1
-- flush message to client
RAISERROR (N'After 1', -- Message text
10, -- Severity
1) -- State
...
SSMS Tools Pack is an Add-In (Add-On) for Microsoft SQL Server Management Studio 2005 and
Microsoft SQL Server Management Studio Express 2005.
It contains a few upgrades to the IDE that I thought were missing from Management Studio.
These are:
- Query Execution History (Soft Source Control): saves every query text you execute in a file or in a database
- Text document Collapsable Regions and Debug sections: regions and debug section known from Visual Studio which are missing in SSMS
- New query template: set the template that will open when you open a new query
- CRUD (Create, Read, Update, Delete) stored procedure generation:...
Indexes are a constant problem in understanding for beginners (and the "not so beginners") in the database world. And don't you just love the
hardcore mathematical explanation of B-Trees and their traversal. Personaly I much rather have visual props and a story to support an explanation.
Appealing visual props are even better. That's how this post originated. So let us begin!
Imagine you live in a pre-computer-in-every-nook-and-crane world (around 1960's :)).
You wake up one morning with a huge craving to read Agatha Christie's books. So you go to the library which has a few million books.
You walk up to the cute librarian (look...
You wouldn't believe how many times i've seen this code
IF (SELECT COUNT(*) FROM Table1 WHERE ... ) > 0
It's understandable though. Logically it's the easiest way to write "if there are any rows matching my condition do this".
But it's also wrong. Plainly and simplly wrong!
Why?
Because when you do a count(*) there is no way to get around an index range scan or a full table scan. For a large resultset this will be a huge resource hog.
So how do i do this you might ask yourself?
Very simple: Use EXISTS!
IF EXISTS(SELECT * FROM Table1 WHERE ...)
Exists stops the execution as soon as...
I've been asked this question so many times now that it'd be easier if i just gave people a link to read. :)
So in short (for those who are too lazy to dwelve deeper into sql internals):
Each row has a null bitmap for columns that allow nulls. If the row in that column is null then a bit in the bitmap is 1 else it's 0.
For variable size datatypes the acctual size is 0 bytes.
For fixed size datatype the acctual size is the default datatype size in bytes set to default value (0 for numbers, '' for chars).
Let's have two simple table t1 and t2....
An interesting topic came up a few days ago in the forums about database compatibility.
It turns out you can still run CLR functions, sprocs etc when changing the compatibility level from 90 to 80.
Of course i had to test this behaviour because i had a hard time believing it.
i used my high precision timer for performace measurements which is written in C# as a test bed.
You can find the whole script in that post.
When i created the database and all CLR functions I changed the compatiblity level to 80 with:
EXEC sp_dbcmptlevel testCLR, 80;
Then I tried using the CLR functions and they worked just...
I've written two more articles on SQLTeam about Service Broker.
The first one shows how to deploy a One to Many scenario over multiple servers. I've shown how to implement auditing from many child servers to one central storage server that holds all data. This is also applicable in a reverse situation of One to Many
The second one explains how and where to start looking when you have to troubleshoot and debug it.
Go and have a read.
Service broker is fun stuff!
Recently i'm seeing a lot of questions about how to modify data in the tables which are part of the multi-join select statement.
And i keep wondering how can this be so hard to remember. The methodology is quite simple.
More here.
This is acctually quite simple. There is 'startup' option that you can set to the procedure.
There are a few limitations though:
- your stored pricedure must reside in the [master] database
- it's owner must be dbo
- it musn't have any input or output parameters
Here is some sample code:
USE master;
GO
-- first set the server to show advanced options
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
-- then set the scan for startup procs to 1
EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE
IF OBJECT_ID('spTest') IS NOT NULL
DROP PROC spTest
GO
-- crate a test stored procedure
CREATE PROC spTest
AS
-- just create a sample database
EXEC('CREATE database db1')
GO
-- set it...
Some time ago i've written about the TableDiff.exe utility. It's a great tool for quick table comparisons and i've grown tired of typing it in the command line.
That's why I created this simple TableDiff GUI to help me along and i've decided to share it with the world. :)
You can download it here.
Hope you find it usefull.
In a previous post I've shown what locks are held when doing "update if exists, insert if not" problem in 2 different ways. Of course i had to see how MERGE statement (new in SQL Server 2008) behaves in this matter.
More here.
DBCC FREEPROCCACHE; and DBCC DROPCLEANBUFFERS; are commands that clean the cached execution plans from memory.
The consequence of running those statements is that all your objects (sprocs, functions, views) must be recompiled which can be quite costly.
There is no way around that. And if you want to recompile 100 sprocs you have to execute each one and each one returns data.
I didn't want this so i created this script that uses a not well know SET option called
SET FMTONLY ON
which returns only schema and no data to the client. Since returning data can take a long time this thing is pretty...
This question pops up a lot everywhere and it's a common business requirement and until SQL Server 2008 doesn't come out with it's MERGE statement that will do that in one go we're stuck with 2 ways of achieving this. The biggest problem with every update/insert (upsert for those who haven't heard the term yet) is locking.
More here.
Let me first say that I like Service Broker. I think it's a great addition to SQL Server.
I've explained a bit about it here.
But if you're not carefull you can get weird things happening to you without apparent reason.
So what happened?
Well first i've changed my receiver stored procedure a bit. This resulted in an error when i tried sending new messages.
And from then on all hell broke loose. My error table grew by the second with new rows all containing the same error message:
The service queue "TargetQueue" is currently disabled
CPU was peaking at 100% , I/O was going...
Every now and then i see a question pop up that asks what is the max this or that in sql server?
More here.
My article about Service Broker fundamentals and simple practical use has been posted on SQLTeam.com. Go check it out.
Service Broker is a new feature in SQL Server 2005. It is an integrated part of the database engine and it provides queuing and reliable direct asynchronous messaging between SQL Server 2005 instances only. In the future this is planned to be extended to non-SQL Server instances. This article shows how to use Service Broker and triggers to capture data changes.
More here.
No it's not some ultra misterious indexing feature that will make your database fly like it's on steroids.
Hypothetical indexes and database objects in general are simply objects created by DTA (Database Tuning Advisor)
Their names look like this:
Indexes
_dta_index_*
Statistics
...
These 2 lists are worth having nearby just in case:
Breaking Changes to Database Engine Features in SQL Server 2005
- Describes the changes made to the Database Engine in Microsoft SQL Server 2005
that could cause applications based on earlier versions of SQL Server to break.
Behavior Changes to Database Engine Features in SQL Server 2005
- Describes the changes in behavior of some Database Engine features in Microsoft SQL Server 2005
from their behavior in earlier versions of SQL Server.
Tests are a pretty much a must in today's world of programming. There's even a whole Test Driven Development methodology that deals with this.
There comes a time when we have to test how our app interacts with the database.
More here
Database mail is a completly rewritten mailing system in SQL Server 2005 built on top of the service broker.
This means that it runs asynchrounously. The mails are put in a queue and are sent from there.
However it's not present in SQL Server Express. I wonder why not because Express supports Service Broker just fine.
Well i've found a great blog post that explains how to "enable" Database Mail in SQL Server Express.
But it's in German so i'll explain in English how to get it to work :)
SQL Express holds all necessary stored procedures, service broker queues, etc... for proper mail handling.
However when...
When selecting data from a table we can select rows which contain more than 8094 bytes.
The problem arises when trying to sort those rows.
in SQL Server 2000 this code:
CREATE TABLE t1 (id INT IDENTITY(1,1), c1 VARCHAR(8000))
CREATE TABLE t2 (id INT IDENTITY(1,1), c2 VARCHAR(8000))
INSERT INTO t1 (c1)
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000) UNION ALL
SELECT REPLICATE('a', 8000)
INSERT INTO t2 (c2)
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000) UNION ALL
SELECT REPLICATE('b', 8000)...
My follow up article on MARS has been published on on SQLTeam.com.
In this follow up I cover some misunderstandings in trasaction handling when using MARS
as well as how to troubleshoot MARS related problems.
More here.
In SQL Server 2005 importing XML files became very easy.
OPENROWSET now supports the BULK keyword which lets us import XML files with ease.
A little example:
CREATE TABLE XmlImportTest
(
xmlFileName VARCHAR(300),
xml_data xml
)
GO
DECLARE @xmlFileName VARCHAR(300)
SELECT @xmlFileName = 'c:\TestXml.xml'
-- dynamic sql is just so we can use @xmlFileName variable in OPENROWSET
EXEC('
INSERT INTO XmlImportTest(xmlFileName, xml_data)
SELECT ''' + @xmlFileName + ''', xmlData
FROM
(
SELECT *
FROM OPENROWSET (BULK ''' + @xmlFileName + ''' , SINGLE_BLOB) AS XMLDATA
) AS FileImport (XMLDATA)
')
GO
SELECT * FROM XmlImportTest
DROP TABLE XmlImportTest
SINGLE_BLOB is recommended...
This book authored by Kalen Delaney whose Inside SQL Server books are compulsory reading for every DBA
hits the nail on the head with this one too.
The book goes into hardcore details of how the storage engine itself works.
Let's review what is covered in each chapter:
Chapter 1: Installing and Upgrading to SQL Server 2005
An overview of things to consider when migrating/upgrading to SQL Server 2005
Chapter 2: SQL Server 2005 Architecture
Hardcore stuff begins. talks about components of the engine and it's memory management.
Chapter 3: SQL Server 2005 Configuration
Covers Configuration Manager and general System Configuration
Chapter 4: Databases and Database Files
Covers pretty much...
A coworker who works mainly in C# wanted to know if there's an IsNullOrEmpty function.
After a brief NO, i've given him this one.
So far it works great.
CREATE FUNCTION dbo.IsNullOrEmpty(@text NVARCHAR(4000))
RETURNS BIT
AS
BEGIN
IF ISNULL(@text, '') = ''
BEGIN
RETURN 1
END
RETURN 0
END
GO
DECLARE @text VARCHAR(100)
SELECT @text = 'gdrfash5'
SELECT dbo.IsNullOrEmpty(@text)
SELECT @text = ''
SELECT dbo.IsNullOrEmpty(@text)
SELECT @text = ' '
SELECT dbo.IsNullOrEmpty(@text)
SELECT @text = null
SELECT dbo.IsNullOrEmpty(@text)
... at installation time was... are you ready? OWC11 Yes... OWC11. Come on, are you kidding me??? this is so 2003 :))
A few days ago i showed how to split string with XML.
Now it's time for concatenation with XML.
DECLARE @t TABLE (col VARCHAR(10))
INSERT into @t
select 'aaaa' UNION ALL
select 'bbbb' UNION ALL
select 'cccc' UNION ALL
select null UNION ALL
select 'dddd'
SELECT * FROM @t
SELECT
(
SELECT col + ', ' as [text()]
FROM @t
ORDER BY col DESC
FOR XML PATH('')
) AS MyCsvList
Both of these (split and concat) methods aren't really anything special. They're just a new way of solving old problem.
But whenever i need them i...
This book is a sequel to T-SQL Querying. And it stands completly side by side it.
I can only recommend that you get it. The amount of new stuff to learn is amazing.
So lets see what each chapter covers:
Chapter 1: DataType Related problems, XML and CLR UDTs
Talks about DateTime problems, patterns matching , conversions, large objects, CLR and of course XML which is the biggest part of the chapter.
Chapter 2: Temporary tables and Table variables
A great explanation of how, why and what in the world of Temp.
Chapter 3: Cursors
Shows that although dreaded by many, they have their rightfull...
Here's a Split function using XML datatype.
It's preety neat and simple compared to all others that i've seen.
Forget While Loops and recursive CTE's.
Enter XML:
IF OBJECT_ID('dbo.Split') IS NOT NULL
DROP FUNCTION dbo.Split
GO
CREATE FUNCTION dbo.Split(@data NVARCHAR(MAX), @delimiter NVARCHAR(5))
RETURNS @t TABLE (data NVARCHAR(max))
AS
BEGIN
DECLARE @textXML XML;
SELECT @textXML = CAST('<d>' + REPLACE(@data, @delimiter, '</d><d>') + '</d>' AS XML);
INSERT INTO @t(data)
SELECT T.split.value('.', 'nvarchar(max)') AS data
FROM @textXML.nodes('/d') T(split)
...
There is a common misconception that IN behaves equaliy to EXISTS or JOIN in terms of returned results.
This is simply not true. To see why not, let's review what each statement does.
More here
Ever wanted to have have a table that contains unique values but needs to have multiple null values also?
Here's how to do it:
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE TABLE t1 (id INT, title VARCHAR(20))
GO
-- optional instead of trigger to disable the insert directly into the table
CREATE TRIGGER trg_t1_DisableInsert ON t1
INSTEAD OF INSERT
AS
BEGIN
-- use 18 to stop further processing
RAISERROR (40000, 18, 1, 'Use view dbo.v1 to insert data into table')
END
GO
CREATE VIEW v1
WITH SCHEMABINDING
AS
SELECT id, title
FROM dbo.t1
...
We've all heard about differences between temporary tables and table variables in SQL Server.
They include performance, storage in memory or disk, tempdb use, etc.
But the biggest and mostly overlooked difference is:
Table variables are Transaction neutral. They are variables and thus aren't bound to a transaction.
Temp tables behave same as normal tables and are bound by transactions.
A simple example shows this difference quite nicely:
BEGIN TRAN
declare @var table (id int, data varchar(20) )
create table #temp (id int, data varchar(20) )
insert into @var
select 1, 'data 1' union all
select 2, 'data 2' union all
select 3, 'data 3'
insert into #temp
select 1, 'data 1' union all
select 2,...
This is an AWSOME book!
Written by Itzik Ben-Gan and coauthored by Lubor Kolar and Dejan Sarka it's definitly worth it's money.
It starts with the chapter on logical query processing in which it explaines the basics of the full select statement and the order of processing it.
Just basic stuff.
Chapter 2 covers the phyisical query processing. Explaines what parsing, algebratization and executions plans are and how it's all tied together.
Chapter 3 covers the query tuning methodology and tools to use for it. In my opinion This chapter is one of the best in the book.
Chapter 4 introduces us to subqueries, CTE's and Rank...
Well in my knowledge brush up for 70-431 Exam I used this book backed up by BOL.
If you have previous SQL Server experience this book and BOL is all you really need to pass the Exam.
If you're a newbie who is just getting it's feet wet then first get some experience and comfortability with SQL server first.
The book is a very easy read and although it has some errors they're not huge so i didn't need errata (fixed errors) for it.
It nicly summarizes the topics that you need to know about and the chapters are just long enough.
Content fits very nicly with...
So as of today i'm a proud member of the MCP club :)
The exam wasn't that hard. I had 35 "Pick a correct answer" questions and 15 simulations.
The simulations threw me off a bit because i didn't expect them.
So you have to be comfortable with T-SQL syntax and Management Studio alike.
Knowing what all of the little buttons and options do helps :)
The tools i used for learning are SQL Server Books Online and
EXAM CRAM: MCTS 70-431: Implementing and Maintaining Microsoft® SQL Server 2005
I've read some more books but they were really not needed to pass this exam.
I'll post my reviews...
This has been a debate for as long as i've known.
How to pronounce SQL? ess-cue-el or sequel?
Each one has it's own supporters. But only one is correct.
And the winner is (drum-roll here):
S-Q-L (ess-cue-el)
And how can i be so sure about that?
It's ANSI declared.
Read more about SQL Pronounciation history here.
P.S.
Most people don't care. That's why it's trivia. :))
There's no such thing as a global variable in SQL Server.
So i had to do it differently.
More here
The most popular method of auditing data is with an
AFTER DELETE, INSERT, UPDATE trigger.
But what happens if you have a Rollback statement in your trigger if some condition is met?
More here
I've written an article about Multiple Active Result Sets (MARS) and it's published on SQLTeam.com
Multiple Active Result Sets is a new SQL Server 2005 feature that, putting it simply, allows the user to run more than one SQL batch on an open connection at the same time.
More here.
We had an interesting discussion here about Katmai (the next SQL Server version)
Of course it was discussed in the latest MVP summit, but noone can say anything because it's all under NDA.
So i'm wondering why.... WHY is it under NDA? Is there some very special reason for that?
Nigel responded to my question with this:
I think it's more to do with managing expectations.
A lot of things talked about are under development and may be dropped from the product if they look like they might impact the release date.
They want an opinion on the usability of proposed functionality from a closed community before a...
I just hate having nulls in my DateTime columns. Having them always mean you also have to handle them in some way in your app.
The most common way is something like this:
public DateTime SomeDate
{
get
{
if (dr["SomeDate"] == DBNull.Value)
return DateTime.MinValue;
else
return (DateTime)dr["SomeDate"];
}
set
{
...
MsSqlSystemResource is a database that complements the master db. It is like the name smartly imples a resource database. All system stored procedures, views and functions are stored here. This databse is by all means hidden from the user. You can't view it in Object Explorer nor with the use of sp_helpDb or selecting from a sys.databases view. So how do we know it's even there? Pretty simple. Go to the Data directory of your SQL installation where the databases are put by default and there you'll see MsSqlSystemResource.mdf and MsSqlSystemResource.ldf. The reason for this db is in my opinion...
While searching for something in BOL i've accidently stumbled onto this little cool command line utility
TableDiff.exe is a table comparison tool that comes with the sql server.
It's installed on the server in the:
"C:\Program Files\Microsoft SQL Server\90\COM\TableDiff.exe"
if your SQL Server is installed in the program files on c: drive.
Example use:
This compares 2 tables in the same database on the same server and creates a new table called DiffsTable that holds the differences:
"C:\Program Files\Microsoft SQL Server\90\COM\tablediff.exe" -sourceserver MyServer1
...
Since i've started playing with SQL Server 2005 i've come accross a few things that i think are very cool.
I write them here so i don't forget about them :))
One of them is a Dedicated Administrator Connection (DAC)
This is a special diagnostic connection which is used by DBA's to troubleshoot a server when the server is refusing other connections
for whatever reason.
DAC can be created with SqlCmd utility or in SSMS with admin:instance_name.
Also only one DAC is allowed per instance.
Remote network access must be enabled with SQL Server Surface Area Configuration
I think this is a very handy and usefull novelty.
You can find more...
When doing apps that deals with date there almost always comes a question on
how to store an incomplete date.
For example:
Person A is born on 1980-02-17.
Person B is born on 1980-02 <- The person doesn't know the exact day (This is acctually a real life scenario)
How to store this wisely in db? You can't really put a datetime DateOfBirth Column.
I usually take 3 int columns (year, month, day) with constraints but i'm wondering...
Does anyone know of a better method?
I've seen this question pop up here, and i think it's usefull to post it here, since not every one reads the forums (you should! :)) We'll in this thread at the end of the first page there's a procedure on how to downgrade a db from SQL Server 2k5 to SQL server 2000. Enjoy it.
Ever missed a Prod(columnName) function that works like a sum but it multiplies the column values?
If you have then you probably know that there's a workaround using a bit of high school math knowledge about base 10 logarithms.
It goes like this:
SELECT exp(sum(log(c1))) as MultiplicationResult FROM test
However this little helper doesn't yield correct results for a large enough set, because it goes from integer to decimal.
So there's a possibilty of an error.
This is also a perfect example of a user defined aggregate that can be implemented in CLR. And becuase there's no int to deciaml transition
it yields correct results.
This...
if you don't know what nested trigers are read here.
Triggers are nested when a trigger performs an action that initiates another trigger.
So if you have 2 table where each has an after update trigger like:
CREATE TABLE trTest1
(
id INT,
NAME VARCHAR(50)
)
GO
INSERT INTO trTest1
SELECT 1, 'NAME 1' UNION ALL
SELECT 2, 'NAME 2' UNION ALL
SELECT 3, 'NAME 3' UNION ALL
SELECT 4, 'NAME 4'
GO
CREATE TABLE trTest2
(
id INT,
NAME VARCHAR(50)
)
GO
INSERT INTO trTest2
SELECT 1, 'NAME 1' UNION ALL
SELECT 2, 'NAME 2' UNION ALL
SELECT 3, 'NAME 3' UNION ALL
SELECT 4, 'NAME 4'
GO
CREATE TRIGGER trigTest1 ON trTest1 FOR UPDATE AS
UPDATE t2
SET id = i.id...
I recently wanted to create a stored procedure that would be only in master database and get some information_schema stuff for the current database.
One requirement is that the sproc has s "sp_" prefix. I also thought that it's the only requirement. I was wrong not for the first nor the last time.
The sproc would always return me information schema data belonging to the master db.
The second thing you have to do is mark the sproc a system object.
In Sql Server 2000 we have to set the system marking to on with master.dbo.sp_MS_upd_sysobj_category
In Sql Server 2005 we have the sys.sp_MS_marksystemobject...
UPDATE: This is now a part of a larger SSMS Add-In called SSMS Tools Pack
I thought I'd post something special as the last post of this year and i was thinking about what it would be.
Fellow SqlTeamer Rockmoose said he'd like to have an add-in for SSMS that would genereate basic
CRUD (Create - Insert, Read - Select, Update, Delete) stored procedures for a table.
And he'd like to have it on the context (right-mouse-click) menu of the table in Object Explorer in SSMS.
It sounded like a very cool little project and with the help of this excellent post about SSMS add-ins
I've decided to...
I was playing with some historical data (family tree) and i wanted to store data in sql server. When looking into family trees you reach the minimum datetime value of 1753-01-01 very soon. But .Net can save dates from 1.1.0001 on. So i went looking into using SQL CLR user defined datatype (UDT).
UDT's are interesting because you have to serialize them.
There are 3 ways of doing that:
- Format.Native
- Format.UserDefined
- Format.Unknown
When Format.Native is used you can only use blittable datatypes. Bol says these are:
bool, byte, sbyte, short, ushort, int, uint, long, ulong, float, double, SqlByte, SqlInt16, SqlInt32, SqlInt64, SqlDateTime,...
I've come across an interesting thing today that i didn't even think could be an issue:
Case senstive object names (tables, columns, triggers, views, etc....).
If your database is created with a Case Sensitive collation then all object names will be Case Sensitive.
I always thought that collations are used for data and not schema but i guess i was wrong.
We had an issue with a view that had a lower case column name in it when it should be uppercase.
A bit of code to prove it:create database testCollation collate Latin1_General_CS_AS -- case sensitive collation
go
use testCollation
create table Test(Col1 int, col2 int)
insert into...
In a previous post I've shown how to use High precision timer.
That method had a drawback of working for whole server not distiguishing between connections.
So if you ran the time measurement in 2 different windows in SSMS the times would be incorrect.
You'd get the correct time in the window which was run last.
This of course isn't very usefull for proper performace diagnosys.
So with little experimenting i've managed to fix the timer to work properly for each connection.
The code is self explanatory. For each function run I return the spid to which it belongs and i store the HiPerfTimer objects in a...
Update: I've created a muliti connection version which can be found here.
Usually time measuring in sql server is done in 2 ways: with the help of GetDate() function or with SET STATISTICS TIME ON.
For doing hardcore performance analysis where any time reduction counts you need more precise measuring capabilities and the two mentioned are only accurate to a milisecond order.
In SQL Server 2000 there was no practical way to change this.
SQL server 2005 to the rescue. It has DMV's which hold time in microseconds which is better. but they can't be always used to measure the duration of the statement. You...
Well SP2 CTP for SQL Server 2005 is here. Amongst all things new and fixed bugs there's one that i wanted to see if it's any good.
That is the vardecimal storage format.
My first thought and try was:
Create table t1(col1 vardecimal)
followed by
Create table t1(col1 vardecimal(18,12))
which both failed nicely with the following message:
Column, parameter, or variable #1: Cannot find data type vardecimal.
Ok then let's try google and MSDN. Both return exactly zero (at the time of writing) explenations of how to do this.
in the above link of new stuff this is said about vardecimal:
Added new functionality in the SQL Server...
I've seen a lot of confusion about the scope and lifetime of temporaray tables.
While i think that ##global temp tables are clear, #local temp tables are a different story.
They seen to sometimes work and sometimes not, especially when used with dynamic sql.
So what is the problem?
Mostly basic misunderstanding.
So because i'm such a nice guy, i'll try to elaborate a bit about "them darn #temps". :)
BOL clearly states:
Local temporary tables are visible only in the current session... ... Temporary tables are automatically dropped when they go out of scope, unless explicitly dropped using DROP TABLE So how is scope/session defined?
Scopes/Sessions...
If there can be a culture insensitive format for dates (yyyymmdd) then why is there no such thing as a culture insensitive format for decimals?
I suggest a use of # or ~ instead of the decimal separator in update and insert scripts only.
And it would be allowed only in numeric/decimal/float datatypes.
so you could do:
create table t1 (MyDecimalCol decimal (16,3))
insert into t1 (MyDecimalCol)
select 123#456 union all
select 789#123
select MyDecimalCol
from t1
-- would give:
123.456 789.123
-- or
123,456 789,123
-- depending on your locale
Wouldn't that be great?
I already suggested to Microsoft a culture neutral decimal separator a while back.
(Does anyone know the URL of the...
I've seen this technique a few months ago on a local MS event called NT Conference
presented by Slovenian MVP Dejan Sarka.
However i haven't seen him blog about it yet (a shame) so after seeing this post
by co blogger David Moloney here on SQLTeam blogs in this post about EAV i decided to give this little
thing a go by myself.
I've also chosen the same DB model as david for comparison:
A Client can have many “end user” defined data elements.
For the purpose of this example we will choose 5 data elements
to focus on the modelling aspect
Age – Integer greater...
DMV's are one of the most usefull features in SQL Server 2005.
There are 2 that I like more than others because they can give you information about
executed SQL statements without the use of SQL Profiler tool.
Of course they aren't a substitute for SQL profiler but rather an another tool you can use
in situations when you can't run SQL Profiler.
Those 2 DMV's are:
- sys.dm_exec_query_stats
- sys.dm_exec_sql_text
They provide much info about executed statements but i used them in this procedure for searching through
executed sql statemnts.
Since dm_exec_query_stats is based on cached query plans the row in it lasts until the plan is...
I've written about bulk insert methods for text files in sql server 2005.
.Net 2.0 brings the SqlBulkCopy Class in System.Data.SqlClient namespace.
Speed wise it can't even compare to native sql server methods but it's usefull
if it's the only viable solution.
SqlBulkCopy.WriteToServer has 4 overloads:
SqlBulkCopy.WriteToServer (DataRow[])
Copies all rows from the supplied DataRow array to a destination table specified by the
DestinationTableName property of the SqlBulkCopy object.
SqlBulkCopy.WriteToServer (DataTable)
Copies all rows in the supplied DataTable to a destination table specified by the
DestinationTableName property of the SqlBulkCopy object.
SqlBulkCopy.WriteToServer (IDataReader)
Copies all rows in the supplied IDataReader to a destination...
I've come accross this white paper that explains how SQL Server 2005 can be used to support row- and cell-level security.
Preety interesting reading.
More here.
In this thread it's shown how to export query results to excel File using OpenRowset. The problem with this is that you have to create an excel file with columns. For me that is simply unacceptable because i had to export any query result to excel and having to make an excel file for each query is simply ridicolous. So I went looking into BCP. Now BCP can't export column names adn i saw some solutions that use a view but nothing really dynamic. So i had to do a little workaround.
It resulted in this stored procedure. Data is...
Well i've done some more testing of bulk import methods i described in my previous post. The difference between previsous tests and these tests are these:
1. Database recovery model was set to Bulk logged. (This improved performance by 2-3 seconds for each method)
2. I tried BULK INSERT and BCP with and without TABLOCK option.
3. I also measured general processor utilization and RAM. IO reads and writes were similar in all methods.
everything else was the same.
Method
...
In a previous post I explained where an SSIS FastParse option can be found
when importing data from Flat files.
I finally had some time to explore all four of SQL Server 2005 Bulk import utilities.
Those are:
1. BCP
2. Bulk Insert
3. OpenRowset with BULK option
4. SQL Server Integration Services - SSIS
I ran each bulk import option 12 times, disregarded best and worst time and averaged the remaining ten times.
Results are:
1.
...
If there was one thing that was going preety steadily on my nerves in VS2005 was region collapsing/opening with mouse.
So i was determined to find a shortcut for it.
And I found so much more...
Every shortcut there is in Visual Studio 2005 is listed here.
All SSMS keybard shortcuts are listed here.
These two pages are the holy grail of fast development if you ask me :))
oh and by the way.... collapsing/opening a region is CTRL + M + M. Yes, hit M twice while holding CTRL.
And do try CTRL + I... it's pure pleasure. a hint: Incremental Search :) <-- select text...
I've stumbled accross this post today that explains the Rozenshtein method of pivoting data.
It's quite interesting. I haven't seen the SIGN() function being used at all in SQL server in practice yet so this is kind of cool if you ask me :)
I ran into this little gem today while doing some deletes and forgeting Delete syntax :)))
There is now Output clause for DELETE, INSERT and UPDATE statements.
It outputs the affected rows into a table variable, a simple resultset or into a table.
I guess auditing is possible this way too now.
Example:
create table test(id int identity(1,1), name varchar(100))
insert into test (name)
select 'name 1' union all select 'name 2' union all select 'name 3' union all
select 'name 4' union all select 'name 5' union all select 'name 6' union all
select 'name 7' union all select 'name 8' union all...
Here's an interesting way of contencating values in one column. It makes use of the new XML capabilities.
use master
select column_name as col1 FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'spt_values'
select (select column_name as col1 FROM INFORMATION_SCHEMA.Columns
WHERE table_name = 'spt_values' for xml raw, elements, type).query('
for $col1 in (row/col1)
...
According to Donald Farmer who visited Slovenia a few months ago importing flat files
with SQL Server Integration Services is the fastest way today to do that.
SSIS now has a special very fast and very optimised library that transforms flat file text
into appropriate SQL Servers datatypes.
On very large files the speed gain is in the range of 7-20% of other ways.
More MSDN info here.
Now how do you get to that cute little option? here you go:
1. Run Visual Studio 2005
2. Under Business Inteligence Project create new Integration Services Project
3. Go to DataFlow tab
4. From Data flow Sources in the toolbox...
We're all familiar with four constant columns that should be in every table.
Those are: CreatedOn, CreatedBy, LastUpdatedOn, LastUpdatedBy or any other names you have for them. :))
But you get my point. Recently i'm leaning into a direction that there should be 2 more columns
added to those four. Those are CreatedOnTZOffset and LastUpdatedOnTZOffset where TZOffset stands Time Zone Offset.
Some may ask why do we need this? Well let's say you have an app that is accesed
from all over the world and you're not just displying data to users. They can
also add and update stuff. Time Zone information...
This is an interesting thing to know. It seems that automatic update of statistics doesn't work for small tables (up to 500 rows) on SQL server 2000.
More here.
in xp_sprintf up to 50 arguments can be specified. sometimes there's a need for more.
This is it and it could as easily be rewritten as a function.
create proc spSPrintF
@body varchar(8000) = '',
@params varchar(1000) = '',
@paramSeparator varchar(10) = ','
as
begin
if @params = ''
begin
select @body
return
end
-- so we don't have to specially handle the last item
...
Maybe i'm reinventing hot water but no matter. It's fun for me :)))
These 2 functions round the number up or down on the decimal position we specify.
create function dbo.FloorEx(@num decimal(38, 33), @decimalPosition int)returns decimal(38, 33)as beginif @decimalPosition < 0 set @decimalPosition = 0return floor(@num*power(10, @decimalPosition))/power(10, @decimalPosition)endgocreate function dbo.CeilingEx(@num decimal(38, 33), @decimalPosition int)returns decimal(38, 33)as beginif @decimalPosition < 0 set @decimalPosition = 0return ceiling(@num*power(10, @decimalPosition))/power(10, @decimalPosition)endgo declareselect @num decimal(38, 33), @decimalPosition int @num = 10.123456, @decimalPosition = 3select dbo dbogo @num, .FloorEx(@num...
I stumbled on an interesting thing yesterday while playing... :))
Seems that getting all data from a table is faster if we add a "phantom" where part
that forces a clustered index seek instead of a scan.
More here.
A simple function that checks if a passed string is a GUID.
create function IsGuid ( @testString varchar(38))
returns int
as
begin
declare @ret int
select @ret = 0,
@testString = replace(replace(@testString, '{', ''), '}', '')
if len(isnull(@testString, '')) = 36 and
@testString NOT LIKE '%[^0-9A-Fa-f-]%' and
-- check for proper positions of hyphens (-)
charindex('-', @testString) = 9 and
...
this is a select trigger on a table. found this in a blog of our first Slovenian Sql Server MVP (i think he's first :))).
http://solidqualitylearning.com/blogs/dejan/archive/2004/11/25/214.aspx
quite interesting...
This is a list of some stuff (mostly beginner) i've learned here on SQLTeam forums and i've written them down over time... i've posted it in forums here and Madhivanan gave the idea to blog it... so i did. :)) maybe this will help in the planned beginners forum :))
1. How to update a column with incrementing numbers:
-- whole table (identity stlye)
declare @table1 table (id int, name varchar(50))
insert into @table1
select null, 'text1' union all
select null, 'text2' union all
...
There were some quetions in the forums about transposing data without the use of aggregate functions. While building a CSV list is nice sometime that's not what we want. So this is a sproc that does just that. It's nothing complex, it just gets the max number data in groups and builds an SQL statement with that many left joins.
create proc spTranspose
@TableName varchar(100),
@PrimaryCol varchar(100), -- parent column to transposing columns
@SecondaryCol varchar(1000) -- CSV string of columns to transpose
as
select @SecondaryCol = replace(@SecondaryCol,...
in a forum i said i once did a test on isnull and coalesce speed test but i haven't documented it. so i did it again. results in articles under test ... :)
or here