Showing posts with label Select. Show all posts
Showing posts with label Select. Show all posts

Tuesday, June 16, 2015

PDW to find out which table are external table

Normal source of getting all the table list in the database.
  1. INFORMATION_SCHEMA.TABLES
  2. sys.tables
  3. sys.objects
  4. sys.all_objects
  5. OBJECTPROPERTY ()
  6. OBJECTPROPERTYEX ()
However, none of above can tell you which table is external table. In order to find out which table are external table, you will have to look into sys.external_tables.

Query
SELECT *
FROM sys.external_tables

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.

Saturday, April 26, 2014

Display MSSQL PDW table rows by node, distribution, partition and COLUMNSTORE state


DBCC PDW_SHOWSPACEUSED () - Displays the number of rows, disk space reserved, and disk space used for a table in a SQL Server PDW database, or displays the disk space reserved and used for an entire SQL Server PDW database.

Advantages
- Useful to check data skew by distribution and node.

Disadvantages
- Not able to display data that sit on delta store, causing confusion that there are no record in the table.
- Not able to further filter.
- Multiple execution require for different table.

DBCC PDW_SHOWPARTITIONSTATS() - Displays the size and number of rows for each partition of a table in a SQL Server PDW database.

Advantages
- Display all row count regardless the data sit on delta store or column store.

Disadvantages
- Does not indicate how many rows of record sit on delta store.
- Not able to further filter.
- Multiple execution require for different table.

Query
SELECT
       Table_Name = OBJECT_NAME(pdw_table_mappings.object_id)
,      Table_Distribution = CASE WHEN pdw_table_distribution_properties.distribution_policy_desc = 'HASH' THEN 'DISTRIBUTE' ELSE pdw_table_distribution_properties.distribution_policy_desc END
,      Index_Type = indexes.type_desc
,      PDW_Node = pdw_nodes_partitions.pdw_node_id
,      Distribution = pdw_distributions.distribution_id
,      Partition_Number = pdw_nodes_partitions.partition_number
,      Partition_Position = CASE WHEN partition_functions.boundary_value_on_right = 1 THEN 'RIGHT' WHEN partition_functions.boundary_value_on_right = 0 THEN 'LEFT' END
,      Partition_Value = partition_range_values.value
,      Rows = CASE WHEN indexes.type IN (0,1) THEN
                           pdw_nodes_partitions.rows
                     ELSE
                           pdw_nodes_column_store_row_groups.total_rows
                     END
,      ColumnStore_State = pdw_nodes_column_store_row_groups.state_description

FROM   sys.pdw_table_mappings

              LEFT JOIN sys.indexes
              ON indexes.object_id = pdw_table_mappings.object_id
              AND indexes.type <> 2

              LEFT JOIN sys.pdw_table_distribution_properties
              ON pdw_table_distribution_properties.object_id = pdw_table_mappings.object_id
                          
              LEFT JOIN sys.pdw_nodes_tables
              ON pdw_nodes_tables.name = pdw_table_mappings.physical_name
             
              LEFT JOIN sys.pdw_distributions
              ON ((pdw_table_distribution_properties.distribution_policy = 2 AND pdw_distributions.pdw_node_id = pdw_nodes_tables.pdw_node_id) OR 1 = 2)
              AND ((pdw_table_distribution_properties.distribution_policy = 2 AND pdw_distributions.name = RIGHT(pdw_nodes_tables.name, 1)) OR 1 = 2)

              LEFT JOIN (   SELECT object_id, index_id, partition_number, pdw_node_id, rows = sum(rows)
                                  FROM sys.pdw_nodes_partitions
                                  GROUP BY object_id, index_id, partition_number, pdw_node_id)pdw_nodes_partitions
              ON pdw_nodes_partitions.object_id = pdw_nodes_tables.object_id
              AND pdw_nodes_partitions.pdw_node_id = pdw_nodes_tables.pdw_node_id
              AND pdw_nodes_partitions.index_id = indexes.index_id
             
              LEFT JOIN sys.partitions
              ON partitions.object_id = indexes.object_id
              AND partitions.index_id = indexes.index_id
              AND partitions.partition_number = pdw_nodes_partitions.partition_number
             
              LEFT JOIN sys.partition_schemes
              ON partition_schemes.data_space_id = indexes.data_space_id

              LEFT JOIN sys.partition_functions
              ON partition_functions.function_id = partition_schemes.function_id

              LEFT JOIN sys.partition_range_values
              ON partition_range_values.function_id = partition_functions.function_id
              AND COALESCE(partition_range_values.boundary_id + 1, 1) = partitions.partition_number   
                    
              LEFT JOIN (   SELECT object_id, index_id, partition_number, state_description, pdw_node_id, total_rows = SUM(total_rows)
                                  FROM sys.pdw_nodes_column_store_row_groups
                                  WHERE total_rows IS NOT NULL
                                  GROUP BY object_id, index_id, partition_number, state_description, pdw_node_id)pdw_nodes_column_store_row_groups
              ON pdw_nodes_column_store_row_groups.object_id = pdw_nodes_partitions.object_id
              AND pdw_nodes_column_store_row_groups.index_id = pdw_nodes_partitions.index_id
              AND pdw_nodes_column_store_row_groups.partition_number = pdw_nodes_partitions.partition_number
              AND pdw_nodes_column_store_row_groups.pdw_node_id = pdw_nodes_partitions.pdw_node_id

Output



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