SQL Server Blog : T-SQL

How To Use Index Hints in SQL Server 2005 and 2008

dacrowlah — Mon, 29 Mar 2010 18:17:00 GMT

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 index at runtime. This is called an INDEX HINT; if you have ever used a table hint, this is similar to one of those. The syntax is fairly simple, immediately proceeding the table being named in the join, you type with(index(INDEX_NAME)) and this tells SQL Server that when it comes time to join this table in, the named index must be used.

Below is a sample, previously the execution plan for this query was using a compound query that had OrgId as the first named column. Use of this index was making SQL Server do an Index Scan and then a Hash Join. After adding the index hint it was using the right index and doing a Clustered Index Seek and Nested Inner Loop Join. Perfect.

alter view OrgProductsView as
select [OrgCatalogs].[OrgId], [Products].[ProductId], [Catalogs].[CatalogId], [Catalogs].[CatalogTypeId], [AdjustedPrice], [ProductTypeId]
from [OrgCatalogs]
inner join [Catalogs] on [OrgCatalogs].[CatalogId] = [Catalogs].[CatalogId]
inner join [CatalogProducts] on [Catalogs].[CatalogId] = [CatalogProducts].[CatalogId]
inner join [Products] with(index(IX_Products_ProductId)) on [CatalogProducts].[ProductId] = [Products].[ProductId]

How to select only the date part of a datetime field in SQL (SQL Server 2000/2005/2008)

dacrowlah — Fri, 29 Jan 2010 05:19:00 GMT

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 also work on other database platforms as well) so I figured I would put it up here on the blog and hopefully it would come in handy for some of you guys as well!

SELECT (CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME)) AS CurrentDate

You can, of couse, replace the call to getdate() with a column from a table that you are selecting from and it will work just as well. The performance hit for using this method is neglible ... especially considering the alternative of casting the calling YEAR(), MONTH() and DAY() on the field and concatenating them back together as strings.

SQL Server 2000/2005/2008 UDF to return a csv list of values from a query

dacrowlah — Thu, 11 Jun 2009 18:22:00 GMT

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 is the perfect place for a simple UDF!

create function [dbo].[udf_ocrd_getProgList](@siteid nvarchar(17))
returns varchar(max)
with execute as caller
as
begin
      DECLARE @ProgTypes nvarchar(max)
      SELECT @ProgTypes = COALESCE(@ProgTypes + ', ', '') +
         CAST(np.progname AS nvarchar(65))
      from ocrd.progsite ps
      inner join ocrd.nprograms np on ps.programid = np.programid
      inner join ocrd.sites sites on sites.siteid = ps.siteidlnk
      where ps.siteidlnk = @siteid
      return (@ProgTypes)
end

and then to execute it...

select sitename, dbo.udf_getProgList(siteid)
from ocrd.sites
where siteid = '20030626152018383'

And it will return "Site Name Here", "Value 1, Value 2, Value 3"

How to find all tables with a column of a particular name in SQL Server 2005 and 2008

dacrowlah — Fri, 13 Feb 2009 07:37:00 GMT

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 = sys.columns.object_id
where sys.columns.name = 'countyid'

How to use the row_number function in SQL Server 2005 and 2008 for server side data paging in T-SQL

dacrowlah — Thu, 29 Jan 2009 18:59:00 GMT

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 response times if network chatter is a problem.

with employeeDataSlice as
(
select *, row_number() over(order by EmployeeID asc) as displayRowNum
from [HumanResources].[Employee]
)
select * from employeeDataSlice where displayRowNum between 1 and 5

How to create a table with a non clustered primary key in SQL Server 2000 2005 and 2008

dacrowlah — Sat, 24 Jan 2009 20:54:00 GMT

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 in the table, then you should probably use a nonclustered primary key. Here is how you do it in T-SQL at the time of table creation:

create table DemonstrationTable
(
TableIdColumn int identity not null primary key nonclustered,
TableValueColumn nvarchar(255)
)
go

