Sunsick's Tech Blog: June 2020

Tuesday, 23 June 2020

[SQL Server] Use custom color for SSMS(SQL Server Management Studio) status bar

[SQL Server] Use custom color for SSMS(SQL Server Management Studio) status bar

If you manage several SQL Servers, it might be difficult to identify which server you are connected.

Default status bar

This is the default color of the status bar.

They are always the same unless you change the color.

Change the color of the status bar

Connect to the new server, press "options >> ".

Check "Use custom color" and press "Select...".

Select the color.

Press "Connect".

You can see that the color of the status bar has been changed to the selected color.

Continuously change the color of the status bar

Connect to the new server, press "options >> ".

As you can see, the previous color has been selected. You need to change the color by press "Select..." and change the color.

You can see that the color has been changed.
Press "Connect".

Check changed colors of the status bar

The firstly connected server.

The secondly connected server.

Tip!
You can save the colors for each server using Registered Servers.

[SQL Server] Use "USE Command" with OPENQUERY to Linked Server

Sometimes, you have to use "USE Command" with OPENQUERY to Linked Server.

Maybe you already tried this.

SELECT * FROM OPENQUERY
 (LinkedServer]
 ,'USE AdventureWorks2016;
 EXEC dbo.usp_test1;')
;

But it does not work.
You see the following error message.

Msg 7357, Level 16, State 1, Line 12

Cannot process the object "USE AdventureWorks2016; EXEC dbo.usp_test1;". The OLE DB provider "SQLNCLI11" for linked server

"LinkedServer" indicates that either the object has no columns or the current user does not have permissions on that object.

You can use "USE Command" by wrapping with "EXEC" command like this.

SELECT * FROM OPENQUERY
       ([LinkedServer],'
       EXEC (
             ''USE AdventureWorks2016;
             EXEC dbo.usp_test1;''
             );'
       )
;

This will work.

Monday, 15 June 2020

[SQL Server] CTE vs LEAD/LAG function

[SQL Server] CTE vs LEAD/LAG function

LEAD provides access to a row at a given physical offset that follows the current row.
LAG provides access to a row at a given physical offset that comes before the current row.

Source Data
Script

-- Source Data Query
SELECT ProductID, 
       ModifiedDate, 
       StandardCost
FROM Production.ProductCostHistory
WHERE ProductID IN(711, 712, 713)
ORDER BY ProductID, 
         ModifiedDate
;

Result

Query Comparisons
CTE + LEFT JOIN
Script

-- Using CTE + LEFT JOIN
WITH LAG_LEAD_CTE
     AS (SELECT ROW_NUMBER() OVER(
                ORDER BY ProductID, 
                         ModifiedDate) AS RN, 
                ProductID, 
                ModifiedDate, 
                StandardCost
         FROM Production.ProductCostHistory
         WHERE ProductID IN(711, 712, 713))
     SELECT A.ProductID, 
            A.StandardCost, 
            A.ModifiedDate, 
            B.StandardCost AS PrevStandardCost, 
            C.StandardCost AS NextStandardCost
     FROM LAG_LEAD_CTE A
          LEFT JOIN LAG_LEAD_CTE B ON A.ProductID = B.ProductID
                                      AND A.RN = B.RN + 1
          LEFT JOIN LAG_LEAD_CTE C ON A.ProductID = C.ProductID
                                      AND A.RN = C.RN - 1
     ORDER BY A.ProductID, 
              A.ModifiedDate
;

Result

Use ROW_NUMBER in order to compare row offset. And join with the previous row and the next row.

LEAD, LAG
Script

-- Using LEAD, LAG function
SELECT ProductID, 
       StandardCost, 
       ModifiedDate, 
       LAG(StandardCost, 1, NULL) OVER(PARTITION BY ProductID
       ORDER BY ModifiedDate) PrevStandardCost, 
       LEAD(StandardCost, 1, NULL) OVER(PARTITION BY ProductID
       ORDER BY ModifiedDate) NextStandardCost
FROM Production.ProductCostHistory
WHERE ProductID IN(711, 712, 713)
ORDER BY ProductID, 
         ModifiedDate
;

Result

The result is the same as CTE + LEFT JOIN. But it's way much simpler that that.

Performance Comparisons
CTE + LEFT JOIN

