When using the const keyword to set a constant variable, the value needs to be set at compile time. The const keyword may not be used with an object of reference type.

In case a reference type needs to be assigned a non-changeable value, it may be set as readonly

Reference types are stored on the run-time heap; they may only be accessed through a reference to that storage. Because reference types are always accessed through references, their lifetime is managed by the .NET Framework. Outstanding references to a particular instance are tracked and the instance is destroyed only when no more references remain. A variable of reference type contains a reference to a value of that type, a value of a more derived type, or a null reference. A null reference is a reference that refers to nothing; it is not possible to do anything with a null reference except assign it. Assignment to a variable of a reference type creates a copy of the reference rather than a copy of the referenced value. For a variable of a reference type, the default value is a null reference.

Value types are stored directly on the stack, either within an array or within another type; their storage can only be accessed directly. Because value types are stored directly within variables, their lifetime is determined by the lifetime of the variable that contains them. When the location containing a value type instance is destroyed, the value type instance is also destroyed. Value types are always accessed directly; it is not possible to create a reference to a value type. Prohibiting such a reference makes it impossible to refer to a value class instance that has been destroyed. Because value types are always NotInheritable, a variable of a value type always contains a value of that type. Because of this, the value of a value type cannot be a null reference, nor can it reference an object of a more derived type. Assignment to a variable of a value type creates a copy of the value being assigned. For a variable of a value type, the default value is the result of initializing each variable member of the type to its default value.

What is RDBMS?

Relational Data Base Management Systems (RDBMS) are database management systems that maintain data records and indices in tables. Relationships may be created and maintained across and among the data and tables. In a relational database, relationships between data items are expressed by means of tables. Interdependencies among these tables are expressed by data values rather than by pointers. This allows a high degree of data independence. An RDBMS has the capability to recombine the data items from different files, providing powerful tools for data usage.

What is normalization?

Database normalization is a data design and organization processes applied to data structures based on rules that help build relational databases. In relational database design, the process of organizing data to minimize redundancy. Normalization usually involves dividing a database into two or more tables and defining relationships between the tables. The objective is to isolate data so that additions, deletions and modifications of a field can be made in just one table and then propagated through the rest of the Database via the defined relationships.

What is denormalization and when would you go for it?

As the name indicates, denormalization is the reverse process of normalization. It’s the controlled introduction of redundancy in to the database design. It helps improve the query performance as the number of joins could be reduced.

What is user defined data types and when you should go for them?

User defined data types let you extend the base SQL Server data types by providing a descriptive name, and format to the database Ex: EXEC sp_addtype lval, ‘varchar (24)', 'NULL'

What is bit data type and what's the information that can be stored inside a bit column?

Bit data type is used to store Boolean information like 1 or 0 (true or false) and now you can also store NULL values also.

Define candidate key, alternate key, and composite key.

A candidate key is one that can identify each row of a table uniquely. Generally a candidate key becomes the primary key of the table. If the table has more than one candidate key, one of them will become the primary key, and the rest are called alternate keys. A key formed by combining at least two or more columns is called composite key.

What's the difference between a primary key and a unique key?

Both primaries key and unique enforce uniqueness of the column on which they are defined. But by Default primary key creates a clustered index on the column, where are unique creates a non- clustered Index by default. Another major difference is that, primary key doesn't allow Nulls, but unique key Allows one NULL only.

What are defaults? Is there a column to which a default can't be bound?

A default is a value that will be used by a column, if no value is supplied to that column while inserting data. IDENTITY columns and timestamp columns can't have defaults bound to them

What's the maximum size of a row?

8060 bytes. 1024 columns per table

What is Stored Procedure?

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.

Auto start or Auto Execute Stored Procedures

SQL Server has the ability to mark a stored procedure as autostart. Autostart stored procedures are useful if you want to perform housekeeping functions regularly or if you have a background daemon procedure that's always expected to be running. You can use the system stored procedure sp_procoption to make a stored procedure start automatically. This procedure allows you to turn options for stored procedures on or off. In SQL Server 2000, the only available option is startup. Here's the syntax of the command to enable a procedure to automatically execute on SQL Server startup: sp_procoption procedure_name,startup,true

System Stored Procedures

SQL Server installs a large number of system stored procedures that are used mostly for administrative and informational purposes. In many cases, these are called behind the scenes by the SQL-DMO objects used by SQL Server Enterprise Manager and other applications. But the system stored procedures can also be called directly; Below are some of the SQL Server system stored procedures, divided into four categories: basic, security, cursor, and distributed query procedures.

Some basic system procedures:

sp_help, sp_helptext, sp_who, sp_grantdbaccess, sp_cursor_list

sp_who Provides information about current Microsoft® SQL Server™ users and processes

Extended Stored Procedures

You can use extended stored procedures to create your own external routines in a language such as C and have SQL Server automatically load and execute those routines just like a regular stored procedure. These are DLLs that can be dynamically loaded and executed.

