SQL interview Questions and Answers II

What are the null values in SQL SERVER: Before understand the null values we have some overview about what the value is. Value is the actual data stored in a particular field of particular record. But what is done when there are no values in the field. That value is something like .Nulls present missing information. We can also call null propagation.

Write a Role of SQL Server 2005 in XML Web Services: SQL Server 2005 creates a standard method for getting the database engine using SOAP via HTTP. By this method, we can send SOAP/HTTP requests to SQL Server for executing T-SQL batch statements, stored procedures, extended stored procedures, and scalar-valued user-defined functions may be with or without parameters.

What are the different types of Locks
There are three main types of locks that SQL Server
• Shared locks are used for operations that do not allow changing or updating data, such as a SELECT statement.
• Update locks are used when SQL Server intends to modify a page, and later promotes the update page lock to an exclusive page lock before actually making the changes.
• Exclusive locks are used for the data modification operations, such as UPDATE, INSERT, or DELETE.

What is Write ahead log in SQL Server 2000: Before understanding it we must have an idea about the transaction log files. These files are the files which hold the data for change in database. Now we explain when we are doing some SQL Server 2000 query or any SQL query like SQL insert query, delete SQL query, update SQL query and change the data in SQL server database it cannot change the database directly to table .SQL server extracts the data that is modified by SQL server 2000 query or by SQL query and places it in memory. Once data is stores in memory user can make changes to that a log file is garneted this log file is garneted in every five minutes of transaction is done. After this SQL server writes changes to database with the help of transaction log files. This is called Write-ahead log.

What does u mean by Extents and types of Extents: An Extent is a collection of 8 sequential pages to hold database from becoming fragmented. Fragment means these pages relates to same table of database these also holds in indexing. To avoid for fragmentation SQL Server assign space to table in extents. So that the SQL Server keep up to date data in extents. Because these pages are continuously one after another. There are usually two types of extends:-Uniform and Mixed. Uniform means when extent its own by a single object means all collection of 8 ages hold by a single extends is called uniform. Mixed mean when more than one object is comes in extents is known as mixed extents.

What is different in Rules and Constraints: Rules and Constraints are similar in functionality but there is a An little difference between them. Rules are used for backward compatibility. One the most exclusive difference is that we can bind rules to a datatype whereas constraints are bound only to columns. So we can create our own datatype with the help of Rules and get the input according to that.

What is defaults in SQL Server and types of Defaults: Defaults are used when a field of columns is almost common for all the rows for example in employee table all living in Delhi that value of this field is common for all the row in the table if we set this field as default the value that is not fill by us automatically fills the value in the field it’s also work as intellisense means when user inputting d it will automatically fill the Delhi. There are two types of defaults object and definitions. Object default:-These defaults are applicable on particular columns. These are usually denied at the time of table designing. When u set the object default field in column state this column in automatically field when u left this filed blank. Definition default:-When we bind the datatype with default let we named this as Dotnet .Then every time we create column and named its datatype as Dotnet it will behave the same that we set for Dotnet datatype.

Transaction: Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fails the transaction fails. Therefore a transaction has only two results: success or failure. Incomplete steps result in the failure of the transaction. Users can group two or more Transact-SQL statements into a single transaction using the following statements: * Begin Transaction * Rollback Transaction * Commit Transaction If anything goes wrong with any of the grouped statements all changes need to be aborted. The process of reversing changes is called rollback in SQL Server terminology. If everything is in order with all statements within a single transaction all changes are recorded together in the database.

CREATE PROCEDURE DeleteEmployee ( @EmployeeID int )
AS

BEGIN TRY
BEGIN TRANSACTION -- Start the transaction
-- Delete the Employee's phone numbers
DELETE FROM EmployeePhoneNumbers WHERE EmployeeID = @EmployeeID
-- Delete the Employee record
DELETE FROM Employees WHERE EmployeeID = @EmployeeID
-- If we reach here, success!
COMMIT
END TRY
BEGIN CATCH
-- Whoops, there was an error
IF @@TRANCOUNT > 0
ROLLBACK
-- Raise an error with the details of the exception
DECLARE @ErrMsg nvarchar(4000), @ErrSeverity int
SELECT @ErrMsg = ERROR_MESSAGE(), @ErrSeverity = ERROR_SEVERITY()
RAISERROR(@ErrMsg, @ErrSeverity, 1)
END CATCH

