SQL This!

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...

It's been a while since I've updated the blog, but I Did want to mention that I am working on a neat article, which focuses on my passion for performance. Stay tuned for the latest details... in the mean time, have you ever been stuck with someone else's database? Or how'bout a vendor database where someone needs you to extend a task. Well finding the stored procedures is relatively simple. Remember just go through profiler, run the process and you can monitor which stored procedures are called sometimes this also provides you some feedback on which tables are being accessed. Other times you may need to report on some of this information, so you may need to search the database on where they decided to store such information. I extended my own version of Narayana's searchalltables procedure, in this new version you'll notice that you get to also search text (and ntext) fields along with only a single while loop. Check out the latest script and article here...

E mail, You use it, your colleges use it, even your systems use it. It's a part of everyday business. If you are a Sql Developer you have probably figured out how to implement email already, Often times I've seen many DBA's and Developers implement it from outside of SQL Server in rather ingenious ways... This article outlines how to setup Sql Server Mail in Sql Server 2000 and 2005 (2008 is the same as Sql Server 2005). By bringing mail inside of your server you can now send reports, alerts and other needed information based on the triggers and alerts that matter to you most. Making execution calls to xp_cmdshell to an opensource program (Blat) and sp_send_dbmail calls for Sql Server 2005 help leverage reporting from Sql Server. Sending E-mail from SQL Server 200X

When you are faced with request from users who will ask things like... i want to know the top 2 machines of every model type that have active leads, you may find yourself baffled and stunned to find that the Select TOP n does very little to help you out. The following article address the issue completely whether you're a sql developer or NOT. For myself it was a new look at existing solutions that we had employed all which were cumbersome and tedious to maintain, the solutions in the article describe the best approach which is easily extensible and flexible. Limit Groups by Number Using Transact-SQL or MS Access

When you inherit a new server sometimes you find that you can't connect to the server, to fix that you may need to simply enable the protocol via Sql Server Network Utility (svrnetcn) that is listening For MSDE. In Windows, click Start and Run . Enter svrnetcn and click OK . Under the General tab , verify that the correct instance for the server is displayed in the Instance(s) on this server box. Highlight your desired protocol and click Enable (double clicking the name also moves the protocol to the enabled protocols box). Click OK . Restart the Sql Server Instance In Windows, click Start and Run . enter services.msc Locate the MSSQLSERVER instance you modified in the Sql Server Network Utility and Restart the service. You may wish to ensure that your users are not logged on or at least notified of this change as it will kick them out of the application