Showing posts with label MSSQL. Show all posts
Showing posts with label MSSQL. Show all posts

Wednesday, October 29, 2014

NULL comparison

Today i had learn a pain lesson of comparing null value with a string.

Lets create a table with 2 column and 2 rows.
Query
DECLARE @Test TABLE(Col1 INT, Col2 INT)
INSERT INTO @Test VALUES (1,2),(2,NULL)

Output

The mistake i had done with following query; the original intention is to get all data except row no.2 which happen the be null at column 2 (filtering condition).

Query
SELECT *
FROM @Test
WHERE Col2 <> 2

Output

There are multiple way to address this issue however i would like to show why the comparison was correct in the way it is.
SELECT CASE WHEN 1 = NULL THEN 1
WHEN 1 <> NULL THEN 2
ELSE 3 END

As you can see, NULL is not comparable, it is not "True" or "False" but "Unknown". For more details please read this.

SET ANSI_NULLS OFF will not help in this scenario as it is not comparing null with null. If will only be helpful if the where clause like
1. WHERE Col2 = NULL
2. WHERE Col2 <> NULL

Solution:
1. Update the source column to other valid value before comparing.
2. Using COALESCE() or ISNULL() to replace null value to other valid value before comparing.
3. Address the null value in the where clause. (WHERE Col2 <> 2 OR Col2 IS NULL)

Tuesday, October 28, 2014

NOLOCK vs READPAST

Comparison of different kind of dirty-read.

Lets create a table with 5 rows of data to start the testing.
CREATE TABLE Test_Locking (Col1 int)
INSERT INTO Test_Locking VALUES (1),(2),(3),(4),(5)

Check the row count of the table before we start.
SELECT COUNT(*) FROM Test_Locking

Why sometime NOLOCK/READPAST is needed? You might had to read this.
A 5 row record table took more than 2 minute without returning any result.

We will be testing all of the DML (Data Manipulation Language) with OPEN transaction; without any commit and rollback activities.

For insert scenario:
Query
BEGIN TRANSACTION
       INSERT INTO Test_Locking VALUES (6)

Output

Execute "ROLLBACK TRANSACTION" after the result were shown.

For update scenario:
Query
BEGIN TRANSACTION
       UPDATE Test_Locking SET Col1 = 6 WHERE Col1 = 1

Output

Execute "ROLLBACK TRANSACTION" after the result were shown.

For delete scenario
BEGIN TRANSACTION
       DELETE Test_Locking WHERE Col1 = 1

Output

Execute "ROLLBACK TRANSACTION" after the result were shown.

Tuesday, April 15, 2014

Add 1 hour to datetime column

Add an hour to datetime column with decimal value

Query
DECLARE @DateTime DATETIME = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)

SELECT
      @DateTime
,     DATEADD(HOUR, 1, @DateTime)
,     @DateTime + 0.04166667

Output

Tuesday, January 7, 2014

Find data in each database (excluded system database)

Using SQL Server undocumented Stored Procedure (sp_msforeachdb) to locate data appear in any column in each database

Query
sp_msforeachdb '
DECLARE @SearchText VARCHAR(MAX) = ''a''

