The ‘Database’ Archives
Posted February 25, 2016 by

Find an Object’s Creation and Modification Dates in SQL Server

SQL Server is an all-in-one IDE for managing multiple user instances, SQL programming, and changing settings using graphical options. Often, we need to find out when a stored procedure/trigger was last modified, or we need to find the creation date of existing objects in SQL server. Finding the creation dates and the modification dates of […]

Posted February 11, 2016 by

SQL Server Three-Valued Logic and NOT IN clause with NULL values

While querying the production server to solve one critical issue, suddenly one query completely caught my attention. We had written a query which was created to find records which exist in Table A but do not exist in Table B, based on a certain column. The query was syntactically correct and ran without any errors, […]

Posted February 11, 2016 by

How to Find Nth/Second Highest and Lowest Salary in SQL

Based on what I have heard from friends and other online users and from my own personal experience, the most common job interview question for database programmers is “How do you find the highest salary in an employee table?” This question tests a candidate‚Äôs knowledge of ranking functions, subqueries, common table expression (CTE) and basic […]

Posted February 9, 2016 by

Beginners Guide to CHECK Constraints in SQL Server

During my schooling, one of my teachers said, “A friend of mine scored 105 on a science test out of 100. I was wondering how it is possible to score 105 out of 100. In this case, the value was inflated. Later, it was revealed that the score was a miscalculation. His score was 97. […]

Posted September 22, 2015 by

Delete and Update Rows Using Inner Join in SQL Server

Inner join is used to select rows from multiple tables based on a matching column in one or more tables. It compares each row value of a table with each row value of another table to find equal values. If equal value are found in multiple columns from multiple tables, they are returned in the […]

Category Database, SQL Server

Posted September 15, 2015 by

One-to-One, One-to-Many Table Relationships in SQL Server

Database designs are closely related to database relationships, the association between two columns in one or more tables. Relationships are defined on the basis of matching key columns. In SQL server, these relationships are defined using Primary Key-Foreign Key constraints. A link is created between two tables where the primary key of one table is […]

Category Database, SQL Server

Posted August 30, 2015 by

SQL Server: Enable and Refresh the IntelliSense Local Cache

SQL Server IntelliSense is one of the smartest built-in tools to increase productivity while programming in SQL Server. However, it is not perfect. Often, when we create new objects like tables, stored procedures, or triggers and try to use them instantly in another code, SQL Server IntelliSense does not pick up those object names and […]

Category Database, SQL Server

Posted August 28, 2015 by

SQL Server: SET NOCOUNT ON Statement with Examples

SET NOCOUNT ON is a set statement which prevents the message which shows the number of rows affected by T-SQL query statements. This is used within stored procedures and triggers to avoid showing the affected rows message. Using SET NOCOUNT ON within a stored procedure can improve the performance of the stored procedure by a […]

Category Database, SQL Server

Posted August 26, 2015 by

SQL Server: Union vs Union All

Union and Union All are used to combine two or more result sets in SQL. A Union set operator is different from SQL Joins. Union combines two sets whereas SQL Joins combines two or more columns based on a matching row condition. This tutorial describes the differences between Union and Union All based on function […]

Category Database, SQL Server

Posted June 14, 2015 by

Sargable Queries in SQL Server with Examples

The most interesting part of my job is performance tuning and optimization in T-SQL. The heart of performance tuning in an SQL server is proper and usable indexing on tables through the use of Sargable queries.

Posted June 2, 2015 by

Top Clause in SQL Server with Examples

Most of the time in real life, we try to find the top three scores in a class, the top five runners in a marathon, or the top 10 goals of the month. SQL server has a feature to select the TOP n records from a table.

Category Database, SQL Server

Posted May 28, 2015 by

DML, DDL, DCL and TCL Statements in SQL with Examples

SQL language is divided into four types of primary language statements: DML, DDL, DCL and TCL. Using these statements, we can define the structure of a database by creating and altering database objects, and we can manipulate data in a table through updates or deletions. We also can control which user can read/write data or […]

Category Database, SQL Server