I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server

SSMS Tools Pack 3.0 is out. Full SSMS 2014 support and improved features.

With version 3.0 the SSMS 2014 is fully supported. Since this is a new major version you'll eventually need a new license. Please check the EULA to see when. As a thank you for your patience with this release, everyone that bought the SSMS Tools Pack after April 1st, the release date of SQL Server 2014, will receive a free upgrade. You won't have to do anything for this to take effect. First thing you'll notice is that the UI has been completely changed....

posted @ Tuesday, May 20, 2014 12:04 AM | Feedback (6) | Filed Under [ .Net SQL Server SSMS Tools Pack ]

Simple Merging Of PDF Documents with iTextSharp 5.4.5.0

As we were working on our first SQL Saturday in Slovenia, we came to a point when we had to print out the so-called SpeedPASS's for attendees. This SpeedPASS file is a PDF and contains thier raffle, lunch and admission tickets. The problem is we have to download one PDF per attendee and print that out. And printing more than 10 docs at once is a pain. So I decided to make a little console app that would merge multiple PDF files into a single file that would be much easier to print. I used an open source...

posted @ Friday, January 10, 2014 7:38 PM | Feedback (1) | Filed Under [ .Net SQL Server ]

SQL Server: Writing CASE expressions properly when NULLs are involved

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: ...

posted @ Monday, March 18, 2013 9:55 PM | Feedback (7) | Filed Under [ SQL Server Back to Basics ]

Few events I’m speaking at in early 2013

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...

posted @ Wednesday, February 13, 2013 1:34 AM | Feedback (0) | Filed Under [ .Net SQL Server ]

SSMS Tools Pack 2.1.0 is out. Added support for SQL Server 2012 RC0.

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...

posted @ Thursday, December 01, 2011 8:34 PM | Feedback (0) | Filed Under [ SQL Server SSMS Tools Pack ]

SQL Server MVP Deep Dives 2. The Awesome Returns.

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...

posted @ Friday, October 07, 2011 7:26 PM | Feedback (0) | Filed Under [ SQL Server Reviews Back to Basics ]

SSMS Tools Pack 2.0 is out! With huge productivity booster features that will blow your mind and ease your job even more.

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...

posted @ Wednesday, September 21, 2011 6:24 PM | Feedback (5) | Filed Under [ .Net SQL Server SSMS Tools Pack ]

Yep, I’m a SQL Server MVP for one more year

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. :)

posted @ Friday, July 01, 2011 6:06 PM | Feedback (3) | Filed Under [ SQL Server ]

Two free SQL Server events I'll be presenting at in UK. Come and say hi!

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...

posted @ Monday, March 28, 2011 7:40 AM | Feedback (1) | Filed Under [ .Net SQL Server ]

SQL Server SQL Injection from start to end

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...

posted @ Wednesday, February 16, 2011 7:00 AM | Feedback (2) | Filed Under [ .Net SQL Server Back to Basics ]

SSMS Tools Pack 1.9.3 is out!

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...

posted @ Tuesday, November 09, 2010 8:00 AM | Feedback (1) | Filed Under [ SQL Server SSMS Tools Pack ]

SQL Server Transaction Marks: Restoring multiple databases to a common relative point

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....

posted @ Wednesday, October 20, 2010 8:00 AM | Feedback (1) | Filed Under [ SQL Server ]

Plagiarism and second chances

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...

posted @ Tuesday, September 14, 2010 9:00 AM | Feedback (7) | Filed Under [ SQL Server Back to Basics ]

SSMS Tools Pack 1.9 is out!

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...

posted @ Tuesday, August 24, 2010 9:00 AM | Feedback (1) | Filed Under [ SQL Server SSMS Tools Pack ]

Yep, I’m a SQL Server MVP.

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! :))

posted @ Thursday, July 01, 2010 4:59 PM | Feedback (21) | Filed Under [ SQL Server ]

SQL University: Database testing and refactoring tools and examples

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...

posted @ Friday, June 04, 2010 2:53 PM | Feedback (3) | Filed Under [ SQL Server Back to Basics ]

SQL University: What and why of database refactoring

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...

posted @ Wednesday, June 02, 2010 9:41 PM | Feedback (0) | Filed Under [ SQL Server Back to Basics ]

SQL University: What and why of database testing

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...

posted @ Monday, May 31, 2010 7:12 PM | Feedback (0) | Filed Under [ SQL Server Back to Basics ]

SSMS Tools Pack 1.8 is out!

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!

posted @ Monday, May 03, 2010 1:29 PM | Feedback (8) | Filed Under [ SQL Server SSMS Tools Pack ]

SQL Server – Find the most expensive operations in Execution plans

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...

posted @ Thursday, January 21, 2010 12:14 PM | Feedback (11) | Filed Under [ SQL Server ]

SQL Server - How many times is the subquery executed?

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...

posted @ Tuesday, January 12, 2010 2:05 PM | Feedback (1) | Filed Under [ SQL Server ]

SQL Server Date and Time fun from all around

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...

posted @ Tuesday, December 08, 2009 6:41 PM | Feedback (6) | Filed Under [ SQL Server Back to Basics ]

SQL Server: Network packet size difference between returning XML and TDS

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...

posted @ Tuesday, December 01, 2009 1:18 PM | Feedback (5) | Filed Under [ SQL Server ]

SSMS Tools Pack 1.7.5.1 is out! Bug fixes for my international users.

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 :)

posted @ Monday, November 02, 2009 6:24 PM | Feedback (5) | Filed Under [ SQL Server SSMS Tools Pack ]

SSMS Tools Pack 1.7 is out! New feature: SQL Snippets

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...

posted @ Monday, October 26, 2009 1:49 PM | Feedback (17) | Filed Under [ SQL Server SSMS Tools Pack ]

SQL Server - How to get the whole group of duplicate rows

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 ...

posted @ Thursday, October 15, 2009 1:53 PM | Feedback (4) | Filed Under [ SQL Server ]

Why I prefer surrogate keys instead of natural keys in database design

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...

posted @ Tuesday, October 06, 2009 1:17 PM | Feedback (49) | Filed Under [ SQL Server Back to Basics ]

The 24 hours of PASS

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!

posted @ Wednesday, August 05, 2009 1:52 PM | Feedback (0) | Filed Under [ SQL Server ]

SQL Server 2005 – Fast Running Totals solution with ordered CTE update?

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...

posted @ Tuesday, July 28, 2009 11:48 AM | Feedback (16) | Filed Under [ SQL Server ]

How to check when was SQL Server installed with a T-SQL query

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 ...

posted @ Thursday, July 16, 2009 6:55 PM | Feedback (4) | Filed Under [ SQL Server ]

SQL Server 2008 for Developers live meeting presentation

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.

posted @ Monday, June 15, 2009 11:22 PM | Feedback (0) | Filed Under [ SQL Server ]

SQL Server 2008 Extended Events - high performance eventing system

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,...

posted @ Friday, May 29, 2009 4:15 PM | Feedback (0) | Filed Under [ SQL Server ]

Free SQL Server 2008 Powerpoint dark template

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 ...

posted @ Thursday, May 07, 2009 1:24 PM | Feedback (1) | Filed Under [ SQL Server ]

Comparing SQL Server HASHBYTES function and .Net hashing

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...

posted @ Tuesday, April 28, 2009 11:43 AM | Feedback (4) | Filed Under [ .Net SQL Server ]

SQL Server - Find missing and unused indexes

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...

posted @ Wednesday, April 08, 2009 11:46 PM | Feedback (5) | Filed Under [ SQL Server ]

SSMS Tools Pack 1.5 is out with Window Connection Coloring

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...

posted @ Monday, February 09, 2009 1:10 PM | Feedback (25) | Filed Under [ SQL Server SSMS Tools Pack ]

The simplest way to delete duplicates and compare two result sets in SQL Server

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...

posted @ Monday, January 05, 2009 2:50 PM | Feedback (7) | Filed Under [ SQL Server ]