Changing a clustered primary key to non-clustered in SQL Server 2000/2005/2008

dacrowlah — Mon, 19 Jan 2009 03:25:00 GMT

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 key gets created and you need to change it to a non-clustered one, follow these steps:

Open the table in design view in SQL Server Management Studio (right click on the table in the object explorer, click 'Modify')
Right Click anywhere over the column definitions, from the context menu click 'Indexes/Keys'
Select your primary keys index by name in the right listbox
In the right pane, under the section titled 'Table Designer' find the item labeled 'Create as Clustered' and change the 'Yes' to 'No'.
Click close, then close the design view of the table, and choose the option to save.
If you have full text catalogs on this table you may need to repopulate them.

That should be it, now you are able to create a clustered index on another column or set of columns on your table.

What does schemabinding mean in SQL 2005 and 2008?

dacrowlah — Tue, 02 Dec 2008 16:06:00 GMT

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 table or tables cannot be modified in a way that would affect the view definition. The view definition itself must first be modified or dropped to remove dependencies on the table that is to be modified. When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced. All referenced objects must be in the same database.

Views or tables that participate in a view created with the SCHEMABINDING clause cannot be dropped unless that view is dropped or changed so that it no longer has schema binding. Otherwise, the Microsoft SQL Server 2005 Database Engine raises an error. Also, executing ALTER TABLE statements on tables that participate in views that have schema binding fail when these statements affect the view definition.

SCHEMABINDING cannot be specified if the view contains alias data type columns.

What does this mean?

Well, to put it simply, once you create a view with schemabinding, you cannot change the underlying tables in a way that would break the view. Examples of this would be removing columns or dropping tables that are specified in the view.

Creating an Indexed View in SQL Server 2005 and 2008

dacrowlah — Wed, 26 Nov 2008 22:33:00 GMT

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, for performance reasons there is an additional piece you should be aware of and this is what is known as an "indexed view".

On the surface, an indexed view is the same thing as a normal view, however the critical difference is that an indexed view actually allows you to create a clustered index on it, effectively working around the "one clustered index per table" limitation. The downside to this is that the indexed view is a full copy of the data from the underlying tables, so for space considerations you will need to take this into account when using them.

Let's look first at how to create a normal view, so that we have something to compare to as we create an indexed view

create view ContactsView as
(
      select [Users].[FirstName], [Users].[LastName], [Contacts].*
      from [Users]
      inner join [Contacts] on [Contacts].[UserID] = [Users].[UserId]
);

What this will do is create a pretty flexible view that will only pull the first and last name of the user from the users table, and all of the columns from the Contacts table and automatically pick up any new columns that are added to the Contacts table, with no further modifications. Indexed views require a more rigid definition of what they will return. For this reason, we cannot use a wildcard (*) in the underlying query. Each column must be individually called out; also, each table named in the query must be in 2 part dot notation, referencing both the schema of the table and the table name itself, and the view must be declared with schemabinding enabled for it.

Below is the modified query to create a view capable of being an indexed view:

create view [dbo].[OrganizationContactsView] with schemabinding as
(
      select [FirstName], [LastName], [Contacts].[UserID], [Contacts].[ContactDescription], [Contacts].[IsPublic]
      from [dbo].[Contacts]
      inner join [dbo].[Users] on [Contacts].[UserID] = [Users].[UserId]
);

Now, we need to create the clustered index on it. Clustered indexes on a view must be unique, otherwise you will not be able to create one; nonclustered indexes will work fine. For the present example, a UserId will only be in the contacts table once per Organization, so we can make a unique clustered index spanning the OrgId and UserId columns, like this:

create unique clustered index IX_OrganizationContactsView_OrgId on [OrganizationContactsView](OrgId,UserId)

And, there we are, a clustered index!

Writing Your First CLR Stored Procedure

dacrowlah — Fri, 31 Oct 2008 23:25:00 GMT

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 their drawbacks. In the world of application developers who use languages like C# and the world of database developers who use T-SQL, there are different definitions of "maintainability", and ultimately it will come down to what camp most of your development staff are in.

