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.
2. What are the properties of the Relational tables?
ANS: Relational tables have six properties:
1. Values are atomic.
2. Column values are of the same kind.
3. Each row is unique.
4. The sequence of columns is insignificant.
5. The sequence of rows is insignificant.
6. Each column must have a unique name.
3. What are different normalization forms?
ANS: 1NF, 2NF, 3NF, BCNF (Boyce Codd Normal Form), 4NF, 5NF, ONF and DKNF.
4. What is Stored Procedure?
ANS: A stored procedure is a named group of SQL statements that have been previously created and stored in the server database. Stored procedures accept input parameters so that a single procedure can be used over the network by several clients using different input data. And when the procedure is modified, all clients automatically get the new version. Stored procedures reduce network traffic and improve performance. Stored procedures can be used to help ensure the integrity of the database. e.g. sp_helpdb, sp_renamedb, sp_depends etc.
5. What is Trigger?
ANS: A trigger is a SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are stored in and managed by the DBMS. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed; DBMS automatically fires the trigger as a result of a data modification to the associated table. Triggers can be viewed as similar to stored procedures in that both consist of procedural logic that is stored at the database level.
6. What is Nested Trigger?
ANS: A trigger can also contain INSERT, UPDATE and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.
7. What is View?
ANS: A simple view can be thought of as a subset of a table. It can be used for retrieving data, as well as updating or deleting rows. Rows updated or deleted in the view are updated or deleted in the table the view was created with. It should also be noted that as data in the original table changes, so does data in the view, as views are the way to look at part of the original table. The results of using a view are not permanently stored in the database.
8. What is a Linked Server?
ANS: 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. With a linked server, you can create very clean, easy to follow, SQL statements that allow remote data to be retrieved, joined and combined with local data. Stored Procedure sp_addlinkedserver, sp_addlinkedsrvlogin will be used add new Linked Server
9. What is Collation?
ANS: Collation refers to a set of rules that determine how data is sorted and compared. Character data is sorted using rules that define the correct character sequence, with options for specifying case sensitivity, accent marks, kana character types and character width.
10. What is 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 thought of as views that take parameters and can be used in JOINs and other Rowset operations.
11. What are different Types of Join?
ANS: There are four types.
1. Cross Join
2. Inner Join
3. Outer Join (Left Outer Join, Right Outer Join and Full Outer Join)
4. Self Join
12. What are primary keys and foreign keys?
ANS: 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.
13. What is Identity?
ANS: 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.
14. 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.
15. What is OLTP (Online Transaction Processing)?
ANS: 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.
16. 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.
17. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
ANS: 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.
18. What are the properties of Sub-Queries?
ANS:
1. A sub query must be enclosed in the parenthesis.
2. A sub query must be put in the right hand of the comparison operator, and
3. A sub query cannot contain an ORDER‐BY clause.
4. A query can contain more than one sub query.
19. What are different Types of Sub-Queries?
ANS:
1. Single‐row sub-query, where the sub‐query returns only one row.
2. Multiple‐row sub-query, where the sub‐query returns multiple rows,. and
3. Multiple column sub-query, where the sub‐query returns multiple columns
20. What is SQL Profiler?
ANS: 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.
21. 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.
22. Which command using Query Analyzer will give you the version of SQL server and operating system?
ANS: SELECT SERVERPROPERTY ('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition').
23. What is SQL Server Agent?
ANS: SQL Server agent plays an important role in the day‐to‐day tasks of a database administrator (DBA). It is often overlooked as one of the main tools for SQL Server management. Its purpose is to ease the implementation of tasks for the DBA, with its full‐function scheduling engine, which allows you to schedule your own jobs and scripts.
24. What is Log Shipping?
ANS: Log shipping is the process of automating the backup of database and transaction log files on a production SQL server, and then restoring them onto a standby server. Enterprise Editions only supports log shipping. In log shipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and can be used this as the Disaster Recovery plan. The key feature of log shipping is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined interval.
25. Name 3 ways to get an accurate count of the number of records in a table?
ANS:
1. SELECT * FROM table1
2. SELECT COUNT(*) FROM table1
3. SELECT rows FROM sysindexes WHERE id = OBJECT_ID(table1) AND indid < 2
26. What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?
ANS: When SET QUOTED_IDENTIFIER is ON, identifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks. When SET QUOTED_IDENTIFIER is OFF, identifiers cannot be quoted and must follow all Transact‐SQL rules for identifiers.
27. What is the difference between a Local and a Global temporary table?
ANS: A local temporary table exists only for the duration of a connection or, if defined inside a compound statement, for the duration of the compound statement. A global temporary table remains in the database permanently, but the rows exist only within a given connection. When connection is closed, the data in the global temporary table disappears. However, the table definition remains with the database for access when database is opened next time.
28. What is BCP? When does it used?
ANS: BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination. BULK INSERT command helps to import a data file into a database table or view in a user‐specified format.
29. What is CHECK Constraint?
ANS: A CHECK constraint is used to limit the values that can be placed in a column. The check constraints are used to enforce domain integrity.
No comments:
Post a Comment