Important SQL Server Concepts

Explain different types of Replication?

Snapshot Replication

Snapshot replication simply takes a "snapshot" of the data on one server and moves that data to another server (or another database on the same server). After the initial synchronization snapshot, replication can refresh data in published tables periodically—based on the schedule you specify. Although snapshot replication is the easiest type to set up and maintain, it requires copying all data each time a table is refreshed.

Between scheduled refreshes, data on the publisher might be very different from the data on subscriber. In short, snapshot replication isn't very different from emptying out the destination table(s) and using a DTS package to import data from the source.

Transactional Replication

Transactional replication involves copying data from the publisher to the subscriber(s) once and then delivering transactions to the subscriber(s) as they occur on the publisher. The initial copy of the data is transported by using the same mechanism as with snapshot replication: SQL Server takes a snapshot of data on the publisher and moves it to the subscriber(s). As database users insert, update, or delete records on the publisher, transactions are forwarded to the subscriber(s).

To make sure that SQL Server synchronizes your transactions as quickly as possible, you can make a simple configuration change: Tell it to deliver transactions continuously. Alternatively, you can run synchronization tasks periodically. Transactional replication is most useful in environments that have a dependable dedicated network line between database servers participating in replication. Typically, database servers subscribing to transactional publications do not modify data; they use data strictly for read-only purposes. However, SQL Server does support transactional replication that allows data changes on subscribers as well.

Merge Replication

Merge replication combines data from multiple sources into a single central database. Much like transactional replication, merge replication uses initial synchronization by taking the snapshot of data on the publisher and moving it to subscribers. Unlike transactional replication, merge replication allows changes of the same data on publishers and subscribers, even when subscribers are not connected to the network. When subscribers connect to the network, replication will detect and combine changes from all subscribers and change data on the publisher accordingly. Merge replication is useful when you have a need to modify data on remote computers and when subscribers are not guaranteed to have a continuous connection to the network.

Difference between ROLLUP and CUBE?

The CUBE and ROLLUP operators are useful in generating reports that contain subtotals and totals. There are extensions of the GROUP BY clause.

–> Difference b/w CUBE and ROLLUP:

– CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.

– ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns.

Let’s check this by a simple example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

select 'A' [class], 1 [rollno], 'a' [section], 80 [marks], 'manoj' stuName into #tempTable UNION select 'A', 2, 'a', 70 ,'harish' UNION select 'A', 3, 'a', 80 ,'kanchan' UNION select 'A', 4, 'b', 90 ,'pooja' UNION select 'A', 5, 'b', 90 ,'saurabh' UNION select 'A', 6, 'b', 50 ,'anita' UNION select 'B', 1, 'a', 60 ,'nitin' UNION select 'B', 2, 'a', 50 ,'kamar' UNION select 'B', 3, 'a', 80 ,'dinesh' UNION select 'B', 4, 'b', 90 ,'paras' UNION select 'B', 5, 'b', 50 ,'lalit' UNION select 'B', 6, 'b', 70 ,'hema' select class, rollno, section, marks, stuName from #tempTable

Output:
class rollno section marks stuName
A 1 a 80 manoj
A 2 a 70 harish
A 3 a 80 kanchan
A 4 b 90 pooja
A 5 b 90 saurabh
A 6 b 50 anita
B 1 a 60 nitin
B 2 a 50 kamar
B 3 a 80 dinesh
B 4 b 90 paras
B 5 b 50 lalit
B 6 b 70 hema

–> WITH ROLLUP:

1 2 3

select class, section, sum(marks) [sum] from #tempTable group by class, section with ROLLUP

Output:
class section sum
A a 230
A b 230
A NULL 460  -- 230 + 230  = 460
B a 190
B b 210
B NULL 400  -- 190 + 210 = 400
NULL NULL 860  -- 460 + 400 = 860 

–> WITH CUBE:

1 2 3

select class, section, sum(marks) [sum] from #tempTable group by class, section with CUBE

Output:
class section sum
A a 230
A b 230
A NULL 460  -- 230 + 230  = 460
B a 190
B b 210
B NULL 400  -- 190 + 210 = 400
NULL NULL 860  -- 460 + 400 = 860
NULL a 420  -- 230 + 190 = 420
NULL b 440  -- 230 + 210 = 440 

Explain WITH TIES IN SQL?

Used when you want to return two or more rows that tie for last place in the limited results set.

We have a table with 6 entires 1 to 4 and 5 twice.

Running

SELECT TOP 5 WITH TIES *
FROM MyTable 
ORDER BY ID;

returns 6 rows, as the last row is tied (exists more than once.)

Where as

SELECT TOP 5 WITH TIES *
FROM MyTable 
ORDER BY ID DESC;

returns only 5 rows, as the last row (2 in this case) exists only once.

What is Data Warehousing?

A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.

Using SQL Server Views?

A view is nothing more than a SQL statement that is stored in the database with an associated name. A view is actually a composition of a table in the form of a predefined SQL query.

A view can contain all rows of a table or select rows from a table. A view can be created from one or many tables which depends on the written SQL query to create a view.

Views, which are a type of virtual tables allow users to do the following −

• Structure data in a way that users or classes of users find natural or intuitive.
• Restrict access to the data in such a way that a user can see and (sometimes) modify exactly what they need and no more.
• Summarize data from various tables which can be used to generate reports.

The WITH CHECK OPTION

The WITH CHECK OPTION is a CREATE VIEW statement option. The purpose of the WITH CHECK OPTION is to ensure that all UPDATE and INSERTs satisfy the condition(s) in the view definition.

Updating a View

A view can be updated under certain conditions which are given below −

• The SELECT clause may not contain the keyword DISTINCT.
• The SELECT clause may not contain summary functions.
• The SELECT clause may not contain set functions.
• The SELECT clause may not contain set operators.
• The SELECT clause may not contain an ORDER BY clause.
• The FROM clause may not contain multiple tables.
• The WHERE clause may not contain subqueries.
• The query may not contain GROUP BY or HAVING.
• Calculated columns may not be updated.
• All NOT NULL columns from the base table must be included in the view in order for the INSERT query to function.

So, if a view satisfies all the above-mentioned rules then you can update that view. The following code block has an example to update the age of Ramesh.

SQL > UPDATE CUSTOMERS_VIEW SET AGE = 35 WHERE name = 'Ramesh';

Inserting Rows into a View
Rows of data can be inserted into a view. The same rules that apply to the UPDATE command also apply to the INSERT command.

Deleting Rows into a View

Rows of data can be deleted from a view. The same rules that apply to the UPDATE and INSERT commands apply to the DELETE command.

Following is an example to delete a record having AGE = 22.

SQL > DELETE FROM CUSTOMERS_VIEW WHERE age = 22;

Dropping Views
Obviously, where you have a view, you need a way to drop the view if it is no longer needed. The syntax is very simple and is given below 

DROP VIEW view_name;