If you have a comfort level or more staff for database maintenance, then traditional stored procedures will fit into your architecture more easily, and CLR stored procedures will augment where specifically needed. However, when used properly CLR stored procedures will be a powerful tool in your development arsenal. The drawbacks to them are that they are deployed very differently (i.e. you can't run a create script in Management Studio and be done with it; you need to either deploy the assembly through Visual Studio to a database, or physically copy the assembly to the database server, and then register it manually in Management Studio, also you do lose some tracing data that you would normally see inside of SQL Profiler. Some of the more appropriate times to use them however are during operations that require high speed text manipulation, cryptography, and remotely accessing resources outside of SQL Server (such as calling a webservice to geocode address data).

Before we begin with creating our first CLR Stored Procedure, we need to enable the CLR in the database that we want to deploy the procedure to; to do this you can reference my previous posting on How to Enable CLR Stored Procedures.

Step One: Createing the CLR Procedure Project

To create the initial project, click on File -> New-> Project; then select Visual C#-> Database-> SQL Server Project

Let's name the project CLRStoredProcedures, and click ok. During the project creation phase, you will be prompted to add a reference to a database for the current project, create one to the AdventureWorks database if you don't already have one, and select that.

Step Two: Add a Stored Procedure to the Project

Next we need to add stored procedure class to the project, where we can add our methods. To do this, right click in the Solution Explorer, select "Add", and then select "Stored Procedure..."

After the item is added to the project you will be looking at a blank stub method in a C# class file, where you can customize the code to do what you need.

Now, let's go ahead and change the code in this file, so we can do something productive with it!

First, we should change the method name to something useful, in this case we can use the name "GetCustomersByTerritoryID". Guessing based on the name, we will need to be passing in an argument, TerritoryID to our procedure, so also add an argument to the function called "TerritoryID" and of type int. Below, is the code for the procedure, you can copy and paste it, and I will explain line by line what is going on.

[Microsoft.SqlServer.Server.SqlProcedure]

public static void GetCustomersByTerritoryID(int TerritoryId)

{

SqlPipe pipe = SqlContext.Pipe;

        SqlCommand cmd = new SqlCommand("select * from [Sales].[Customer] where [TerritoryID] = @TerritoryID");
        cmd.Parameters.AddWithValue("@TerritoryID", TerritoryId);
        using (SqlConnection con = new SqlConnection("context connection=true"))
        {
            try
            {
                cmd.Connection = con;
                con.Open();
                pipe.ExecuteAndSend(cmd);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (con != null)
                    con.Close();

            }
        }
    }

Ok, once we get inside the declaration of the procedure, the first line is SqlPipe pipe = SqlContext.Pipe; this is the mechanism that we have available to us to send any results back to the client. I am going to make some basic assumptions that you have experience working with ASP.NET or data access with C#, and can already see the similarities here. We start off with instantiating a SqlCommand object, giving it a query to execute that is properly parameterized, and opening a database connection. Now, notice here that it does have a very different connection string that we are used to. It does not specify a server, database, username or password... or for that matter anything we normally see in a connection string. That is because we are already connected to the database and are already authenticated, so none of that is necessary. We simply set the "Context Connection=true" flag, and we're set!

The next big difference you see is that we are calling the ExecuteAndSend() function on the pipe object, and passing in the SqlCommand object as an argument; doing this executes the command and sends any subsequent results back to the caller.

Step Three: Build, Deploy, and Execute!

Now that we have the code already laid down, the final step is upon us, we first build the class file by clicking "Build" on the top menu, then choose the option to build it, then "Build" again, and choose deploy. After this says "Deploy Successful" in the status pane, we jump over into Management Studio, open up a query window to the AdventureWorks database, and try executing it

And that's it! We are done with our first CLR Stored procedure, you can call this like you normally would any stored procedure, and better yet, you can take advantage of the .NET framework from inside of SQL Server 2005 and 2008 from languages and platforms other than ASP.NET! ColdFusion, Classic ASP, PHP, all of them can benefit from this integration with no requirements on the application side! Attached to this post is the actual C# class file used in this tutorial.

How to enable CLR Stored procedures on SQL Server 2005 and 2008

dacrowlah — Mon, 27 Oct 2008 01:53:00 GMT

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 in Query Analyzer, while connected to the database that needs this option enabled:

EXEC sp_configure 'show advanced options' , '1'; 
reconfigure; 

EXEC sp_configure 'clr enabled' , '1' ;
reconfigure; 

EXEC sp_configure 'show advanced options' , '0'; 
reconfigure;

Making the case for stored procedures

dacrowlah — Sun, 26 Oct 2008 07:23:00 GMT

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/comfort level, and the appropriate tool for the appropriate job. This will be my attempt at defining (my opinion of) the right tool for the right job, while allowing for maximum flexibility.

Now, for those who are not aware of the difference, a prepared statement is straight SQL sent to the db for execution, but with explicitly declared parameters so as to prevent SQL injection attacks. The techniques on how to do this vary from language to language, but in ColdFusion it is through the use of the <cfqueryparam> tag, and in .NET, you use the SqlCommand object, type of text, and where you want the parameter values, you place a “?”, then add the parameters in the proper order.

Now, prepared statements are not a bad thing at all; they offer some of the same benefits of stored procedures: explicit parameterization, elevated chances of execution plan reuse, saves overhead on the db side of having to do type checking/conversion, etc. One additional benefit of using prepared statements, it’s a heck of a lot easier to write secure dynamic SQL for complex query building.

However, stored procedures are a far better choice 99% of the time; complex dynamic SQL being the sole exception that I would consent to allowing in any database I was in charge of administering or code being checked in by a developer who worked for me. Stored procedures have several distinct advantages over prepared statements (or inline SQL in general) , and some of them become more apparent only after having built large, complex, enterprise level applications that have multiple developers and support staff working on them.

Guaranteed execution plan reuse
As long as you aren’t doing dynamic SQL within your stored proc and concatenating strings for execution, absolutely no chance of a SQL injection attack happening
Performance increases for saving on permissions checks – when you submit a prepared statement for execution the database server needs to check the permissions chain on every object that that query touches, EVERY time it executes. This is not the same as an execution plan. With a stored procedure you create the procedure and GRANT EXECUTE for that proc to the Id that your web application connects to the database as.
Carefully controlled access to data – sometimes your data has sensitive information (like Social Security Numbers, secret question/answer information, etc) and while you have to both store that information, and maintain a level of security around it so that not everyone in the IT department has access to it, you create a stored proc, and enforce access to the data through stored procedures. This method also gives you the ability to give partial access to data (such as a redacted SSN, instead of the full SSN)
Modularity of code – complex logic for doing multiple operations to data in the database is easily ported from one process to another. As an example, one application I routinely work on has 3 different methods for creating a user (new user auto-registration, administrative interface for creating them by uploading an excel file, and one that creates them via a scheduled batch job from excel files uploaded by clients) and there is some pretty complex logic for finding duplicate users across multiple clients and preventing new users from being created who exist elsewhere. Since this piece of code is in the stored procedure, adding new sources and tools to create users is dead simple… call the stored procedure and that is that. One argument that I heard to try and minimize the impact of this advantage “what if you had a single, well architected application written in a single language and the query was easily available from other sections of code within the application, why is there a modularity advantage for this? Well, if you have a single language in your application, and its easily accessible everywhere else, it does lower the points this one gives you, but that was kind of my point here, it lends flexibility even if you don’t anticipate needing it now.
Minimizing the amount of traffic sent over the wire. Sometimes as developers we think 10Mbps/100Mbps/1000Mbps Ethernet has so much bandwidth that sending this little tiny 20 line query over the wire has no impact and won’t cause any appreciable slowdown. Well, that is true for the most part, but wait till your application starts getting a ton of traffic… the more packets you send over the wire, the higher your collision rate on the network, and you’ll see reduced network efficiency, sad but true; also one of the most often overlooked reasons for using stored procedures.
Wise choices in location of your business logic will greatly reduce your round trips to the database when using stored procedures. If you are performing an operation that can be completed from a solid set of parameters from start to finish and the end result (success/failure) is the only thing you need to know, a stored procedure is what you need. Since this was just used as an example I’ll use it again: say you are creating a user you have first name, last name, email, address, list of positions, departments, and supervisors. If you have ALL of that logic in your middle tier and not in a stored procedure, you will have to keep going back and doing lookups for data, making more round trips to save, add, and then complete. All of these round trips add to network overhead, time spent encoding/decoding data to send across the wire, process... locate, return… lots of data, lots of network chatter. This would all be eliminated with a stored procedure, you would feed one set of data to the proc, and execute it, and everything will be handled server side and the result handed back to you.
Centralization of maintenance, changes, security, and stronger separation of tiers.
Ease in separation in application tiers for troubleshooting. If you write a stored proc, use it in your application, and suddenly start seeing weird results in the application, you can go, execute the proc, and eyeball the results… do they look weird? No? Then the chances are good the problem lies in your application layer, start looking there. Many years of supporting messy applications and code taught me this lesson all too well.

Getting a range of values from an arbitrary starting point in T-SQL

dacrowlah — Thu, 07 Aug 2008 17:40:00 GMT

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 be displayed. The present method only displayed the top 10 scores, and did not include the current players score, so they never really saw where people were in relation to them.

He wanted something a bit more relevant to the player. The solution was to display the closest 9 scores in relation to the player, 4 greater than, and 5 less than. This is the solution I presented to him on a conceptual basis, actual implementation may vary and you would probably want to create an indexed view to run this query against with a clustered index on the column containing the players score and the players UserId, adding other columns as necessary to display the data you want. This example will run as is without creating any tables or making any temp tables (uses only Table type variables).

declare @PlayerScores table (ScoreValue int)
declare @TempScores table(ScoreValue int)
declare @currentScore int 

set @currentScore = 0

while (@currentScore < 101)
      begin 
            set @currentScore = @currentScore + 1
            insert into @PlayerScores (
                  [ScoreValue]
            ) values ( 
                  /* ScoreValue - int */ @currentScore )  
      end 

set @currentScore = 50

insert into @TempScores (
      [ScoreValue]
) 
select top 5 [ScoreValue] 
  from @PlayerScores
 where [ScoreValue] > @currentScore
 order by [ScoreValue] asc
insert into @TempScores (
      [ScoreValue]
)  
select top 5 [ScoreValue] 
  from @PlayerScores
 where [ScoreValue] <= @currentScore
 order by [ScoreValue] desc


select * 
  from @TempScores
 order by [ScoreValue]

Engineering Managers: Top Ten Things Your DBA Should Be Doing

dacrowlah — Tue, 24 Jun 2008 17:28:00 GMT

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 as many of these tasks can be fairly time intensive depending on the size of the SQL Server installation and number of servers/databases/jobs that the DBA has to maintain.

1.) Ensuring backups are running (and complete successfully)

Recently a round of SQL injection attacks put our backup/recovery strategy to the test and uncovered a few flaws that we hadn't really encountered before. Namely, applications that continuously try and maintain connections to the database (note: a database should be in single user mode to do a recovery properly as the restore process needs to have exclusive access)

2.) Ensuring storage space/RAM is not a concern

Here at my job, when we launch a new site it's usually (these days) a statewide deployment, with the states Governor, senators, lobbyists, and other political figures present, which means lots of news coverage, promotional material distribution, and the like. Which always results in a sizable spike in traffic for about a month afterwards, then it normalizes again but is on average higher than it was before. Starting late last year, this spike in traffic made the SQL Server logs grow exponentially and eventually took down the site due to a non-proactive system administrator and no full-time DBA. It's a good idea to keep this kind of thing as automated as possible. As a matter of fact, all new HP Servers come with Insight Manager which can do this for you; I'd suggest finding someone capable to configure it for you.

3.) Proactively monitoring database performance

This is somewhat of an open ended statement, and can mean a lot of different things to different people. Work with your DBA to determine a set of metrics that are important to you for knowing what's going on in the database. Starting with SQL Server 2005, there is a rich set of data from the DMVs (Dynamic Management Views) available for snapshots into the internals of SQL Server. If you cannot find anything to start with take a look on MSDN for information on whats available, from there you will get some ideas. Some suggestions to start are the Top 100 Most resource intensive queries on your system. Start with the queries that are utilizing the highest amount of IO, as disk access is the slowest part of a database usually.

