SQL This!

This question comes up quite a bit when DBAs are faced with space issues... the simple solution most people say is to do a quick SELECT COUNT(*) FROM , but that ends up being such a costly solution and heavy I/O cost if you have a lot of tables or a lot of data. Fortunately you can obtain the number of rows without having to run such a heave I/O intensive operation. In Sql Server 2000 the command looks like this: Select OBJECT_NAME(ID), rows From sysindexes Where id = OBJECT_ID( TableName) AND indid While I found that the number of rows will differ slightly it is close enough to make needed judgment calls based on row count. (I.E. defragging, partitioning, archiving, etc.) In Sql Server 2005 the command looks like this: SELECT OBJECT_NAME(object_id) TableName, rows FROM sys.partitions WHERE OBJECT_NAME(object_id) = TableName note, be sure to replace TableName with your name in order to locate the rowcount for the individual table. You can alternatively modify the Where clause to be by...

While you work on your newest indexes / table design it is often necessary to clean up the cache on Sql Server to ensure that your changes are actually making a difference. Typically to clear the cache you'll want to run, DBCC DROPCLEANBUFFERS. If you are also executing your test via sprocs you'll want to clear that cache as well to do that just run, DBCC FREEPROCCACHE. This ensures that you are always starting from the same point at each run. If you do not clear the cache between tests you can't be sure that your time results are accurate.

P erformance.... it's an intangible and it seems that no matter how fast and how optimized you set your system up, it seems that there is always new information telling you what you should be doing as well. It seems that there really is no end on to what you can do with software, and when you think you've reached the end of that intangible, then there are all the sorts of things you can do in the hardware world to further push the performance limits. Don't get me wrong, I dig being able to find these tidbits... these jewels of information. It just seems sometimes that you've completed implementing something when now new information, could have helped you out before you started planning. So, What do you do when you continue to manage more and more Sql Servers in your environment? I started out with 8 unhealthy servers. I kept on them and have been able to kick the developers square in the butt!* Part of the butt kicking allowed me to set new rules in place. I...

I had the mis-fortune of working with a database where the developers decided to store the IP address as an integer. I believe they did this in order to help improve the performance of their database, or storage, or possibly both. The front end dealt with all the conversion of IP to INT and vice versa. When I needed to make a little bit of enhancements because the front end was just literally timing out at the amount of data that it needed. I decided to change how I retrieved some of my reports, and thus run into the following link, in where the author shares two functions to aid in the conversion of IP to INT and INT to IP. The article is quite good and even has a few numbers on what the performance is on each style of how one might choose to store the IP address information. How should I store an IP address in SQL Server? I know my new years resolution was going to be broken the moment I published the first article of the year :D HA!. I do have an article on working with index...

Ever needed a quick Date formula? In my world I do not need such funky date formulas all the time, only during the occasional Report week of the year. With that said though, I know others run into this need frequently. I found this while perusing one of my favorite sites, enjoy as there is very good detail and sample code on how to achieve just what you need. (Link to Database Journal) Calculating Days of the Week and Accounting Months 5-4-4

If this is your first time visiting my blog, welcome! For those who are returning, welcome back! SqlThis is aimed at being a quick "Reference" site. In general I do not write a whole lot of fluff about the topics at hand, just the absolute necessary to help understand the script/example and that's it, in some ways SqlThis is a repository of Tsql Links, but often times when I've run into unique solutions or solutions that I want to revisit, I will post them here too, so it's sort of a mesh of links and articles. I also get tips and tricks from fellow DBAs in the community that will provide me w/ Articles or Links. I hope this year to get a new tip/trick at least once a month. I've been posting to this blog for almost 2 years now. Next month will actually be my 2 year anniversary. And while other sites give away Ipods and Laptops (google microsoft laptop giveaway). I will be providing something much more coveted... ... more tsql code :)