(9 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ProductCostHistory'. Scan count 9, logical reads 18, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

LEAD, LAG

(9 rows affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ProductCostHistory'. Scan count 3, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Using LEAD, LAG is way much better than CTE + LEFT JOIN.
(Of course, it depends on the situation, but it general)

References

LEAD (Transact-SQL): https://docs.microsoft.com/en-us/sql/t-sql/functions/lead-transact-sql?view=sql-server-ver15
LAG (Transact-SQL): https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15

Sunday, 14 June 2020

[SQL Server] EOMONTH - the last day of the month

[SQL Server] EOMONTH - the last day of the month

From SQL Server 2012, EOMENTH system function can be used for getting the last day of the month containing a specified date.

Before SQL Server 2012
Script

-- Before SQL Server 2012.
DECLARE @DT VARCHAR(10);
SET @DT = '2019-02-20';

SELECT DATEADD(d, -1, DATEADD(m, 1, CONVERT(DATE, LEFT(@DT, 8) + '01'))) AS 'the last day of the month', 
       CONVERT(DATE, CONVERT(CHAR(8), @DT) + '01') AS 'the first day of the month'
GO

Result

Using EOMONTH
Script

-- Using EOMONTH Function
DECLARE @DT VARCHAR(10);
SET @DT = '2019-02-20';

SELECT EOMONTH(@DT) AS 'the last day of the month', 
       CONVERT(DATE, CONVERT(CHAR(8), @DT) + '01') AS 'the first day of the month'
GO

Result

References

EOMONTH (Transact-SQL): https://docs.microsoft.com/en-us/sql/t-sql/functions/eomonth-transact-sql?view=sql-server-ver15

[SQL Server] Simple ISDATE Test

[SQL Server] Simple ISDATE Test

ISDATE System function returns 1(true) when the expression is a valid date(datetime) type.

Script

SELECT ISDATE('20190101') AS '1', 
       ISDATE('2019/01/01') AS '2', 
       ISDATE('2019-01-01') AS '3', 
       ISDATE('19-01-01') AS '4', 
       ISDATE('2019-04-31') AS '5', 
       ISDATE(NULL) AS '6', 
       ISDATE('201l-12-31') AS '7', 
       ISDATE('123') AS '8', 
       ISDATE('2019-01-01 15:55:59.455') AS '9', 
       ISDATE('2019-01-01 15:55:59.4551') AS '10', 
       ISDATE('1752-12-31') AS '11', 
       ISDATE('1753-01-01') AS '12', 
       ISDATE('2999-01-01') AS '13', 
       ISDATE('9999-12-31') AS '14', 
       ISDATE('01/2019/12') AS '15';

Result

Description

Standard ISO Type. Returns "1".
Standard JAPAN type. Returns "1"
Standard ISO8601 (Also ODBC canonical) type. Returns "1"
Cannot recognize "19" as YEAR. Returns "0"
April 31st does not exist. Returns "0"
NULL does not regard as a date type. Returns "0"
There's "l", not "1". Returns "0"
It's a numeric type. Returns "0"
Standard ODBC canonical (with milliseconds) type. Returns "1"
Look like Standard ODBC canonical (with milliseconds) type. But it's not because of the additional "1". Returns "0"
This is because SQL Server Date type uses the Gregorian calendar and originally from Sysbase Database. SQL Server cannot recognize the date before 1753. Returns "0"
From January 1st, 1753, there's no problem to recognize to a date type. Returns "1"
Standard ISO8601 type. Returns "1"
Standard ISO8601 type. Returns "1"
Recognizes as January 12th, 2019. Returns "1"

Reference

CAST and CONVERT (Transact-SQL) - Date and Time styles: https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver15#date-and-time-styles
ISDATE (Transact-SQL): https://docs.microsoft.com/en-us/sql/t-sql/functions/isdate-transact-sql?view=sql-server-ver15

[SQL Server] Simple ISNUMERIC Test

[SQL Server] Simple ISNUMERIC Test

ISNUMERIC System function returns 1(true) when the expression is a valid numeric type.

Script

SELECT ISNUMERIC('1234') AS '1', 
       ISNUMERIC('A587') AS '2', 
       ISNUMERIC('222!') AS '3', 
       ISNUMERIC('123l123') AS '4', 
       ISNUMERIC('157.123') AS '5', 
       ISNUMERIC(NULL) AS '6', 
       ISNUMERIC('') AS '7', 
       ISNUMERIC('0') AS '8', 
       ISNUMERIC('12345678901234567890') AS '9', 
       ISNUMERIC('1234567890123456789012345678901234567890123456789012345678901234567890') AS '10', 
       ISNUMERIC('1.234567890123456789012345678901234567890123456789012345678901234567890') AS '11';

Result

Description

Returns "1".
Includes a character - A. Returns "0"
Includes a special character - !. Returns "0"
Includes a character (looks like 1, but it's not) - l. Returns "0"
Returns "1"
NULL does not regard as a numeric type. Returns "0".
Blank does not regard as a numeric type. Returns "0".
Returns "1".
It's bigger than BIGINT type, but still a numeric type. Returns "1"
Very big number(70 digits), but still a numeric type. Returns "1"
69 places of decimals, but still a numeric type. Returns "1"

Reference

ISNUMERIC (Transact-SQL): https://docs.microsoft.com/en-us/sql/t-sql/functions/isnumeric-transact-sql?view=sql-server-ver15

[SQL Server] TRY_CAST, TRY_CONVERT

[SQL Server] TRY_CAST, TRY_CONVERT

TRY_CAST and TRY_CONVERT are basically the same as CAST and CONVERT. The big difference is that TRY_CAST and TRY_CONVERT return NULL when they fail to CONVERT data type.

TRY_CAST and TRY_CONVERT are the same but different expression the same as CAST and CONVERT.

CONVERT

DECLARE @INPUT VARCHAR(100)

SET @INPUT = '100,2';

SELECT CONVERT(FLOAT,@INPUT) AS INPUT_CONVERT;

Msg 8114, Level 16, State 5, Line 5

Error converting data type varchar to float.

TRY_CONVERT

DECLARE @INPUT VARCHAR(100)

SET @INPUT = '100,2';

SELECT TRY_CONVERT(FLOAT,@INPUT) AS INPUT_CONVERT;

This is also useful when you have to join 2 tables with the different data types and you can ignore unmatched columns.

e.g.

SELECT
 tbl1.Col1,
 tbl2.Col2
FROM
 Table1 tbl1 INNER JOIN Tabl2 tbl2 ON tbl1.ID = TRY_CONVERT(INT,tbl2.KeyID)

Tuesday, 2 June 2020

[SQL Server] Simple experiment - Insert blank into the numeric column

[SQL Server] Simple experiment - Insert blank into the numeric column

What will happen if you insert blank value to the numric type columns?

Let's try it.
Create a table.

CREATE TABLE dbo.tbl_NumberTest
(
	SEQ           INT IDENTITY(1, 1) NOT NULL, 
	DATATYPE      VARCHAR(200) NOT NULL, 
	ColBigInt     BIGINT, 
	ColBit        BIT, 
	ColDecimal    DECIMAL(5, 2), 
	ColInt        INT, 
	ColMoney      MONEY, 
	ColNumeric    NUMERIC(5, 2), 
	ColSmallInt   SMALLINT, 
	ColSmallMoney SMALLMONEY, 
	ColTinyInt    TINYINT, 
	ColFloat      FLOAT, 
	ColReal       REAL
);
GO



Insert some data

INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColBigInt) VALUES ('ColBigInt', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColBit) VALUES ('ColBit', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColDecimal) VALUES ('ColDecimal', '');
Msg 8114, Level 16, State 5, Line 27
Error converting data type varchar to numeric.
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColInt) VALUES ('ColInt', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColMoney) VALUES ('ColMoney', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColNumeric) VALUES ('ColNumeric', '');
Msg 8114, Level 16, State 5, Line 37
Error converting data type varchar to numeric.
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColSmallInt) VALUES ('ColSmallInt', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColSmallMoney) VALUES ('ColSmallMoney', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColTinyInt) VALUES ('ColTinyInt', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColFloat) VALUES ('ColFloat', '');
(1 row affected)
 
INSERT INTO dbo.tbl_NumberTest (DATATYPE, ColReal) VALUES ('ColReal', '');
(1 row affected)



You can see that some of them had failed.

Select data

SELECT * FROM dbo.tbl_NumberTest;

Conclusion

As you can see, by default, they had been inserted as "0" without errors except NUMERIC type and DECIMAL Type.

- DECIMAL type = NUMERIC Type

[SQL Server] Set a database to a single-user mode

[SQL Server] Set a database to a single-user mode

Set a database to a single user - in order to have maintenance or something.

Test Steps
How to change a database to a single-user mode with T-SQL

ALTER DATABASE SingleDBTest
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO

Check from Object Explorer

Test with T-SQL

USE SingleDBTest
GO