Tag: SQL Server

SQL Server 2014 Tutorial – SQL Server Management Studio

Problem Statement You need to manage your Microsoft SQL Server environment using SSMS (SQL Server Management Studio). Solution Microsoft SQL Server Management Studio (SSMS) lets the administrators to perform the following Configure SQL Server components. Create Database, tables, stored procedures and views. Manage data inside the database. and more… Follow the below steps to manage your SQL Server environment in Microsoft SQL Server Management Studio…

CASE expression in a Query in SQL Server

You can use the CASE expression in a T-SQL query in SQL Server to get the same behavior of the switch statement in the programming languages. Below is a sample Query demonstrating the implementation of the CASE expression in a query in SQL Server. CASE expression in a Query in SQL Server The query associates a Department group name to a country USA and UK…

Using Where clause in T-SQL Query

If you want to restrict the results of the query to only the records that you need instead of returning all the records from the table , you can use the WHERE clause to filter the records. You can simply specify the WHERE clause and also specify the conditions that the rows must meet. How to Filter records using WHERE clause in SQL Server ?…

Using FETCH and OFFSET to get N records in SQL Server

In SQL Server , you can use the OFFSET and FETCH and apply paging and retreive N records at a time. OFFSET AND FETCH clause are part of the ORDER BY clause and hence you must include the ORDER BY clause. For example , you might want to skip the first 4 records and retrieve 5 records from the query , you could use the…

Alias Names for Tables in SQL Query

There are times when you write query and end up qualifying the columns references by indicating the table name. For example , In the above query , the Department table is referred in the GroupName and Name column. Instead of mentioning the table table , we could provide a shortname or alias name for the table and use the alias name instead. The full use…

How to specify the Sort order for a Query in SQL Server ?

If you want the sort the records in descending order by more than one column instead of the default ascending order , you can use the DESC or DESCENDING to this. How to specify the Sort order for a Query in SQL Server ? Here’s the query which retrieves the Department records in Descending order by two columns – Name and GroupName.

How to get the Size of all tables in a SQL Server ?

In SQL Server 2014 Management Studio , you can get the size of all tables in a SQL Server from the Object explorer details window. How to get the Size of all tables in a SQL Server ? 1. In SQL Management Studio 2014 , navigate to the Object explorer details screen from View -> Object explorer details menu or by simply pressing the F7…

SQL Server 2014 Tutorial – Display System Databases in Object Explorer.

Problem Statement You need to view the System Databases in Object Explorer. Solution By default , there are few System databases that gets created when you install SQL Server. Some of these databases are master – This is the primary system database which is needed for the SQL Server to start and work fine. It contains the information about databases , logins , configurations etc….

How to disable Foreign Key constraints using T-SQL in SQL Server ?

You can enable or disable the foreign key constraints for a table in a SQL Server using the ALTER statement. How to disable Foreign Key constraints using T-SQL in SQL Server ? Here’s the T-SQL query to disable all the constraints of the table “Employee”. If you want to enable all the constraints of the table , here’s how you do it.

How to drop a table if it exists in SQL Server 2014 ?

Assume that you want to write a SQL Query in SQL Server which checks if the table exists in database and want to drop it , you can use the OBJECT_ID function to determine the table existence by passing the table name and the ‘U’ as parameters. How to drop a table if it exists in SQL Server 2014 ?

How to get the first character of a string in SQL Server ?

Assume a scenario where you have a column in your table in SQL Server database where you want to retreive the first character of the column value. How to get the first character of a string in SQL Server ? You can use the LEFT function and specify the column name as well as the number of characters from left that you want to retreive.

SQL Server 2014 Tutorial – Install SQL Server 2014 on Windows 10 machine

Problem Statement You need to install SQL Server 2014 Express edition on Windows 10 machine. Solution There are several ways in which you can install SQL Server. These includes options via command prompt, unattended, server core etc. In this tutorial, we will explore the simplest method using the SQL Server 2014 Setup wizard. Follow the below steps to install SQL Server 2014 1. Download SQL…

SQL Server 2014 Tutorial – List all the databases in SQL Server using Query

Problem Statement You need to list all the databases that are present in the current SQL Server instance. Solution The list of all the databases from the SQL Server instance can be obtained from the sys.databases object as shown below. 1. Open SQL Management Studio and connect to the SQL Server instance with your login details. 2. Click the “New Query” button and enter the…

SQL Server 2014 Tutorial – Manage SQL Server services

Problem Statement You need to manage the SQL Server services that are installed on your server machine. Solution Use SQL Server Configuration Manager tool to manage the SQL Server services on your machine. Following are some of the actions that you can perform using the SQL Server Configuration Manager – Start , Stop , Pause , Restart a SQL Server Service – Modify or configure…

Column Alias in SQL Query

When returning the query results , you want to provide an alternate name for the column for readability purpose , you can use the AS clause to specify the column alias in SQL Query. How to specify Column alias in SQL Query ? Below is an example of the usage of the column alias in SQL Query. Every column in the Query result has a…