1.5.14

SQL SERVER INTERVIEW QUESTIONS 2014 BOOKLET -7 By Mr.J.Venkatesan Prabu - Managing Director of Kaashiv Infotech

            
      SQL SERVER INTERVIEW QUESTIONS 2014- SET 7
By 
"Mr.J.Venkatesan Prabu" 

KAASHIV INFOTECH

     [ The  Asia, India & Tamil Nadu Book Of  Record Holders ]


                                                       
    SQL Server Interview Questions 2014
SQL  SERVER –Booklet 1
-         Gives you the interview tips for SQL Server

 SQL SERVER INTERVIEW QUESTIONS WITH ANSWERS 

1.What is CLR?

In SQL Server 2008, SQL Server objects such as user-defined functions can be created using such CLR languages. This CLR language support extends not only to user-defined functions, but also to stored procedures and triggers. You can develop such CLR add-ons to SQL Server using Visual Studio 2008.

2. What are synonyms?

Synonyms give you the ability to provide alternate names for database objects. You can alias object names; for example, using the Employee table as Emp. You can also shorten names. This is especially useful when dealing with three and four part names; for example, shortening server.database.owner.object to object.

3. What is LINQ?

Language Integrated Query (LINQ) adds the ability to query objects using .NET languages. The LINQ to SQL object/relational mapping (O/RM) framework provides the following basic features:
  1. Tools to create classes (usually called entities) mapped to database tables
  2. Compatibility with LINQ's standard query operations
  3. The DataContext class, with features such as entity record monitoring, automatic SQL statement generation, record concurrency detection, and much more
4. What is Isolation Levels?

Transactions specify an isolation level that defines the degree to which one transaction must be isolated from resource or data modifications made by other transactions. Isolation levels are described in terms of which concurrency side-effects, such as dirty reads or phantom reads, are allowed.
Transaction isolation levels control:
  1. Whether locks are taken when data is read, and what type of locks are requested.
  2. How long the read locks are held.
  3. Whether a read operation referencing rows modified by another transaction:
    1. Blocks until the exclusive lock on the row is freed.
    2. Retrieves the committed version of the row that existed at the time the statement or transaction started.
    3. Reads the uncommitted data modification.
5. What is use of EXCEPT Clause?

EXCEPT clause is similar to MINUS operation in Oracle. The EXCEPT query and MINUS query returns all rows in the first query that are not returned in the second query. Each SQL statement within the EXCEPT query and MINUS query must have the same number of fields in the result sets with similar data types.

6. How would you handle error in SQL SERVER 2008?

SQL Server now supports the use of TRY...CATCH con handling. TRY...CATCH lets us build error handling at the level we need, in the way w to, by setting a region where if any error occurs, it will break out of the region and head to an error handler. 
The basic structure is as follows:
BEGIN TRY
stmts..
END TRY
BEGIN CATCH
stmts..
END CATCH

7.What is RAISEERROR?

RaiseError generates an error message and initiates error processing for the session. RAISERROR can either reference a user-defined message stored in the sys.messages catalog view or build a message dynamically. The message is returned as a server error message to the calling application or to an associated CATCH block of a TRY | CATCH construct.

8. How to rebuild Master Database?

Master database is system database and it contains information about running server's configuration. When SQL Server 2005 is installed it usually creates master, model, msdb, tempdb resource and distribution system database by default. Only Master database is th one which is absolutely must have database. Without Master database SQL Server cannot be started. This is the reason it is extremely important to backup Master database.
To rebuild the Master database, Run Setup.exe, verify, and repair a SQL Server instance, and rebuild the system databases. This procedure is most often used to rebuild the master database for a corrupted installation of SQL Server.

9. What is XML Datatype?
The xml data type lets you store XML documents and fragments in a SQL Server database. An XML fragment is an XML instance that is missing a single top-level element. You can create columns and variables of the xml type and store XML instances in them. The xml data type and associated methods help integrate XML into the relational framework of S Server.

10. What is Data Compression?

In SQL SERVE 2008 Data Compression comes in two flavors:
  1. Row Compression: Row compression changes the format of physical storage of data. It minimize the metadata (column information, length, offsets etc) associated with each record. Numeric data types and fixed length strings are stored in variable-length storage format, just like Varchar.
  2. Page Compression: Page compression allows common data to be shared between rows for a given page. Its uses the following techniques to compress data:
    1. Row compression.
    2. Prefix Compression. For every column in a page duplicate prefixes are identified. These prefixes are saved in compression information headers (CI) which resides after page header. A reference number is assigned to these prefixes and that reference number is replaced where ever those prefixes are being used.
  3. Dictionary Compression: Dictionary compression searches for duplicate values throughout the page and stores them in CI. The main difference between prefix and dictionary compression is that prefix is only restricted to one column while dictionary is applicable to the complete page.
