What are primary keys and foreign keys?
Primary keys are the unique identifiers for each row. They must contain
unique values and cannot be null. Due to their importance in relational
databases, Primary keys are the most fundamental of all keys and constraints. A
table can have only one Primary key.
Foreign keys are both a method of ensuring data integrity and a
manifestation of the relationship between tables.
What is User Defined Functions? What
kind of User-Defined Functions can be created?
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.
Different Kinds of User-Defined
Functions created are:
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.
What is Identity?
Identity (or AutoNumber) is a column that automatically generates
numeric values. A start and increment value can be set, but most DBA leave
these at 1. A GUID column also generates numbers; the value of this cannot be
controlled. Identity/GUID columns do not need to be indexed.
What is DataWarehousing?
- Subject-oriented,
meaning that the data in the database is organized so that all the data
elements relating to the same real-world event or object are linked
together.
- Time-variant,
meaning that the changes to the data in the database are tracked and
recorded so that reports can be produced showing changes over time.
- Non-volatile,
meaning that data in the database is never over-written or deleted, once
committed, the data is static, read-only, but retained for future
reporting.
- Integrated,
meaning that the database contains data from most or all of an
organization’s operational applications, and that this data is made
consistent.
When is the use of UPDATE_STATISTICS
command?
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.
What is the difference between a
HAVING CLAUSE and a WHERE CLAUSE?
They specify a search condition for a group or an aggregate. But the
difference is that HAVING can be used only with the SELECT statement. HAVING is
typically used in a GROUP BY clause. When GROUP BY is not used, HAVING behaves
like a WHERE clause. Having Clause is basically used only with the GROUP BY
function in a query whereas WHERE Clause is applied to each row before they are
part of the GROUP BY function in a query.
What are the properties and different
Types of Sub-Queries?
Properties of Sub-Query
- 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
an ORDER-BY clause.
- A query can contain more than
one sub-query.
Types of Sub-query
- Single-row
sub-query, where the sub-query returns only one row.
- Multiple-row
sub-query, where the sub-query returns multiple rows, and
- 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 are hampering performances by executing too slowly.
Use SQL Profiler to monitor only the events in which you are interested.
If traces are becoming too large, you can filter them based on the information
you want, so that only a subset of the event data is collected. Monitoring too
many events adds overhead to the server and the monitoring process and can
cause the trace file or trace table to grow very large, especially when the
monitoring process takes place over a long period of time.
What are the authentication modes in
SQL Server? How can it be changed?
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.
https://www.youtube.com/channel/UCKLRUr6U5OFeu7FLOpQ-FSw/videos
0 comments
Post a Comment