What is the Difference Between a CTE and a Temp Table?
CTE tables are used in a single query or procedure. Temp tables exist until the connection is closed.
SQL Server CTE Basics. … Introduced in SQL Server 2005, the common table expression (CTE) is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. You can also use a CTE in a CREATE VIEW statement, as part of the view’s SELECT query.
https://www.red-gate.com/simple-talk/sql/t-sql-programming/sql-server-cte-basics/
How do you create a table?
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
What is the SQL Check command?
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
What is the difference between a clustered and a nonclustered index?
A clustered index affects the way the rows of data in a table are stored on disk. When a clustered index is used, rows are stored in sequential order according to the index column value; for this reason, a table can contain only one clustered index, which is usually used on the primary index value.
A nonclustered index does not affect the way data is physically stored; it creates a new object for the index and stores the column(s) designated for indexing with a pointer back to the row containing the indexed values.
You can think of a clustered index as a dictionary in alphabetical order, and a nonclustered index as a book’s index.
/ **************** BELOW – LESS IMPORTANT ********************* /
What are temp tables?
Temporary tables are temporary storage structures. You may use temporary tables as buckets to store data that you will manipulate before arriving at a final format. The hash (#) character at the beginning of the table name is used to declare a temporary table. A single hash (#) specifies a local temporary table. A Double Hash is a global temp table
What is the difference between global and local temp tables?
CREATE TABLE #tempLocal ( nameid int, fname varchar(50), lname varchar(50) )
Local temporary tables are available to the current connection for the user, so they disappear when the user disconnects.
Global temporary tables may be created with double hashes (##). These are available to all users via all connections, and they are deleted only when all connections are closed.
CREATE TABLE ##tempGlobal ( nameid int, fname varchar(50), lname varchar(50) )
Once created, these tables are used just like permanent tables; they should be deleted when you are finished with them. Within SQL Server, temporary tables are stored in the Temporary Tables folder of the tempdb database.
Overall ADVANTAGE: You don’t have to drop table
What are Transactions in SQL? How are transactions used?
Transactions allow you to group SQL commands into a single unit. The transaction begins with a certain task and ends when all tasks within it are complete. The transaction completes successfully only if all commands within it complete successfully. The whole thing fails if one command fails. The BEGIN TRANSACTION, ROLLBACK TRANSACTION, and COMMIT TRANSACTION statements are used to work with transactions. A group of tasks starts with the begin statement. If any problems occur, the rollback command is executed to abort. If everything goes well, all commands are permanently executed via the commit statement.
What is the difference between truncate and delete?
Truncate is a quick way to empty a table. It removes everything without logging each row. Truncate will fail if there are foreign key relationships on the table. Conversely, the delete command removes rows from a table, while logging each deletion and triggering any delete triggers that may be present.
TRUNCATE:
- TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.
- TRUNCATE removes the data by deallocating the data pages used to store the table’s data, and only the page deallocations are recorded in the transaction log.
- TRUNCATE removes all rows from a table, but the table structure, its columns, constraints, indexes and so on, remains. The counter used by an identity for new rows is reset to the seed for the column.
- You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.
- TRUNCATE cannot be rolled back.
- TRUNCATE is DDL Command.
- TRUNCATE Resets identity of the table
- DELETE:
- DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.
- If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE
- DELETE Can be used with or without a WHERE clause
- DELETE Activates Triggers.
- DELETE can be rolled back.
- DELETE is DML Command.
- DELETE does not reset identity of the table.
Note: DELETE and TRUNCATE both can be rolled back when surrounded by TRANSACTION if the current session is not closed. If TRUNCATE is written in Query Editor surrounded by TRANSACTION and if session is closed, it cannot be rolled back but DELETE can be rolled back.
What does the NOLOCK query hint do?
Table hints allow you to override the default behavior of the query optimizer for statements. They are specified in the FROM clause of the statement. While overriding the query optimizer is not always suggested, it can be useful when many users or processes are touching data. The NOLOCK query hint is a good example because it allows you to read data regardless of who else is working with the data; that is, it allows a dirty read of data — you read data no matter if other users are manipulating it. A hint like NOLOCK increases concurrency with large data stores.
SELECT * FROM table_name (NOLOCK)
Microsoft advises against using NOLOCK, as it is being replaced by the READUNCOMMITTED query hint. There are lots more query hints with plenty of information online.
What is a query execution plan?
SQL Server has an optimizer that usually does a great job of optimizing code for the most effective execution. A query execution plan is the breakdown of how the optimizer will run (or ran) a query. There are several ways to view a query execution plan. This includes using the Show Execution Plan option within Query Analyzer; Display Estimated Execution Plan on the query dropdown menu; or use the SET SHOWPLAN_TEXT ON command before running a query and capturing the execution plan event in a SQL Server Profiler trace.
What is a view? What is the WITH CHECK OPTION clause for a view?
A view is a virtual table that consists of fields from one or more real tables. Views are often used to join multiple tables or to control access to the underlying tables.
WITH CHECK OPTION is an optional clause on the CREATE VIEW statement that specifies the level of checking to be done when inserting or updating data through a view. If the option is specified, every row that is inserted or updated through the view must conform to the definition of that view.
WITH CHECK OPTION cannot be specified if the view is read-only. The definition of the view must not include a subquery.
If the view is created without a WITH CHECK OPTION clause, insert and update operations that are performed on the view are not checked for conformance to the view definition. Some checking might still occur if the view is directly or indirectly dependent on another view that includes WITH CHECK OPTION. Because the definition of the view is not used, rows might be inserted or updated through the view that do not conform to the definition of the view. This means that the rows cannot be selected again using the view.
What does the SQL Server Agent Windows service do?
SQL Server Agent is a Windows service that handles scheduled tasks within the SQL Server environment (aka jobs). The jobs are stored/defined within SQL Server, and they contain one or more steps that define what happens when the job runs. These jobs may run on demand, as well as via a trigger or predefined schedule. This service is very important when determining why a certain job did not run as planned — often it is as simple as the SQL Server Agent service not running.
How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships. One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships. Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.
What is an execution plan? When would you use it? How would you view the execution plan?
An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad- hoc query and is a very useful tool for a developer to understand the performance characteristics of a query or stored procedure since the plan is the one that SQL Server will place in its cache and use to execute the stored procedure or query. From within Query Analyzer is an option called “Show Execution Plan” (located on the Query drop-down menu). If this option is turned on it will display query execution plan in separate window when query is ran again.
Which TCP/IP port does SQL Server run on? How can it be changed?
SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties.
What is a Primary key?
Only one can exist per table.
Both primary key and unique key enforces uniqueness of the column on which they are defined. By default primary key creates a clustered index on the column, Another major difference is that, primary key doesn’t allow NULLs.
What is a Unique key?
A unique key creates a nonclustered index by default. Unique Key allows one null.
What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
They both specify a search condition for a group or an aggregate. But the difference is that HAVING can be used only with the SELECT statement. HAVING is typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves like a WHERE clause. Having Clause is basically used only with the GROUP BY function in a query whereas WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
What is SQL Profiler?
SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures are hampering performances by executing too slowly.
What are the authentication modes in SQL Server? How can it be changed?
Windows mode and Mixed Mode – SQL and Windows.
Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate. You can nest stored procedures and managed code references up to 32 levels.
How to get the count of the number of records in a table?
SELECT COUNT(*) FROM table1
SELECT rows FROM sysindexes WHERE id = OBJECT_ID (table1) AND indid < 2
What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?
When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact-SQL rules for identifiers.
What is FOREIGN KEY?
A FOREIGN KEY constraint prevents any actions that would destroy links between tables with the corresponding data values. A foreign key in one table points to a primary key in another table. Foreign keys prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.
What is CHECK Constraint?
A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
What is NOT NULL Constraint?
A NOT NULL constraint enforces that the column will not accept null values. The not null constraints are used to enforce domain integrity, as the check constraints.
How to get @@ERROR and @@ROWCOUNT at the same time?
If @@Rowcount is checked after Error checking statement then it will have 0 as the value of @@Recordcount as it would have been reset. And if @@Recordcount is checked before the error-checking statement then @@Error would get reset. To get @@error and @@rowcount at the same time do both in same statement and store them in local variable.
SELECT @RC = @@ROWCOUNT, @ER = @@ERROR
What is a Stored Procedure? – A procedure that has its own script stored in the database. Script handle parameters and queries the database
What are the advantages of using Stored Procedures?
Stored procedure can reduced network traffic and latency, boosting application performance.
Stored procedure execution plans can be reused, staying cached in SQL Server’s memory, reducing server overhead.
Stored procedures help promote code reuse.
Stored procedures can encapsulate logic. You can change stored procedure code without affecting clients.
Stored procedures provide better security to your data.
What is subquery? Explain the Properties of a Subquery?
A subquery is a query within a query, usually encapsulated within Parenthesis.
A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?
Unindexed table or Heap. Microsoft Press Books and Book on Line (BOL) refers it as Heap. A heap is a table that does not have a clustered index and, therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together. Unindexed tables are good for fast storing of data. Many times it is better to drop all indexes from table and then do bulk of inserts and to restore those indexes after that.
What is a Trigger?
A trigger is an action set up to handle certain types or values of input.
They are a special class of stored procedure defined to execute automatically when an UPDATE, INSERT, or DELETE statement is issued against a table or view.
- What are the Different Types of Triggers? In Sql Server we can create four types of triggers
- Data Definition Language (DDL) triggers,
- Data Manipulation Language (DML) triggers
- CLR triggers
- Logon triggers.
What is a View?
A view is a virtual construction of one or more tables.
What is an Index?
An Index is a location, usually refers to a start.
What are Different Types of Join?
Inner, Outer, Union, (full, right, left)
Joins, self joins – Joining One table to itself.
http://blog.sqlauthority.com/2010/07/08/sql-server-the-self-join-inner-join-and-outer-join/
Join with Junction Table – Table that combines other tables that share fields. Constrained to each other.
What are the effects of nulls in table joins?
https://technet.microsoft.com/en-us/library/ms190409(v=sql.105).aspx
What are User-defined Functions? What are the types of User-defined Functions that can be created?
Like functions in programming languages, SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.
What is SQL Injection?
SQL Injection is an attack in which attacker taking advantage of an insecure application over internet by running the SQL command against the database and to steal information from it that too using GUI of the website.
This attack can happen with the applications in which SQL queries are generated in the code.
The attacker tries to inject their own SQL into the statement that the application will use to query the database.
For example suppose the below query string is used for a search feature in a website and a user may have the inserted “Arpit” as the keyword to search. If in the code behind the keyword is directly used into the SQL statement, it will be like.
String sql = “Select EmpName, City from EmployeeMaster where EmpName like ‘%” + txtSearch.Text + “%’”;
But the attacker might enter the keyword like
‘ UNION SELECT name, type, id from sysobjects;–
This way attacker can get details of all the objects of the application database and using that
attacker can steal further information.
What is Scheduled job and how to create it?
-If we want to execute any procedural code automatically at a specific time either once or repeatedly then we can create a Scheduled job for that code.r
-Following are the steps to create a Scheduled Job.
- Connect to your database of SQL server in SQL Server Management Studio.
- On the SQL Server Agent. There you will find a Jobs folder. Right click on jobs and choose Add New.
- A New Job window will appear. Give a related name for the job.
- Click next on the “Steps” in the left menu. A SQL job can have multiple steps either in the form of SQL statement or a stored procedure call.
- Click on the “Schedules” in the left menu. A SQL job can contain one or more schedules. A schedule is basically the time at which sql job will run itself. You can specify recurring schedules also.
-Using scheduled job you can also create alert and notifications.
What is a Cursor?
A cursor enables the rows in a result set to be processed sequentially. In SQL procedures, a cursor makes it possible to define a result set (a set of data rows) and perform complex logic on a row by row basis.
What are cursors and when they are useful?
-When we execute any SQL operations, SQL Server opens a work area in memory which is called Cursor.
-When it is required to perform the row by row operations which are not possible with the set-based operations then Cursor is used.
-There are two of cursors – Implicate Cursor and Explicit Cursor
When the programmer wants to perform the row by row operations for the result set containing more than one row, then he explicitly declare a cursor with a name.
They are managed by OPEN, FETCH and CLOSE.
%FOUND, %NOFOUND, %ROWCOUNT and %ISOPEN attributes are used in both types of cursors.
http://www.mssqltips.com/sqlservertip/1599/sql-server-cursor-example/
What is Normalization of database? What are its benefits?
-Normalization is a set of rules that are to be applied while designing the database tables which are to be connected with each other by relationships. This set of rules is called Normalization.
-Benefits of normalizing the database are
- No need to restructure existing tables for new data.
- Reducing repetitive entries.
- Reducing required storage space
- 4. Increased speed and flexibility of queries.
What is difference between stored procedure and user defined function?
It is not possible to change the table data with the use of User defined functions but you can do it by using stored procedure.
The execution of User defined function will be stopped if any error occurred in it. But in the case of Stored procedure when an error occurs the execution will ignore the error and jumps to the next statement.
We can use User defined function in XML FOR clause but we can use stored procedure in XML FOR clause.
It is not possible to make permanent changes to server environment whereas stored procedure can change some of the server environment.
User defined functions do not return output parameters while stored procedure can return output parameters.
UDFs – User Defined Function returns the numbers of days in month.
What are the basic functions for master, msdb, model, tempdb databases? (The Importance?)
Master database – contains catalog and data for all databases of the SQL Server instance and it holds the engine together. Because SQL Server cannot start if the master database is not working.
msdb database – contains data of database backups, SQL Agent, DTS packages, SQL Server jobs, and log shipping.
tempdb – contains temporary objects like global and local temporary tables and stored procedures.
Model – is a template database which is used for creating a new user database.
What is the difference between UNION and UNION ALL?
UNION – selects only distinct values whereas UNION ALL selects all values and not just distinct ones.
What are constraints?
SQL Server user’s constraints to enforce limitations on the data or types of data that can be entered into a particular column in table. There are following types of constraints.
Unique, Default, Check, Primary Key, Foreign Key, Not Null.
Can SQL Servers linked to other servers like Oracle?
SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. E.g. Oracle has an OLE-DB provider for oracle that Microsoft provides to add it as linked server to SQL Server group.
Extra Materials
SQL Tuning Using Execution Plan – Figuring out ways to make your database faster
http://www.codeproject.com/Articles/9990/SQL-Tuning-Tutorial-Understanding-a-Database-Execu
Temporary Tables – Local Temp Table
Local temp tables are only available to the current connection for the user; and they are automatically deleted when the user disconnects from instances. Local temporary table name is stared with hash (“#”) sign.
http://www.codeproject.com/Articles/42553/Quick-Overview-Temporary-Tables-in-SQL-Server-2005
Dynamic SQL – These are stored Procedures – The basic syntax for using sp_executesql:
http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure
Preventing SQL Injections – ENCRYPT DATA?
http://www.codeproject.com/Articles/9378/SQL-Injection-Attacks-and-Some-Tips-on-How-to-Prev
Transaction Management- A transaction is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.
SQL Server operates in the following transaction modes.
Autocommit transactions
Explicit transactions
Implicit transactions
Batch-scoped transactions
http://www.codeproject.com/Questions/289192/what-are-the-transaction-in-sql-server
Locks –
- Shared or S-locks – Shared locks are sometimes referred to as read locks. There can be several shared locks on any resource (such as a row or a page) at any one time. Shared locks are compatible with other shared locks.
- Exclusive or X-locks –Exclusive locks are also referred to as write locks. Only one exclusive lock can exist on a resource at any time. Exclusive locks are not compatible with other locks, including shared locks.
- Update or U-locks –Update locks can be viewed as a combination of shared and exclusive locks. An update lock is used to lock rows when they are selected for update, before they are actually updated. Update locks are compatible with shared locks, but not with other update locks.
http://www.codeproject.com/Articles/342248/Locks-and-Duration-of-Transactions-in-MS-SQL-Serve
SQl Server agent Jobs – SQL Server Agent allows you to automate a variety of administrative tasks.
http://databases.about.com/od/sqlserver/ss/sql_server_agent.htm
Defending Against SQL Injection Attacks
The good news is that there actually is a lot that web site owners can do to defend against SQL injection attacks. Although there is no such thing as a 100 percent guarantee in network security, formidable obstacles can be placed in the path of SQL injection attempts.
- Comprehensive data sanitization. Web sites must filterall user input. Ideally, user data should be filtered for context. For example, e-mail addresses should be filtered to allow only the characters allowed in an e-mail address, phone numbers should be filtered to allow only the characters allowed in a phone number, and so on.
- Use a web application firewall. A popular example is the free, open source moduleModSecurity which is available for Apache, Microsoft IIS, and nginx web servers. ModSecurity provides a sophisticated and ever-evolving set of rules to filter potentially dangerous web requests. Its SQL injection defenses can catch most attempts to sneak SQL through web channels.
- Limit database privileges by context. Create multiple database user accounts with the minimum levels of privilege for their usage environment. For example, the code behind a login page should query the database using an account limited only to the relevent credentials table. This way, a breach through this channel cannot be leveraged to compromise the entire database.
- Avoid constructing SQL queries with user input.Even data sanitization routines can be flawed. Ideally, using SQL variable binding with prepared statements or stored procedures is much safer than constructing full queries.
Test yourself with w3shcools.com
Complex stored procedures, study examples
