SQL Server - What is database or database management systems (DBMS)?

DBMS (Database Management System ) is a software program (‘'s) designed to define, manipulate, retrieve and manage data in a database.


SQL Server - What are examples of DBMS?

MySQL, PostgreSQL, Microsoft Access, SQL Server, FileMaker, Oracle, RDBMS, dBase, Clipper, and FoxPro


SQL Server - Normalization In Database - 1NF, 2NF, 3NF and BCNF

 

 

 

Normalization the process of organizing data in a database that includes creating tables and establishing relationships between the tables.

The process is used to help eliminate redundant data.

Five normalization forms (NF's):

In this article, we are going to cover first three normaizations, which are most frequently used.


SQL Server -Difference between Stored Procedure and User Defined Function

 

 

 

Difference between Function and Stored Procedure (UDF vs SP)

  • Function can be used inside Select / Having / Where statement. Stored procedure can be used in Select / Having / Where section.
  • Stored procedure can call function but function can not call stored procedure
  • Function must return output but in stored procedure is it not mandatory to return output
  • Stored procedure can have output parameter but function will not have output parameter.
  • Function can not be used for Insert/Update, its select only. Stored procedure can be used for Insert/Update/Select.
  • Stored procedure can have exception handling code ( try-catch block ) . Exception handling code is not allowed in function.
  • Output Table valued functions can be used similar to table objects in SQL.
  • Stored procedures are complied code but function are compiled every time it execute. 
  • Functions stops execution on error. Stored procedure it just ignores the
    error and move to next statement.

SQL Server - Query Performance Optimization

 

    Analyze Poor Performing Queries For Optimization

  • Query Only When You Must

  • Select Only Columns You Need

  • Use Parameterized Queries As Much As Possible

  • Avoid correlated subqueries

  • Rewrite Subqueries to Use JOIN

  • Avoid Looping

  • Use CTE – Common Table Expression

  • If Possible Move Frequently Used Queries Into Views

  • Apply Indexes Effectively

    • Create Highly-Selective Indexes

    • Create Multiple-Column Indexes

    • Avoid Indexing Small Tables

    • Use Indexes with Filter Clauses

      • SARG operators  include =, >, <, >=, <=, IN, BETWEEN, and LIKE .

    • Index the ORDER-BY / GROUP-BY / DISTINCT Columns for Better Response Time

    • Use Execution Plan tool in SQL Server for creating indexes


SQL Server - Delete vs Truncate

 

  Delete Truncate
Type DML DDL
Rollback Possoble Not possible
Space Reclamation Does not recover space Recover space
Indexes NA Renders unusable indexes usable again
Object Type Delete can be applied to tables and tables inside a cluster. Truncate applies only to tables or the entire cluster.
Identity Columns NA Resets the sequence for IDENTITY column types
Locking At row level At table and page level
Where Clause Can use where clause Can not use in where clause
Data Removal Deletes specified data if where condition exists Removes all the data
Speed Slower than truncate Faster in performance wise
Keeps Logs Yes No
Activate Trigger Activates a trigger because the operation is logged individually Cannot activate a trigger because the operation does not log individual row deletions.

 


SQL Server - What is Indexes?

An index is used to speed up the performance of queries. It does this by reducing the number of database data pages that have to be visited/scanned.

  • An index can be used to efficiently find all row matching some column in your query and then walk through only that subset of the table to find exact matches.
  • If you don't have indexes on any column in the 'where' clause, the SQL server have to walk through the whole table and check every row to see if it matches, which may be a slow operation on big tables.

Types of Indexes:

  • Clustered Index

    • clustered index determines the physical order of data in a table. There can be only one clustered index per table.
    • If you create a primary key on the table you create clustered index on this table. The primary key is always clustered index.
  • Non-Clustered Index

    • All other indexes on a table are termed non-clustered.
    • A non-clustered index is also a binary tree but it doesn't create a physical order of rows. So the leaf nodes of non-clustered index contain PK (if it exists) or row index.

A table without a clustered index is called a "heap" because it's a pile of unstructured data‚Äč


SQL Server - Index Seek vs Index Scan

An index scan is where SQL server reads the whole of the index looking for matches - the time this takes is proportional to the size of the index.

|–Table Scan(OBJECT:([ORDERS]), WHERE:([ORDERKEY]=(2)))

An index seek is where SQL server uses the b-tree structure of the index to seek directly to matching records

|–Index Seek(OBJECT:([ORDERS].[OKEY_IDX]), SEEK:([ORDERKEY]=(2)) ORDERED FORWARD)

 

  • In general an index seek is preferable to an index scan (when the number of matching records is proprtionally much lower than the total number of records), as the time taken to perform an index seek is constant regardless of the toal number of records in your table.
  • Note however that in certain situations an index scan can be faster than an index seek (sometimes significantly faster) - usually when the table is very small, or when a large percentage of the records match the predicate.

SQL Server - Implicit vs Explicit Transaction

Implicit transaction:

  • The transaction remains in effect until you issue a COMMIT or ROLLBACK statement. After the first transaction is committed or rolled back, the instance of the Database Engine automatically starts a new transaction the next time any of these statements is executed by the connection. The instance keeps generating a chain of implicit transactions until implicit transaction mode is turned off.
  • It can be set using SET IMPLICIT_TRANSACTIONS { ON | OFF}
  • Under a multiple active result sets (MARS) session, a Transact-SQL implicit transaction becomes a batch-scoped transaction. If the batch-scoped transaction is not committed or rolled back when the batch completes, SQL Server automatically rolls back the transaction. 

Explicit Transaction:

  • An explicit transaction is one in which you explicitly define both the start and end of the transaction.
  • SET IMPLICIT_TRANSACTIONS { ON | OFF}
  • DB-Library applications and Transact-SQL scripts use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, or ROLLBACK WORK Transact-SQL statements to define explicit transactions.

SQL Server - Using cursor with example (Iteration-looping)

Using cursor in Sql server with example (Iteration-looping)

Processing each record Row By Row - one at a  time is need of most Sql Developer.  Most of us know its using cursor but might forgot syntax.
Here is syntax / example :

PRINT 'DECLARE VARIBLE REQURED DURING ITERATION'

DECLARE @Row_EmployeeName AS VARCHAR(255)

PRINT 'DECALRE CURSOR'

DECLARE MyCursor CURSOR FOR

SELECT TOP 3 Name as EmployeeName

FROM employee

ORDER BY Name

PRINT 'OPEN CURSOR'

OPEN MyCursor

PRINT 'GET NEXT RECORDD - Here it will be first into @Row_EmployeeName'

FETCH NEXT FROM MyCursor

INTO @Row_EmployeeName

PRINT 'CHECK IF WE HAVE RECORDS- if yes iterate else stop'

IF @@FETCH_STATUS <> 0

PRINT ' <<None>>'

PRINT 'ITERATE EACH ROW';PRINT'==============================================================';

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT ' PROCESSING CODE- TO DO - FOR EACH RECORD'

PRINT ' @Row_EmployeeName -- ' + @Row_EmployeeName

PRINT ' GET NEXT RECORDD # - into @Row_EmployeeName'

PRINT '==============================================================';

FETCH NEXT FROM MyCursor

INTO @Row_EmployeeName

END

PRINT 'CLOSE CURSOR'

CLOSE MyCursor;

PRINT 'DEALLOCATE CURSOR'

DEALLOCATE MyCursor;

Explanation step by step :

1. 1. DECALRE CURSOR

Here we assign name (‘MyCursor’) to cursor and then get data for processing. In our example we are going to process each row in employee table, will be using Name as our field to process/key field.

DECLARE MyCursor CURSOR FOR

SELECT TOP 3 Name as EmployeeName

FROM employee

ORDER BY Name

2. 2. DECLARE VARIABLE

We need to declare variable so that during processing each row one by bone we can assign it value of Key field from source

DECLARE @Row_EmployeeName AS VARCHAR(255)

3. 3. OPEN CURSOR AND CLOSE CURSOR

Now our cursor has data. Following is syntax to open and close cursor.

OPEN MyCursor

...

CLOSE MyCursor

4. 4. SET VALUE TO VARIABLE

Following syntax basically set next key value. It will get first record if it’s not been processed.

FETCH NEXT FROM MyCursor

INTO @Row_EmployeeName

5. 5. PROCESS EACH ROW

Now cursor has all values and between opening and closing cursor we need to write code for processing each row one by one. We already set value of first record using step 4.

Following is syntax to process all row (including first row ):

IF @@FETCH_STATUS <> 0

PRINT ' <<None>>'

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT ' PROCESSING CODE- TO DO - FOR EACH RECORD'

PRINT ' @Row_EmployeeName -- ' + @Row_EmployeeName

PRINT ' GET NEXT RECORDD # - into @Row_EmployeeName'

PRINT '===============================================';

FETCH NEXT FROM MyCursor

INTO @Row_EmployeeName

END

Here @@FETCH_STATUS will become 0 if there is no row or all rows have been processed.

That’s why we are using it in while and when one record is processed need to fetch next using

FETCH NEXT FROM MyCursor

INTO @Row_EmployeeName

6. 6. DEALLOCATE CURSOR

Once we are done with all processing best practice is to deallocate cursor .

Hope this useful !!!


SQL Server - What is cursor?

User SQL cursor when want to traverse rows one by one and perform some operations.

Example:

DECLARE vend_cursor CURSOR

FOR SELECT * FROM Purchasing.Vendor

OPEN vend_cursor

FETCH NEXT FROM vend_cursor

WHILE @@FETCH_STATUS = 0

BEGIN

--Perfrom some operation

END

CLOSE vendor_cursor;

DEALLOCATE vendor_cursor;

 


SQL Server - What is Ranking Functions?

Ranking functions return a ranking value for each row in a partition.

Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.

Ranking Functions:


SQL Server - Temporary table

  • Table variables (DECLARE @t TABLE) are visible only to the connection that creates it, and are deleted when the batch or stored procedure ends.

  • Local temporary tables (CREATE TABLE #t) are visible only to the connection that creates it, and are deleted when the connection is closed.

  • Global temporary tables (CREATE TABLE ##t) are visible to everyone, and are deleted when all connections that have referenced them have closed.

  • Tempdb permanent tables (USE tempdb CREATE TABLE t) are visible to everyone, and are deleted when the server is restarted.