SQL This!

A very good read for even the most veteran of developers, the following top 10 mistakes list is not necessarily the top 10 mistakes of all time, but it is a good guideline... SQL Server query design: 10 mistakes to avoid

Vadivel's blog: Avoid using sp_rename ...

Managing tables into filegroups can be a very daunting task when you start to look at huge database systems. The following script always helps me to locate what table is in which filegroup. It helps to keep things neat and tidy. SELECT o.name, s.groupname, f.name FROM dbo.sysfilegroups s INNER JOIN dbo.sysindexes i ON i.groupid = s.groupid INNER JOIN dbo.sysfiles f ON f.groupid = s.groupid INNER JOIN dbo.sysobjects o ON i.id = object_id(o.name) AND i.indid in (0, 1)

There are plenty of tools out in the wonderful world of the web to provide you with this very detail of information. Do you need it? Well Gui's can be fun and down right useful when in the right environment. In my world I use software to help keep track of my LIVE/Production systems but, what about those other systems that are not really live, but are development and test. Sometimes you start to run out of room, and you wonder why... sometimes you want a quick count of the tables that either consume the most space in terms of disk, or rows... How can you do it quickly? The following snippet is not groundbreaking, but it does provide a valuable service. More importantly it answer the questions of... which table is bigger... which tables are unused. etc, etc. You can even take this to the next level and keep track of which tables grow the most by surveying all your databases. And if you really like GUIs you can graph a trend on which tables grow the most.. stuff that is alrea...

Let's Face it, when working with Sql Server it does pose many security threats when unsecured, even while secure you have an ultimate login to try and crack. That of course is the "SA". Unless you set up your Sql Server to NT Authentication only, but sometimes you don't have much of a choice. The following article has some interesting methods to try and upgrade your passwords to be MORE secure. Find SA Password (Brute Force) Find Passwords (Dictionary) Analysis of Password Security Password Generator ASCII Table

Sql Server 2000 Reporting Services is an awesome reporting tool, but what they forgot to pack in is something simple like a 2nd Y axis (wich I still haven't figured out how to do), and a stinking Trendline. I mean, c'mon, even EXCEL does trendlines! So my quickie solution is the following script, All you have to do is populate the @TrendTable and you're on your way!!! DECLARE @TrendTable TABLE (PID INT IDENTITY (1,1), XColumn smalldatetime, YColumn1 REAL, YColumn2 INT, Trend REAL) --------- /* At this point populate the @TrendTable with your data, the Xcolumn is of course plotted data on the x axis, On this example I've put in a Y column and a 2nd Y column for your charting needs, YColumn1 is the column that will be trended. */ -------- DECLARE @n REAL DECLARE @sumX REAL DECLARE @sumY REAL DECLARE @sumXY REAL DECLARE @sumX2 REAL DECLARE @sumY2 REAL DECLARE @sumX_2 REAL DECLARE @sumY_2 REAL DECLARE @m REAL DECLARE @b REAL DECLARE @r REAL SELECT @n = COUNT...

Don't end up in urban legend hell!, The following articles really do bring to light the urban legends of database engines. Read along and get de-mystified. These all List to the Register's site. The myths and legends of the holy land of the database The parable of the beer and diapers Tackling Apache zombies Microsoft was caught stealing secrets from Borland

I dunno if you've heard of Red-Gate. but so far they've created some very top notch software to help out with both webservices and Sql Server. I've personally tried both their Sql Data Compare and Sql Compare wich helps match both schema and data accross diffrent databases and servers. This has many benefits as you can easily work in a test environment and have the ability to work with some close to live data. and after you've completed your work you can propagate the changes to your test environment with out a hassle not to mention you can minimize the time it takes to roll changes to your live environment. But really what I wanted to talk about today was Sql Prompt. It's Intellisense for Sql Server. That's it. Well that's not just it. It loads a systray app in your system and hooks directly to your Query Analyzer. Off the cuff I found it to be extreamly effective. I started to write my Select statement and it was able to appropriately give me bac...

I cruised the net last week looking for a simle "Find and Replace" script. sorta like a split for VB only that I need the replace to occur at every n th character. Not finding what I wanted I ended up writing my own, the idea was that I'd start at the lenght that my string needs to be then crawl backwards until I find a space (or breakpoint) then replace it with my Replace string. There may be a better way.. if you have one feel free to comment. CREATE FUNCTION dbo.SplitAndReplace ( @String AS VARCHAR(8000), @SplitString CHAR(1), @ReplaceString VARCHAR(255), @BreakInterval AS INT ) RETURNS VARCHAR(8000) AS BEGIN /* SET @String = 'This is as very simple text that needs to be broken down for processing in the external system, the process should auto insert an exclamation-pound sign in lew of a space that is at 60 characters' SET @BreakInterval = 60 SET @ReplaceString = '!#' SET @SplitString = ' ' */ DECLARE @X AS INT DECLARE @Y AS INT SELECT...

Sometimes you just have to know How to Set Up a SQL Server Stress Test . SQLIOstress is a bit diffrent than SQLIO wich actually gives you back only IO resutls, whereas SQLIOstress will yeild results in the manner that SQL maintains files.

UPDATED! 11/11/11 Every DBA runs into the following topic from time to time in their career, How Do you go about Migrating Logins from One SQL Server to Another ? From time to time you'll want to move actual sql login users to another server be it an upgrade, or if you have a good development - test - production cycle, you might be more used to this. Or maybe just maybe you're starting this cycle out, initially just run the Sproc provided by Microsoft called sp_help_revlogin There are two methods posted on the Microsoft site: Method 1 is for Sql Server 2000 to 2000  KB 246133 Method 2 is for Sql Server 2000 to 2005  KB 246133 Method 3 is for Sql Server 2005/2008 KB 918992 You will need to use Query Analyzer for Sql Server 2000 or Management Studio for 2005/2008  Copy the results and paste them to the new query window Go to the source server and open up a new query window and be sure you are pointing to your source database check that your Management s...