SQL Server database maintenance

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.

SQL Server database maintenance

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’

SQL Server database maintenance

Explain the concepts behind placing indexes with filegroups.

Indexes can be placed on different file groups to increase performance. By default, indexes are created on the same file group as the base table. Placing indexes with different filegroups are more commonly used when type of access cannot be predicted. Spreading the index across different file groups can be done to achieve this. This also ensures that all disks are being accessed since all data and indexes are spread evenly across all disks.

Example of creating an index on a specific file group:

CREATE UNIQUE CLUSTERED INDEX sample ON t1.c

Explain how to make views updatable in SQL Server 2000[Hint - using Instead of Triggers and Partitioned views]

INSTEAD OF Triggers: Cause the trigger to fire instead of executing the triggering event or action. It prevents unnecessary changes to be made.

Example: Causes the trigger to fire instead of the update (action)
CREATE TRIGGER Employee_update ON Employee INSTEAD OF
UPDATE AS

{
TRIGGER Definition
}

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 portioned view can either be local or distributed. A local portioned view resides on the same instance of the SQL server while the distributed may reside on a different 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