SQL Server Management Studio 2008 suggests missing indexes with actual execution plan

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...

posted @ Monday, December 29, 2008 8:10 PM | Feedback (7) | Filed Under [ SQL Server ]

Advanced SQL Server 2005 Express Job Scheduling

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...

posted @ Wednesday, December 03, 2008 11:29 AM | Feedback (5) | Filed Under [ SQL Server ]

Custom user configurable SQL Server Profiler events

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 ...

posted @ Thursday, October 16, 2008 10:59 AM | Feedback (2) | Filed Under [ SQL Server ]

SSMS Tools PACK 1.1 - now with SQL Server Management Studio 2008 support

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...

posted @ Monday, October 06, 2008 10:59 AM | Feedback (8) | Filed Under [ .Net SQL Server SSMS Tools Pack ]

Normalization for databases is like Dependency Injection for code

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...

posted @ Wednesday, September 17, 2008 10:38 AM | Feedback (6) | Filed Under [ .Net SQL Server Back to Basics ]

SQL Server 2005 temporary tables bug, feature or expected behavior?

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...

posted @ Thursday, August 21, 2008 4:56 PM | Feedback (22) | Filed Under [ SQL Server ]

Immediate deadlock notifications without changing existing code

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...

posted @ Friday, July 18, 2008 11:00 AM | Feedback (12) | Filed Under [ SQL Server ]

SQL Server 2005: Immediate Deadlock notifications

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...

posted @ Wednesday, May 21, 2008 11:47 AM | Feedback (8) | Filed Under [ SQL Server ]

Ease your SSMS experience: SSMS Tools PACK 1.0 is out!

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...

posted @ Thursday, May 08, 2008 10:35 AM | Feedback (16) | Filed Under [ SQL Server SSMS Tools Pack ]

SSMS Tools PACK is featured in May issue of SQL Server Magazine

  Just as the title says :) You can read the article here.   Thanx to Kevin Kline for publishing it!

posted @ Sunday, May 04, 2008 1:57 PM | Feedback (5) | Filed Under [ SQL Server Misc SSMS Tools Pack ]

SQL Server 2005: Get full information about transaction locks

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, ...

posted @ Tuesday, April 29, 2008 8:01 PM | Feedback (16) | Filed Under [ SQL Server ]

How SQL Server short-circuits WHERE condition evaluation

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...

posted @ Monday, February 25, 2008 10:54 AM | Feedback (10) | Filed Under [ SQL Server ]

Integrating Profiler and PerfMon Log Files

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

posted @ Friday, February 08, 2008 2:49 PM | Feedback (0) | Filed Under [ SQL Server ]

Back to Basics: Count, Count, Count, Sum or how to Count

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)...

posted @ Monday, February 04, 2008 5:24 PM | Feedback (4) | Filed Under [ SQL Server Back to Basics ]

Introduction to locking in SQL Server 2005

  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

posted @ Wednesday, December 12, 2007 8:36 PM | Feedback (3) | Filed Under [ SQL Server Back to Basics ]

SSMS Tools Pack 0.9.5 add-in for SQL Server Management Studio is out

    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...

posted @ Friday, December 07, 2007 2:22 PM | Feedback (5) | Filed Under [ SQL Server SSMS Tools Pack ]

SQL Server 2005: Synonyms in T-SQL (analogous to Aliases in .Net)

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...

posted @ Monday, November 26, 2007 9:37 PM | Feedback (8) | Filed Under [ SQL Server ]

Free SQL Server tools that might make your life a little easier

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...

posted @ Tuesday, November 20, 2007 6:31 PM | Feedback (89) | Filed Under [ SQL Server ]

SQL Server 2005 evil DBA prank

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 ...

posted @ Thursday, November 15, 2007 10:05 PM | Feedback (5) | Filed Under [ .Net SQL Server ]

Map SQL Server Profiler EventClass ID to its name in a saved trace table

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...

posted @ Friday, November 09, 2007 9:00 PM | Feedback (4) | Filed Under [ SQL Server ]

