Mareo Code

Just a quick reminder/note. What will be the result for below string comparision? What do you think?

IF ('   ' <> '')
	print 'different'
ELSE
	print 'equal'

If you answered ‘different’ then you are wrong :) The result will be ‘equal’. Why?
Because SQL ignores trailing spaces when comparing strings.

MSDN link:
http://msdn.microsoft.com/en-us/library/ms131092.aspx

And we must remember that .NET DateTime can represent a larger scope of date than the datetime type in SQL Server 2005.

My WSUS database has 1.4 GB size… I was wondering what Microsoft did to achieve this kind of ‘goal’ ;) with just only about 1500 computers inside.  I would like to know which tables take the most space on my HD. That was tbXml with 1GB size… Amazing…

You can find helpful SQL code on Pinal Dave webpage:

http://blog.sqlauthority.com/2007/01/10/sql-server-query-to-find-number-rows-columns-bytesize-for-each-table-in-the-current-database-find-biggest-table-in-database/

Yesterday Microsoft has published security bulletin about vulnerability in SQL Server series. I had to again search how to find my SQL Server service pack number. To avoid this in the future I will post it here :)

SELECT
  SERVERPROPERTY ('ProductVersion'),
  SERVERPROPERTY ('ProductLevel'),
  SERVERPROPERTY ('Edition'),
  SERVERPROPERTY ('ServerName')

--SQL Server stores milliseconds with a precision of 1/300ths of a second.
-- So in other words, milliseconds are stored as:
--.000
--.003
--.007
--.010
--.013
--.017
-- etc.

SET DATEFIRST 1
SET DATEFORMAT ymd

SELECT
    DATEADD(ms, 0, CONVERT(varchar(10), GETDATE(), 120)) as 'StartToday'
    ,DATEADD(ms, -3, CONVERT(varchar(10), GETDATE()+1, 120)) as 'EndToday'

Result:

StartToday              EndToday
----------------------- -----------------------
2008-09-22 00:00:00.000 2008-09-22 23:59:59.997

(1 row(s) affected)

Microsoft SQL Server 2005/2008

Run this code in your database context in SQL Management Studio:

SELECT
    'DROP PROCEDURE [' + name + ']'
as 'CopyThisColumnAndExecuteToDeleteAllProcedures'
FROM
    sys.procedures
WHERE
    [type] = 'P'
    AND is_ms_shipped = 0
    AND [name] NOT LIKE 'sp[_]%diagram%'
ORDER BY
    [name] ASC

Copy generated statements and just run them to DELETE these stored procedures.

Simply the best!

Same method you can use for tables or other objects.

Take a look on pack of very nice date functions, conversions, calculations.

Thanks to Pinal Dave(http://www.SQLAuthority.com) and Vivek Jamwal

—-Yesterday
SELECT DATEADD(d,-1,GETDATE()) 'Yesterday'
—-First Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week'
—-Last Day of Current Week
SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week'
--First Day of Last Week
SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week'

All (much more!) statements you can see in original post on SQLAuthority: here

-- How to get shordate from full date format
-- Input: date (datetime) Output: yyyy-mm-dd (varchar)

SELECT
    CONVERT(VARCHAR(10),GETDATE(),120) as Date

Microsoft SQL Server 2005

I almost finished my current C# project where I have created a lot of stored procedures. By a lot I mean almost 200 :) (I know it is a personal feeling :)) Few days ago I was just curious how many lines I wrote in my stored procedures. To satisfy my curiosity I wrote T-SQL code which show me the truth ;)

Blank lines with just Enter pressed are not counted.

Score for my project database is: 142 stored procedures, 9419 lines (most of all 537 lines in one SP)

@ResultType = 1

@ResultType = 0

So what is your best score? ;) How many SPs and lines did you write in your project? :)

SQL code below.
Read more…

This ISO 8601 weeks standard is mostly in use in corporations and business.

The International Organization for Standardisation, based in Switzerland, issued Standard 8601 — Representation Of Dates And Times, in 1988. This provides some standardization for “week numbers”. Of course, compliance with these standards is entirely voluntary, so your business may or may not use the ISO definitions.

While this provides some standardization, it can lead to unexpected results — namely that the first few days of a year may not be in week 1 at all. Instead, they will be in week 52 of the preceding year! For example, the year 2000 began on Saturday. Under the ISO standard, weeks always begin on a Monday. In 2000, the first Thursday was Jan-6, so week 1 begins the preceding Monday, or Jan-3. Therefore, the first two days of 2000, Jan-1 and Jan-2, fall into week 52 of 1999.

An ISO week number may be between 1 and 53. Under the ISO standard, week 1 will always have at least 4 days. If 1-Jan falls on a Friday, Saturday, or Sunday, the first few days of the year are defined as being in the last (52nd or 53rd) week of the previous year.

Unlike absolute week numbers, not every year will have a week 53. For example, the year 2000 does not have a week 53. Week 52 begins on Monday, 25-Dec, and ends on Sunday, 31-Dec. But the year 2004 does have a week 53, from Monday, 27-Dec , through Friday, 31-Dec.

Under the ISO standard, a week always begins on a Monday, and ends on a Sunday. The first week of a year is that week which contains the first Thursday of the year, or, equivalently, contains Jan-4.

-- Here it is example from SQL Book Online by Microsoft
CREATE FUNCTION [dbo].[ISOweek]  (@DATE datetime)
RETURNS int
AS
BEGIN
   DECLARE @ISOweek int
   SET @ISOweek= DATEPART(wk,@DATE)+1
      -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0)
      SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
         AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END

Read this article if you want to learn more about week number calculations.
http://www.cpearson.com/excel/weeknum.htm

Remember to add always:

SET DATEFIRST 1

before any date calculation in this standard

Example of use above User Definied Function:

SET DATEFIRST 1

SELECT dbo.ISOweek(getdate())