Why left join not returning nulls?

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 )

SQL server nchar and nvarchar size

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:

which shows length 100

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

SQL SERVER : Login Failed. The Login is From an Untrusted Domain and Cannot be Used with Windows Authentication

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/

SQL Server Date and Time styles

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

Style numberInput/Output Format
100 mon dd yyyy hh:miAM (or PM)
1011 = mm/dd/yy
101 = mm/dd/yyyy
1022 = yy.mm.dd
102 = yyyy.mm.dd
1033 = dd/mm/yy
103 = dd/mm/yyyy
1044 = dd.mm.yy
104 = dd.mm.yyyy
1055 = dd-mm-yy
105 = dd-mm-yyyy
1066 = dd mon yy
106 = dd mon yyyy
1077 = Mon dd, yy
107 = Mon dd, yyyy
108hh:mi:ss
109 mon dd yyyy hh:mi:ss:mmmAM (or PM)
11010 = mm-dd-yy
110 = mm-dd-yyyy
11111 = yy/mm/dd
111 = yyyy/mm/dd
11212 = yymmdd
112 = yyyymmdd
113 dd mon yyyy hh:mi:ss:mmm (24h)
114hh:mi:ss:mmm (24h)
 120 yyyy-mm-dd hh:mi:ss (24h)
 121 yyyy-mm-dd hh:mi:ss.mmm (24h)
22 mm/dd/yy hh:mi:ss AM (or PM)
23yyyy-mm-dd
126yyyy-mm-ddThh:mi:ss.mmm (no spaces)
127yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
130dd mon yyyy hh:mi:ss:mmmAM
131dd/mm/yyyy hh:mi:ss:mmmAM

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)

How to compare SQL Server Table schemas

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.

SQL SERVER BCP UTF8 import and export, support UTF8 after SQL SERVER 2014SP2

The bulk copy program utility (bcp) is a very powerful bulk copy tool between database and flat file. Normally used for large number of data import and export. But if you sql server version is older then 2014 SP2, unlucklly you cannot process UTF8 data even if there is a code page parameter -C , but it doesn’t support UTF8. Refer to Microsoft community document

code_pageSpecific code page number; for example, 850.

** Important ** SQL Server does not support code page 65001 (UTF-8 encoding).

If you want to process UTF8, then must need to upgrade SQL Server to version 2014 SP2 or later. Once you installed the upgrade, then you can use BCP to import or export UTF8 as below:

To import UTF-8 data to SQL Server, use the BCP utility and run the following bcp t_users in “D:\test.csv” -c -C 65001

To export UTF-8 data to SQL Server, use the BCP utility and run the following bcp t_users out ” D:\test.csv ” -c -C 65001

SQL performance tuning: variable in where condition cause performance issue

I am seeing a hug performance difference between two quires that are almost identical.

Query 1:

declare @type varchar(10) = ‘OR-‘
select * from client_details t1, Orders t2
where t1.client_code = @type +t2.client_code

Query 2:

select * from client_details t1, Orders t2
where t1.client_code = ‘OR-‘+t2.client_code

client_details has around 400 thousands records, Orders table has around 200 thousands.

Query 1 take long then 5 hours to processing, but Query 2 is finished within few minutes.

I did lots of search and testing, it seems SQL SERVER optimizer issue.

Please the difference of Literal Values and local variable from Kendra Little’s Blog:

https://www.brentozar.com/archive/2014/06/tuning-stored-procedures-local-variables-problems/

Solution to fix my issue, I created an additional column called type and then use the value from this new column:

select * from client_details t1, Orders t2
where t1.client_code = t2.OrderType +t2.client_code