Ever need to group dates and round them to intervals? I had this exact need for a report on wait stats I was working on for an upcoming post. I wanted the flexibility to be able to group a list of dates by either minute, hour, date, etc. And then also round those dates within [...]
Posts Tagged: SQL Server 2000
Please, no TRIM()?
A couple weeks back I was asked “How come Microsoft has yet to put TRIM() in SQL?” I don’t really know why there isn’t a TRIM() function in SQL Server. There is an LTRIM for function removing leading spaces. And an RTRIM function for removing trailing spaces. But there isn’t a TRIM function. Let’s be [...]
Checking EXISTS – What’s Your Practice?
Michael Swart (Database Whisperer) posted a great write up and interview with Brad Schulz that is worth checking out. Michael’s post links to a post from Brad on the use of EXIST and what is required for the SELECT portion of the statement. Hope you are still with me. Definitely worth the read and +1 [...]
Rounding Up to the Nearest 1,000
A co-worker came over and asked how to round up to the nearest 1,000. He tried to use ROUND but found that it always split between going up or going down depending on the value and the report he was working on always had to round up. Almost before he finished asking, actually before he [...]
Scripting Object Level Permissions
A while ago while making changes to a client database, I needed to determine all of the object level permissions that were in the database I was working on. The system stored procedure sp_helprotect turned out to be pretty useful in obtaining this information. The procedure accepts four parameters: sp_helprotect [ [ @name = ] [...]
Last Time SQL Server Restarted
Not a really hard thing to figure out, but for some crazy reason I always forget. So, hopefully actually writing it out for the world to see will help me remember. Since tempdb is created every time that SQL Server restarts, the created date for that database can be used to determine the last time [...]
Incrementing Values
Occasionally, I run across procedures that mimic the functionality of the IDENTITY property. There are always various reasons for these procedures to exist, some valid and some not quite so. Recently while trying to tune one of these procedures that someone had added interesting locking hints to a creative rewrite to the procedure was suggested. [...]
Microsoft Certification Second Shot Exam, It’s Back…
It turns out that the Microsoft Certfication Second Shot Exam (FAQ) is back… For a limited time, you can get a free, second shot at any IT professional, developer, or Microsoft Dynamics certification exam. Just register for this offer before your first exam, and you will get two chances to pass. But this offer won’t [...]
This blog entry brought to you by the procedure "sp_MSForEachDB"
My undocumented stored procedure of the day is sp_MSForEachDB. This procedure allows SQL statments to loop through all database names on a server with a dynamic SQL string. The procedure accepts the variable @command1 for the dynamic SQL string, and the database name is passed into the string through the use of "?". Unlike using [...]
Room with a View
Every so often someone will get me on the topic of why "Select *" is an official Bad Practice. There are various arguements against "Select *" but one that a lot of programmers may not be aware of is the relationship between "Select *" and views. When a view is created with "Select *" it [...]
_1262.png)