4.) Ensuring that a usable disaster recovery plan is in place

This takes time, resources, planning, and people dedicated enough to the job to want to do it in off hours and on weekends, otherwise you could have some upset customers when your site goes down for "Testing". It is pretty important though, as who wants to be the one to tell the Boss that the site won't come back up cause you can't figure out why the database restore software gives a generic error message? I didn't want to, but I was that guy!

5.) Ensuring an adequate set of tools are available and are familiar with their functionality

Every profession has its set of tools and utilities that make life easier. Database professionals ... are... well... often left out. However, there are some good tools out there for them, you just have to look for them! Red-Gate is an excellent starting point, their one product, SQL-Prompt is a godsend for people who do a lot of T-SQL development and use SQL Server Management Studio. IntelliSense for T-SQL! YAAY! (note: SQL Server 2008 Management Studio has this built in when connected to a 2008 Server)

6.) Ensuring that all database changes submitted by developers are reviewed and signed off on

This is also somewhat open-ended, and is dependant on your organization having a structured change management process. Let's face it though, not all developers take the time to really understand how to properly program in SQL, and may not be making best use of existing indexes, so make sure your DBA looks over all the new SQL code before it goes to production. It can save time on hard-to-locate performance hits later.

7.) Identifying rapidly growing objects in the database (transaction logs, tables, etc)

