Tag: SQL Server
How to ALTER a Column from Null to NOT Null in SQL Server ?
Assume a scenario where you have a table that contains columns which are Nullable integer column. You might want to update them to NOT NULL column (adding NOT NULL constraint) and set its value to zero. How to ALTER a Column from Null to NOT Null in SQL Server ? This would be a two step process where you should first update the column values…
Inner Join in SQL Server
You can use inner join to get the result of two tables that match the criteria. For example , assume that you have a Person table and a Phone Number table where every person would have 0 or more phone number associated to him and you wish to get the person records who have at least one phone number. Here’s the query on how to…
How to check if a column exists in a table in SQL Server ?
If you want to find out if a column exists in a SQL Server table , you can use the sys.columns table and specify the name of the column and table as parameter. How to check if a column exists in a table in SQL Server ? For example , you want to find out if the GroupName column exists in the table “HumanResources.Department” table…
Sleep Command in SQL Server
If you are looking at writing a query to sleep for or WAIT for a certain amount of time before the query execution can begin , you can use the WAITFOR command. For example , if you want to wait for 1 minute , the query or command for the same will be this.
Specify a port number when connecting to SQL Server in Management Studio
There are times when you want to connect to a SQL Server instance that is running on a different port number than default port (1433). How to specify a port number when connecting to a SQL Server instance in SQL Management Studio ? Just specify the SQL Server instance that you are connecting to along with the port number with comma as delimiter. For example…
How to Disable All Constraints in the Database temporarily in SQL Server ?
There are times when you may want to disable all the constraints in the your SQL Server Database tables when importing the data from the external data source . How to Disable All Constraints in the Database temporarily in SQL Server ? Below is a sample query that lets you to quickly disable the constraints like foreign keys , unique keys etc for the database…
SQL Server 2014 Tutorial – Personalize SQL Management Studio
Problem Statement You need to personalize SQL Server Management Studio. Solution There are various personalization options available for the SQL Server users in the SQL Management Studio. For example, if you want the Object explorer to be place on a different position instead of showing it in the left side of the SSMS, you can just move it by clicking it on the title bar…
How to get the list of all tables from a SQL Server database ?
If you need to get the list of all tables from a SQL Server database , you can query the information_schema.tables by specifying the TABLE_TYPE parameter search criteria to “BASE TABLE’. How to get the list of all tables from a SQL Server database ?
Sorting the Data in a Query in SQL Server
You want the results of the query to be displayed in a sorted order based on some query. In this case , you use the ORDER BY clause in the query by specifying the column on which the sort to work. For example , you want to sort the Departments table in the database by the column DepartmentName. Here’s how the query looks like This…
How do you check if an index exists in a table?
You can find out if the index exists for a table by querying the sys.indexes table as shown in this blog post’s code sample. How do you check if an index exists in a table? This will list out all the indexes of the table.
Using NOT operator to inverse the condition in SQL Server
Imagine a scenario where you want to retreive records that you dont want. You can use the NOT operator to reverse the search condition. For example , You want to get all the departments having the GroupName other than ‘Research and Development’. The other way of getting the same result is using the != in every condition.
How to Declare a variable in SQL Server ?
You will most likely declare a variable in the SQL Server and use it in the SQL Statements. You can use the DECLARE statement and specify the variable and datatype and optionally provide a default value. For example , You can declare an NewEmployeeJobTitle variable using the DECLARE statement and provide a default value as ‘Chief Executive Officer’ DECLARE @NewEmployeeJobTitle nvarchar(50) = ‘Chief Executive Officer’;…