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.
Read more →
I've written an article here on SQL Team about Application Locks in SQL Server 2005
Application locks aren't a well known area of locking in SQL Server, but they can be very useful for special scenarios.
Read more →
it's my opinion that today we unfortunately live in a world that requires some kind of harder problem solving skills from maybe 20% of the population. And I'm being very very generous.
Read more →
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.
Read more →
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
Read more →
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.
Read more →
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.
Read more →
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
Read more →
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.
Read more →
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.
Read more →
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!
Read more →
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.
Read more →
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
Read more →
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 →
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 →
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 →
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 →