Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 

SQL SERVER – Case Sensitive SQL Query Search using Collate

SQL SERVER – Case Sensitive SQL query search using Collate   SQL SERVER is not case sensitive therefore there are scenarios in which SQL query search may provide same result for various inputs which only differ with respect to ...

SQL SERVER: Copy structure of an existing table without data into new table

SQL SERVER: Copy structure of an existing table without data into new table   Create a table named "tblStudents" in database and insert some dummy data into it.   CREATE TABLE tblStudents ( StudentId INT NO...

Auto Generated Auto Incremented Alphanumeric Sequential Code in SQL SERVER

Create a table tbEmployeeMaster by following query :-   CREATE TABLE tbEmployeeMaster ( EmpId INT NOT NULL IDENTITY(1,1) PRIMARY KEY, EmpName VARCHAR(100), EmpCode VARCHAR(15) )  ...

Cursor in SQL Server

A cursor is a database object that can be created at runtime. It is used to get data  from a result set line by line(or row by row) rather than executing  all the rows in the result set at a single time like an SQL-transaction. Generall...

Pagination stored procedure in SQL Server

In this blog we illustrate how to get the pagination data from SQL server stored procedure. To create a such type of a stored procedure, we need to send a minimum three parameters(startRowIndex, pageSize, totalCount).     1. ...

Triggers in the SQL server

A trigger is a unique store procedure that is executes to give a response to particular action on the table of a database using  the following SQL statements: 1. Data Manipulation Language (DML) SQL Statements (like INSERT, UPDATE or DE...

SQl Server : Check table existence

A common problem encountered during SQL server development is to check if a particular table exists or not. There are different approaches to solve this problem and in this blog we will list out these approaches. For illustration purpose we will ...

SQl Server : Get size of tables in database

Many times when we  manage SQL server databases we need to  determine how much space each table is consuming on disk. In this blog we will learn two approaches for solving this frequently encountered common problem. Approach 1: We...

CONVERT() function in SQL Server

CONVERT()  function is basically used to converts an expression of one data type to another data type. This function display date/time data in different formats.   Syntax:- CONVERT(data_type(length),expression,style) data_type...

Different Types of SQL Keys

  Hi Reader’s,   In this blog we will discuss about the concept of keys used in SQL SERVER. These keys play a very important role in database related task in SQL like retrieve data/record from the table according to the...

SQL Inbuilt Functions

Hi Readers, In this blog, we will discuss the inbuilt functions of SQL which are used to perform calculations on data. All inbuilt functions are provided to make the task easier to do and we can easily complete a complex task with the help...

SQL Server : Best Practices

Following is a list of best practices for SQL Server 1) Use correct formatting    Following is a sample select query for reference:             SELECT X.column...

Avoid Concat function in SQL

While writing queries in stored procedures we manipulate with the string data type columns in which concatenating string is very common.   So we all are very familiar with the function CONCAT provided by the SQL Server for joining/merg...

DATEADD function in SQL

While manipulating with the date and time data of the SQL columns we have requirements where we want to alter the date or time based on some condition or for checking validation in our application.   For doing such kind of operation in...

What is STUFF in SQL Server

STUFF():-          STUFF is SQL SERVER function which is used to insert the string in another string. It deletes the specified number of characters from first string and insert the new string in the plac...

What is COALESCE in SQL Server

COALESCE():-      COALESCE is a function in SQL Server which returns the first non NULL expression amongst its parameter. If all the parameters are evaluated to NULL, It will return NULL Syntex:-    CO...

Limit Keyword in SQL

By default, all results that satisfy the conditions specified in the SQL statement are returned. However, this may not always be what we want, as sometimes we only want to retrieve a subset of records.   In MySQL, this is accomplished ...

How to trace SQL query or SP on a page

HI Guys, Some times we want to know that what SQL operation is running on a particular page or a event, then we have to debug our code base and we get the result, but it's time taking process to debug code line by line and as well as some ...

What is NOWAIT in SQL Server

Today i will discuss one interesting topic NOWAIT in SQL Server and would explain  how to use it.   As we know about the transaction in SQL it is unit of work which is to be executed as whole. Suppose we are updating some rec...

what is NOLOCK in SQL Server

Hi guys, I want to tell you the use of NOLOCK with simple example but before this lets discuss some terms   Lock:- When multiple users or applications access the non-shareable resource at the same time, Locking allows to access the ...

SQL datatype

SQL data type is an attribute that tell us the type of the object. SQL server has 6 type of datatype Exact numeric data type 1. bigint- Range start from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 2. int- Range start from ...

Differece between ROW_NUMBER(), RANK() and DENSE_RANK() in SQL Server

ROW_NUMBER() RANK() DENSE_RANK()   All of these three functions are used to calculate the Id of row but in different way. I am using the below script for examples ​ CREATE TABLE Marks ( SubjectId INT, Marks INT ...

What is view in Sql server

A View is a virtual table which is created form another table and its content are defined by a query. In real scenarios we use views for security purpose by allowing users to access data through the view, without granting the users permissions to...

Binary Relationship

A Binary relationship is when there is relation between two entities.This relationship is further divided into three types. One to One One to Many Many to one Many to Many 1.One to One: In this relationship, one entity can be assoc...

SQL COMMANDS

SQL is Structure Query Language which is developed by E.F.Codd.Today almost all Relational Database Management Systems (RDBMS) uses SQL as the standard database language. We can use it to do different types of operations in RDBMS. SQL COMMAND...

SQL Server : Read only databases

Databases whose data is not required to be changed should be considered to be set as READ ONLY.Databases can be set to READ ONLY mode and back using T-SQL and SSMS. Following are the scripts that can be used to set database read only and bac...

SQL Server : Useful datetime functions to find specific dates

Following are some useful SQL Server Date functions to find specific dates. 1) Today SELECT GETDATE() 'Today' 2) Yesterday SELECT DATEADD(d,-1,GETDATE()) 'Yesterday' 3) First Day of Current Week SELECT DATEADD(wk,DATEDIFF(wk...

SQL Server : Global Variables

SQL Server provides a number of global variables, which are very useful.The values of these variables is maintained by the server. All the global variables represent information specific to the server or a current user session.The names of global...

SQL Server : How to Enable or Disable All the Triggers on a Table and Database?

Sometimes we need to disable trigger on a table especially when performing admin tasks on a table. Following commands provide a quick way to disable all the triggers for a table. Please note that when we are diabling triggers on a table we w...

SQL Server : How to do case sensitive search?

Sometimes during application development we need to do case sensitive search. Let us see how we can do the same in SQL Server. For illustration purpose we will use a table called Technology which has a column named Platform.Now let us say colu...

SQL : How to update top N records in a table?

It is a common requirement in SQL server development to update top N records in SQL server.In this blog we will see two approaches to accomplish the same. 1) Using UPDATE with TOP UPDATE TOP (100) Table1 SET field1 = 1 However witho...

SQL Server : Error handling using TRY-CATCH

Before SQL Server 2005, the only practical way to trap errors in SQL was using the old-fashioned @@error system variable. Although this is still supported, in this blog we will learn how to use TRY-CATCH for handling errors.TRY-CATCH block is si...

SQL Server Transactions

A Transaction groups a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully finish.If all of these tasks execute successfully, then a transaction is committe...

SQL: Difference between inner join and outer join

Joins are used to combine the data from two tables and return specific rows of data from the tables. A join can be either an inner join or an outer join, depending on what is expected in the result. INNER JOIN: Gets all matching rows i...

To Run Stored Procedure everyday

For doing that we need to create procedure first and also define the repeating interval to call it again We will create procedure named getnotification from the particular database that gets fired after 3:30 hours on a daily basis. Wh...

SQL : How to get last N records based on ordering of a specific column in SQL Server ?

Sometimes we may be required to get the get last N records of a table in SQL server based on ordering on a specific column . In this post we will create a query to get the result. First let us create a table which we will use in our query. CR...

SQL : How to reset identity seed after deletion of records in SQL server ?

By using the seed value for every new record Microsoft SQL Server's identity column generates sequential values. In this post we will learn how to reseed an identity column. Following is the command to reset the identity property : DBCC C...

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

Often we may be required to get the names of all columns in a table in SQL server. In this post we will see the different options available in SQL server to fetch the column names. 1) We can use the below query to get the column names. SELE...