A chance to win one of fifty ApexSQL Developer Studios

  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.

posted @ Thursday, November 01, 2007 3:33 PM | Feedback (1) | Filed Under [ SQL Server Misc ]

SQL Server: The one and only locale insensitive date format

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...

posted @ Monday, October 22, 2007 10:21 AM | Feedback (12) | Filed Under [ SQL Server ]

SQL Server: Filtering Numeric data from a character based column

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 ...

posted @ Thursday, October 18, 2007 6:00 PM | Feedback (9) | Filed Under [ SQL Server ]

SQL Server: Why is TRUNCATE TABLE a DDL and not a DML operation and difference from DELETE

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...

posted @ Wednesday, October 03, 2007 10:20 PM | Feedback (10) | Filed Under [ SQL Server ]

SQL Server: Notify client of progress in a long running process

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 ...

posted @ Monday, October 01, 2007 6:22 PM | Feedback (12) | Filed Under [ SQL Server ]

SSMS Tools Pack - an add-in for SQL Management Studio 2005 is out (beta stage)

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:...

posted @ Thursday, September 20, 2007 3:16 PM | Feedback (37) | Filed Under [ SQL Server SSMS Tools Pack ]

Back To Basics: What is a Clustered and a Non-Clustered index (plus an appealing visual prop)

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...

posted @ Tuesday, September 18, 2007 8:15 PM | Feedback (24) | Filed Under [ SQL Server Back to Basics ]

SQL Server: The proper and fastest way to check if rows matching a condition exist

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...

posted @ Thursday, September 13, 2007 5:58 PM | Feedback (13) | Filed Under [ SQL Server ]

How does SQL Server really store NULL-s

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....

posted @ Thursday, September 06, 2007 10:50 PM | Feedback (7) | Filed Under [ SQL Server ]

SQL Server 2005: An error or intended behavior with Database Compatibility?

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...

posted @ Thursday, August 30, 2007 2:01 PM | Feedback (0) | Filed Under [ SQL Server ]

Service Broker goodies: Cross Server Many to One (One to Many) scenario and How to troubleshoot it

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!  

posted @ Tuesday, August 21, 2007 3:37 PM | Feedback (0) | Filed Under [ SQL Server ]

SQL Server: How to modify data in any table in a resultset made up of joins

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.

posted @ Sunday, August 19, 2007 1:26 AM | Feedback (0) | Filed Under [ SQL Server ]

SQL Server: How to run a stored procedure at SQL Server start-up

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...

posted @ Tuesday, August 14, 2007 4:34 PM | Feedback (6) | Filed Under [ SQL Server ]

SQL Server 2005: TableDiff.exe GUI

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.  

posted @ Friday, August 10, 2007 11:58 AM | Feedback (70) | Filed Under [ SQL Server ]

SQL Server 2008: Locking with MERGE statement compared to Update/Insert in SQL Server 2005

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.

posted @ Friday, August 03, 2007 9:30 PM | Feedback (1) | Filed Under [ SQL Server ]

SQL Server: Automated database object recompiling after cleaning plan cache with FMTONLY

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...

posted @ Thursday, August 02, 2007 5:21 PM | Feedback (2) | Filed Under [ SQL Server ]

SQL Server: Best way to Update row if exists, Insert if not

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.

posted @ Monday, July 30, 2007 8:17 PM | Feedback (34) | Filed Under [ SQL Server ]

SQL Server 2005: How to successfully shoot yourself in the foot with Service Broker

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...

posted @ Wednesday, July 25, 2007 8:46 PM | Feedback (4) | Filed Under [ SQL Server ]

A few maximum limitations for SQL Server 2005

Every now and then i see a question pop up that asks what is the max this or that in sql server?

More here.

posted @ Tuesday, July 24, 2007 5:13 PM | Feedback (10) | Filed Under [ SQL Server ]

Centralized Asynchronous Auditing with Service Broker

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.

posted @ Monday, July 16, 2007 5:36 PM | Feedback (1) | Filed Under [ SQL Server ]

