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

To insert into database NULL value from C# code we can’t use just simple declaration for empty string like string textToInsert = “”; This will insert into database just empty string instead NULL value.

To handle this case you have to use special SQL type: System.Data.SqlTypes.SqlString.Null

Dummy example (do nothing special but shows the way) below:

using (SqlConnection cn = new SqlConnection(connectionString))
{
    string sqlText = "stored_procedure_name";
    using (SqlCommand cmd = new SqlCommand(sqlText, cn))
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.Add("@TaskType", SqlDbType.NVarChar, 255).Value = System.Data.SqlTypes.SqlString.Null;

        try
        {
            cn.Open();
            cmd.ExecuteNonQuery();
        }
        catch (SqlException sqlEx)
        {
        }

    }
}

System.Data.SqlTypes namespace has all SQL Server types so if you need to insert NULL value then use suitable one for your column data type.

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