SQL : How to Delete using INNER JOIN in SQL Server ?

In this post we will see how to use JOIN for deleting data from SQl server table. Let us first create tables which we will use for understanding the deletion process using JOINS. -- Create table1 CREATE TABLE #Table1 (Col1 INT, Col2 VARCHAR(5...

SQL : How to check if a stored procedure exists before creation?

In this article we will learn how to check if a stored procedure exists before creating it. We can use the below script which will drop the proc if it exists and then recreate it. IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name...

SQl : How to replace a string in SQL server column ?

We know that the data can be changed inside a field by using update command. In this article I will guide you to replace part of the data without changing the entire data of the field. For this we will use replace command to replace a part of t...

Encryption in SQL

For providing security and privacy we need to store data in encrypted form. In SQL this can be done with series of steps: CREATE TABLE dbo.Employees( EmployeeID int primary key, EmployeeName varchar(50) NULL, n...

import assemblies in SQL

In SQL Server there are situations when you need to import assemblies to perform a specific task For doing that you need to write the path where assembly is present and provide the permissions to the assembly and associating with the particu...

To find duplicate records in SQL

In SQL if we dont have used primary keys then our id gets repeated accidentally or unintentionally. In this case we need to find out how many records are same. We need to find out the same tuples or rows from a table. For doing that...

SQL : How to pass an array of parameters to stored procedure using XML?

In this article we will see how to pass an array of parameters to a stored procedure using xml. For illustration purpose we will be passing the below list of id's : <ids> <id>1</id> <id>2</id> <...

SQL : How to get duplicate rows based on specific fields in table ?

In the following article we will go through a solution to a very common requirement of getting duplicate rows from SQL Server table based on specific columns. Let us first create a table and add sample data to this table. Col1 in the table is an...

SQL : How to remove duplicates in SQL table?

In the following article we will go through a solution to a very common requirement of removing delete duplicate rows from SQL server table. Let us first create a table and add sample data to this table. Col1 in the table is an identity column. ...

SQL : How to get count of duplicate records?

In the following article we will go through a solution to a very common requirement of getting count of duplicate rows from SQL server table. Let us first create a table and add sample data to this table. Col1 in the table is an identity column....

APEX SQL

APEX SQL APEX in SQL is a product from Microsoft that is used for synching of data stored on different servers. We have our main data stored in a main server and replica of those data are stored on different servers. Consider...

Temporary Tavles in SQL

Temporary tables in SQL are used for manipulation of data for a short period of time. It can be used as a accumulator like we have in microprocessor which acts as mediator to store and perform operations into it. Temporary table in ...
prev 1
Sign In
                           OR                           
                           OR                           
Register
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: