Tag: SQL Server

Exit the Current Scope with a return value in SQL Server

AbundantCode 6 days ago in SQL Server 0

You can use the RETURN statement to discontinue the execution of a T-SQL batch statement or a stored procedure and provide a status code or value on return. For example , you want to display the Employees whose MaritalStatus is Divorced. You want to return a value -1 to indicate that no records exist and also You do not want the SQL Statements following it…

How to change the ‘Edit Top 200 Rows’ in SQL Management Studio ?

AbundantCode 7 days ago in SQL Server 0

When working with the SQL Server Management Studio and trying to edit the table records , you would have noticed the context menu option “Edit Top 200 Rows”. How do you change this to display all the records for editing instead of “Edit top 200 rows” in the context menu ? There are couple of ways in which you can do it. 1. Right click…

How to rename a column in SQL Server ?

AbundantCode 7 days ago in SQL Server 0

One of the options for the user to rename a column in SQL Server is using the so_rename stored procedure and specifying the parameters. Here’s the syntax of the sp_rename. For example , if you want to rename the column description in the temp_abundantcode table to comment , this is how it looks.

How to Check for NULL value in a Query in SQL Server ?

AbundantCode 6 days ago in SQL Server 0

You might want to check if a column contains a NULL value or NOT and filter the rows. You can use the IS NULL and IS NOT NULL keywords in SQL Server to perform the NULL check. How to Check for NULL value in a Query in SQL Server ? The NULL values cannot be easily identified using the = operator because the NULL refers…

How to get all the column names of a table in SQL Server ?

AbundantCode 6 days ago in SQL Server 1

If you have a scenario where you need to get all the column names of a table in SQL Server , you can query the database’s INFORMATION_SCHEMA.Columns table by specifying the table name. How to get all the column names of a table in SQL Server ? Here’s a query to demonstrate how to achieve this. This would display all the columns of the table…

How to find the duplicate records on certain fields in SQL Server ?

AbundantCode 7 days ago in SQL Server 0

You can use the group by and having clause in your T-SQL query to find out the duplicate records from the table. How to find the duplicate records on certain fields in SQL Server ? Lets have a look at the query that uses the group by and having clause on the Person table in the AdventureWorks database. The firstname column is used as the…

SQL Server 2014 Tutorial – Assign TCP/IP Port to Database Engine

AbundantCode 7 days ago in SQL Server 1

Problem Statement You need to change the SQL Server 2014 port number from 1433 to a different port number. Solution By default , SQL Server runs on the port number 1433. You can change it to different port number by following the below steps. 1. Open SQL Server 2014 Configuration Manager from the Windows Start menu. 2. In the left side bar of the Sql…

GOTO Label in SQL Server

AbundantCode 6 days ago in SQL Server 0

There are times when you want to jump to a specific section (Label) in the code when a condition satisfies. You can create a label and then use the GOTO statement to branch directly to the code. To create a label , simply specify the label name followed by the colon. Label1: Here’s a query demonstrating the usage of the Label named “RecordExistsLabel” and GOTO…

How to Update From Select Statement in SQL Server ?

AbundantCode 6 days ago in SQL Server 0

Below is a sample query that demonstrates how to update a table by getting the data from the select query in SQL Server. Assume that there are 2 tables AbundantCodeEmployee and AbundantCodeDepartment which contains the id . The id(in department) in this example refers to the department head . The columns “DepartmentName” is updated in the query based on the value retreived  . How to Update From…

How to remove a column from an existing table in SQL Server?

AbundantCode 6 days ago in SQL Server 0

Use the DDL (Data definition language) query to remove a column from an existing table in SQL Server. How to remove a column from an existing table in SQL Server? ALTER command will allow the user to remove a column from the table. Syntax to remove the column from the table. ALTER TABLE <tablename> DROP COLUMN <columnname>; For example , assume that you have a…

How to get specific columns from a Table in SQL Query ?

AbundantCode 6 days ago in SQL Server 0

If you want to get the data from the specific columns from a table in your database , you can specify them in the query. For example, This would return the data of all the columns available in the Department table. If you want to get the data of the specific columns from the Department table , you can specify the column names in the…

NULLIF function in SQL Server

AbundantCode 6 days ago in SQL Server 0

The NULLIF function in SQL Server returns a NULL value if the two input expressions return the same value else the first value (expression) is returned. For example , assume that you want to compare two columns (Address1 and Address2) in the Address table and want to return NULL if both are same to indicate there is NO change and if the addresses are different…

How to Add a Column with Default Value to Existing Table in SQL Server ?

AbundantCode 6 days ago in SQL Server 0

Looking to add a column with default value to an existing table in SQL Server ? Below is an example and sample Data Definition Language Query for achieving the same. How to Add a Column with Default Value to Existing Table in SQL Server ? Assume that the table name is “ACEmployee” which already exists and you want to add a new column called “Status”…