SQL Server 2005: Hypothetical index - a what?!?!

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 ...

posted @ Monday, July 16, 2007 1:46 PM | Feedback (0) | Filed Under [ SQL Server ]

Behaviour and Breaking changes from SQL Server 2000 to 2005

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.

posted @ Tuesday, July 10, 2007 7:21 PM | Feedback (0) | Filed Under [ SQL Server ]

Ways to revert a database to pre-Test state after Unit testing

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

posted @ Friday, July 06, 2007 7:00 PM | Feedback (15) | Filed Under [ .Net SQL Server ]

Enabling Database Mail on SQL Server Express

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...

posted @ Sunday, July 01, 2007 9:47 PM | Feedback (22) | Filed Under [ SQL Server ]

Sorting data with row size greater than 8094 works in SQL Server 2005

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)...

posted @ Tuesday, June 26, 2007 4:12 PM | Feedback (1) | Filed Under [ SQL Server ]

Multiple Active Result Sets (MARS) – Transactions and Debugging

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.

posted @ Monday, June 18, 2007 3:20 PM | Feedback (0) | Filed Under [ SQL Server ]

SQL Server 2005: Easily importing XML Files

  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...

posted @ Monday, June 18, 2007 8:47 AM | Feedback (8) | Filed Under [ SQL Server ]

Book Review: Inside SQL Server 2005: THE STORAGE ENGINE

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...

posted @ Saturday, June 16, 2007 1:30 AM | Feedback (0) | Filed Under [ SQL Server Reviews ]

Sql Server: IsNullOrEmpty function

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)  

posted @ Wednesday, June 13, 2007 3:21 PM | Feedback (10) | Filed Under [ SQL Server ]

SQL Server 2008: A component that surprised me...

  ... at installation time was... are you ready?       OWC11   Yes... OWC11.  Come on, are you kidding me??? this is so 2003 :))

posted @ Monday, June 04, 2007 10:34 PM | Feedback (1) | Filed Under [ SQL Server ]

SQL Server 2005: Concat values XML Style

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...

posted @ Friday, June 01, 2007 9:49 AM | Feedback (6) | Filed Under [ SQL Server ]

Book Review: Inside SQL Server 2005: T-SQL PROGRAMMING

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...

posted @ Friday, May 25, 2007 10:03 PM | Feedback (5) | Filed Under [ SQL Server Reviews ]

SQL Server 2005: Split string XML Style

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) ...

posted @ Tuesday, May 22, 2007 7:23 PM | Feedback (5) | Filed Under [ SQL Server ]

SQL Server: JOIN vs IN vs EXISTS - the logical difference

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

posted @ Friday, May 18, 2007 6:52 PM | Feedback (48) | Filed Under [ SQL Server ]

SQL Server 2005: How to have a Unique Constraint with multiple NULLS on a column

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 ...

posted @ Thursday, May 17, 2007 12:27 PM | Feedback (19) | Filed Under [ SQL Server ]

Biggest difference between Temp tables and Table variables in Sql Server

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,...

posted @ Sunday, May 13, 2007 5:11 PM | Feedback (23) | Filed Under [ SQL Server ]

Book Review: Inside SQL Server 2005: T-SQL QUERYING

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...

posted @ Friday, May 11, 2007 11:27 AM | Feedback (8) | Filed Under [ SQL Server Reviews ]

Book Review: MCTS 70-431 Exam Cram: Implementing and Maintaining Microsoft SQL Server 2005

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...

posted @ Thursday, May 10, 2007 10:09 PM | Feedback (6) | Filed Under [ SQL Server Reviews ]

I Passed the SQL Server 2005 MCP 70-431: Implementation and Maintainance Exam

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...

posted @ Thursday, May 10, 2007 12:21 PM | Feedback (16) | Filed Under [ SQL Server Misc ]

S-Q-L or Sequel? A bit of trivia for the weekend...

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. :))

posted @ Saturday, April 28, 2007 3:15 PM | Feedback (11) | Filed Under [ SQL Server ]

