Tag: database
How to get the first day of the month in SQL Server ?
If you want to get the first day of the month of a input datetime variable from a SQL Query , here’s how you do it.
Exit the Current Scope with a return value in SQL Server
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 escape a single quote in SQL Server ?
You can escape a single quote in your T-SQL query in SQL Server by simply doubling them. Here’s a simple example that demonstrates how the single quote ie escaped in SQL Server. How to escape a single quote in SQL Server ?
How to change the ‘Edit Top 200 Rows’ in SQL Management Studio ?
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…
What is the equivalent of MySQL’s Now() function in SQL Server ?
MySQL has the function Now() which is a Datetime type field which shows the current date as well as the time. What is the equivalent of MySQL’s Now() function in SQL Server ? In SQL Server , you can use the GetDate() function to achieve the same.
How to rename a column in SQL Server ?
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 get all the column names of a table in SQL Server ?
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 ?
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…
GOTO Label in SQL Server
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…
NULLIF function in SQL Server
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…
Getting all the Foreign Key constraints list of a table in SQL Server
You might need to write a T-SQL Query to retrieve all the foreign key constraints of a given table in SQL Server. For these cases , you can use the sp_fkeys stored procedure. How to get all the Foreign Key constraints list of a table in SQL Server ?
How to return the SHA1 hash value of a string in SQL Server ?
SQL Server provides a function HashBytes function that returns the SHA1 , MD2 , MD4 and few other hash value of the specified input. How to return the SHA1 hash value of a string in SQL Server ? Just pass the ‘SHA1’ as the first parameter and the input as second parameter value to the HashBytes function to get the SHA1 hash value.