SQL This!

I'm coding that's what's up... http://xcodethis.blogspot.com

If you are familiar with TSQL and want to get a row count from your tables you might be tempted to run a simple Select count(*) from myTable This however can be a lengthy wait if you have millions and millions of records. Not to mention the potential impact on a production system. Instead consider selecting your rowcounts from your system tables SELECT OBJECT_NAME(id), ROWS, indid FROM sysindexes WHERE indid AND OBJECT_NAME(id) IN ('myTableName') Looking forward beyond Sql Server 2005 and beyond the following select should be used and will provide you with the needed information as system tables access may be depracated SELECT DISTINCT OBJECT_NAME(P.object_id) AS [Name], ROWS FROM sys.indexes I INNER JOIN sys.partitions P ON P.object_id = I.object_id AND P.index_id = I.index_id WHERE I.index_id AND OBJECT_NAME(P.object_id) IN ('myTableName') So why am I searching for index smaller than 2? In short from the article below you'll find that pure data pages have indi...

Hi everyone... So some new things are going to be happening with my blog... I'm going to pick some helpful hints that help me and my fellow DBA's with what we do on a Daily or weekly basis. These should be helpful for all but you are more than welcomed to share your experiences on what Tips help you the most. One thing that is comming is an article on Wait times... I had the pleasure of trying out Confio's Ignite software. And while it does some really great things, such as pointing out where the bottle necks are for performance on your server. It can be pretty pricey and really does not introduce anything you can't already do. I'm planning on rolling out a short but continuous list of I/O wait times what they are and how to use them to boost performance on your SQL Server within the coming weeks, I will not concentrate on Sql Server 2005/2008 alone, I know that is the trend in the tech community but there are still many organizations that use Sql Server 2000, ...

I had recently been reading a lot about the the wifi issues surrounding the iPad, and though this post is not SQL related, it kinda is for me. You see I was using a Remote Desktop app on my iPad when the connection started to drop out on me. Something new that I had started to experience. I determined that it had to do with bluetooth being enabled. I tried disabling it and the iPad returned to it's normal bandwidth loving behavior... the thing was that I was using a bluetooth keyboard with it to type up my sql... so I had to find an alternate solution... Solution one... you can actually just turn up the brightness on your iPad and it will keep the wifi from going into sleep mode.. wierd right? or in my case so I didn't need to fumble around I decided to changes some wifi settings of my TrendNet Router, I found the settings at the macrumors forum, http://forums.macrumors.com/showthread.php?t=889348&page=6 the settings on my wireless that fixed my problem (apparently perm...

This is kinda cool. I currently do not have a need for it as my job does not require me to write new tsql code on a daily basis anymore. However, when I used to write ranking code it was always a LONG and TEDIOUS process not really because of the Ranking code, but mostly because of what it takes to get to the ranking code. ARG! My Ranking routine believe it or not consisted of a small segment of code with a CURSOR! This is an snippet of code from what I used to use and is actually still in production today declare tstcur CURSOR FOR SELECT id FROM @temp declare @rank as integer declare @tID as integer OPEN tstcur FETCH NEXT FROM tstcur INTO @tID WHILE @@FETCH_STATUS = 0 BEGIN select @rank = count(*)+1 FROM @temp WHERE final_score > (select final_score from @temp where id = @tID) insert into @temp (id, Division, responses, max_points, total_points, final_score, rank) select id, Division, 0, max_points, total_points, final_score, 0 from @temp where id = @tID update @temp set...

Often when a database needs to be renamed a common tool that I've used in the past was always to use the sp_renamedb procedure. And that's one of the reasons I love the internet, there is always a better way to do something. Take the tips on how to rename your database without the use of this procedure. What is neat about the following article is that it also changes the logical and physical names of the database. see more at this link... http://www.mssqltips.com/tip.asp?tip=1891

F ixing smallish databases which are less than 1-2 gb may be just annoying when you are dealing with multiple indexes, but try managing some of those larger ERP databases with literally thousands of tables! Talk about a database from hell, having to sift through 10 of thousands of indexes can be a real chore if you're searching for performance bottle necks. There are some great solutions out there that all cost money per instance or per site license and can get quite pricey. But just about all of those products are charging you for something you can do on your own. T he article below is an extension of my previous blog on maintaining those indexes It's the script that has evolved from some very basic loops and DMV ( dm_db_index_physical_stats ). In my script (follow the link below) You'll find that I chose to stick to a SAMPLED stats, which essentially looks at the number of compressed pages. If you're talking millions of row of data and a very short maintenance w...