Archive
SQL Server – How to Move Table to Another Schema
Starting with SQL Server 2005 all tables are grouped into schemas. While creating a table if the schema name is not specified it is created in the default schema of the user creating it. you can use ALTER SCHEMA command to move tables between schemas.
For example, if I create a table using below script it will be created under my default schema which is dbo:
USE [SqlAndMe]
GO
CREATE TABLE Employee
(
ID INT,
Name VARCHAR(20)
)
GO
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM sys.tables
WHERE name = 'Employee'
GO
Result Set:
name schema
Employee dbo
(1 row(s) affected)
As you can see from the output the table is currently in dbo schema.
Now to move this table to another schema using ALTER SCHEMA command, first we need to create the schema if it does not exist already. After that we can move table to new schema.
USE [SqlAndMe]
GO
CREATE SCHEMA HumanResources
GO
ALTER SCHEMA HumanResources
TRANSFER dbo.Employee
GO
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM sys.tables
WHERE name = 'Employee'
GO
Result Set:
name schema
Employee HumanResources
(1 row(s) affected)
As you can see from the output the Employee table is now moved to HumanResources schema.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – How to find Default data and log path for SQL Server 2012
To identify default data and log directories in SQL Server 2012 you can use SERVERPROPERTY() function. In SQL Server 2012 two new parameters are added to SERVERPROPERTY() function namely, InstanceDefaultDataPath and InstanceDefaultLogPath which returns the default data and log directories respectively.
It can be used as below:
SELECT [Default Data Path] = SERVERPROPERTY('InstanceDefaultDataPath')
SELECT [Default Log Path] = SERVERPROPERTY('InstanceDefaultLogPath')
GO
Result Set:
Default Data Path
C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\
(1 row(s) affected)
Default Log Path
C:\Program Files\Microsoft SQL Server\MSSQL11.SQL2012\MSSQL\DATA\
(1 row(s) affected)
You can also get this information from Server Properties dialog box in SQL Server Management Studio.
To identify this information for earlier version of SQL Server you need to use xp_instance_regread extended stored procedure which returns data from registry.
Earlier I posted on identifying default data directory for multiple instances through registry which returns this information for all instances installed on server.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Finding TCP Port Number SQL Instance is Listening on
By default SQL Server listens on TCP port number 1433, and for named instances TCP port is dynamically configured. There are several options available to get the listening port for SQL Server Instance.
Here are a few methods which we can use to get this information.
Method 1: SQL Server Configuration Manager
Method 2: Windows Event Viewer
Method 3: SQL Server Error Logs
Method 4: sys.dm_exec_connections DMV
Method 5: Reading registry using xp_instance_regread
Let's see how you can use each of these methods in detail:
Method 1: SQL Server Configuration Manager:
Step 1. Click Start > All Programs > Microsoft SQL Server 2012 > Configuration Tools > SQL Server Configuration Manager
Step 2. Go to SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for <Instance Name>
Step 3. Right Click on TCP/IP and select Properties
Step 4. In TCP/IP Properties dialog box, go to IP Addresses tab and scroll down to IPAll group.
If SQL Server if configured to run on a static port it will be available in TCP Port textbox, and if it is configured on dynamic port then current port will be available in TCP Dynamic Ports textbox. Here my instance is listening on port number 61499.
Method 2: Windows Event Viewer:
When SQL Server is started it logs an event message as 'Server is listening on [ 'any' <ipv4> <port number>' in windows event logs. Here <port number> will be actual port number on which SQL Server is listening.
To view this using Event Viewer:
Step 1. Click Start > Administrative Tools > Event Viewer.
Note: If Administrative Tools are not available on Start menu, go to Start > Control Panel > System and Maintenance > Administrative Tools > View event logs
Step 2. Navigate to Event Viewer > Windows Logs > Application
Step 3. Since huge amount of event are logged, you need to use filtering to locate the required logs. Right click on Application and select Filter Current Log...
Step 4. You can filter the events by Event ID and Event source. The event we are interested in has Event ID of 26022, and it's source is SQL Server Instance. You need to filter by both Event ID and SQL Server Instance if you have multiple instances installed, for a single instance you can filter by Event ID only. Click on OK to apply the filter.
Step 5. Once the filter is applied, Locate message 'Server is listening on [ 'any' <ipv4> …'. As we can see from below screenshot that SQL Server Instance is running on TCP Port 61499.
Method 3: SQL Server Error Logs:
When SQL Server is started it also logs an message to SQL Server Error Logs. You can search for port number in SQL Server Error Logs by opening SQL Server Error Log in notepad or via T-SQL using extended stored procedure xp_ReadErrorLog as below:
EXEC xp_ReadErrorLog 0, 1, N'Server is listening on', N'any', NULL, NULL, 'DESC'
GO
Result Set:
LogDate ProcessInfo Text
2013-03-21 13:34:40.610 spid18s Server is listening on [ 'any' <ipv4> 61499].
2013-03-21 13:34:40.610 spid18s Server is listening on [ 'any' <ipv6> 61499].
(2 row(s) affected)
As we can see from the output that SQL Server Instance is listening on 61499.
Note: This method does not work if SQL Server Error Logs have been cycled. See sp_Cycle_ErrorLog for more information.
Method 4: sys.dm_exec_connections DMV:
DMVs return server state that can be used to monitor SQL Server Instance. We can use sys.dm_exec_connections DMV to identify the port number SQL Server Instance is listening on using below T-SQL code:
SELECT local_tcp_port
FROM sys.dm_exec_connections
WHERE session_id = @@SPID
GO
Result Set:
local_tcp_port
61499
(1 row(s) affected)
As we can see from the output… same as above 
Method 5: Reading registry using xp_instance_regread:
Port number can also be retrieved from Windows Registry database.
We can use extended stored procedure xp_instance_regread to get port number information using below T-SQL code:
DECLARE @portNumber NVARCHAR(10)
EXEC xp_instance_regread
@rootkey = 'HKEY_LOCAL_MACHINE',
@key =
'Software\Microsoft\Microsoft SQL Server\MSSQLServer\SuperSocketNetLib\Tcp\IpAll',
@value_name = 'TcpDynamicPorts',
@value = @portNumber OUTPUT
SELECT [Port Number] = @portNumber
GO
Result Set:
Port Number
61499
(1 row(s) affected)
As we can see … same as above
Note: The above code will only work if SQL Server is configured to use dynamic port number. If SQL Server is configured on a static port, we need to use @value_name = 'TcpPort' as opposed to @value_name = 'TcpDynamicPorts'.
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – How to check if you are running 32-bit or 64-bit version
There are two different command you can use to check if you are running 32-bit or 64-bit version of SQL Server.
Using @@VERSION:
You can user @@VERSION system variable to determine edition and architecture of SQL Server as below:
SELECT @@VERSION
Result Set:
Microsoft SQL Server 2012 (SP1) – 11.0.3000.0 (Intel X86)
Oct 19 2012 13:43:21
Copyright (c) Microsoft Corporation
Developer Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
(1 row(s) affected)
This command returns both SQL Server and Operating system information. In the first line you get information about SQL Server, it's version and architecture. Here, x86 = 32-bit architecture and x64 = 64-bit architecture. Same it true for operating system.
Using SERVERPROPERTY():
We can also user SERVERPROPERTY() function to get information about SQL Server Edition and architecture.
SELECT SERVERPROPERTY('Edition')
Result Set:
Developer Edition
(1 row(s) affected)
From the output we can see that it's a Developer Edition instance and architecture is 32-bit. If it is 64-bit then '(64-bit)' will be appended to edition. i.e. For 64-bit the output will be 'Developer Edition (64-bit)'
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Differences between Clustered and Non-Clustered Indexes
Both Clustered and Nonclustered Indexes have same physical structure in SQL Server. Both are stored as a B-Tree structure in SQL Server.
Below are some characteristics of Clustered Indexes and Nonclustered Indexes in SQL Server.
Clustered Index:
1. The leaf node of a Clustered Index contains data pages of the table on which it is created
2. Clustered Index enforces a logical order on the rows. Rows are ordered based on Clustering Key
3. If the table does not have Clustered Index it is referred to as a "Heap"
4. A Clustered Index always has Index Id of 1
5. A Table can have ONLY 1 Clustered Index
6. A Primary Key constraint creates a Clustered Index by default *
* A Primary Key constraint can also be enforced by Nonclustered Index, You can specify the index type while creating Primary Key
Nonclustered Index:
1. The leaf nodes of a Nonclustered Index consists of Index pages which contain Clustering Key or RID to locate Data Row *
* When Clustered Index is not present leaf node points to Physical Location of the row this is referred to as RID. When a Clustered Index is present this points to Clustering Key (Key column on which Clustered Index is created)
2. Nonclustered Index does not order actual data, It only orders columns present in the Nonclustered Index based on Index Key specified at the time of creation of Nonclustered Index.
3. A table may not have any Nonclustered Indexes
4. Nonclustered Indexes have Index Id > 1
5. Prior to SQL Server 2008 only 249 Nonclustered Indexes can be created. With SQL Server 2008 and above 999 Nonclustered Indexes can be created
6. A Unique Key constraint created a Nonclustered Index by default *
* A Unique Key constraint can also be enforced by Clustered Index, You can specify the index type while creating Unique Key
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Import Data from Excel using T-SQL
To import data from an Excel file to SQL Server you can use SQL Server Import and Export Wizard. You can also import Excel data using T-SQL OPENROWSET function. OPENROWSET function can be used to import data from and OLEDB data source.
For this example I have created a simple excel file which has a single row of data and column headers, and the sheet is named [Employees] as below:
I have stored this file in my C:\ root folder.
Now to import this data to SQL Server you can use OPENROWSET as below:
SELECT *
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\Emp.xlsx',
'SELECT * FROM [Employees$]')
Result Set:
As you can see this is very easy and straight forward. However, the above query only displays data from Excel file, it does not import it to database.
To import this data to SQL Server Table you can modify the SELECT statement to SELECT INTO
USE [SqlAndMe]
GO
SELECT * INTO dbo.ImportedEmployeeData
FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0 Xml;HDR=YES;Database=C:\Emp.xlsx',
'SELECT * FROM [Employees$]')
GO
The above statement will create a new table ImportedEmployeeData and import excel data to the table.
Although this method seems straight forward, there are a few errors you may encounter:
Error Message 1:
Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)" reported an error.
The provider did not give any information about the error.
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Cause 1: You may receive this error if the Excel file is currently opened.
Solution 1: Close the Excel file if it is currently opened and try executing again.
Error Message 2:
Msg 15281, Level 16, State 1, Line 2
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server.
A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.
Cause 2: The cause of this error is mentioned in the error message. You may receive this error message if Ad Hoc Distributed Queries are not enabled by system administrator.
Solution 2: You can use sp_configure to enable this component. Since this is an advanced option you also need to enable 'Show Advanced Options' to enable this component. Use below commands to enable this:
EXEC sp_configure 'Show Advanced Options', 1
RECONFIGURE
GO
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
GO
Result Set:
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. Run the RECONFIGURE statement to install.
Error Message 3:
Msg 7302, Level 16, State 1, Line 1
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
Cause 3: You may receive this error message if registry settings are not set properly
Solution 3: To resolve this error, run below commands to fix registry issues:
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
GO
EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
GO
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
SQL Server – Saving Changes Not Permitted in Management Studio
SQL Server Management Studio does not allow you to save changes to a table which require table re-creation such as changing data type for a column. When you perform such changes you will run into following error message:
Here, I have tried changing data type for 'Name' column from NVARCHAR(50) to CHAR(50). Since this requires table re-creation Management Studio does not allow this operation to be completed, and it is doing so for your own good!
This activity can be completed without re-creating table if you are using T-SQL. Following T-SQL code can be used to avoid table re-creation:
USE [SqlAndMe]
GO
ALTER TABLE [dbo].[Customers]
ALTER COLUMN Name CHAR(50)
GO
The above statement will change the data type of the 'Name' column to CHAR(50) without having to re-create table.
We are better off with this option enabled. Although, if you are a GUI fan you can disable this safety net.
1. Go to Tools > Options
2. Go to Designers > Table and Database Designers
3. Uncheck Prevent saving changes that require table re-creation.
You can find more information on why not to disable this option here:
SQL Server – ALTER COLUMN – Management Studio v. T-SQL
Hope This Helps!
Vishal
If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe
