What is DTS Package?
An organized collection of connections, Data Transformation Services (DTS) tasks, DTS transformations, and workflow constraints defined by the DTS object model and assembled either with a DTS tool or programmatically
Differences between ISQL and OSQL?
All DB-Library applications, such as isql, work as SQL Server 6.5–level clients when connected to SQL Server 2000. They do not support some SQL Server 2000 features. The osql utility is based on ODBC and does support all SQL Server 2000 features. Use osql to run scripts that isql cannot run.
Like most DB-Library applications, the isql utility does not set any connection options by default. Users must issue SET statements interactively or in their scripts if they want to use specific connection option settings.
The isql utility is started directly from the operating system with the case-sensitive options listed here. After starting, isql accepts Transact-SQL statements and sends them to SQL Server 2000 interactively. The results are formatted and printed on the standard output device (the screen). Use QUIT or EXIT to exit from isql.
If you do not specify a user name when you start isql, SQL Server 2000 checks for the environment variables and uses those, for example, isqluser=(user) or isqlserver=(server). If no environment variables are set, the workstation user name is used. If you do not specify a server, the name of the workstation is used.
What is the Referential integrity?
Referential integrity between tables is enforced by default when you create a relationship in your database diagram. An enforced relationship ensures each value entered in a foreign key column matches an existing value in the related primary key column.
You can change the conditions under which referential integrity is enforced by editing the relationship's properties.
Authentication Mechanisms in SQLServer ?
Microsoft® SQL Server™ can operate in one of two security (authentication) modes:
- Windows Authentication Mode (Windows Authentication)
Windows Authentication mode allows a user to connect through a Microsoft Windows NT® 4.0 or Windows® 2000 user account.
- Mixed Mode (Windows Authentication and SQL Server Authentication)
Mixed Mode allows users to connect to an instance of SQL Server using either Windows Authentication or SQL Server Authentication. Users who connect through a Windows NT 4.0 or Windows 2000 user account can make use of trusted connections in either Windows Authentication Mode or Mixed Mode.
What is INSTEAD OF TRIGGERS?
INSTEAD OF triggers override the standard actions of the triggering statement (INSERT, UPDATE, or DELETE). Instead of Triggers executes instead of triggering action. It can be applied to views and tables. The primary advantage of this trigger is one can update a view which is made up of multiple tables.
INSTEAD OF triggers can be defined on either tables or views; however, INSTEAD OF triggers are most useful for extending the types of updates a view can support. For example, INSTEAD OF triggers can provide the logic to modify multiple base tables through a view or to modify base tables that contain these columns:
- timestamp data type , Computed columns , Identity columns
What is the transaction?
A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:
What are ACID properties?
Atomicity : A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed.
Consistency : When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity.
Isolation : Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.
Durability : After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.
What is normalization? -
Well 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.
1st NF: Eliminate the repeating groups in the table.
2nd NF: All Non-key columns should depend on entire primary key, but not on partial key.
3rd NF: Eliminate interdependencies between non-key attributes.
If the table should be in 3rd normal form, the minimum no. of tables should be one.
What is a Stored Procedure? -
A stored procedure is pre-loaded in memory for faster execution. It allows the DBMS control of permissions for security purposes. It also eliminates the need to recompile components when minor changes occur to the database
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 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 Server. Clustered and the Non-Clustered.
What is the basic difference between clustered and a non-clustered index?
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 cursor and types of cursors ?
- Well cursors help us to do an operation on a set of data that we retrieve 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 retrieval one by one and remove rows which have duplicate values.
Can you tell me the difference between DELETE & 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. DELETE TABLE is a logged operation, so the deletion of each row gets logged in the transaction log, which makes it slow. TRUNCATE TABLE also deletes all the rows in a table, but it won't log the deletion of each row, instead it logs the deallocation of the data pages of the table, which makes it faster. Of course, TRUNCATE TABLE can be rolled back.
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. And also Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.
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 Candidate Key ?
A column or set of columns that have a unique value for each row in a table. Each candidate key value uniquely identifies a single row in the table. Tables can have multiple candidate keys. One candidate key in a table is specified by the database designer to be the primary key for the table, and any other candidate key is called an alternate key.
What is Code Page in Sql ?
For character and Unicode data, a definition of the bit patterns that represent specific letters, numbers, or symbols (such as 0x20 representing a blank space and 0x74 representing the character "t"). Some data types use 1 byte per character; each byte can have 1 of 256 different bit patterns.
What is DCL and Different Commands under this ?
The subset of SQL statements used to control permissions on database objects. Permissions are controlled using the GRANT and REVOKE statements
What are the types of Commands available in SQL and Describe those ?
DDL is Data Definition Language statements. Some examples:
· CREATE - to create objects in the database
· ALTER - alters the structure of the database
· DROP - delete objects from the database
· TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
· COMMENT - add comments to the data dictionary
· GRANT - gives user's access privileges to database
· REVOKE - withdraw access privileges given with the GRANT command
DML is Data Manipulation Language statements. Some examples:
- SELECT - retrieve data from the a database
- INSERT - insert data into a table
- UPDATE - updates existing data within a table
- DELETE - deletes all records from a table, the space for the records remain
- CALL - call a PL/SQL or Java subprogram
- EXPLAIN PLAN - explain access path to data
- LOCK TABLE - control concurrency
DCL is Data Control Language statements. Some examples:
- COMMIT - save work done
- SAVEPOINT - identify a point in a transaction to which you can later roll back
- ROLLBACK - restore database to original since the last COMMIT
- SET TRANSACTION - Change transaction options like what rollback segment to use
What is dynamic cursor Static cursors?
dynamic cursor : A cursor that can reflect data modifications made to the underlying data while the cursor is open. Updates, deletes, and inserts made by users are reflected in the dynamic cursor
static cursor : A cursor that shows the result set exactly as it was at the time the cursor was opened. Static cursors do not reflect updates, deletes, or inserts made to underlying data while the cursor is open. They are sometimes called snapshot cursors.
What is extended stored procedure ?
A function in a dynamic link library (DLL) that is coded using the SQL Server 2000 Extended Stored Procedure API. The function can then be invoked from Transact-SQL using the same statements that are used to execute Transact-SQL stored procedures. Extended stored procedures can be built to perform functionality not possible with Transact-SQL stored procedures
What is forward-only cursor?
A cursor that cannot be scrolled; rows can be read only in sequence from the first row to the last row.
What types of Joins?
Inner join: An operation that retrieves rows from multiple source tables by comparing the values from columns shared between the source tables. An inner join excludes rows from a source table that have no matching rows in the other source tables
Left outer join : A type of outer join in which all rows from the left-most table in the JOIN clause are included. When rows in the left table are not matched by rows in the right table, all result set columns that come from the right table are assigned a value of NULL
Right outer join: A type of outer joins in which all rows in the right-most table in the JOIN clause are included. When rows in the right table are not matched in the left table, all result set columns that come from the left table are assigned a value of NULL.
Self-join: A join in which records from a table are combined with other records from the same table when there are matching values in the joined fields. A self-join can be an inner join or an outer join. In database diagrams, a self-join is called a reflexive relationship.
Outer join : A join that includes all the rows from the joined tables that have met the search conditions, even rows from one table for which there is no matching row in the other join table. For result set rows returned when a row in one table is not matched by a row from the other table, a value of NULL is supplied for all result set columns that are resolved to the table that had the missing row.
What are pivot elements?
To rotate rows to columns, and columns to rows, in a cross tabular data browser. To choose dimensions from the set of available dimensions in a multidimensional data structure for display in the rows and columns of a cross tabular structure
What is Replication and Types you can set up in SQL Server?
- Snapshot replication
- Transactional replication (with immediate updating subscribers, with queued updating subscribers)
- Merge replication
Snapshot replication : It distributes data exactly as it appears at a specific moment in time and doesn’t monitor for updates. It can be used when data changes are infrequent. It is often used for browsing data such as price lists, online catalog, or data for decision support where the current data is not required and data is used as read only.
Transactional replication : With this an initial snapshot of data is applied, and whenever data modifications are made at the publisher, the individual transactions are captured and propagated to the subscribers.
Merge Replication : It is the process of distributing the data between publisher and subscriber, it allows the publisher and subscriber to update the data while connected or disconnected, and then merging the updates between the sites when they are connected.
Authentication in SQL Server ?
There are two kinds of authentications available in SQL Server those are given below SQL Server Authentication: One of two mechanisms for validating attempts to connect to instances of SQL Server. Users must specify a SQL Server login ID and password when they connect. The SQL Server instance ensures the login ID and password combination are valid before allowing the connection to succeed. Windows authentication is the preferred authentication mechanism.
Windows Authentication: One of two mechanisms for validating attempts to connect to instances of SQL Server. Users are identified by their Windows user or group when they connect. Windows Authentication is the most secure mechanism for connecting to SQL Server.
Explain different types of Backups available in SQL Server? Given a particular scenario, how would you go about choosing a backup plan?
Types of backups you can create in SQL Sever 7.0+ are Full database backup, differential database backup, transaction log backup, filegroup backup. Check out the BACKUP and RESTORE commands in SQL Server books online. Be prepared to write the commands in your interview. Books online also has information on detailed backup/restore architecture and when one should go for a particular kind of backup.
What is Qury optimization and how you will achieve that ?
Query Optimizer: It is a component which analysis the query and determines the most efficient way to request the data and thus optimizing the sql statement. The process of choosing an execution plan out of several plans is called optimizing and this is done by query optimizer.
SQL Server Query Optimizer is a cost based optimizer. Each possible execution plan has an associated cost in terms of computing resources. The query optimizer chooses that plan which has lowest estimated cost.
What are the Temporary Tables and why these are?
Temporary tables are like permanent table but they are created in tempdb, and they are deleted automatically when no longer in use.
What are constraints? Explain different types of constraints.
Constraints enable the RDBMS enforce the integrity of the database automatically, without needing you to create triggers, rule or defaults.
Types of constraints: NOT NULL, CHECK, UNIQUE, PRIMARY KEY, FOREIGN KEY
What is Table Variable ?
Functions and variables can be declared to be of type table. table variables can be used in functions, stored procedures, and batches.
Use table variables instead of temporary tables, whenever possible. table variables provide the following benefits:
- A table variable behaves like a local variable. It has a well-defined scope, which is the function, stored procedure, or batch in which it is declared.
Within its scope, a table variable may be used like a regular table. It may be applied anywhere a table or table expression is used in SELECT, INSERT, UPDATE, and DELETE statements. However, table may not be used in the following statements:
INSERT INTO table_variable EXEC stored_procedure
SELECT select_list INTO table_variable statements.
table variables are cleaned up automatically at the end of the function, stored procedure, or batch in which they are defined.
- table variables used in stored procedures result in fewer recompilations of the stored procedures than when temporary tables are used.
- Transactions involving table variables last only for the duration of an update on the table variable. Thus, table variables require less locking and logging resources.
Assignment operation between table variables is not supported. In addition, because table variables have limited scope and are not part of the persistent database, they are not impacted by transaction rollbacks.
No comments:
Post a Comment