Tuesday, 31 December 2019

Using OPENQUERY in SQL Server


OPENQUERY is a function falling under the Rowset preferences. This function can be used to fetch the records or information from one server to another by specifying a linked server and desired commands. 

The resemblance of using OPENQUERY is as though the records are being fetched directly from the table using the FROM clause.

Format:
SELECT * FROM OPENQUERY (
[linkedserver_name],
'your query'
)
From the format, we can notice that the OPENQUERY allows two arguments to be passed. One for passing the linked server and the other to fill in your desired query.

Example:
USE SQLArena_TEST
SELECT * FROM OPENQUERY (
[link_test],'
SELECT COUNT(1) AS COUNT from ArenaLink_Testing..PT')


From the example, we can notice that the current database from where I have been accessing is SQLArena_TEST. Additionally, by filling in the arguments with the linked server name (link_test) and my SELECT query, we can fetch the results of the corresponding server data.

The OPENQUERY is an alternate for using linked servers in the query directly. Sometimes, we can come across an error as “NT AUTHORITY\ANONYMOUS LOGON” error. If you come across this error while accessing the query, then make sure the outbound configuration and Kerberos settings are set up correctly.

Additionally, we can as well fetch the server information using a global function that cannot be achieved by passing directly the linked server name. Getting remote server configuration details can be achieved only using the OPENQUERY in SQL Server.

Further, OPENQUERY as well supports pass-through execution commands on INSERT, UPDATE and DELETE operations.


Sunday, 29 December 2019

Comparing CROSS APPLY and INNER JOIN in SQL Server


Do CROSS APPLY and INNER JOIN produces the same result set?

I have been talking with many of my DB friends, and they have come up with a question of comparing the CROSS APPLY and INNER JOIN. The APPLY is an SQL Server operator that was introduced in 2005 for comparing the expressions on table A column with the expressions on the table B column. I have come up with a scenario of usage of both CROSS APPLY and INNER JOIN to make the comparison and understanding easier. 

CROSS APPLY

The usage of CROSS APPLY is very simple, which returns the expressions that are matched between the left table and the right table or vice versa.


INNER JOIN

The usage of INNER JOIN is simpler than that of a CROSS APPLY, where it returns the same result set as that of a CROSS APPLY. The result set is nothing but the matching expressions between the left table and the right table or vice versa.

Let’s take an example of CROSS APPLY and INNER JOIN for comparison


1. Create a new table as Hospital

CREATE TABLE [Hospital](
[HospitalID] [int] NOT NULL PRIMARY KEY,
[Name] VARCHAR(250) NOT NULL,
) ON [PRIMARY]

2. Create a new table as Doctors

CREATE TABLE [Doctors](
[DocID] [int] NOT NULL PRIMARY KEY,
[FName] VARCHAR(250) NOT NULL,
[LName] VARCHAR(250) NOT NULL,
[HospitalID] [int] NOT NULL REFERENCES [Hospital](HospitalID),
) ON [PRIMARY]

3. Insert values into Hospital table

INSERT [Hospital] ([HospitalID], [Name]) 
VALUES (1, N'Hospital1')
INSERT [Hospital] ([HospitalID], [Name]) 
VALUES (2, N'Hospital2')
INSERT [Hospital] ([HospitalID], [Name]) 
VALUES (3, N'Hospital3')
INSERT [Hospital] ([HospitalID], [Name]) 
VALUES (4, N'Hospital4')
INSERT [Hospital] ([HospitalID], [Name]) 
VALUES (5, N'Hospital5')
INSERT [Hospital] ([HospitalID], [Name]) 
VALUES (6, N'Hospital6')
GO

4. Insert values into Doctors table

INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])
VALUES (1, N'Pradeep', N'TS', 1 )
INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])
VALUES (2, N'Johnson', N'Hall', 2 )
INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])
VALUES (3, N'Tom', N'Krueger', 3 )
INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])
VALUES (4, N'Bill', N'Hokkins', 3 ) 
INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])
VALUES (5, N'Kim', N'Bills', 3 ) 
INSERT [Doctors] ([DocID], [FName], [LName], [HospitalID])
VALUES (6, N'James', N'Clark', 3 )

Comparing CROSS APPLY and INNER JOIN

We’ll try to implement the CROSS APPLY operator between the Hospital table and the Doctors table and check the results with the execution plan.

SELECT * FROM Hospital H
CROSS APPLY
(
SELECT * FROM DOCTORS D
WHERE D.HospitalID=H.HOSPITALID
)B
GO





Now, let’s try to implement the INNER JOIN operator between the Hospital table and the Doctors table and check the results with the execution plan. 

SELECT * FROM HOSPITAL H
INNER JOIN DOCTORS D
ON
H.HOSPITALID=D.HOSPITALID

RESULT: The CROSS APPLY and INNER JOIN produces the same result sets with the same execution plan.

Question: Where does CROSS APPLY works efficiently and outcomes the results of an INNER JOIN?

Just imagine if we would require just the top 1 result from table B verse the matching records on table A, then the CROSS APPLY is worthy for us. This condition cannot be achieved just using an INNER JOIN operator in SQL Server, which would require an additional CTE concept implementation along with INNER JOIN logic. 

Saturday, 28 December 2019

Using xp_logininfo to Find Active Directory Logins in SQL Server


The xp_logininfo is an extended stored procedure used to find the windows Active Directory users and groups. In the production environment, I have come across colleagues asking for their permissions on select databases. In such instances, the xp_logininfo can come handy to check the logins for respective windows users and groups.

Here is a sample example of parameters and usage of xp_logininfo stored procedure:
The xp_logininfo has three parameters,


@acctname for specifying the windows AD login
@option for specifying to search either ‘members’ of the login or ‘all’ of them
@privilege for getting the output variable from the command we put in 

Example:

EXEC master.dbo.xp_logininfo
@acctname='',
@option=''
@privilege='' -- this is not needed

If we would require all the assigned permission path for the AD login specified

EXEC master.dbo.xp_logininfo
@acctname='SQLArenaLogin',
@option='all'

Or

EXEC master.dbo.xp_logininfo ' SQLArenaLogin, 'all'







If we would require the members of the AD login specified

EXEC master.dbo.xp_logininfo ' SQLArena_ADTEST, 'members'

Thursday, 26 December 2019

Check Available and Free Disk Space in SQL Server


Without having the remote access to the server, it becomes a blind-spot to figure the available and free disk space in SQL Server. While I have been working on different areas of accessing the files, exchanging files, and setting appropriate files in respective disks, the hard drive location happens to be very vital. At times, I see that the logical files had to be put based on free space availability. Using the below scripts can help you figure out the drive space in no time. 
Further, there are two options to figure out the total and free space of a disk. Let’s look at the methods to check them.

1. Using the xp_fixeddrives Extended Stored Procedure

The below extended stored procedure gives us two columns, one with the drive name and the other with the free space in drive in MB. Just execute the SP in master as below.

EXEC master..xp_fixeddrives







2. Using sys.master_files + sys.dm_os_volume_stats

The query using the sys.master_files and sys.dm_os_volume_stats can give us a result set with four columns – LogicalName, Drive, TotalSpaceInMB and AvailableSpaceMB.

USE MASTER

SELECT DISTINCT dovs.logical_volume_name AS LogicalName,
dovs.volume_mount_point AS Drive,
CONVERT(INT,dovs.total_bytes/1048576.0) AS TotalSpaceInMB,
CONVERT(INT,dovs.available_bytes/1048576.0) AS AvailableSpaceInMB
FROM sys.master_files mf
CROSS APPLY sys.dm_os_volume_stats(mf.database_id, mf.FILE_ID) dovs
ORDER BY AvailableSpaceInMB ASC
GO








Probably, you should go with option 2 to check the drive space as it can facilitate in comparing the drive total space available with the free space available.