SQL Server database maintenance

Explain the concepts of faster differential backups.

Differential backups are a faster mechanism of taking backups. The back up taken is only of the recently made changes in the data from the last differential backup taken. They support frequent backups which decrease the risk of data loss. The information about the differential database for each file is maintained in a catalog in the primary filegroup.

Explain the concepts of Parallel Database consistency check (DBCC)

DBCC performs a check on the tables and views for any corruptions. The command DBCC CHECKTABLE checks for integrity of the data, index, text, ntext, and image pages for the specified table or indexed view. DBCC checks if the pointers are consistent, data on each page is rational, the offsets of pages are proper and indexes are in their proper sort order. The DBCC CHECKTABLE returns a result set.

Example: To checks the data page integrity of the authors table.

DBCC CHECKTABLE (‘sample’)
GO

Define Indexed view.

An index view has a unique clustered index created on it. They exist as rows on the disk. Because they are saved on the disk, the response time to a query is fast at the cost of space consumption. They are more commonly used in scenarios when data modification is less.

Syntax:

Create Index
CREATE [UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name
ON table_name

The view is created using the CREATE VIEW syntax

Define Distributed partitioned views.

Partitioned view:
Partitioned view joins the horizontally portioned data. This data may belong to one ore more servers. It makes the data appear as one table. A partioned view can either be local or distributed. Distributed may reside on a different server. The original table is replaced with several smaller member tables. Each member table is on a separate member server.

Syntax: 
The view is then created by UNIONing all the tables and an updateable partitioned View results

Server 1 :
CREATE TABLE Customer1
(CustomerID INTEGER PRIMARY KEY
CHECK (CustomerID BETWEEN 1 AND 32999),
... -- Additional column definitions)
Similar tables created for Server 2 and 3
Partitioned view for server 1
CREATE VIEW Customers AS
SELECT * FROM CompanyDatabase.TableOwner.Customer1
UNION ALL
SELECT * FROM Server2.CompanyDatabase.TableOwner.Customer2
UNION ALL
SELECT * FROM Server3.CompanyDatabase.TableOwner.Customer3

SQL Server database maintenance 

Define Full-text indexing.

A full text index has an index of type FULL TEXT. Full text indexing can only done for CHAR, VARCHAR, or TEXT columns. Currently searching using FULL TEXT index is only available in MYISAM tables. The full text index can be specified while creating the table or altering the table.

For e.g. CREATE TABLE TEST_SAMPLE(notes TEXT, FULLTEXT(notes)) TYPE=MyISAM

For searching:

SELECT * FROM TEST_SAMPLE WHERE MATCH(notes) AGAINST('test');

Define Log shipping.

Log shipping is the process of shipping or automatically sending the transaction log which is already backed up at the primary server, to the secondary server. Once the log is copied to the secondary server instance, it is restored. The log can be shipped from one primary server instance to multiple secondary server instances. Log shipping increases data availability because if the primary database becomes unavailable, any of the secondary databases can be brought online manually.

What are the three types of Database files? Explain them. [Hint - Primary data file(.mdf), Secondary data files(.ndf), Log files(.ldf)]

Database files are used for mapping the database over some operating system files. Data and log information are separate. SQL server database has three types of database files:

Primary: starting point of a database. It also points to other files in database. Extension: .mdf

Secondary: All data files except primary data file is a part of secondary files. Extension: .ndf

Log files: All log information used to recover database. Extension: .ldf

User defined data type concepts, syntax and an example.

User defined data types are most commonly used when consistency among several tables storing the same type of data is desired. User defined data types can also be used when several tables must store the same type of data in a column .Consistency in terms of same data type, length, and null ability is required. The user defined data type in SQL is based on system data type. When a user defined data type is created it must have a name, a system data type, whether or nor it can accept NULL values.

Syntax:

Sp_addtype is used to create a user defined data type in transact SQL.

Example:

To create a user defined data type for postal code.

Sp_addtype postal_Code, ‘varchar(10)’ , ‘NOT NULL’