Thursday, November 16, 2006

10 mistakes to avoid

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

Monday, November 13, 2006

Finding your group(file)

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)

Friday, October 27, 2006

How to spot a big table

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 already available by vendors for thousands a month.. not groundbreaking like I said earlier but certainly useful and a good exercise to practice. Once you have it you can save a ton of Money on the GUIs out there...

Below I took advantage of using the undocumented stored procedure sp_msforeachtable, and the global variable '?'. Essentially for each table in the database run this sproc (stored procedure).


--By: Francisco
--Date: 10/27/2006
--Purpose: Find Out wich Tables Yield the most space.
IF EXISTS (select * from TEMPDB.dbo.sysobjects WITH(NOLOCK) where id = object_id(N'TEMPDB.dbo.#SpaceUsed')) BEGIN
DROP TABLE #SpaceUsed
END
CREATE TABLE #SpaceUsed (TableName VARCHAR(255),Rows INT, Reserved Varchar(255), Data VarChar(255), Index_Size VarChar(255), Unused VarChar(255))
INSERT INTO #SpaceUsed (TableName, Rows, Reserved, Data, Index_Size, UnUsed)
EXEC sp_msforeachtable 'EXEC sp_spaceused ''?'''


SELECT TableName, Rows, Reserved, Data, Index_Size, Unused FROM #SpaceUsed
ORDER BY CAST(REPLACE (DATA, 'KB', '') AS INT) DESC

Wednesday, August 30, 2006

How Safe are your passwords?

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

Tuesday, August 29, 2006

Going against the Trend

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(*), @sumX=SUM(PID),
@sumY=SUM(YColumn1),
@sumXY=SUM(PID*YColumn1),
@sumX2=SUM(POWER(PID,2)),
@sumY2=SUM(POWER(YColumn1,2))
FROM @TrendTable

SELECT @sumX_2 = POWER(@sumX,2), @sumY_2 = POWER(@sumY,2)

--slope
SELECT @m = (@n*@sumXY-@sumX*@sumY)/(@n*@sumX2-@sumX_2)
--y intercept
SELECT @b = (@sumY-@m*@sumX)/@n
--r
SELECT @r = (@n*@sumXY-@sumX*@sumY)/SQRT((@n*@sumX2-@sumX_2)*(@n*@sumY2-@sumY_2))

UPDATE @TrendTable
SET Trend = @m * PID + @b


SELECT * FROM @TrendTable

Friday, June 02, 2006

Wednesday, May 24, 2006

Intellisense for SQL Server

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 back names of tables to complete and even name of stored procedures that were available to me. One of the options that I liked the best, was the join feature, in which it helps you complete a join statement super quick by giving you a small list of all available joinable fields. you can type at your desire or pick from the list... ultra cool.
Well now for the hook and sinker... It's Free :). Unlike the other products that help make them money. Probably the most useful app they have on their site is completely free. Of course after using this little proggy you're gonna probably want to return and see what other cool apps they have. To get your copy click on the following link:
SQL Intellisense, Intellisense for SQL Server - SQL Prompt

After using it for a few minutes I was able to get the hang of it. Extra niceties are things such as auto-completion for your parameter names for a sproc (stored procedure). nice touch guys!

btw, nope I do not work for Red-Gate, I just happend to have really liked a few of their Software Titles. :)

---*update*---
SQLprompt will only be free until Sept 1st.

Monday, May 01, 2006

Find and Replace

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 nth 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 @X = 0, @Y = 0


WHILE (LEN(RTRIM(LTRIM(@String))) - @Y) / @BreakInterval > 0
BEGIN

WHILE @SplitString <> SUBSTRING(@String, @Y + (@BreakInterval - @X), 1)
BEGIN
SET @X = @X + 1
END


SET @String = STUFF(@String, @Y + (@BreakInterval - (@X )) , 1, @ReplaceString)
SET @Y = @Y + (@BreakInterval - @X)
END
RETURN @String
END

Thursday, January 19, 2006

How to Set Up a SQL Server Stress Test Environment in 8 Steps: Lessons From the Field

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.

Wednesday, January 18, 2006

Migrating Logins from One SQL Server to Another

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


    1. 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
    1. Go to the source server and open up a new query window and be sure you are pointing to your source database
    2. check that your Management studio is set to return your results as text
    3. you can do this by right clicking within the query window then choosing results to text
    4. Paste your script into the query window and execute the script by clicking the run button or F5.
    5. copy the results and open up a new Management Studio Query window that is connected to your Destination Server and be sure you've selected the destination database.
    6. Paste the result text, and edit out any users you don't want to re-create
    Now that you've migrated the users you can simply backup your database as you normally do and restore it to the new environment.

    These steps are only necessary if you need to migrate users from one physical server to another. You won't need to run these steps if you are on the same server and you've simply created your test database in the same environment, the users already exist in the database from your backup and restore, and they already exist in the server system database as well. This is only necessary when you have two completely separate servers/instances.

    Good luck and happy TSQLing