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?
Read more →
We're all familiar (i hope :)) with this construct:
try { // ... some code here ... } catch (Exception ex) { // one of these 2 lines are usually seen throw; // presereves the full call stack //throw ex; // changes the origin of exception to this method } finally { // more stuff here } It's a standard error catching routine in .
Read more →
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.
Read more →
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.
Read more →
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".
Read more →
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):
Read more →
I haven't been using this function at all. Who knows why... maybe because i didn't know about it :)
But in my latest pet project i had to do something really quickly on a lot of items in a list.
Read more →
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.
Read more →
A negative Width value?? I can hear it now:
Are you insane Mladen?? Do you want our apps going around looking ugly??
Well... of course I do! It means i can go around fixing them for big bucks :))
Read more →
In a lengthy article about WebDAV i've written a long while ago I've explained how and what WebDAV is and what are the problems you might encounter.
Suggesting by the comments (over 200) it's a pretty popular topic but there's no help on the net for anything more complex than simple operations because
Read more →
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.
Read more →
And no, you shouldn't look at the back of it. :)
Whenever I talk to anyone about looking at the computer monitor for a longer period of time everyone thinks i'm nuts when i say i can look at it for 12 hours straight with no problem at all.
Read more →
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.
Read more →
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
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
This question pops up a lot everywhere and it's a common business requirement and until SQL Server 2008 doesn't come out with its MERGE statement that will do that in one go we're stuck with 2 ways of achieving this.
Read more →
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.
Read more →
Every now and then i see a question pop up that asks what is the max this or that in sql server?
Well here are some maximum values:
Bytes per short string column8,000Bytes per GROUP BY, ORDER BY8,060Columns in GROUP BY, ORDER BYLimited only by number of bytesBytes per index key900Bytes per foreign key900Bytes per primary key900Bytes per row8,060Bytes per varchar(max), varbinary(max), xml, text, or image column2^31-1Characters per ntext or nvarchar(max) column2^30-1Clustered indexes per table1Columns per index key16Columns per foreign key16Columns per primary key16Columns per base table1,024Columns per SELECT statement4,096Columns per INSERT statement1,024Connections per client32,767Database size1,048,516 terabytes Databases per instance of SQL Server32,767Filegroups per database32,767Files per database32,767File size (data)16 terabytesFile size (log)2 terabytesForeign key table references per table253Identifier length (in characters)128Instances per computer50 (Workgroup Edition only 16)Locks per connectionMaximum locks per serverLocks per instance of SQL ServerUp to 2,147,483,647Nested stored procedure levels32Nested subqueries32Nested trigger levels32Nonclustered indexes per table249Parameters per stored procedure2,100Parameters per user-defined function2,100REFERENCES per table253Rows per tableLimited by available storageTables per databaseLimited by number of objects in a databasePartitions per partitioned table or index1,000Statistics on non-indexed columns2,000Tables per SELECT statement256Triggers per tableLimited by number of objects in a databaseUNIQUE indexes or constraints per table249 nonclustered and 1 clusteredUser connections32,767XML indexes249
Read more →