Category: SQL Tips

The ISNULL function and the COALESCE expression have a similar purpose but ISNULL can have two parameters only, COALESCE can have multiple input.

Both are checking the parameters in order and returns the current value of the first expression that initially doesn’t evaluate to NULL.

SELECT ISNULL(hourly_wage * 40 * 52, salary) AS income
FROM dbo.wages

SELECT COALESCE(hourly_wage * 40 * 52, salary, commission) AS income
FROM dbo.wages

The NULLIF function returns a NULL value if the two parameters are equal. for instance, we could use NULLIF if we wanted to return NULLs whenever the field equal giving value:

SELECT NULLIF(4,4) AS Same, NULLIF(5,7) AS Different;

The msg_str argument in RAISERROR (Transact-SQL) described the structure of message string and the use of parameters in the string. It’s very useful if we want to build dynamic text or string with multiple parameters. In this article, we are listed variety of format sample.

Conversion specifications format

% [[flag] [width] [. precision] [{h | l}]] type

flag

Is a code that determines the spacing and justification of the substituted value.

width

Is an integer that defines the minimum width for the field into which the argument value is placed. If the length of the argument value is equal to or longer than width, the value is printed with no padding. If the value is shorter than width, the value is padded to the length specified in width.

An asterisk (*) means that the width is specified by the associated argument in the argument list, which must be an integer value.

precision

Is the maximum number of characters taken from the argument value for string values. For example, if a string has five characters and precision is 3, only the first three characters of the string value are used.

For integer values, precision is the minimum number of digits printed.

An asterisk (*) means that the precision is specified by the associated argument in the argument list, which must be an integer value.

{h | l} type

Is used with character types d, i, o, s, x, X, or u, and creates shortint (h) or longint (l) values.

Examples

string format

SELECT FORMATMESSAGE(‘Hello %s!’, ‘TEST’);
SELECT FORMATMESSAGE(‘Hello %20s!’, ‘TEST’);
SELECT FORMATMESSAGE(‘Hello %-20s!’, ‘TEST’);
SELECT FORMATMESSAGE(‘Hello % 20s!’, ‘TEST’);
SELECT FORMATMESSAGE(‘Hello %5.3s!’, ‘TEST12345678’);

number format

SELECT FORMATMESSAGE(‘Signed int %i, %d %i, %d, %+i, %+d, %+i, %+d’, 5, -5, 50, -50, -11, -11, 11, 11);
SELECT FORMATMESSAGE(‘Signed int with up to 3 leading zeros %03i’, 5);
SELECT FORMATMESSAGE(‘Signed int with up to 20 leading zeros %020i’, 5);
SELECT FORMATMESSAGE(‘Signed int with leading zero 0 %020i’, -55);
SELECT FORMATMESSAGE(‘Bigint %I64d’, 3000000000);
SELECT FORMATMESSAGE(‘Unsigned int %u, %u’, 50, -50);
SELECT FORMATMESSAGE(‘Unsigned octal %o, %o’, 50, -50);
SELECT FORMATMESSAGE(‘Unsigned hexadecimal %x, %X, %X, %X, %x’, 11, 11, -11, 50, -50);
SELECT FORMATMESSAGE(‘Unsigned octal with prefix: %#o, %#o’, 50, -50);
SELECT FORMATMESSAGE(‘Unsigned hexadecimal with prefix: %#x, %#X, %#X, %X, %x’, 11, 11, -11, 50, -50);

SQL Server has a couple of inbuilt functions to get the day of week from the given date. To get the name of the day of week, you can use DATENAME function and to get the number of the day of week, you can use DATEPART function.

Example

SELECT DATENAME(WEEKDAY, GETDATE())
/* Result */
Monday

SELECT DATEPART(WEEKDAY, GETDATE())
/* Result */
2

By default, SQL server always uses Sunday as the first day of the week. You can use SET DATEFIRST 1 to set Monday as the first day of the week.

Issue

I am trying to get all products and category name if category is still active with below left join script. But I cannot get production information if category is not active. What’s wrong with this left join script?

select p.name, c.name
from products p
left join categories c on p.categoryid = c.categoryid
where c.isactive = 1 or c.isactive is null

Reason

To find out what’s the issue of this script have, we need to understand order of a SQL query. In the script above we used SELECT, FROM, LEFT JOIN and WHERE

1. FROM and JOIN: The FROM clause, and subsequent JOINs are first executed to determine the total working set of data that is being queried
2. WHERE: Once we have the total working set of data, the WHERE constraints are applied to the individual rows, and rows that do not satisfy the constraint are discarded.
3. SELECT: Any expressions in the SELECT part of the query are finally computed.

The WHERE clause is execution after the ON clause. which means the predicates in the ON clause are applied to the table before the join, WHERE clause is semantically applied to the result of the join.

So below script will get all the data from product table and will keep categories data as null if cannot find category.

select p.name, c.name
from products p
left join categories c on p.categoryid = c.categoryid

WHERE clause is applied to the JOIN result which will remove products result if category missing.

where c.isactive = 1 or c.isactive is null

Solution

if we want to get all product information even if categories is missing or is not active, we need to changed the WHERE clause to ON clause as below:

select p.name, c.name
from products p
left join categories c on p.categoryid = c.categoryid
and (c.isactive = 1 or c.isactive is null )

This blog I will show you a very trick issue thing a bout nchar and nvarchar actual size and storage size.

Issue

When I use sp_help to check the table property. I got below result:

but when I using SSMS to check the column size, it shows nvarchar(50)

so what’s the different between sp_help and SSMS design view?

Finding

I checked sp_help , it is get the max length field from sys.all_columns. In Microsoft document website, it mentioned maximum length (in bytes) of the column.

But we know nchar and nvarchar, the storage size is two times n bytes (n is the actual number of characters ). so this explained, sp_help is not real character size, it is the storage size in bytes. But SSMS design view is using real character size.

For details you can check Microsoft article nchar and nvarchar and sys.all_columns

Recently, I try to setup a new SQL SERVER on AWS. When I try to connect to server via private IP. I got below SQL Login error:

Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication. [CLIENT: 172.31.23.96]
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The operating system error code indicates the cause of failure. The logon attempt failed [CLIENT: 172.31.23.96]

WORKAROUND/SOLUTION

Loopback check can be removed by adding a registry entry as follows:

• Edit the registry using regedit. (Start –> Run > Regedit )
• Navigate to: HKLM\System\CurrentControlSet\Control\LSA
• Add a DWORD value called “DisableLoopbackCheck”
• Set this value to 1

Refer : https://blogs.msdn.microsoft.com/dataaccesstechnologies/2012/12/19/error-message-login-failed-the-login-is-from-an-untrusted-domain-and-cannot-be-used-with-windows-authentication/

One of the most frequently question I asked is which date format style number I should to use. Here’s a summary of different date and time style matrix that we can used in T-SQL as part of the CONVERT function.

Matrix of Date and Time style

Sample convert string to datetime

select convert(datetime, ‘2015/12/31 13:58:03’,111)

select convert(datetime, ‘2012-11-07T18:26:20.096Z’,127)

We can use sp_help to display all table schema

sp_help import_OAN_clients

Will get below result:

But how we can compare two tables schema quickly via T-SQL? We can use INFORMATION_SCHEMA.COLUMNS to compare two tables schema easily, but this way cannot compare table cross database or instance. In this blog I will show how to compare it with internal function sys.dm_exec_describe_first_result_set

Create Demo Tables

CREATE TABLE demo1(
	[User_ID] [nvarchar](50) NULL,
	[FirstName] [nvarchar](50) NULL,
	[LastName] [nvarchar](100) NULL,
	[BirthDate] datetime NULL,
	[Citizenship] [nvarchar](100) NULL,
	[Country] [nvarchar](20) NULL
) 
CREATE TABLE demo2(
	[User_ID] [nvarchar](50) NULL,
	[FirstName] [nvarchar](100) NULL,
	[LastName] [nvarchar](100) NULL,
	[BirthDate] [nvarchar](30) NULL,
	[Citizenship] [nvarchar](100) NULL,
	[Country] [nvarchar](100) NULL
) 

How sys.dm_exec_describe_first_result_set is working

If you want to know all detail specs of sys.dm_exec_describe_first_result_set, you can go to microsoft developer community

before we start compare two tables, let’s see what kind of information the function can get

select * from sys.dm_exec_describe_first_result_set ( N’SELECT * FROM dbo.demo1′, NULL, 1)

We can see the returned schema as below:

Doing Comparisons

To do two tables comparison, we just need to simply doing OUTER JOIN between the two function results. (To do comparisons between to database or instance, please use linked servers and full part name to address the table. like product.dbo.demo2)

select a.column_ordinal,b.column_ordinal,a.name,b.name ,a.is_nullable,b.is_nullable ,a.system_type_name,b.system_type_name,a.max_length,b.max_length,a.precision,b.precision,a.scale,b.scale
from sys.dm_exec_describe_first_result_set ( N’SELECT * FROM dbo.demo1′, NULL, 1) as a
full outer join sys.dm_exec_describe_first_result_set ( N’select * from dbo.demo2′, NULL, 1) as b
on a.name = b.name

We can see the comparison result between to tables now.