SQL - Basic Interview Questions

What is Stored Procedure?

Stored procedures are nothing but a stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database.

e.g.  sp_helpdb, sp_renamedb, sp_depends etc.

What is Trigger?

A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level. Stored procedures, however, are not event-drive and are not attached to a specific table as triggers are. Stored procedures are explicitly executed by invoking a CALL to the procedure while triggers are implicitly executed. In addition, triggers can also execute stored procedures.

Nested Trigger: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

What is View?

A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database. The data accessed through a view is actually constructed using standard T-SQL select command and can come from one to many different base tables or even other views.

What is Index?

An index is a physical structure containing pointers to the data. Indices are created in an existing table to locate rows more quickly and efficiently. It is possible to create an index on one or more columns of a table, and each index is given a name. The users cannot see the indexes; they are just used to speed up queries. Effective indexes are one of the best ways to improve performance in a database application. A table scan happens when there is no index available to help a query. In a table scan SQL Server examines every row in the table to satisfy the query results. Table scans are sometimes unavoidable, but on large tables, scans have a terrific impact on performance.

What is a Linked Server?

Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

What is Cursor?

Cursor is a database object used by applications to manipulate data in a set on a row-by-row basis, instead of the typical SQL commands that operate on all the rows in the set at one time. In order to work with a cursor we need to perform some steps in the following order:

Declare cursor

Open cursor

Fetch row from the cursor

Process fetched row

Close cursor

Deallocate cursor

VBA Vs Power BI, PowerPivot, PowerQuery, PowerView and PowerMap

VBA and the Power tools target different pieces of the pie and different use scenarios. For an analyst I would focus on the Power stuff and using VBA for production needs. 

VBA have been around for almost as long as MS Excel and in all this time VBA has created a space for itself and all its related software over many years, Software like Power BI, Power Pivot etc are all relatively new and can be seen as a high level problem solver where as with VBA, the level goes a lot lower and will most definitely involve role players at that lower level

VBA falls short in that many users are scared of it - the interface is too busy, too many moving parts and the object model takes a long time to get familiar with - it is ot particulary easy to break into VBA coding.

PowerPivot falls short in that the types of operations that it performs are somewhat more limited - the output is always a reconfigured version of what was put into it, with a few fancy calculations and metrics.

Where PowerQuery really shines is that you can take a small amount of data and make it big. Running a full fledged simulation is easy, contorting and expanding and blowing up your data is easy. A user can create reusable and global abstract functions and apply across a workbook. 

Power Query is the new swiss army knife for accountants and controllers in my eyes. Although "only meant" as a self-service ETL-tool, it's so versatile that you can extend it's use cases in so many areas.

Its main drawback compared to VBA - lack of runtime parameters, I/O, user prompts etc. is easily worked around by good design and trial and error. The programming language is relatively easy to pick up and more intuitive than VBA IMHO.

PowerQuery also easily interfaces with PowerPivot directly - it is not a either-or proposition, although I find that given how easy it s to mash-up tables in PowerQuery, I can do most of the heavy lifting that I would have used PowerPivot for directly in PowerQuery. I will concede that if I were better with PowerPivot, I might change my position, but The financial data that I work with and the projects that I have do not require the sort of fancy analysis that PowerPivot measures are better suited for. 

Powerquery is very underrated and under appreciated - much more powerful and useful in everyday tasks than VBA and even basic excel formulas.

The Power BI stuff appears to be on an unannounced track to replace SSRS. It is much better and modern. The new announcements about open source visualizations, mobile, and more will make Power BI even more competitive in that space. Take an xls combining Power BI using Sharepoint 2013+ data source and a few VBA supported easy buttons and you have a powerful tool.

If data cleansing is a big part of your work, PowerQuery should always come before PowerPivot. As a convert from VBA to PowerQuery, A huge drawback to VBA is that VBA is scary to outsiders - if you are not a programmer, you think it is like Pandora's Box; and if you are a programmer, you have likely seen to many excel "experts" that have macro-recorded themselves incredibly non-extendable and difficult to follow scripts that they cant even read their own code for.

PowerQuery is fresh and intuitive. If you are an outsider reading a query that a novice developed exclusively from the UI, you can pick it up and read it and make it your own with minimal effort.

Event Notifications vs. SQL Trace

Event Notifications convey the very same data as DDL triggers and occur on the very same events, but they are asynchronous and loosely coupled as SQL Trace. 

To know more on click on Event Notifications vs. Triggers.

SSRS - KPI Reports (Indicators and Gauges)

Five fearless white ducks

Funny baby- First School Day

Injured but fearless Pigeon

Difference between Trigger and Stored Procedures

 This topics is picked from Microsoft Business Intelligence (Data Tools) - Triggers.

Difference between Trigger and Stored Procedures