"Global variables" in SQL Server

There's no such thing as a global variable in SQL Server. So i had to do it differently.

More here

posted @ Monday, April 23, 2007 10:20 PM | Feedback (27) | Filed Under [ SQL Server ]

SQL Server: How to audit rollback-ed data in a trigger?

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

posted @ Friday, April 13, 2007 5:58 PM | Feedback (0) | Filed Under [ SQL Server ]

Multiple Active Result Sets

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.

posted @ Tuesday, April 03, 2007 3:10 PM | Feedback (7) | Filed Under [ .Net SQL Server ]

Why is MVP Summit under NDA?

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...

posted @ Wednesday, March 28, 2007 3:46 PM | Feedback (3) | Filed Under [ SQL Server Misc ]

I don't like having Nulls in DateTime columns

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 { ...

posted @ Wednesday, March 14, 2007 7:21 PM | Feedback (27) | Filed Under [ .Net SQL Server ]

MsSqlSystemResource - SQL Server 2005's 5th system database

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...

posted @ Monday, March 12, 2007 4:34 PM | Feedback (1) | Filed Under [ SQL Server ]

TableDiff.exe - a cool SQL Server 2005 tool

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 ...

posted @ Saturday, March 03, 2007 4:47 PM | Feedback (30) | Filed Under [ SQL Server ]

SQL Server 2005 Dedicated Administrator Connection

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...

posted @ Monday, February 19, 2007 9:18 PM | Feedback (4) | Filed Under [ SQL Server ]

How to store an incomplete date?

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?  

posted @ Saturday, February 17, 2007 2:05 AM | Feedback (9) | Filed Under [ SQL Server ]

How to Downgrade a Database from SQL Server 2005 to SQL Server 2000

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.

posted @ Thursday, February 15, 2007 10:45 AM | Feedback (12) | Filed Under [ SQL Server ]

T-SQL vs CLR: SQL Server 2005 group multiplication

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...

posted @ Monday, February 12, 2007 12:22 PM | Feedback (9) | Filed Under [ .Net SQL Server ]

Help in getting nested triggers from your database

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...

posted @ Friday, February 09, 2007 8:32 PM | Feedback (3) | Filed Under [ SQL Server ]

Running a master db residing stored procedure in current database context

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...

posted @ Thursday, January 18, 2007 5:25 PM | Feedback (29) | Filed Under [ SQL Server ]

CRUD stored procedures generating SSMS Add-in

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...

posted @ Thursday, December 28, 2006 11:10 PM | Feedback (40) | Filed Under [ SQL Server SSMS Tools Pack ]

Extending DateTime to include dates less than 1753-01-01 in SQL Server 2005 with CLR UDT

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,...

posted @ Saturday, December 16, 2006 10:11 PM | Feedback (8) | Filed Under [ .Net SQL Server ]

When are SQL Server schema objects Case Senstive?

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...

posted @ Thursday, December 07, 2006 2:05 PM | Feedback (7) | Filed Under [ SQL Server ]

Multi Connection High precision time measuring in SQL Server 2005

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...

posted @ Saturday, December 02, 2006 9:46 PM | Feedback (0) | Filed Under [ .Net SQL Server ]

High precision time measuring in SQL Server 2005 with the help from CLR and unsafe code

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...

posted @ Wednesday, November 29, 2006 12:24 PM | Feedback (3) | Filed Under [ .Net SQL Server ]

SQL Server 2005 vardecimal storage format

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...

posted @ Friday, November 10, 2006 11:49 AM | Feedback (4) | Filed Under [ SQL Server ]

A bit about sql server's local temp tables

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...

posted @ Friday, November 03, 2006 10:15 PM | Feedback (16) | Filed Under [ SQL Server ]

I want a culture insensitive decimal separator for Sql server

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...

posted @ Tuesday, October 24, 2006 11:20 AM | Feedback (7) | Filed Under [ SQL Server ]

Entity-Attribute-Value (EAV) model with SQL Server 2005 xml datatype

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...

posted @ Saturday, October 14, 2006 5:03 PM | Feedback (7) | Filed Under [ SQL Server ]

SQL Server 2005 profiling with Dynamic Management Views (DMV's)

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...

posted @ Thursday, September 21, 2006 10:00 AM | Feedback (7) | Filed Under [ SQL Server ]

Bulk import text files using .net 2.0 SqlBulkCopy class in C#

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...

posted @ Saturday, August 26, 2006 9:03 PM | Feedback (48) | Filed Under [ .Net SQL Server ]

Implementing Row- and Cell-Level Security in Classified Databases Using SQL Server 2005

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.

posted @ Saturday, August 12, 2006 5:16 PM | Feedback (1) | Filed Under [ SQL Server ]

Export Query results to Excel with Column names in T-SQL

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...

posted @ Tuesday, July 25, 2006 3:11 PM | Feedback (84) | Filed Under [ SQL Server ]

More on Flat File Bulk Import methods speed comparison in SQL Server 2005

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 ...

posted @ Saturday, July 22, 2006 3:05 PM | Feedback (12) | Filed Under [ SQL Server ]

Flat File Bulk Import methods speed comparison in SQL Server 2005

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. ...

posted @ Monday, July 17, 2006 5:32 PM | Feedback (11) | Filed Under [ SQL Server ]

VS 2005 and SQL Server Management Studio Keyboard Shortcuts - pure magic

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...

posted @ Friday, June 16, 2006 4:49 PM | Feedback (6) | Filed Under [ .Net SQL Server ]

The Rozenshtein Method - yet another way to pivot data in SQL Server

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 :)  

posted @ Wednesday, June 14, 2006 11:20 AM | Feedback (8) | Filed Under [ SQL Server ]

SQL Server 2005 Output Clause

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...

posted @ Thursday, June 08, 2006 3:58 PM | Feedback (4) | Filed Under [ SQL Server ]

Column values Concat the SQL Server 2005 way

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) ...

