“What is the difference between a primary key constraint and a unique key constraint?”
This is probably the most frequently-asked job interview question for database developers. In this article, we will try to answer it.
Let’s start by looking at what primary and unique keys are, as well as at their similarities.
Prior to SQL Server 2016, table-level security was the default lowest level of security for a database. In other words, a user could be restricted to access a table as a whole. However, in some cases we need users to have access to a table, but not to specific rows within the table. Prior to SQL Server 2016, this required custom stored procedures to be written for the provision of such fine-grained security. However, such stored procedures are prone to SQL injection and other security caveats.
Real-world database applications need to make multiple requests from the front end to the database in order to carry out all sorts of functions.
If an application is data-intensive, such as the ones used in banks or airports etc, the number of data trips can be huge. Each request to a database utilizes bandwidth and requires time to execute. Without the table-valued parameters, a front application needs to make multiple data trips in order to manipulate multiple rows of data. However, with table-valued parameters, multiple rows can be inserted, updated and deleted from a database using a single parameterized command that takes a table-valued parameter. Read More
A database can have hundreds of thousands of records. It is easy to insert and select these records via database management systems like SQL Server or MySQL etc. However, it is not easy to display thousands of records on a single webpage or in a desktop application. Space and memory constraints make it difficult to display a huge number of records all at once.
A common solution to such a problem is to implement paging. (Note, this is not memory paging implemented by operating systems) Paging in programming refers to displaying data via a series of pages. A random Google search may result in thousands of results. Google uses paging to display these results. If you scroll down the Google page with search results you would see the following:
Here you can see the number of pages that the search result is divided into. You can click the Next link to see more pages.
In this article, we will see how OFFSET FETCH NEXT operators can be used to implement paging on front-end applications. We will begin with a simple example using the OFFSET FETCH NEXT operator and will then see how it can be practically used with the help of a stored procedure.
The RANK, DENSE_RANK and ROW_NUMBER functions are used to retrieve an increasing integer value. They start with a value based on the condition imposed by the ORDER BY clause. All of these functions require the ORDER BY clause to function properly. In case of partitioned data, the integer counter is reset to 1 for each partition.
In this article, we will study the RANK, DENSE_RANK and ROW_NUMBER functions in detail, but before that, let’s create dummy data that these functions can be used on unless your database is fully backed up. Read More
Both EXCEPT and NOT IN operators are used to filter records from a table based on a specific criterion. In this article, we will look at these operators in detail and will investigate the differences between them. Read More
The ROLLUP and CUBE operators are used to return results aggregated by the columns in the GROUP BY clause.
The GROUPING and GROUPING_ID functions are used to identify whether the columns in the GROUP BY list are aggregated (using the ROLLUP or CUBE operators) or not.
There are two major differences between the GROUPING and GROUPING_ID Functions.
They are as follows:
On the other hand, the GROUPING_ID function also returns an integer. However, it performs the binary to decimal conversion after concatenating the outcome of all of the GROUPING functions.
In this article, we will see the GROUPING and GROUPING_ID functions in action with the help of examples. Read More
A logon trigger, as the name suggests, is a trigger that fires in response to a LOGON event in SQL Server.
In simple terms, a logon trigger fires whenever someone tries to establish a new connection to a database server. The trigger fires after the user authentication and the login phase completes but before the user session is actually initiated. Read More
The PIVOT statement is used to convert table rows into columns, while the UNPIVOT operator converts columns back to rows. Reversing a PIVOT statement refers to the process of applying the UNPIVOT operator to the already PIVOTED dataset in order to retrieve the original dataset.
In this article, we will study these three concepts on different examples.
You often come across scenarios where you have to calculate a running total of a quantity.
A running total refers to the sum of values in all cells of a column that precedes the next cell in that particular column.
Let’s take a look at an example to make this clearer.