Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Server – Filtering objects in Object Explorer – Management Studio

When you are working on a database with a large number of objects sometimes it becomes a challenge to locate objects. Object Explorer in SQL Server Management Studio lists all objects according to category and it may be hard to locate specific object.

To resolve this you can Object Explorer… Read more

1 comments, 279 reads

Posted in SQL and Me on 28 May 2013

SQL Server – Show/Hide Results Pane in Management Studio 2012

In earlier version of SQL Server Management Studio (2005, 2008 and 2008 R2) you can show/hide results pane using keyboard shortcut Ctrl+R. This shortcut was also present in Query Analyzer (SQL Server 2000).

This shortcut is no longer available in SQL Server Management Studio 2012. In this version you… Read more

5 comments, 439 reads

Posted in SQL and Me on 20 May 2013

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… Read more

0 comments, 827 reads

Posted in SQL and Me on 13 May 2013

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… Read more

1 comments, 366 reads

Posted in SQL and Me on 6 May 2013

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… Read more

4 comments, 2,642 reads

Posted in SQL and Me on 1 May 2013

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… Read more

0 comments, 385 reads

Posted in SQL and Me on 22 April 2013

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… Read more

6 comments, 650 reads

Posted in SQL and Me on 19 April 2013

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… Read more

4 comments, 3,582 reads

Posted in SQL and Me on 15 April 2013

SQL Server – Add Date/Time to output file of BCP / SQLCMD (2)

Last time I posted about How you can add date/time to output file name, in which I used xp_cmdshell to execute the BCP/SQLCMD command using TSQL, which means we need to have xp_cmdshell server feature enabled for that solution work. There is a workaround available to that solution when… Read more

1 comments, 283 reads

Posted in SQL and Me on 12 April 2013

SQL Server – Add Date/Time to output file of BCP / SQLCMD

You can export data from SQL Server using BCP command for SQLCMD utility. However, these utilities does not support dynamic file names when exporting data. For generating dynamic file names you can use solution provided below. In the examples below I have appended date/time to exported files. You can modify… Read more

1 comments, 1,651 reads

Posted in SQL and Me on 9 April 2013

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)… Read more

1 comments, 2,142 reads

Posted in SQL and Me on 1 April 2013

SQL Server – Executing Multiple Script Files Using SQLCMD

There are different options available when you want to execute a .sql script file on a server. You can open the script file in SQL Server Management Studio and execute it, or you can use SQLCMD to execute a script from command line. However, when you have large number of… Read more

2 comments, 4,631 reads

Posted in SQL and Me on 25 March 2013

SQL Server – Changing Default Database Location for Server

When you create a new database in SQL Server without explicitly specifying database file locations, SQL Server created files in default location. This default location is configured when installing SQL Server.

If you need to change this default location once SQL Server is installed, you can change this in server… Read more

1 comments, 763 reads

Posted in SQL and Me on 18 March 2013

SQL Server – Start/Stop SQL Server from command line

You can start/stop SQL Server services using Services Console or SQL Server Configuration Manager. You can also perform these operation using command line in Windows. For this you must run command prompt with Administrative privileges.

Method 1: Start/Stop SQL Server using NET command:

You can start/stop SQL Server from command… Read more

1 comments, 899 reads

Posted in SQL and Me on 11 March 2013

SQL Server – How to Rename Table/Column in SQL Server

To rename an object in SQL Server you can use sp_rename system stored procedure or you can do this via Object Explorer in SQL Server Management Studio.

Method 1: To rename an object using SQL Server Management Studio:

Step 1. Right Click on the object in Object Explorer and select… Read more

0 comments, 925 reads

Posted in SQL and Me on 4 March 2013

SQL Server – Finding out Database creation time

Finding database creation time is simple if database has not been detached from server. You can find database creation time using below methods.

Using sp_helpdb:

You can find the creation time of database using sp_hepldb system procedure.

EXEC sp_helpdb 'SqlAndMe'

GO

This will return database creation time along with other… Read more

1 comments, 351 reads

Posted in SQL and Me on 19 February 2013

SQL Server – Cycle Error Logs for SQL Server and SQL Server Agent

SQL Server error logs can fill up quickly, and when you are troubleshooting something you may need to go through huge log. However you can cycle the error log to manage the amount of log you need to go through. When you cycle error log the current log file is… Read more

1 comments, 345 reads

Posted in SQL and Me on 8 February 2013

SQL Server – Attach Database with MDF file only

Earlier on my blog I posted about how you can attach a database using T-SQL when no log file is available. You can catch that here. In this post we will see how it can be attached using GUI.

To attach a database with no .ldf file, follow below… Read more

1 comments, 988 reads

Posted in SQL and Me on 30 January 2013

SQL Server – Check Enabled Trace Flags for Server

In SQL Server you can enable a Trace Flag at session (effective for current session only) level and global level. If you are not sure which Trace Flags have been enabled you can use DBCC TRACESTATUS() command to get a list of enabled Trace Flags.

DBCC TRACESTATUS() takes two arguments… Read more

2 comments, 704 reads

Posted in SQL and Me on 24 January 2013

SQL Server – Enabling backup CHECKSUM with Trace Flag 3023

You can use WITH CHECKSUM option to perform checksum when backup is created. When used this verifies each page for checksum and torn page.

You can use it in a BACKUP command as below:

BACKUP DATABASE [SqlAndMe]

TO DISK = N'C:\SqlAndMe.bak'

WITH CHECKSUM

GO

 

Once the backup is created… Read more

2 comments, 966 reads

Posted in SQL and Me on 15 January 2013

Newer posts

Older posts