SQL Server is a relational database management system. It provides the functionality of storing and retrieving data to applications. Microsoft developed SQL Server on April 24, 1989. There are several editions of SQL Server serving different audiences.
By default SQL Server runs on port 1433. We can configure the server to listen to a specific port. This can be done by changing the TCP/IP properties from configuration-box in SQL Server.
Different index configurations possible for a table are:
When only a single clustered index is present.
When only a single non-clustered index is present.
When more than one non-clustered indexes is present.
When a single clustered index and a single non-clustered index is present.
When a single clustered index and more than one non-clustered indexes are present.
When there are no indexes present.
Indexing is a way of optimizing database query time. It helps in quickly locating and accessing the data in the database. It avoids full table scans.
| Clustered Index | Non-Clustered Index |
|---|---|
| It physically sorts the rows of a table based on the primary key or on a column that is unique and not null (generally we use primary key). | This is an index structure that is separate from the actual table which sorts one or more selected columns. Think of it as another table with few columns sorted together. For example, if you have a phone_book table where it is sorted by the name of a person and you want to know the count of people living in a specific country. What you’ll do? You’ll make another table with columns ‘country’ and ‘count_of_people’ which is sorted by the country name. Now finding the number of people in a given country will be much faster otherwise you will have to do the full table scan to get the answer. This is a non-clustered index. |
| Querying data is fast. A typical use case can be where there are range-based queries on the primary key. | Querying data is faster when there is no index in a table. A scenario where it can be used is when you want to find no. of people from a particular country in the phonebook. Even if the phonebook is sorted by name, you would like to have some kind of mapping of country with no. of people living there for a faster answer to such queries. |
| There can only be one clustered index per table. | There can be many non-clustered indexes per table. |
| It doesn’t need extra disk space. | It requires extra space to store those indexes. |
| It is faster than the non-clustered index. | It is slower than the clustered index in terms of SELECT queries. |
| Updation and Insertion are slow as the sorted order has to be maintained (can be faster when insertion always happens at the last, e.g.: Index on ID col). | Updation and Insertion are slow as the sorted order has to be maintained. |
Authentication means identifying a user based on its username and password.
Two authentication modes on SQL Server are
It is the default authentication mode in SQL Server. Trusted user and group accounts are authenticated when they login to the system. They do not have to present any additional credentials.
It supports Windows authentication as well as SQL Server authentication. Windows authentication is the same as above. SQL Server maintains a username and password for authentication of valid users.
You can choose an authentication mode by changing Server Authentication on Security page in Properties of SQL Server Management Studio.
Database design plays a vital role in the performance of SQL Server-based applications. Generally, when data is less, people do not care about database design. As when data is less there aren’t any noticeable performance issues because of bad database design. When data grows, there will be noticeable performance issues because of data redundancy. This is where normalization of the database comes into play. Right design principles ensure better performance at the later stage of software development. There will not be much redundant data. This will lead to reduced storage space. Also, it will reduce overhead to maintain consistency in the database.
Functions are part of SQL. A function in SQL Server is a group of statements that might take input, perform some task and return a result.
There are two types of function in SQL Server:
These functions are built-in ready-to-use and provided by SQL Server. Pass in input parameters if it takes one and get the result.
Example: Below code show min, max, and sum of ‘salary’ column values from ‘employee’ table
SELECT MIN(salary) AS MinSalary, MAX(salary) AS MaxSalary, SUM(salary) AS TotalSalary FROM employee
These are the functions that are written by users.
CREATE FUNCTION getAverageSalary(@salary int) RETURNS int AS BEGIN RETURN(SELECT @salary) END
CHECK constraint is applied to any column to limit the values that can be placed in it. It helps to enforce integrity in the database.
Suppose, your website caters to users between age 18 and 60 years. You can use CHECK to ensure that users who are creating an account have age in that range.
CREATE TABLE Users ( id int NOT NULL, first_name varchar(255) NOT NULL, last_name varchar(255) NOT NULL, age int CHECK (age >= 18 AND age <= 60) );
The trigger is a special type of stored procedure. It gets invoked automatically when an event like INSERT, UPDATE, DELETE, etc. happens on the database server. You can use it, for example, to enforce referential integrity in the database. Suppose you want to delete an author from the ‘authors’ table. You can use triggers to delete all rows in the ‘books’ table which has ‘author_id’ as of the deleted author.
Types of triggers:
DML trigger gets fired whenever a user tries to manipulate data using DML(Data Manipulation Language) event on the database server. DML events are INSERT, DELETE, or UPDATE.
DDL trigger gets fired whenever a user tries to manipulate data using DDL(Data Definition Language) event on the database server. DDL events are CREATE, ALTER, DROP, etc.
Logon trigger gets fired when a LOGON event is raised whenever a user’s session is created.
CREATE [OR REPLACE ] TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF } {INSERT | UPDATE | DELETE} [OF col_name] ON table_name [REFERENCING OLD AS o NEW AS n] [FOR EACH ROW] WHEN (condition) DECLARE Declaration-statements BEGIN Executable-statements EXCEPTION Exception-handling-statements END;
Temporary tables are the tables that ephemeral in nature. Temporary tables are useful when we want to work with a small subset of records from a large table. Instead of filtering records, again and again, we can store that small subset of records in a temporary table. We can perform our queries on it.
| Local Temporary Tables | Global Temporary Tables |
|---|---|
| Local temporary tables are only visible to that session of SQL Server that has created it. | Global temporary tables are visible to all SQL Server sessions. |
| Local temporary tables are automatically dropped when the session of the SQL Server that has created it is closed. | Global temporary tables are dropped when the last session of SQL Server referencing to the global temporary tables is closed. |
| Local temporary tables are prefixed with a single pound ‘#’ symbol. | Global temporary tables are prefixed with double pounds ‘##’ symbol. |
| SQL Server appends some random numbers at the end of the local temporary table name. | SQL Server doesn’t append any random numbers at the end of the global temporary table name. |
SQL Server Agent is a background tool for Microsoft SQL Server. It helps the database administrator(DBA) to schedule a job to run after a specific interval of time. These tasks can be scheduling backups, handling reporting services subscription or log-shipping tasks.
Suppose your server is running fine. Your application is booming all over the internet. Then, due to short-circuiting your servers went on fire. Now, all the data is gone and there’s nothing to show. Scary? It should be. This is the reason we always want to back up our data. So that in case of any disaster like hardware or software failure there will not be any data loss.
There are several types of backup options.
This backup includes all database objects, system tables, and data. Transactions that occur during the backup are also recorded.
This backup records the transactions since the previous backup. Previous backup can be transaction log backup or full backup (whichever happened last). It then truncates the transaction log. Transaction log backup represents the state of the transaction log at the time the backup is initiated rather than at the time the backup completes. Transaction log backup functions incrementally rather than differentially. When restoring transaction log backup, you must restore in the sequence of transaction log backups.
This backup backs the data that has been altered since the last full backup. Differential backup requires less time than a full database backup. Differential backups record transactions that occur during the differential backup process.
Scheduled tasks in SQL Server are predefined steps or tasks. SQL Server Agent automates these tasks. It executes them sequentially and at a scheduled time.
In SQL Server, COALESCE function returns the first non-null value from a list. If all the values evaluate to null then it will also return null.
SELECT COALESCE(NULL, 2, 1, 3) /* OUTPUT: 2 */
Exceptions in SQL Server are handled using the try and catch block.
BEGIN TRY --code which might raise exception END TRY BEGIN CATCH --code to run if error occurs in try block END CATCH
Try block contains the code which might raise exception. Catch block contains the code which runs in case an exception occurs in the try block.
BEGIN TRY --divide-by-zero error SELECT 5 / 0 AS Error; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_STATE() AS ErrorState, ERROR_SEVERITY() AS ErrorSeverity, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage; END CATCH;
The above code raises divide-by-zero error and handles it in the catch block. There are some special functions which are only usable inside the catch block. Outside the catch block they return NULL.
These functions are:
SQL Server Reporting Services(SSRS) is a set of tools that helps in generating reports. Businesses can use it for getting visual insights into data.
A common use case can be a shopping store. The owner of the store might want to see how the sales of different products are performing. The owner might also want to see the performances in a particular quarter of a year. What could be better than a beautiful and detailed visualization for this? SSRS does that exact thing. It will help us visualize data, meeting our requirements. We can see charts, graphs, and whatnot.
Log shipping is a process in which we automate the back-up process of transaction log files. We back-up the files from a primary database server to a secondary(stand by) database server.
To set up the log shipping process you must have sysadmin rights on the server.
Log shipping helps in mitigating the risk of disasters. In case of a disaster like a production server failure, we can use the secondary server.
SQL Server Master Data Services is a product made by Microsoft to develop the Master Data Management(MDM) solutions. It is built on top of SQL Server for backend processing.
The possibility of inconsistency in the database gives rise to the need for Master Data Management. It becomes especially important when there is a huge amount of data.
Consider a scenario, there is a Credit Card company which maintains a database. There is a table “Customer” which has the “Address” attribute. There is another table “Bills” which also has the “Address” attribute for a customer. Now suppose a customer moves to a different location and changes his address immediately. It gets updated in the “Customer” table but not in the “Bills” table. Now, this is a disaster as all the bills of this particular customer will be sent to the wrong address.
The disaster can be avoided by setting up a master customer data. All the tables would reference there for use. This way there will not be any inconsistency and extra memory consumption.
Hotfixes in SQL Server are the updates to fix issues which are not released publicly.
Patches in SQL Server are the updates to fix known issues or bugs. Microsoft releases patches publicly.
There are two magic tables in SQL Server: "inserted" and "deleted". These tables are called magic tables because they do not exist physically.
"inserted" table stores the data of last inserted row or “insert operation”. "deleted" table stores the data of last deleted row or “delete operation”.
Note: An update operation is actually a delete operation + an insert operation i.e., old data is deleted and new data is inserted.
For the sake of example, we assume there is a table with name Employee and columns ‘ID’ and ‘Name’.
SQL Server provides two ways to view data in magic tables.
One way is to use triggers on delete, insert or update operations in a table to view the data in “inserted” and “deleted” magic tables.
CREATE TRIGGER Inserted_Trigger ON Employee FOR INSERT AS BEGIN SELECT * FROM Inserted END
Whenever an insert operation will be done on Employee table, this trigger will be called and this will show the “inserted” table.
Use OUTPUT clause: Another way is to use the OUTPUT clause to view data in “inserted” or “deleted” magic tables. OUTPUT returns the copy of data inserted into or deleted from a table.
INSERT INTO Employee OUTPUT Inserted.* VALUES('Robert')