Table scan or Unexpected output due to improper where condition applied for DateTime filter by serverku

When you are using date filter with queries, like to find the records from tables for the particular year/month, we are approaching some extra data type conversion on fields. Below are some examples.

Should not use,

 
-- #1. : This cause to table scan
SELECT *  
FROM OrderDetails
WHERE datepart(month,Orderdate) =?
AND datepart(year,Orderdate) = ?

-- #2. : This cause to unexpected output 
SELECT * 
FROM OrderDetails
WHERE convert(varchar,OrderDate,112) between
'20110101' and '20110131' 

Should use,

 
-- #1.
SELECT * 
FROM OrderDetails
WHERE convert(varchar,Orderdate,112) >= '20110101' 
AND convert(varchar,Orderdate,112) < '20110201' -- EndDate + 1 

-- #2.
SELECT * 
FROM OrderDetails
WHERE cast (OrderDate as datetime) BETWEEN
 '2011-01-01 00:00:00.000' AND '2011-01-31 23:59:59.900'

Create table with sample records or use existing table which has datetime data type field, Run query above and check the results with execution plan. Hope you like it.

COUNT(*) with a LEFT JOIN may produce unexpected results - SQL Server by serverku

We should not use count (*) for left outer join two or more tables with group by. Instead we can use right table's column for count, otherwise it will come up with unexpected output.

Way cause for unexpected output

 
SELECT 
    a.id, 
    COUNT(*)
FROM table1 a
LEFT JOIN table2 b ON (a.id=b.id)
GROUP BY a.id
GO

Right way

 
SELECT 
    a.id, 
    COUNT(b.id) 
FROM table1 a
LEFT JOIN table2 b ON (a.id=b.id)
GROUP BY a.id
GO

Create tables with sample records, run above queries for them and see difference. Did you know this earlier?

Trace the query with specific session in SQL Server Profiler by serverku

One more enhancement i found with Query Editor of SQL Server 2012 and later version, we can trace the query with specific current session in SQL Server Profiler. For that just need to right click in Query Editor and you can find the option Trace Query in SQL Server Profiler or press CTRL + ALT + P, which will open SQL Profiler filtered with that current session.

Below is a screen shot for the same.

Did you know this or used ever?

Linked Server and OPENQUERY with Execl Source - SQL Server by serverku

As I have written posts for the linked servers with SQL Server option in previous post, the same way we have another option is available and these are with other data sources as a linked server. With SQL Server as a linked server, we can communicate with two SQL servers. But we can also communicate other data sources like excel, csv and others as well. Let us link our SQL server with Excel and get the data from excel. For that we need to go through the same way as I did for SQL Server linked server, but here we need to choose option of other data sources.

How can we do it using TSQL?

 
USE [master]
GO

EXEC [master].[dbo].[sp_addlinkedserver] 
@server='ExcelImport',
@srvproduct='Excel', 
@provider='Microsoft.Jet.OLEDB.4.0', 
@datasrc='D:\Import\Import_1.xls',
@provstr= 'Excel 8.0'

GO

EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'rpc', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'use remote collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'EXCELIMPORT', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO
USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'EXCELIMPORT', @locallogin = NULL , @useself = N'False'
GO

It has other data source also like as following,

Now, after creating excel as linked server, we have time to communicate with it and fetch data through linked server & OPENQUERY

 
SELECT 
* 
FROM ExcelImport...[Sheet1$]

-- OR --

SELECT 
* 
FROM OPENQUERY(ExcelImport, 'SELECT * FROM [Sheet1$]')

Your comments are appreciated.

Working with Linked Servers in SQL Server by serverku

We may have a need for some logic to move the data from one server database to another server database on production environment. Also for the distributed transactions or for the cross server database queries we require it.

How to do it? With Linked servers we can perform any distributed transactions between servers. Also, we can execute remote servers stored procedure with linked server communication.

How to create it?
Herewith we have small demo which I have captured during setup it.

1. Go to the Server Objects --> Linked Servers, Right click on it and click on New Linked Servers.
2. Specify Server name which you want to linked to current server.
3. Go to Security tab and select the option for link.

The option details as msdn and book online are following,

Local login : Specify the local login that can connect to the linked server. The local login can be either a login using SQL Server Authentication or a Windows Authentication login. Use this list to restrict the connection to specific logins, or to allow some logins to connect as a different login.

Impersonate : Pass the username and password from the local login to the linked server. For SQL Server Authentication, a login with the exact same name and password must exist on the remote server. For Windows logins, the login must be a valid login on the linked server.

Remote User : Use the remote user to map users not defined in Local login. The Remote User must be a SQL Server Authentication login on the remote server.

Remote Password : Specify the password of the Remote User.

Not be made : Specify that a connection will not be made for logins not defined in the list.

Be made without using a security context : Specify that a connection will be made without using a security context for logins not defined in the list.

Be made using the logins current security context : Specify that a connection will be made using the current security context of the login for logins not defined in the list. If connected to the local server using Windows Authentication, your windows credentials will be used to connect to the remote server. If connected to the local server using SQL Server Authentication, login name and password will be used to connect to the remote server. In this case a login with the exact same name and password must exist on the remote server.

Be made using this security context : Specify that a connection will be made using the login and password specified in the Remote login and With password boxes for logins not defined in the list. The remote login must be a SQL Server Authentication login on the remote server.

We have a some server options like RPC, RPC out as follows

We have seen as how can we create linked server from SSMS. Now we will have script to create linked servers as well.

 USE [master]
GO

EXEC master.dbo.sp_addlinkedserver @server = N'PARESH-PC1', @srvproduct=N'SQL Server'
GO

EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'rpc out', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'PARESH-PC1', @optname=N'use remote collation', @optvalue=N'true'
GO

USE [master]
GO
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'PARESH-PC1', @locallogin = NULL , @useself = N'False', @rmtuser = N'dba', @rmtpassword = N'dba@test'
GO

How to get list of linked servers?

SQL Server provide system stored procedure as well system tables , from we can have the details for the same.

• sp_linkedservers
• sys.servers

 
-- 1. Fetch the data using tsql

SELECT 
* 
FROM paresh-pc.demo.dbo.dempteable

-- 2. Running remote stored procedures

EXECUTE paresh-pc.demo.dbo.DemoSP 'test_param'

I hope you enjoyed linked servers. Please share your comments what you are performing with linked servers.