11. What is Catalog Views?

Catalog views return information that is used by the SQL Server Database Engine. Catalog Views are the most general interface to the catalog metadata and provide the most efficient way to obtain, transform, and present customized forms of this information. All user- available catalog metadata is exposed through catalog views.

12. What is PIVOT and UNPIVOT?

A Pivot Table can automatically sort, count, and total the data stored in one table or spreadsheet and create a second table displaying the summarized data. The PIVOT operator turns the values of a specified column into column names, effectively rotating a table.
UNPIVOT table is reverse of PIVOT Table

13.What is Dirty Read ?

A dirty read occurs when two operations say, read and write occurs together giving the incorrect or unedited data. Suppose, A has changed a row, but has not committed the changes. B reads the uncommitted data but his view of the data may be wrong so that is Dirty Read.

14. What is Aggregate Functions?

Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions ignore NULL values except COUNT function. HAVING clause is used, along with GROUP BY, for filtering query using aggregate values.

Following functions are aggregate functions.

AVG, MIN CHECKSUM_AGG, SUM, COUNT, STDEV, COUNT_BIG, STDEVP, GROUPING, VAR, MAX. VARP

15. What do you mean by Table Sample?

TABLESAMPLE allows you to extract a sampling of rows from a table in the FROM clause. The rows retrieved are random and they are not in any order. This sampling can be based on a percentage of number of rows. You can use TABLESAMPLE when only a sampling of rows is necessary for the application instead of a full result set.


KaaShiv InfoTech Offers Best Inpant Training in Chennai.


            The training at KAASHIV INFOTECH focus on developing the technical oriented concepts that turn graduates into employable assets. Handled only by professionals from MNC companies, we know how to equip you with strong technologies fundamentals.

INPLANT TRAINING SCHEDULE FOR CSE/IT/MCA STUDENTS
Day
Programme
 Day 1
BigData (Practical Demos)
Day 2
Windows 8 App Development (Practical Demos)
Day 3
Ethical Hacking (Facebook Hack,Server/Website Hacking(20 Attacks) 
Day 4
Cloud Computing (Live Server Demo,Live Pjt Implementation)
Day 5
CCNA (-Networking-Router Configurations Practical Demo)
INPLANT TRAINING SCHEDULE FOR ELECTRONIC/ELECTRICAL/EIE STUDENTS
Day
Programme
Day 1
Embedded System  (Embedded Program Designing ,Chip Burning)
Day 2
Wireless System  (Device Designing,Controlling Fans with Wireless Sensors)
Day 3
CCNA  (-Networking-Router Configurations Practical Demo)
Day 4
Ethical Hacking  (Facebook Hack,Server/Website Hacking(20 Attacks)
Day 5
Matlab  (Capture Image,Processing, Animate Images-Practical Demos)

Mechanical/Civil Inplant training Schedule

Day
Programme
Day 1
Aircraft Designing
Day 2
Vehicle Movement in Airports
Day 3
3D Packaging Designs
Day 4
3D Modeling
Day 5
3D Window Shading


Address:

KAASHIV INFO TECH
Shivanantha Building,
X41, 5th Floor,2nd Avenue,
(Near Ayyappan Temple)
Anna Nagar, Chennai = 600040.
Send Us Your Request Email To  arun@kaashivinfotech.com,
                                                      kaashiv.info@gmail.com ,
                                                            venkat@kaashivinfotech.com
Contact Number : 9840678906 ,9003718877 , 9962345637 .



Visit our other websites

http://www.inplanttrainingchennai.com
http://www.inplanttraining-in-chennai.com
http://www.Kaashiveinotech.com
http://www.internshipinchennai.in

https://plus.google.com/u/0/b/110228862465265998202/dashboard/overview

https://plus.google.com/u/0/b/117408505876070870512/dashboard/overview

https://plus.google.com/u/0/b/104468163439231303834/dashboard/overview

https://plus.google.com/u/0/b/117640664472494971423/dashboard/overview

Feedback URLS

KaaShiv InfoTech Facebook Page
https://www.facebook.com/KaaShivInfoTech

Inplant Training Program in Chennai
https://www.facebook.com/pages/Inplant-Training-Program-in-Chennai/1402097696706380

Internship Training Program in Chennai
https://www.facebook.com/pages/Internship-in-Chennai-KaaShiv/1446147235603704

 Facebook Inplant Training page

https://www.facebook.com/pages/Inplant-Training/256116284550327






Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

My T-SQL Gallery @code.msdn.microsoft


Created my own T-SQL Gallery in Microsoft site. Do visit the same and share your feedback,

http://code.msdn.microsoft.com/VenkatSQLSample/Thread/List.aspx

Thanks and Regards,
Venkatesan Prabu .J

SQL Server Interview questions - Part 1

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

What is SQL whats its uses and its component ?
The Structured Query Language (SQL) is foundation for all relational database systems. Most of the large-scale databases use the SQL to define all user and administrator interactions. It enable us to retrieve the data from based on our exact requirement. We will be given a flexibility to store the data in our own format.


The DML component of SQL comprises four basic statements:
* SELECT to get rows from tables
* UPDATE to update the rows of tables
* DELETE to remove rows from tables
* INSERT to add new rows to tables

What is DTS in SQL Server ?
Data Transformation Services is used to transfer the data from one source to our required destination. Considering am having some data in sql server and I need to transfer the data to Excel destination. Its highly possible with dialogue based tool called Data Transformation services. More customization can be achieved using SSIS. A specialized tool used to do such migration works.


What is the difference between SQL and Pl/Sql ?

Straight forward. SQL is a single statement to finish up our work.Considering, I need some data from a particular table. “Select * from table” will fetch the necessary information. Where as I need to do some row by row processing. In that case, we need to go for Procedural Logic / SQL.

What is the significance of NULL value and why should we avoid permitting null values?
Null means no entry has been made. It implies that the value is either unknown or undefined.We should avoid permitting null values because Column with NULL values can't have PRIMARY KEY constraints. Certain calculations can be inaccurate if NULL columns are involved.

Difference between primary key and Unique key?
Both constraints will share a common property called uniqueness. The data in the column should be unique. The basic difference is,
· Primary key won’t allow null value. Whereas, unique key will accept null value but only one null value.
· On creating primary key, it will automatically format the data inturn creates clustered index on the table. Whereas, this characteristics is not associated with unique key.
· Only one primary key can be created for the table. Any number of Unique key can be created for the table.

Select Statement in SQL Server

Select Statement in SQL Server

String Functions in sql server

String Functions in sql server
Substring/Len/replace/Ltrim/Rtrim

SQL Server Interview Question - Part 2

What is normalization?

Normalization is the basic concept used in designing a database. Its nothing but, an advise given to the database to have minimal repetition of data, highly structured, highly secured, easy to retrieve. In high level definition, the Process of organizing data into tables is referred to as normalization.


What is a stored procedure:
Stored procedures are precompiled T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements. As, its precompiled statement, execution of Stored procedure is compatatively high when compared to an ordinary T-SQL statement.


What is the difference between UNION ALL Statement and UNION ?
The main difference between UNION ALL statement and UNION is UNION All statement is much faster than UNION,the reason behind this is that because UNION ALL statement does not look for duplicate rows, but on the other hand UNION statement does look for duplicate rows, whether or not they exist.

Example for Stored Procedure?
They are three kinds of stored procedures,1.System stored procedure – Start with sp_2. User defined stored procedure – SP created by the user.3. Extended stored procedure – SP used to invoke a process in the external systems.Example for system stored proceduresp_helpdb - Database and its propertiessp_who2 – Gives details about the current user connected to your system. sp_renamedb – Enable you to rename your database


What is a trigger?

Triggers are precompiled statements similar to Stored Procedure. It will automatically invoke for a particular operation. Triggers are basically used to implement business rules.


What is a view?
If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.


What is an Index?
When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.


What are the types of indexes available with SQL Server?

There are basically two types of indexes that we use with the SQL ServerClustered -

1. It will format the entire table, inturn physically sort the table.

2. Only one clustered index can be created for a table.

3. Data will be located in the leaf level.

4. By default, primary key will create clustered index on the table.

Non-Clustered Index

1. It wont touch the structure of the table.

2. It forms an index table as reference to the exact data.

3. A reference to the data will be located in the leaf level.

4. For a table, we can create 249 non clustered index.

Happy Learning!!!
Regards,
Venkatesan Prabu .J

SQL Interview question

Extent Vs Page?

Pages are low level unit to store the exact data in sql server. Basically, the data will be stored in the mdf, ldf, ndf files. Inturn, pages are logical units available in sql server.The size of the page is 8KB.

Eight consecutive pages will form an extent 8 * 8KB = 64KB.

Thus I/O level operation will be happening at pages level.The pages will hold a template information at the start of each page (header of the page).

They are,

1. page number,

2. page type,

3. the amount of free space on the page,

4. the allocation unit ID of the object that owns the page.

Extents will be classifed into two types,

1. Uniform extents

2. Mixed extents

Uniform Extents:It occupied or used by a single object. Inturn, a single object will hold the entire 8 pages.Mixed

Extents:Mulitple objects will use the same extent. SQL Server will allow a max of eight objects to use a shared extent.

Property of SQL Server :Initally if an object is created, sql server will allocate the object to the mixed extent and once if the size reaches 8 pages and more... immediately, a new uniform extent will be provided for that particular object.

Herecomes, our fragmentation and reindexing concepts.



Best Joke - Enjoy it

Best Joke - Enjoy it