What is COMMIT and ROLLBACK statement in SQL: Commit statement helps in termination of the current transaction and does all the changes that occur in transaction persistent and this also commits all the changes to the database. COMMIT we can also use in store procedure. ROLLBACK do the same thing just terminate the current transaction but one another thing is that the changes made to database are ROLLBACK to the database.

What is difference between OSQL and Query Analyzer: Both are the same but there is little difference OSQL is command line tool which is execute query and display the result same a query analyzer but query analyzer is graphical and OSQL is a command line tool. OSQL have not ability like query analyzer to analyze queries and show statics on speed of execution and other useful thing about OSQL is that its helps in scheduling.

What are Data Integrity and its categories: Enforcing data integrity ensures the quality of the data in the database. For example, if an employee is entered with an employee_id value of 123, the database should not allow another employee to have an ID with the same value. If you have an employee_rating column intended to have values ranging from 1 to 5, the database should not accept a value of 6. If the table has a dept_id column that stores the department number for the employee, the database should allow only values that are valid for the department numbers in the company.

Two important steps in planning tables are to identify valid values for a column and to decide how to enforce the integrity of the data in the column. Data integrity falls into these categories:
1) Entity integrity 2) Domain integrity 3) Referential integrity 4) User-defined integrity
Entity Integrity: Entity integrity defines a row as a unique entity for a particular table. Entity integrity enforces the integrity of the identifier column(s) or the primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY constraints, or IDENTITY properties).

Domain Integrity: Domain integrity is the validity of entries for a given column. You can enforce domain integrity by restricting the type (through data types), the format (through CHECK constraints and rules), or the range of possible values (through FOREIGN KEY constraints, CHECK constraints, DEFAULT definitions, NOT NULL definitions, and rules).

Referential Integrity: Referential integrity preserves the defined relationships between tables when records are entered or deleted. In Microsoft� SQL Server� 2000, referential integrity is based on relationships between foreign keys and primary keys or between foreign keys and unique keys (through FOREIGN KEY and CHECK constraints). Referential integrity ensures that key values are consistent across tables. Such consistency requires that there be no references to nonexistent values and that if a key value changes, all references to it change consistently throughout the database. When you enforce referential integrity, SQL Server prevents users from: � Adding records to a related table if there is no associated record in the primary table. � Changing values in a primary table that result in orphaned records in a related table. � Deleting records from a primary table if there are matching related records. For example, with the sales and titles tables in the pubs database, referential integrity is based on the relationship between the foreign key (title_id) in the sales table and the primary key (title_id) in the titles table.

User-Defined: Integrity User-defined integrity allows you to define specific business rules that do not fall into one of the other integrity categories. All of the integrity categories support user-defined integrity (all column- and table-level constraints in CREATE TABLE, stored procedures, and triggers).
SQL Server runs on which TCP IP port and from where can you change the default port: SQL Server runs on port 1433 but we can also change it for better security and from the network Utility TCP/IP properties -->Port number, both on client and the server.

What is the difference between a HAVING CLAUSE and a WHERE CLAUSE

Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.

When do you use SQL Profiler: SQL Profiler utility allows us to basically track Connections to the SQL Server and also determine activities, such as which SQL Scripts are running, failed jobs etc?
ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept that Database Professionals generally look for when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved.
Atomicity is an all-or-none proposition.
Consistency guarantees that a transaction never leaves your database in a half-finished state.
Isolation keeps transactions separated from each other until they’re finished.
Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination.
Above four rules are very important for any developers dealing with databases.

Can you explain the role of each service: SQL SERVER - is for running the databases SQL AGENT - is for automation such as Jobs, DB Maintenance, Backups DTC - Is for linking and connecting to other SQL Servers.

Difference between Triggers and Stored procedures
A STORED PROCEDURE is a set of structured query language statements that you assign a name and store it in to the database in a compiled form so that it can share between numbers of programs.
Some advantages of Store Procedure.-they allow faster execution. -they can reduce network traffic.
Triggers are basically used to implement business rules. A trigger is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database. Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

Creates a DML, DDL, or logon trigger.

DML triggers execute when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.
DDL triggers execute in response to a variety of data definition language (DDL) events. These events primarily correspond to Transact-SQL CREATE, ALTER, and DROP statements, and certain system stored procedures that perform DDL-like operations.
Logon triggers fire in response to the LOGON event that is raised when a user sessions is being established.
Note: These triggers fire when any valid event is fired, regardless of whether or not any table rows are affected.

CREATE TRIGGER tr_InetLog_INSERT
ON InetLog
FOR INSERT
AS

IF EXISTS (SELECT * FROM inserted WHERE Target = 'AboutUs.htm')
BEGIN
UPDATE LogSummary
SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE Target = 'AboutUs.htm')
WHERE LogSum_Category = 'About Us'
END

IF EXISTS (SELECT * FROM inserted WHERE Target = 'Services.htm')
BEGIN
UPDATE LogSummary
SET LogSum_Count = (SELECT COUNT(*) FROM InetLog WHERE Target = 'Services.htm')
WHERE LogSum_Category = 'Services'
END
Go

Types:
1. After Trigger
2. Multiple After Triggers
3. Instead Of Triggers
4. Mixing Triggers Type

What is Normalization: The logical design of the database, including the tables and the relationships between them, is the core of an optimized relational database. A good logical database design can lay the foundation for optimal database and application performance. A poor logical database design can impair the performance of the entire system. Normalizing a logical database design involves using formal methods to separate the data into multiple, related tables.

A greater n umber of narrow tables (with fewer columns) is characteristic of a normalized database. A few wide tables (with more columns) is characteristic of an non-normalized database. Reasonable normalization often improves performance. When useful indexes are available, the Microsoft� SQL Server� 2000 query optimizer is efficient at selecting rapid, efficient joins between tables. Some of the benefits of normalization include: �Faster sorting and index creation. � A larger number of clustered indexes. For more information, Narrower and more compact indexes. �Fewer indexes per table, which improves the performance of INSERT, UPDATE, and DELETE statements. �Fewer null values and less opportunity for inconsistency, which increase database compactness. As normalization increases, so do the number and complexity of joins required to retrieve data. Too many complex relational joins between too many tables can hinder performance.

Reasonable normalization often includes few regularly executed queries that use joins involving more than four tables. Sometimes the logical database design is already fixed and total redesign is not feasible. Even then, however, it might be possible to normalize a large table selectively into several smaller tables. If the database is accessed through stored procedures, this schema change could take place without affecting applications. If not, it might be possible to create a view that hides the schema change from the applications.

What are different normalization forms?

1NF: Eliminate Repeating Groups: Make a separate table for each set of related attributes, and give each table a primary key. Each field contains at most one value from its attribute domain.
2NF: Eliminate Redundant Data: If an attribute depends on only part of a multi‐valued key, remove it to a separate table.
3NF: Eliminate Columns Not Dependent On Key: If attributes do not contribute to a description of the key, remove them to a separate table. All attributes must be directly dependent on the primary key.
BCNF: Boyce‐Codd Normal Form: If there are non‐trivial dependencies between candidate key attributes, separate them out into distinct tables.
4NF: Isolate Independent Multiple Relationships: No table may contain two or more 1:n or n:m relationships that are not directly related.
5NF: Isolate Semantically Related Multiple Relationships: There may be practical constrains on information that justify separating logically related many‐to‐many relationships.
ONF: Optimal Normal Form: A model limited to only simple (elemental) facts, as expressed in Object Role Model notation.
DKNF: Domain‐Key Normal Form: A model free from all modification anomalies is said to be in DKNF.
Remember, these normalization guidelines are cumulative. For a database to be in 3NF, it must first fulfill all the criteria of a 2NF and 1NF database.

Can you explain what View is in SQL: View is just a virtual table nothing else which is based or we can say develop with SQL SELECT query? So we can say that it’s a real database table (it has columns and rows just like a regular table), but one difference is that real tables store data, but views cant. View data is generated dynamically when the view is referenced. And view can also reference one or more existing database tables or other views. We can say that it is filter of database.

How to get which Process is Blocked in SQL SERVER: There are two ways to get this sp_who and sp_who2 . You cannot get any detail about the sp_who2 but its provide more information than the sp_who . And other option from which we can find which process is blocked by other process is by using Enterprise Manager or Management Studio, these two commands work much faster and more efficiently than these GUI-based front-ends.

Can you tell me the difference between DELETE and TRUNCATE commands

Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.

What are the Global Temporary Tables: We can create global temporary tables but these are not using much in sql and the name of this table start with two pound signs? For example, ##interviewqsn is a global temporary table. As the name suggest these table is Global temporary tables and visible to all SQL Server connections. When we create any one of these all users can see it.