This goes hand in hand with #2, however, this is more of a proactive approach, usually automation techniques will let you know when you're starting to approach a pre-determined threshold. Doing this proactively, you will know when that time is coming, and you can have a strategy in place to handle it.

8.) Ensuring scheduled tasks are running (and complete successfully)

Backups, data cleansing, ETL tasks, all of them. No one likes coming in in the morning and finding an application down because a nightly dataload did not complete properly or a partner did not upload it. Be ready to roll back any partial updates!

9.) Ensuring the disaster recovery plan actually works

I reiterate because I love you. No, really, I do. I mean it. Call me?

10.) Be familiar with what your platform can do

Sounds simple enough, but I can't tell you the number of times I've interviewed potential candidates, and they couldn't tell me the difference between an inner join and an outer join. But a DBA that can't tell me what Profiler is? Nah.. not acceptable. If nothing else, know the toolsets that came with the product like the back of your hand, sometimes they're too generic for a specific task, but I will lay money on them being useful 99% of the time!

How to alter a table that is being published for replication

dacrowlah — Wed, 07 May 2008 01:13:00 GMT

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
-- @article is the name of the table you want to alter
-- @subcriber is the computer name where the Replication Subscription is
-- @destination_db is the name of the database

exec sp_dropsubscription @publication = 'DATABASENAME'
, @article = 'TABLENAME'
, @subscriber = 'SERVERNAME'
, @destination_db = 'DATABASENAME'

exec sp_droparticle @publication = 'DATABASENAME'
, @article = 'TABLENAME'

-- Write your alter statement here

alter table TABLENAME alter column columnName varchar(2000) null

exec sp_addarticle @publication = 'DATABASENAME'
, @article = 'TABLENAME'
, @source_table = 'TABLENAME'

exec sp_addsubscription @publication = 'DATABASENAME'
, @article = 'TABLENAME'
, @subscriber = 'SERVERNAME'
, @destination_db = 'DATABASENAME'