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.

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/

When recently almost 1500 new computers reported in our WSUS I thought, damn, must to move them from Unassigned Computers group into suitable groups (notebooks, desktops etc.) for easiest management. Unfortunatelly it is a big pain on ass (we don’t use client targeting option in our environment) because of:

- there is no drag & drop in WSUS console (!)

- if you select a lot of computers manually and choose from menu to move them into another group the WSUS Console MMC often start to freeze :/

So.. I look on this 1500 computers and was really in bad mood because of necessity to spend few hours on manually selection (by Ctrl+click) computers and choosing move command from context menu… H+O+R+R+I+B+L+E ;)

Fortunately we have access to WSUS database, so after short investigation and few tries I wrote SQL code which move all computers (selected by you through computer name pattern)  from Unassigned Computers group into group of your choice. After few minutes and few modifications (computer names pattern) the task is done.

I use it on WSUS 3.0 SP1 database. The code is selfexplained I hope. Enter your data in lines with ++++++ Change it to your needs ++++++ comment and run in WSUS database context.

SQL code below.
Read more…

--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())

The function determines if it is a leap year or not.  Basically, to determine if it is a leap year, either of the following conditions must be met:

• The year must be divisible by 4 and must NOT be divisible by 100.
• The year must be divisible by 400.

Original article: http://www.sql-server-helper.com/functions/is-leap-year.aspx

--User definied function to get number of days

CREATE FUNCTION [dbo].[ufn_IsLeapYear] ( @pDate    DATETIME )
RETURNS BIT
AS
BEGIN

IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
YEAR( @pDate ) % 400 = 0
RETURN 1

RETURN 0

END
GO

Example of use:

DECLARE @DaysInYear INT

SET @DaysInYear = 365 + [dbo].[ufn_IsLeapYear] (GETDATE())