SQL This!

If you have been regretting making the move from Sql Server 2000 to Sql Server 2005, you will be in complete shock and awe when you find that you no longer have DTS to move your data around. The good news is that in Sql Server 2005 you get Sql Server Integration Services (SSIS) a full feature ETL product for Sql Server 2005. If you want to move things moving quickly take a look at the Sql Server 2005 Import / Export Wizard short article which outlines just how easy shuffling your data around can really be.

Take the post about finding columns and incorporate it with this post from a college of mine. http://www.artfulsoftware.com/infotree/tip.php?id=753 Now you're off and running turning lists to comma lists, in corporate that with the list table function I posted sometime back, and you can go backwards and forwards!

Forgive the fact that the following SQL is not formatted , but this topic came up today on a list that I frequent, which was how to get all the fieldnames of a view (or table etc). this dandy little select statement will gather just that and all you need to really parse it for is your table name or column name... maybe you want to know how many times you named something "EntryTime" this is an easy way to find all that information at a glance. This is a Sql Server 2005 version (If you need it I can post a Sql Server 2000 version as well.) SELECT t.name AS TableName, sc.name AS SchemaName, c.name AS ColumnName, c.column_id AS ColumnID, c.precision AS [Precision], types.name AS TypeName, basetypes.name AS BaseTypeName, st.name AS TypeSchemaName, CASE WHEN c.max_length>=0 AND basetypes.name IN (N'nchar', N'nvarchar') THEN c.max_length/2 ELSE c.max_length END AS Length, c.scale AS Scale, CONVERT(bit, c.is_identity) AS [Identity], CONVERT(bit, c.is_computed) AS...

The other day a co-worker mentioned one of his biggest pet peeves about Sql's new IDE. Of course he likes the old IDE, but what he detested the most was the slow bloat time it took to get his SSMS loaded on the screen. When we timed the boot time for his Enterprise Manager to load it took nearly 7 seconds. Not bad, but then proceeded to tell me how much longer SSMS (Sql Server Management Studio) took to load. I guessed that it could possibly be about 10 seconds (only 3 more seconds) but I was shocked to see it load in nearly 25 seconds. it was nearly 3x less productive just loading the thing before you could connect to do anything. I couldn't believe that it was nearly that slow on his computer, I had never timed it on my pc. So when I returned I had to try it. Again the load time was nearly 20 seconds on my machine (about 18 seconds to be exact). I was not entirely pleased with my results but thankfully Google provided the answer, one quick search for "Turbo char...

I had an issue at work the other day, something that seems kind of odd to me. You see in Sql Server 2000 you could setup a view with an order by. Of course this required a TOP 100 PERCENT to be specified at the top of the query. You could then take that view and base new views off of it and all results would be ordered by the original view. In Sql Server 2005 (SP1 or even SP2) everything changed, now you could no longer get the correct order by if you coupled it w/ the TOP 100 PERCENT in your view, but then again, if you do not use a the TOP keyword you can't save your view. The solution to how to get around the issue is in the following blog article. http://executioniseverything.blogspot.com/2007/01/order-by-in-views-sql-server-2005.html A proposed solution has been placed on the MSDN site so you can vote on it. It seems the proposal is to do away with the ability to create ordered by views which technically go against the rules of rational theory.... https://connect.microso...

If you're an active DBA, you'll find that your mornings are spent reviewing failed jobs and checking to see what else needs watching. At my company we were fortunate to purchase Idera's Diagnostic Manager. It is a good tool for reviewing your databases at a quick glance and identify any areas that need attention, ie. Space constratins, failed jobs and other critical processes. Not everyone has the ability to go out and spend thousands of dollars on a monitoring solution. That's why I really liked the following article by David Bird over at Sql Server Central (one of my favorite Sql Sites) titled SQL Overview IV - DBA's Morning Review (http://www.sqlservercentral.com/articles/Administration/63042/). In it he describes a set of reports that you can use to review your servers quickly during the morning and spend more time fixing problems than reviewing things. I Plan to implement many of these solutions in my own environment and see how well it stacks up to our p...

On occasion you may be tasked with switching your recovery model for a database. This task is very trivial and you may already know how to do this via Sql Server Enterprise Manager or even Management Studio. But you can do this via Tsql as well (as with all things sql server related) The syntax is basically ALTER DATABASE Northwinds SET RECOVERY SIMPLE The recovery Models available are, Simple, BulkLogged and Full. The Simple Recovery model is as you guessed the basic recovery model which pretty much allows you to recover your database from a full backup. The simple recovery model is ideal for test and development databases where data loss is not generally a concern. You may wish never to use this model in a production environment unless the business is willing to loose up all data changes since the last Full or Differential backup. In this model you do not have to maintain the Transaction log as nothing is generally written to it. The BulkLogged Recovery model will log e...