Monday, March 2, 2009
What is OLTP(OnLine Transaction Processing)
In OLTP - online transaction processing systems relational database design use the discipline of data
modeling and generally follow the Codd rules of data normalization in order to ensure absolute data
integrity. Using these rules complex information is broken down into its most simple structures (a table)
where all of the individual atomic level elements relate to each other and satisfy the normalization
rules.
modeling and generally follow the Codd rules of data normalization in order to ensure absolute data
integrity. Using these rules complex information is broken down into its most simple structures (a table)
where all of the individual atomic level elements relate to each other and satisfy the normalization
rules.
What is the basic functions for master, msdb, model, tempdb databases
The Master database holds information for all databases located on the SQL Server instance and is the
glue that holds the engine together. Because SQL Server cannot start without a functioning master
database, you must administer this database with care.
The msdb database stores information regarding database backups, SQL Agent information, DTS
packages, SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in
the instance.
glue that holds the engine together. Because SQL Server cannot start without a functioning master
database, you must administer this database with care.
The msdb database stores information regarding database backups, SQL Agent information, DTS
packages, SQL Server jobs, and some replication information such as for log shipping.
The tempdb holds temporary objects such as global and local temporary tables and stored procedures.
The model is essentially a template database used in the creation of any new user database created in
the instance.
What are the different types of replication? Explain.
The SQL Server 2000-supported replication types are as follows:
· Transactional
· Snapshot
· Merge
Snapshot replication distributes data exactly as it appears at a specific moment in time and does not
monitor for updates to the data. Snapshot replication is best used as a method for replicating data that
changes infrequently or where the most up-to-date values (low latency) are not a requirement. When
synchronization occurs, the entire snapshot is generated and sent to Subscribers.
Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data
modifications are made at the Publisher, the individual transactions are captured and propagated to
Subscribers.
Merge replication is the process of distributing data from Publisher to Subscribers, allowing the
Publisher and Subscribers to make updates while connected or disconnected, and then merging the
updates between sites when they are connected.
What are the OS services that the SQL Server ins
· Transactional
· Snapshot
· Merge
Snapshot replication distributes data exactly as it appears at a specific moment in time and does not
monitor for updates to the data. Snapshot replication is best used as a method for replicating data that
changes infrequently or where the most up-to-date values (low latency) are not a requirement. When
synchronization occurs, the entire snapshot is generated and sent to Subscribers.
Transactional replication, an initial snapshot of data is applied at Subscribers, and then when data
modifications are made at the Publisher, the individual transactions are captured and propagated to
Subscribers.
Merge replication is the process of distributing data from Publisher to Subscribers, allowing the
Publisher and Subscribers to make updates while connected or disconnected, and then merging the
updates between sites when they are connected.
What are the OS services that the SQL Server ins
Properties of Sub-Query
A subquery must be enclosed in the parenthesis.
A subquery must be put in the right hand of the comparison operator, and
A subquery cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.
A subquery must be put in the right hand of the comparison operator, and
A subquery cannot contain a ORDER-BY clause.
A query can contain more than one sub-queries.
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.
Valued and Multi-statement Table-valued.
What is User Defined Functions?
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.
and return a single scalar data value or a table data type.
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.
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.
Define candidate key, alternate key, 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.
Difference between a primary key and a unique key
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.
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.
How do you implement one-to-one, one-to-many and many-to-many
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.
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 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.
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 BCP? When do we use it?
BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same.
What are the authentication modes in SQL Server
Windows mode and mixed mode (SQL & Windows). users names and passwords will stored in master db in the sysxlogins table.
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.
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..
What is a Join in SQL Server
Join actually puts data from two or more tables into a single result set.
What do you mean by COLLATION
Collation is basically the sort order. There are three types of sort order Dictionary case sensitive, Dictonary - case insensitive and Binary.
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.
How Can we use Truncate command on a table which is referenced by FOREIGN KEY?
We cannot use Truncate command on a table with Foreign Key because of referential integrity.
When do we use the UPDATE_STATISTICS command
This command is basically used when we do a large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
What are cursors
Cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retreival one by one and remove rows which have duplicate values.
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. There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered. The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.
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 a Trigger
Triggers are basically used to implement business rules. Triggers 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.
What is Normalization
A relational database is basically composed of tables that contain related data. So the Process of organizing this data into tables is actually referred to as normalization.
What is Normalization
A relational database is basically composed of tables that contain related data. So the Process of organizing this data into tables is actually referred to as normalization.
how to select the second largest salary from a table?
Select Top 1 from Employee where Salary Not IN ( select Top 1 from Employee order by Salary Desc)order by Salary Desc
OR
select max(sal) from employee where sal != (select max(sal) from employee)
OR
Select Max(salary)From EmployeeWhere salaryNOT IN (Select max(salary) From Employee)
OR
select max(sal) from employee where sal != (select max(sal) from employee)
OR
Select Max(salary)From EmployeeWhere salaryNOT IN (Select max(salary) From Employee)
Types of Constraints
1.PRIMARY constraint
2.FOREIGN constraint
3.UNIQUE constraint
4.CHECK constraint
5.DEFAULT constraint
2.FOREIGN constraint
3.UNIQUE constraint
4.CHECK constraint
5.DEFAULT constraint
Types of Transaction Isloation level in SQL server
Four Types of Transaction Isloation level
1)READ UNCOMMITTED
2)READ COMMITTED
3)REPEATABLE READ
4)SERIALIZABLE
1)READ UNCOMMITTED
2)READ COMMITTED
3)REPEATABLE READ
4)SERIALIZABLE
Difference between Delete and Truncate
a..DELETE command can be rolled back but TRUNCATE cannot be rolled back
b..Delete is a DML command. Truncate is a DDL command.
c..Delete will delete one by one row and also return the no of row deleted but truncate will drop table and recreate it it will not return the no of rows deleted hence truncate is faster then delete
d.. DELETE we can specify WHERE clause to delete selective rows whereas with TRUNCATE we can’t.
b..Delete is a DML command. Truncate is a DDL command.
c..Delete will delete one by one row and also return the no of row deleted but truncate will drop table and recreate it it will not return the no of rows deleted hence truncate is faster then delete
d.. DELETE we can specify WHERE clause to delete selective rows whereas with TRUNCATE we can’t.
Type of cursors
1)Static cursors
2)Dynamic cursors
3)Forward-only cursors
4)Keyset-driven cursors
Static cursors detect few or no changes but consume relatively few resources while scrolling, although they store the entire cursor in tempdb.
Dynamic cursors detect all changes but consume more resources while scrolling, although they make the lightest use of tempdb.
Keyset-driven cursors lie in between, detecting most changes but at less expense than dynamic cursors.
2)Dynamic cursors
3)Forward-only cursors
4)Keyset-driven cursors
Static cursors detect few or no changes but consume relatively few resources while scrolling, although they store the entire cursor in tempdb.
Dynamic cursors detect all changes but consume more resources while scrolling, although they make the lightest use of tempdb.
Keyset-driven cursors lie in between, detecting most changes but at less expense than dynamic cursors.
How Three command to get version or operating system related information
Select @@Version
Or
EXEC sp_MSgetversion
or
Exec xp_msver
Or
EXEC sp_MSgetversion
or
Exec xp_msver
What are sub-queries? Give example?
Sub-query means a Query within a Query.
This is the Example:
Select Employee_Id, Employee_name From Employees Where Employee_Id IN (Select Mgr_Id from Manager)
This is the Example:
Select Employee_Id, Employee_name From Employees Where Employee_Id IN (Select Mgr_Id from Manager)
Subscribe to:
Posts (Atom)