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 →
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.
Read more →
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)
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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:
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
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.
Read more →
... at installation time was... are you ready?
OWC11
Yes... OWC11. Come on, are you kidding me??? this is so 2003 :))
Legacy Comments
Dejo Smejo
2007-06-05
re: SQL Server 2008: A component that surprised me.
Read more →
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'
Read more →