DECLARE @SQL VARCHAR(MAX) = ''SELECT [Database Name] = ''''?'''', [Schema Name] = TABLESCHEMA, [Table Name] = TABLENAME, [Column Name] = COLUMNNAME, [Occur Count] = SUM(CNT) FROM ('' + STUFF(
            (SELECT     '' UNION ALL SELECT TABLESCHEMA = '''''' + TABLES.TABLE_SCHEMA + '''''', TABLENAME = '''''' + TABLES.TABLE_NAME + '''''', COLUMNNAME = CAST('''''' + COLUMN_NAME + '''''' AS VARCHAR(MAX)), CNT = 1 FROM ?.['' + TABLES.TABLE_SCHEMA + ''].['' + TABLES.TABLE_NAME + ''] WHERE '' + CASE WHEN DATA_TYPE = ''image'' THEN ''CAST('' ELSE '''' END + ''CAST(['' + COLUMN_NAME + '']'' + CASE WHEN DATA_TYPE = ''image'' THEN '' AS VARBINARY(MAX))'' ELSE '''' END + '' AS VARCHAR(MAX)) LIKE ''''%'' + @SearchText + ''%''''''
            FROM  ?.INFORMATION_SCHEMA.TABLES
                  JOIN ?.INFORMATION_SCHEMA.COLUMNS
                  ON TABLES.TABLE_CATALOG = COLUMNS.TABLE_CATALOG
                  AND TABLES.TABLE_SCHEMA = COLUMNS.TABLE_SCHEMA
                  AND TABLES.TABLE_NAME = COLUMNS.TABLE_NAME
            WHERE TABLES.TABLE_TYPE = ''BASE TABLE'' AND TABLES.TABLE_CATALOG NOT IN (''master'',''model'',''msdb'',''tempdb'')
            FOR XML PATH(''''))
            , 1, 11, '''') + '')Src GROUP BY TABLESCHEMA, TABLENAME, COLUMNNAME''
EXEC (@SQL)'

Output


Reference
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=190619

Sunday, December 29, 2013

Update column with random range number

create random number that fall between range (0.28 - 0.57).

Query
SELECT      0.28 +
            (SELECT     ((ROUND((RandumNumber.RandumNumber*100)/3,0))/100) -
                        (ROUND((RandumNumber.RandumNumber*100)/25,0)/100)

            FROM        (SELECT    (ABS(CHECKSUM(NEWID()))%100)/100.0 AS                             RandumNumber)RandumNumber)
GO 5

Output

Reference
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=190503

Thursday, December 19, 2013

Find table used in agent jobs step with SSIS

Find table that been used inside SSIS package by using system table.

Query
SELECT
      job.name
,     job.description
,     jobstep.step_name
,     [SSIS Path] = folder.foldername + '\' + package.name
FROM  sysjobs job
      JOIN sysjobsteps jobstep
      ON job.job_id = jobstep.job_id
      AND jobstep.subsystem = 'SSIS'
      AND (jobstep.command LIKE '%/SERVER ' + @@SERVERNAME + '%'
            OR jobstep.command LIKE '%/SERVER "(local)"%'
            OR jobstep.command LIKE '%/SERVER "."%')
      JOIN sysssispackagefolders folder
      JOIN sysssispackages package
      ON folder.folderid = package.folderid
      ON jobstep.command LIKE '%' + folder.foldername + '\' + package.name + '%'
WHERE CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)) LIKE '% TABLE NAME %'
      and job.enabled = 1

Tuesday, December 17, 2013

Get working days/weekday count

To get the number of weekdays/ working day (exclude Saturday and Sunday) between 2 dates.

Query
DECLARE @startdate DATETIME = '20130501', @enddate DATETIME = '20130531'

;WITH CTE AS(
      SELECT      @startdate AS Dates
      UNION ALL
      SELECT      Dates + 1
      FROM  CTE
      WHERE Dates < @enddate
)
SELECT
      [Date Column] = Dates,
      [English name of the day] = DATENAME(dw, Dates),
      [Formula] = DATEDIFF(DAY, 0, Dates)%7
FROM  CTE


As you can refer, the [Formula] column close together with [English name of the day]. We can easily filter Saturday and Sunday from the query by apply (WHERE DATENAME(dw, Dates) NOT IN ('Saturday', 'Sunday')). However, what if the server are supporting multi-languages? Something like below.

To solve this issue, the [Formula] came in place to solve the issue. By apply (WHERE DATEDIFF(DAY, 0, Dates)%7 < 5), weekends (Saturday and Sunday) are filtered, disregard the database languages.

Sunday, November 10, 2013

Identify marble category with rule


Query
;WITH Mable AS(
       SELECT 1 Mable
       UNION ALL
       SELECT Mable + 1
       FROM Mable
       WHERE Mable < 100
),     GatesA AS(
SELECT
       *, HopperA = CASE WHEN Mable % 3 = 0 THEN 1 ELSE 0 END
FROM   Mable
),     GatesB AS(
SELECT *, HopperB = ROW_NUMBER() OVER (ORDER BY Mable) % 4
FROM   GatesA
WHERE  HopperA <> 1)
SELECT
       SUM(CASE WHEN A.HopperA = 1 THEN 1 ELSE 0 END)
,      SUM(CASE WHEN B.HopperB = 0 THEN 1 ELSE 0 END)
,      COUNT(1) 
       - SUM(CASE WHEN A.HopperA = 1 THEN 1 ELSE 0 END) 
       - SUM(CASE WHEN B.HopperB = 0 THEN 1 ELSE 0 END)
FROM   GatesA A
       LEFT JOIN GatesB B
       ON A.Mable = B.Mable

Tuned Query by khtan
;WITH Marble AS(
       SELECT 1 Marble
       UNION ALL
       SELECT Marble + 1
       FROM Marble
       WHERE Marble < 100
)
SELECT
       SUM(CASE WHEN Marble % 3 = 0 THEN 1 ELSE 0 END)
,      SUM(CASE WHEN (Marble + 1) % 6 = 0 THEN 1 ELSE 0 END)
,      COUNT(1)
- SUM(CASE WHEN Marble % 3 = 0 THEN 1 ELSE 0 END)
- SUM(CASE WHEN (Marble + 1) % 6 = 0 THEN 1 ELSE 0 END)
FROM  Marble

How it work?
First step, understand and list down the marble flow.

Second Step, Identify which marble should enter red bucket. This can easily achieve by using modulo 3, any number that does not have any remainder should enter red bucket. In this case, marble 3, 6, 9, 12, 15... should enter red bucket.

Third Step, Identify which marble should enter aqua bucket. After removing all the marble that enter red bucket, we had to identify the FOURTH marble that should enter aqua bucket. This can be achieve by re-numbering the marble and modulo 4.
However, there were much simple solution by using maths. Notice that every gap of marble that fall into Aqua Bucket is 6. For example, range 5 to 0 is 6, range 11 to 6 is 6, range 17 to 12 is 6. 

We could utilize modulo 6 for this solution. But if we blindly utilize modulo 6, the result will be incorrect as there were no marble named 0; therefore we had to add 1 into original numbering to achieve the required result.

Therefore, adding 1 to the original numbering (referring to Number + 1 column) shows that multiplication of six (later modulo 6) will be the marble that should enter red bucket.
Inspired by khtan
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=189443&SearchTerms=marble

Thursday, November 7, 2013

Assign N range of number to row


Sample Data
DECLARE @RandomNumber TABLE(Col1 int)
INSERT INTO @RandomNumber VALUES (102),(523),(413),(9),(126),(83091),(36)


Query
DECLARE @n INT = 3

SELECT
       *
,      AssignedNumber = (ROW_NUMBER() OVER (ORDER BY Col1) - 1) % @n + 1
FROM   @RandomNumber

Desired Output

Inspired by Ifor
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=189487

Tuesday, October 22, 2013

Get data inside text file (.txt) that stored in Image Column

Create a new text file named Text File.txt located at D:\MSSQL.

Enable Ad hoc query for SQL Server
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO

EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

Create a table with image data type column
CREATE TABLE TextFile(TextFileData image)

Insert the text file as a whole into SQL Server
INSERT INTO TextFile(TextFileData)
SELECT * FROM
OPENROWSET(BULK N'D:\MSSQL\Text File.txt', SINGLE_BLOB) AS Document

Check the data are inserted correctly

Now, convert the data into readable sentence.
DECLARE @Output VARCHAR(MAX) = (SELECT CAST(CAST(TextFileData AS VARBINARY(MAX)) AS VARCHAR(MAX)) FROM TextFile)


PRINT @Output

Desired Output

Tuesday, October 8, 2013

Refill identity gap with new data (compare together with old data)


Sample Data (Original Table)
DECLARE @Sample TABLE(IdentityColumn INT,IDcolumn VARCHAR,PRIMARY KEY(IdentityColumn, IDcolumn))

INSERT INTO @Sample SELECT
1, 'a' UNION ALL SELECT
2, 'b' UNION ALL SELECT
4, 'c' UNION ALL SELECT
5, 'd' UNION ALL SELECT
8, 'e'



Sample Data (Refill Data Table)
DECLARE @Refill TABLE(IDcolumn VARCHAR PRIMARY KEY)                                         
INSERT INTO @Refill SELECT
'f' UNION ALL SELECT
'g' UNION ALL SELECT
'h' UNION ALL SELECT
'c' UNION ALL SELECT
'd'


Query
;WITH TotalRowCount AS(
       SELECT
              Cnt = COUNT(1)
       FROM   (SELECT
                     IDcolumn1 = Sample.IDcolumn,
                     Refill.IDcolumn
              FROM   @Sample Sample
FULL JOIN @Refill Refill
                     ON Sample.IDcolumn = Refill.IDcolumn)Temp
), RebuildRowNumber AS(
       SELECT
              Cnt = 1
       UNION ALL
       SELECT
              Cnt = RebuildRowNumber.Cnt + 1
       FROM   RebuildRowNumber
              JOIN TotalRowCount
              ON RebuildRowNumber.Cnt < TotalRowCount.Cnt
), MissingGap AS(
       SELECT
              RebuildRowNumber.Cnt,
              Refill.IDcolumn
       FROM   RebuildRowNumber
              LEFT JOIN @Sample Sample
              ON RebuildRowNumber.Cnt = Sample.IdentityColumn
              FULL JOIN @Refill Refill
              ON Sample.IDcolumn = Refill.IDcolumn
       WHERE  Sample.IDcolumn IS NULL
), MakeFullSet AS(
       SELECT
              MissingGap1.Cnt,
              MissingGap2.IDcolumn
       FROM   MissingGap MissingGap1,
              MissingGap MissingGap2
       WHERE  MissingGap2.IDcolumn IS NOT NULL
              AND MissingGap1.Cnt IS NOT NULL
), FindUnique AS(
       SELECT
              RN = ROW_NUMBER() OVER (PARTITION BY IDcolumn ORDER BY Cnt) - DENSE_RANK()
                   OVER (ORDER BY IDcolumn),
              *
       FROM   MakeFullSet
)
INSERT INTO @Sample
SELECT
       Cnt,
       IDcolumn
FROM   FindUnique
WHERE  RN = 0

SELECT *

FROM   @Sample

Desired Output