-
Sometimes the SQL Server query optimizer does not pick the best index to use in a join operation. This can be caused by a multitude of factors (out of date statistics, wrong indexing strategy for a table, etc) ... for what ever reason it happens, there is a way to force SQL Server to pick the correct...
-
Have you ever needed to get only the date part out of a datetime field in SQL and wanted to drop the precision of the timepart? It's actually surprisingly easy, and with this method is pretty fast as well. I've had to do this many times but always forget the syntax in T-SQL (though this should...
-
Today I had to do a common task that I've done several times, but suddenly realized I should put up here, because its a fairly common issue. Say you have TableA has a one to many relationship to TableB, and you want all of the values in TableB.dbo.ColumnA to be in a csv list based on the FK. This...
-
This is a simple query for finding all instances of a column with a particular name along with the table that it belongs to. This will not work on SQL Server 2000. select sys.objects . name as TableName , sys.columns . name as ColumnName from sys.columns inner join sys.objects on sys.objects . object_id...
-
This technique is fairly handy for server side data paging, it essentially executes the statement server side, then grabs a subsection of the rows (that you specify) and only returns those to the client. This will reduce network traffic pretty considerably, and subsequently speed up your applications...
-
Unless otherwise specified when you create your tables primary key in SQL Server 2000, 2005 and 2008 will have a clustered index on it. This will be a good default choice if you are going to be joining other tables together on this value. If you are going to be joining or selecting based on another value...
-
By default, when you create a primary key column in SQL Server, it makes it a clustered index. Sometimes this will be a default advantage, sometimes a serious disadvantage for performance as clustered indexes are the cornerstone of performance tuning in SQL Server. In the event that a clustered primary...
-
In order for us to be able to create an indexed view, we need to create that view with SCHEMABINDING specified. What exactly does SCHEMABINDING mean? Here is the definition from MSDN: SCHEMABINDING Binds the view to the schema of the underlying table or tables. When SCHEMABINDING is specified, the base...
-
As you probably are already aware, a view in SQL Server is generated off of a pre-defined select statement, and is a flexible solution to use when ever you are doing database driven application development; particularly when you are using an ORM tool (like SubSonic or EntitySpaces as examples). However...
-
In SQL Server 2005 and 2008 the Common Language Runtmie (CLR) is tightly integrated with the database engine, allowing you to write stored procedures in any .NET language such as C# and VB.NET. CLR stored procedures have some distinct advantages over traditional stored procedures, and they also have...
-
In order to make CLR stored procedures run, you must first enable the CLR in SQL Server, other wise you will just get this error: Execution of user code in the .NET Framework is disabled. Enable "clr enabled" configuration option. Enabling the CLR is fairly simple, just run this SQL Statement...
-
On a few of the mailing lists I am on for developers (primarily ones related to web development), there is a recurring topic that always gets people into rather heated debates: to use stored procedures or prepared statements? Now, either one you choose to use, is largely dependent on two things: preference...
-
Recently a friend of mine called with a question about how to do something in T-SQL that he wanted to do for the leaderboard for a game he wrote. The issue was that tens of thousands of people are playing his game (on the iPhone) and the screen real estate only allowed for about 10 players scores to...
-
Recently I got a request for a top 10 list of things that a DBA should be doing on a daily basis from an engineering managers perspective. Well, here is what I came up with, and please note, that this list is dependant on the poor guy being a full-time DBA, not some programmer who also doubles as a DBA...
-
Many times I have found myself needing to make changes to an existing table that is being published for replication. Here is a script that I have been using for a few years now that has served me well and never caused issues. Hope you find it useful. -- @publication is the name of the Replication Publication...