Extended stored procedures are DLLs that SQL Server can dynamically load and execute. They aren't separate processes spawned by SQL Server—they run directly in the address space of SQL Server. The DLLs are created using the Open Data Services (ODS) API, which SQL Server also uses.

When does one need to recompile stored procedure?

If a new index is added from which the stored procedure might benefit, optimization does not automatically happen (until the next time the stored procedure is run after SQL Server is restarted).

SQL Server provides three ways to recompile a stored procedure:

The sp_recompile system stored procedure forces a recompile of a stored procedure the next time it is run.

Creating a stored procedure that specifies the WITH RECOMPILE option in its definition indicates that SQL Server does not cache a plan for this stored procedure; the stored procedure is recompiled each time it is executed. Use the WITH RECOMPILE option when stored procedures take parameters whose values differ widely between executions of the stored procedure, resulting in different execution plans to be created each time. Use of this option is uncommon, and causes the stored procedure to execute more slowly because the stored procedure must be recompiled each time it is executed.

You can force the stored procedure to be recompiled by specifying the WITH RECOMPILE option when you execute the stored procedure. Use this option only if the parameter you are supplying is atypical or if the data has significantly changed since the stored procedure was created.

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; the 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.

What are INSERTED and DELETED Tables

Two special tables are used in trigger statements: the deleted table and the inserted table. SQL Serverâ„¢ 2000 automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly. The inserted and deleted tables are used primarily in triggers to:

Extend referential integrity between tables

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?

It is a virtual table that stores the Query to execute and return a result set. ( It does not actually store any data)

A view is a virtual table whose contents are defined by a query. Like a real table, a view consists of a set of named columns and rows of data. However, a view does not exist as a stored set of data values in a database. The rows and columns of data come from tables referenced in the query defining the view and are produced dynamically when the view is referenced.

VIEW WITH CHECK OPTION

Forces all data modification statements executed against the view to adhere to the criteria set within select_statement. When a row is modified through a view, the WITH CHECK OPTION ensures the data remains visible through the view after the modification is committed.

VIEW WITH ENCRYPTION

Indicates that SQL Server encrypts the system table columns containing the text of the CREATE VIEW statement. Using WITH ENCRYPTION prevents the view from being published as part of SQL Server replication.

What is User Defined Functions?

User-Defined Functions allow defining its own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type.

What kind of User-Defined Functions can be created?

There are three types of User-Defined functions in SQL Server 2000 and they are Scalar, Inline Table-Valued and Multi-statement Table-valued.

Scalar User-Defined Function

A Scalar user-defined function returns one of the scalar data types. Text, ntext, image and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

Inline Table-Value User-Defined Function

An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and in essence provide us with a parameterized, non-updateable view of the underlying tables.

Multi-statement Table-Value User-Defined Function

A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement.

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.

Clustered indexes define the physical sorting of a database table’s rows in the storage media. For this reason, each database table may have only one clustered index. Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself.

If a column has a PRIMARY KEY constraint with a clustered index defined on it, then the constraint needs to be dropped before a new constraint is defined on the column

When a PK constraint is created a unique clustered index gets created automatically. The index cannot be dropped explicitly. It is dropped only when the table or the constraint is dropped. If a clustered index already exists, then the PK constraint gets rejected.

What is the difference between clustered and a non-clustered index?

Clustered indexes A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index.

Non-clustered indexes are created outside of the database table and contain a sorted list of references to the table itself. A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk

What are the different index configurations a table can have?

A table can have one of the following index configurations:

No indexes, a clustered index, a clustered index and many no clustered indexes A no clustered index many onclustered indexes.

What is cursors?

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 cursorpen cursor

Fetch row from the cursor

Process fetched row

Close cursor

Deallocate cursor

Static Cursors

The complete result set of a static cursor is built in tempdb when the cursor is opened. A static cursor always displays the result set as it was when the cursor was opened. The cursor does not reflect any changes made in the database that affect either the membership of the result set or changes to the values in the columns of the rows that make up the result set.

Dynamic Cursors

Dynamic cursors are the opposite of static cursors. Dynamic cursors reflect all changes made to the rows in their result set when scrolling through the cursor. The data values, order, and membership of the rows in the result set can change on each fetch. All UPDATE, INSERT, and DELETE statements made by all users are visible through the cursor.

Forward-only Cursors

A forward-only cursor does not support scrolling; it supports only fetching the rows serially from the start to the end of the cursor. The rows are not retrieved from the database until they are fetched. Because the cursor cannot be scrolled backward, however, changes made to rows in the database after the row was fetched are not visible through the cursor,

Keyset-driven Cursors