posted @ Friday, June 02, 2006 11:47 PM | Feedback (1) | Filed Under [ SQL Server ]

SSIS FastParse option - the fastest way to import large Flat Files into Sql Server 2005

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...

posted @ Friday, May 26, 2006 12:19 PM | Feedback (19) | Filed Under [ SQL Server ]

Do you know when your data was modified? Or do you?

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...

posted @ Sunday, May 21, 2006 5:37 PM | Feedback (10) | Filed Under [ SQL Server ]

Manual Update statistics on small tables may provide a big impact

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. 

posted @ Friday, May 12, 2006 10:24 PM | Feedback (2) | Filed Under [ SQL Server ]

spSPrintF - an alternative to xp_sprintf

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 ...

posted @ Friday, May 05, 2006 2:34 PM | Feedback (4) | Filed Under [ SQL Server ]

Extended SQL Server Ceiling and Floor functions

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...

posted @ Wednesday, April 05, 2006 9:59 AM | Feedback (2) | Filed Under [ SQL Server ]

Clustered Index seek and scan on selecting all data from a table

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.

posted @ Tuesday, April 04, 2006 2:45 PM | Feedback (2) | Filed Under [ SQL Server ]

SQL Server IsGuid() function

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 ...

posted @ Tuesday, August 16, 2005 5:02 PM | Feedback (6) | Filed Under [ SQL Server ]

a select trigger...

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...

posted @ Sunday, August 07, 2005 3:19 PM | Feedback (4) | Filed Under [ SQL Server ]

Beginner questions frequently asked... and answered...

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 ...

posted @ Monday, August 01, 2005 11:34 AM | Feedback (19) | Filed Under [ SQL Server ]

Transpose data

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,...

posted @ Tuesday, March 15, 2005 10:36 PM | Feedback (2) | Filed Under [ SQL Server ]

IsNull vs Coalesce Speed test

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

posted @ Wednesday, November 24, 2004 9:39 PM | Feedback (7) | Filed Under [ SQL Server ]

Powered by:
Powered By Subtext Powered By ASP.NET