Category >
MSSQLSERVER
|| Published on :
Monday, May 11, 2015 || Views:
3370
||
Top SQL And SQL SERVER Interview Questions Part 1 SQL SERVER Interview Questions Interview Questions
Question 1: What is RDBMS?
Ans: 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.
Question 2: What is normalization?
Ans: Database normalization is a data design and organization process 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.
Question 3: What's the difference between a primary key and a unique key?
Ans: Both primary 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 nonclustered
index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key
allows one NULL only.
Question 4: Difference between Function and Stored Procedure?
Ans: 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.
Question 5: When is the use of UPDATE_STATISTICS command?
Ans: This command is basically used when a large processing of data has occurred. If a large amount of
deletions any modification or Bulk Copy into the tables has occurred, it has to update the indexes to
take these changes into account. UPDATE_STATISTICS updates the indexes on these tables
accordingly.
Question 6: Which TCP/IP port does SQL Server run on? How can it be changed?
Ans: SQL Server runs on port 1433. It can be changed from the Network Utility TCP/IP properties –> Port
number.both on client and the server.
Question 7: What are the authentication modes in SQL Server? How can it be changed?
Ans: Windows mode and mixed mode (SQL & Windows).
To change authentication mode in SQL Server click Start, Programs, Microsoft SQL Server and click SQL
Enterprise Manager to run SQL Enterprise Manager from the Microsoft SQL Server program group.
Select the server then from the Tools menu select SQL Server Configuration Properties, and choose the
Security page.
Question 8: Where are SQL server users names and passwords are stored in sql server?
Ans: They get stored in master db in the sysxlogins table.
Question 9: What is User Defined Functions?
Ans: User-Defined Functions allow to define its own T-SQL functions that can accept 0 or more parameters
and return a single scalar data value or a table data type.
Question 10: What kind of User-Defined Functions can be created?
Ans: 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. Also, the ability to pass parameters into a TSQL
select command or a group of them gives us the capability to in essence create a parameterized,
non-updateable view of the data in the underlying tables. Within the create function command you
must define the table structure that is being returned. After creating this type of user-defined function,
It can be used in the FROM clause of a T-SQL command unlike the behavior found when using a stored
procedure which can also return record sets.