The membership and order of rows in a keyset-driven cursor are fixed when the cursor is opened. Keyset-driven cursors are controlled by a set of unique identifiers (keys) known as the keyset. The keys are built from a set of columns that uniquely identify the rows in the result set. The keyset is the set of the key values from all the rows that qualified for the SELECT statement at the time the cursor was opened. The keyset for a keyset-driven cursor is built in tempdb when the cursor is opened. Inserts to the database made outside the cursor are not visible in the cursor unless the cursor is closed and reopened

sp_helpindex depart1

Cursor Functions

@@CURSOR_ROWS

CURSOR_STATUS

@@FETCH_STATUS

What is the use of DBCC commands?

DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.

E.g. DBCC CHECKDB - Ensures that tables in the db and the indexes are correctly linked.

DBCC CHECKALLOC - To check that all pages in a db are correctly allocated.

DBCC CHECKFILEGROUP - Checks all tables file group for any damage.

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.

Storped Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server.

How to implement one-to-one, one-to-many and many-to-many relationships while

Designing tables?

One-to-One relationship can be implemented as a single table and rarely as two tables with primary and foreign key relationships.

One-to-Many relationships are implemented by splitting the data into two tables with primary key and foreign key relationships.

Many-to-Many relationships are implemented using a junction table with the keys from both the tables forming the composite primary key of the junction table.

What is difference between DELETE & TRUNCATE commands?

DELETE

DELETE removes rows one at a time and records an entry in the transaction log for each deleted row.

If you want to retain the identity counter, use DELETE instead.

DELETE Can be used with or without a WHERE clause.

DELETE Activates Triggers.

DELETE can be rolled back. DELETE is DML Command.

DELETE does not reset identity of the table.

TRUNCATE

TRUNCATE is faster and uses fewer system and transaction log resources than DELETE.

TRUNCATE can not be Rolled back. TRUNCATE is DDL Command.

TRUNCATE Resets identity of the table.

You cannot use TRUNCATE on a table referenced by a FOREIGN KEY constraint. TRUNCATE cannot activate a trigger.

TRUNCATE removes all rows from a table, but the table structure and its columns, constraints, indexes.

Difference between Function and Stored Procedure?

UDF can be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section where as Stored procedures cannot be.

UDFs that return tables can be treated as another rowset. This can be used in JOINs with other tables.

Inline UDF's can be though of as views that take parameters and can be used in JOINs and other Rowset operations.

.

What types of Joins are possible with Sql Server?

Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.

Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

What is self join?
A table can be joined to itself in a self-join.

What is sub-query? Explain properties of sub-query.

A sub query is a SELECT statement that is nested within another T-SQL statement

A sub query must be enclosed in the parenthesis.

A sub query must be put in the right hand of the comparison operator, and

A sub query cannot contain a ORDER-BY clause.

A query can contain more than one sub-query

Sub-queries are generally used to return a single row as an atomic value, though they may be used to compare values against multiple rows with the IN keyword.

What are types of sub-queries?

Single-row sub query, where the sub query returns only one row.

Multiple-row sub query, where the sub query returns multiple rows.

Multiple column sub query, where the sub query returns multiple columns.

What is SQL Profiler?

SQL Profiler is a graphical tool that allows system administrators to monitor events in an instance of Microsoft SQL Server. You can capture and save data about each event to a file or SQL Server table to analyze later. For example, you can monitor a production environment to see which stored procedures is hampering performance by executing too slowly.

Which TCP/IP port does SQL Server run on? How can it be changed?

SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port number. It can be changed both on client and the server.

What are the authentication modes in SQL Server? How can it be changed?

Windows mode and mixed mode (SQL & Windows).

Where are SQL server user’s names and passwords are stored in sql server?

They get stored in master db in the syslogins table.

Which command using Query Analyzer will give you the version of SQL server and operating system?

SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY('edition')

Can you have a nested transaction?

Yes, very much. Check out BEGIN TRAN, COMMIT, ROLLBACK, SAVE TRAN and @@TRANCOUNT

What is an extended stored procedure? Can you instantiate a COM object by using T-SQL?

An extended stored procedure is a function within a DLL (written in a programming language like C, C++ using Open Data Services (ODS) API) that can be called from T-SQL, just the way we call normal stored procedures using the EXEC statement.

What is the system function to get the current user’s user id?

USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID(), HOST_NAME().

What is the difference of a LEFT JOIN and an INNER JOIN statement?

A LEFT JOIN will take ALL values from the first declared table and matching values from the second declared table based on the column the join has been declared on. An INNER JOIN will take only matching values from both tables

Can a stored procedure call itself or recursive stored procedure? How many levels SP nesting possible?

Yes. Because Transact-SQL supports recursion, you can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. You can nest stored procedures and managed code references up to 32 levels.

What is @@ERROR?

The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

What is Raise error?

Stored procedures report errors to client applications via the RAISERROR command. RAISERROR doesn't change the flow of a procedure; it merely displays an error message, sets the @@ERROR automatic variable, and optionally writes the message to the SQL Server error log and the NTapplication event log.