Contents	Triggers	Stored Procedures
Implementation	Triggers can only be implemented on tables or views. A trigger cannot be written within a stored procedure.	Stored Procedure in independent code that can be specific to database. A stored procedure can be written within a trigger
Event and action	Triggers are event and action based to perform some specific tasks and they are free to run automatically whenever the event is occurred on the table on which the trigger is defined	They are not based on the event and action. They are not able to run automatically and depend on the other manual process.
Execution Mode	They execute implicitly.	A stored procedure must be called explicitly.
Schedule Mode	This is not possible for triggers because they are event and action based.	Stored procedures can be easily scheduled through a job to execute on a predefined time.
Transactions	Transactions are not allowed in Triggers.	Transaction statements like begin transaction, commit transaction, and rollback can be used easily inside a stored procedure.
Parameters	This feature is not applicable in triggers.	Stored Procedures may or may not have one or more input/ output parameters to perform some tasks.
Front end applications	This feature is not applicable for triggers.	Stored Procedures can be easily called from the front end (.asp files, .aspx files, .ascx files, .rdl etc.)
Return Value specific	A trigger cannot return a value.	Stored procedures can return values.
Debugging	Print commands cannot work inside the trigger.	Print commands can work inside a stored procedure for debugging purposes.
Nesting	Directly you cannot call another trigger within a trigger.	A stored procedure can be called from inside another stored procedure.
Task Specific	The Triggers for auditing work: Triggers normally are used for auditing work. They can be used to trace the activities of table events.	Stored procedures are used for performing tasks. Stored procedures are normally used for performing user specified tasks.

To know more about SQL - Triggers.

SSRS – Chart based Drill-down Report

SSRS – Chart based Drill-down Report: A chart based drill down report is similar to row drill down report but main difference is that it’s based on the category groups and will...

Naughty Kids - Priceless childhood

SSRS - Column Based Drill Down

Microsoft Business Intelligence (Data Tools): SSRS - Column Based Drill Down Report: A Column Drill Down report is similar to row drill report but main difference is that it’s based on the columns and will provide the add...

SQL - Partition Indexes

Microsoft Business Intelligence (Data Tools): SQL - Partition Indexes: Table partitions are the most important feature of the SQL and provide a way to spread a single table over multiple partitions. In this w...

SQL – Indexes

Microsoft Business Intelligence (Data Tools): SQL – Indexes:

Indexes in a database are analogous to indexes that you find in a book. Indexes can help queries to find data quickly in a database. An index is a data structure (most commonly a B- tree) that stores the values for a specific column in a table. An index is created on a column of a table. An index consists of column values from one table, and that those values are stored in a data structure.  The index also stores pointers to the table row so that other column values can be retrieved – read on for more details on that. It is important to understand that a database index does not store the values in the other columns of the same table.

SQL - RESOURCE_SEMAPHORE

What is RESOURCE_SEMAPHORE in SQL?

RESOURCE_SEMAPHORE: 

Occurs when a query memory request cannot be granted immediately because of other concurrent queries. High waits and wait times can indicate excessive number of concurrent queries or excessive memory request amount. 

COMMON for DSS like workload and large queries such as hash joins; must wait for memory quota (grant) before it is executed. 

Difference between Filtered and Indexed View

Difference between Filtered Index and Indexed View

Filtered indexes have the following advantages over indexed views-

Criteria	Filtered Index	Indexed Views
Maintenance Costs	Reduced index maintenance costs because the query processor uses fewer CPU resources to update a filtered index	The query processor uses more CPU resources to update a Indexed View.
Plan Quality	Improved plan quality because during query compilation, the query optimizer considers using a filtered index in more situations	They are not so benificial in the query optimizer as Filtered Index
Online index rebuilds	A Filtered indexes while they are available for queries	Online index rebuilds are not supported for indexed views
Non-unique indexes	Filtered indexes can be non-unique	Indexed views must be unique
Only One Table	A Filtered Index is created on column(s) of a particular table.	Index Views can be created on column(s) from multiple base tables.
Simple WHERE criteria	A Filtered Index can not use complex logic in its WHERE clause, for example the LIKE clause, NOT IN, OR and dynamic / non-deterministic predicates like WHERE col >= DATEADD(DAY, -1, GETDATE()) are not allowed, only simple comparison operators are allowed.	This limitation does not apply to indexed views and you can design your criteria as complex as you want.

Slow Indexes

The Oracle database is rather verbose in this respect and has three distinct operations that describe a basic index lookup:

INDEX UNIQUE SCAN

The INDEX UNIQUE SCAN performs the tree traversal only. The Oracle database uses this operation if a unique constraint ensures that the search criteria will match no more than one entry.

INDEX RANGE SCAN

The INDEX RANGE SCAN performs the tree traversal and follows the leaf node chain to find all matching entries. This is the fallback operation if multiple entries could possibly match the search criteria.

TABLE ACCESS BY INDEX ROWID

The TABLE ACCESS BY INDEX ROWID operation retrieves the row from the table. This operation is (often) performed for every matched record from a preceding index scan operation.

SQL - Indexes Interview Questions

How many clustered indexes can a SQL Server table have?
A table can contain only one clustered Index. 

Do I need to have a clustered index on every table?
SQL Server doesn’t need to have a clustered index on every table. In absence of a clustered index, SQL Server uses a heap as table store.

How many indexes on a SQL Server table can be queried as fast as a clustered index?
You are not limited to one index. Any index can be as fast as a clustered index. Adding INCLUDE columns to a non-clustered index doesn’t affect anything else than this particular index. There is no penalty that hurts all other indexes!

How many indexes can support an index-only scan?
The simple answer is: as many as you like. 

Why and where do we need full text index?

What is the difference between noise words and stop words? 


when should be we noise words vs stop words?

To know more on SQL - Indexing. Please visit SQL - Indexex