Babu's Study Deck Flashcards

Question

What is a cursor?

Answer 1

``` It is a loop that allows you to do row by row processing. You should avoid using a cursor because it kills performance. The following order must be followed: • Declare cursor • Fetch row from the cursor • Process fetched row • Close cursor • Deallocate cursor ```

Answer 2

Specifies how data is sorted and compared in a database. It provides the sorting rules, case, and accent sensitivity properties for the data in the database.

Answer 3

* Function must return a value but in Stored Procedures it is optional (Procedure can return zero or n values) * Functions can have only input parameters for it whereas Procedures can have input/output parameters * Functions can be called from Procedures whereas Procedures cannot be called from Function

Answer 4

It is a SELECT statement that is nested within another T-SQL statement. A subquery can be used anywhere an expression can be used.

Answer 5

* Must be enclosed in the parenthesis * Must be put in the right hand of the comparison operator * Cannot contain an ORDER BY clause * Can contain more than one subquery

Answer 6

* Cross JOIN * Inner JOIN * Outer JOIN * Self-JOIN

Answer 7

* Cross Join that does not have a WHERE clause produces the Cartesian product of the tables involved in the join * The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table

Answer 8

Displays only the rows that have a match in both joined tables and is the default type of JOIN in the Query and View Designer.

Answer 9

It includes rows even if they do not have related rows in the joined table. There are three types of Outer JOINS: • Left Outer JOIN all the rows in the first-named table which appears leftmost in the JOIN clause, are included. Unmatched rows in the right table do not appear. • Right Outer JOIN all the rows in the second-named table which appears rightmost in the JOIN clause are included. Unmatched rows in the left table are not included. • Full Outer JOIN all the rows in all joined tables are included, whether they are matched or not.

Answer 10

When one table joins to itself with one or two aliases to avoid confusion. Can be of any type, as long as the joined tables are the same. It is unique in that it involves a relationship with only one table. It can also be an Outer JOIN or an Inner JOIN.

Answer 11

Primary keys are the unique identifiers for each row and must contain unique values which cannot be null. A table can have only one Primary key. Foreign keys are a method of ensuring data integrity and manifestation of the relationship between tables.

Answer 12

Allow defining its own T-SQL functions that can accept zero or more parameters and return a single scalar data value or a table data type.

Answer 13

* Scalar User-defined Function returns one of the scalar data types. Data types not supported are: text, ntext, image, and timestamp * 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 returns a table, and it 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.

Answer 14

A special type of column that is used to automatically generate key values based on a provided seed (starting point) and increment.

Answer 15

* 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 * Changes to the data in the database are tracked and recorded so that reports can be produced showing changes over time (time-variant) * Non-volatile – the database is never over-written or deleted, once committed, the data is static, read-only, but retained for future reporting * Integrated – the database contains data from most or all of an organization’s operational applications, and that this data is made consistent

Answer 16

* MDX – Multidimensional Expressions retrieves data from SSAS cubes * DMX – Data Mining Extensions used for data mining structures. * XMLA – XML for Analysis commonly used in administration tasks such as backup or restore database, copy and move database, or for learning Meta data information.

Answer 17

It is a computer that is located in close proximity to the production server(s) and can be used to temporarily replace a production server if it experiences a hardware failure. It can also be used to verify that you can recover databases from their full backups.

Answer 18

A redundant method in which one system runs simultaneously with an identical primary system. Upon failure of the primary system, the hot standby system immediately takes over, replacing the primary system. The data is mirrored in real time.

Answer 19

A method of redundancy in which the secondary (i.e., backup) system runs in the background of the primary system. Data is mirrored to the secondary server at regular intervals, which means that there are times when both servers do not contain the exact same data.

Answer 20

A method of redundancy in which the secondary (i.e., backup) system is only called upon when the primary system fails. The system on cold standby receives scheduled data backups, but less frequently than a warm standby. Cold standby systems are used for non-critical applications or in cases where data is changed infrequently. Has to be turned on manually and physically replaces the previous server.

Answer 21

Is as known as uncommitted dependency, and it occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.

Answer 22

Rows can logically disappear from an indexed view based on Outer JOIN when you insert data into a base table. This makes incrementally updating Outer JOIN views relatively complex to implement, and the performance of the implementation would be slower than for views based on standard (INNER) JOIN.

Answer 23

1. FROM 2. ON 3. OUTER 4. WHERE 5. GROUP BY 6. CUBE | ROLLUP 7. HAVING 8. SELECT 9. DISTINCT 10. TOP 11. ODER BY

Answer 24

It can be changed from the Network Utility TCP/IP properties – Port number, both on client and the server.

Answer 25

A clustered index describes the order in which records are physically stored on the disk, and a table can only have one clustered index. It is essentially a sorted copy of the data in the indexed columns. A non-clustered index defines a logical order that does not match the physical order on disk. The leaf node of a non-clustered index does not consist of the data pages. The leaf nodes contain index rows.

Answer 26

* No indexes * A clustered index * A clustered index and many non-clustered indexes * A non-clustered index * Many non-clustered indexes

Answer 27

* Case Sensitivity: A and a, B and b, etc. * Accent Sensitivity: a and á, o and ó, etc. * Kana Sensitivity: When Japanese Kana characters. Hiragana and Katakana are treated differently, it is called Kana sensitive. * Width Sensitivity: A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently, it is width sensitive.

Answer 28

A category of data processing that is focused on transaction-oriented tasks. It typically involves inserting, updating, and/or deleting small amounts of data in a database. It also mainly deals with large numbers of transactions by a large number of users. Ensures data integrity.

Answer 29

The primary key creates a clustered index on the column (used to identify a row (record) in a table. A unique key prevents duplicate values in a column and allows one Null and creates a non-clustered index by default.

Answer 30

DELETE removes the rows from a table on the basis of 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 running the command.

Answer 31

* Shared Locks: Read-only operations such as a SELECT statement * Update Locks: * Exclusive Locks * Intent Locks * Schema Locks * Bulk Update Locks

Answer 32

Optimistic Locking: A strategy where you read a record, take note of a version number and check that the version hasn’t changed before you write the record back. If the record is dirty, abort the transaction and the user can re-start it. Pessimistic Locking: The record is locked for exclusive use until you have finished with it. It has much better integrity than optimistic locking but requires you to be careful with your application design to avoid Deadlocks.

Answer 33

The command is used when a large amount of data is processed. If a large amount of deletions, modifications 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.

Answer 34

The HAVING clause can be used only with SELECT statement and is typically used in a GROUP BY clause. It behaves like a WHERE clause when GROUP BY is not used. Basically, the HAVING clause is used only with the GROUP BY function in a query. The WHERE clause is applied to each row before they are part of the GROUP BY function in a query.

Answer 35

A cache of database connections maintained so that the connections can be reused when future requests to the database are required. They are used to enhance the performance of executing commands on a database.

Answer 36

* Must be enclosed in the parenthesis * Must be put on the right hand of the comparison operator * Cannot contain an ORDER BY clause, however sub-query can use ORDER BY when used with TOP clause * Can contain more than one sub-query

Answer 37

* Single-row: the sub-query returns only one row * Multiple-row sub-query: the sub-query returns multiple rows * Multiple column: the sub-query returns multiple columns

Answer 38

A graphical tool that allows system administrators to monitor events. An interface to create and manage traces and analyze and replay trace results. Events are saved in a trace file that can later be analyzed or used to replay a specific series of steps when trying to diagnose a problem.

Answer 39

* Window Mode | * Mixed Mode – SQL and Windows

Answer 40

1. SELECT @@VERSION 2. SELECT SERVERPROPERTY (‘Edition’) AS Edition, SERVERPROPERTY (ProductLevel’) AS ProductLevel, SERVERPROPERTY (‘ProductVersion’) AS ProductVersion GO

Answer 41

It allows you to schedule your own jobs and scripts, and it also helps to ease the implementation of tasks for the DBA, with its full-function scheduling engine.

Answer 42

Yes, because it supports recursion. You can write stored procedures that call themselves. Recursion can be defined as a method of problem solving wherein the solution is arrived at by repetitively applying it to subsets of the problem. A common application of recursive logic is to perform numeric computations that lend themselves to repetitive evaluation by the same processing steps. Stored procedures are nested when one stored procedure calls another or executes managed code by referencing a CLR routine, type, or aggregate.

Answer 43

Stored procedures can be nested up to 32 levels.

Answer 44

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. The Express edition does not support 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 can be used as the Disaster Recovery plan. The key feature is that it will automatically backup transaction logs throughout the day and automatically restore them on the standby server at defined intervals.

Answer 45

1. SELECT * FROM table1 2. SELECT COUNT(*) FROM table1 3. SELECT rows FROM sysindexes WHERE id = OBJECT(table1) AND indid < 2 4. Select a column from a table and look at the count at the lower right for the count

Answer 46

vIdentifiers can be delimited by double quotation marks, and literals must be delimited by single quotation marks.

Answer 47

Identifiers cannot be quoted and must follow all T-SQL rules for identifiers.

Answer 48

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 accessible across the connections. Once the connection where original global table is declared dropped this becomes unavailable. Can be seen by all users connected to the server.

Answer 49

It is used to overwrite existing characters using this syntax: STUFF (string_expression, start, length, replacement_characters), where string_expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement_characters are the new characters interjected into the string.

Answer 50

The REPLACE function is used to replace existing characters of all occurrences. Using the syntax REPLACE (string_expression, search_string, replacement_string), every incidence of search_string found in the string_expression will be replaced with replacement_string.

Answer 51

A unique identifier for a row within a database table. Every table should have a primary key constraint to uniquely identify each row, and only one primary key constraint can be created for each table. It is used to enforce entity integrity. CANNOT HAVE A NULL.

Answer 52

It enforces the uniqueness of the values in a set of columns; so, no duplicate values are entered. They are used to enforce entity integrity as the primary key constraints. Can have one NULL.

Answer 53

Prevents any actions that would destroy links between tables with the corresponding data values. A FOREIGN key in one table points to a primary key in another table. They prevent actions that would leave rows with foreign key values when there are no primary keys with that value. The foreign key constraints are used to enforce referential integrity.

Answer 54

Is used to limit the values that can be placed in a column, and they are used to enforce domain integrity. I

Answer 55

It enforces that the column will not accept null values and is used to enforce domain integrity, as the check constraints.

Answer 56

The UNION command is used to select related information from two tables, much like the JOIN command. When using the UNION command, all selected columns need to be of the same data type and only distinct values are selected. Union does not return duplicates. UNION ALL command selects all values. The UNION ALL command will not eliminate duplicate rows, instead it pulls all rows from all the tables fitting your query specifics and combines them into a table.

Answer 57

The database server uses a B-tree structure to organize index information and generally, has the following types of index pages or nodes: • Root node: A root node contains node pointers to only one branch node. • Branch node: A branch node contains pointers to leaf nodes or other branch nodes, which can be two or more. • Leaf nodes: A leaf node contains index items and horizontal pointers to other leaf nodes, which can be many.

Answer 58

Include both in the same statement and store them in a local variable. SELECT @RC = @@ROWCOUNT, @ER = @@ERROR

Answer 59

The database server uses a B-tree structure to organize index information and generally, has the following types of index pages or nodes: • Root node: A root node contains node pointers to only one branch node. • Branch node: A branch node contains pointers to leaf nodes or other branch nodes, which can be two or more. • Leaf nodes: A leaf node contains index items and horizontal pointers to other leaf nodes, which can be many.

Answer 60

Scheduled tasks are automated processes that run on regular or predictable cycles. The user can determine the order in which tasks run by creating job steps within a SQL Server Agent job. Job steps give the user control over flow of execution. If one job fails, the user can configure SQL Server Agent to continue to run the remaining tasks or to stop execution.

Answer 61

* Can reduce network traffic and latency, boosting application performance * Execution plans can be reused because they are cached in SQL Server’s memory, reducing server overhead * Help promote code reuse * Encapsulate logic. Can change stored procedure code without affecting clients * Provide better security to data

Answer 62

An unindexed table or Heap. A Heap is a table that does not have a clustered index and therefore, the pages are not linked by pointers. The IAM pages are the only structures that link the pages in a table together.

Answer 63

The fast storing of data. Many times, it is better to drop all the indexes from table and then do bulk of INSERTS and restore those indexes after that.

Answer 64

SQL Server can be linked to any server provided it has OLE-DB provider from Microsoft to allow a link. Oracle has an OLE-DB provider that Microsoft provides to add it as a linked server to the SQL Server group.

Answer 65

BCP (BulkCopy) is a tool used to copy huge amount of data from tables and views. It does not copy the complete structure from source to destination.

Answer 66

It is used when you need to import and export large amounts of data in and out of SQL Server databases quickly and easily. The BULK INSERT command helps to import a data file into a database table or view in a user -specified format

Answer 67

* To rename a database use sp_renamedb ‘oldname’, ‘newname * To rename a table use sp_RENAME ‘Table_First’, ‘Table_Last’ GO * To rename column use sp_rename ‘TableName.[OldcolumnName]’, ‘NewColumnName’, ‘Column’

Answer 68

They are used to display or change server-level settings. To change the database-level settings, use ALTER DATABASE.

Answer 69

Change the settings that affect only the current user session.

Answer 70

Commit makes it permanent and rollback goes back to the original state before the transaction started

Answer 71

A road map that graphically or textually shows the data retrieval methods chosen by the SQL Server query optimizer for a stored procedure or ad-hoc query.

Answer 72

You would use it for performance tuning to make something faster.

Answer 73

Within the Query Analyzer, there is an option called “Show Execution Plan” (in the Query drop-down menu), if this option is turned on, it will display query execution plan in a separate window when the query is ran again.

Answer 74

Table aliases are used to avoid any ambiguity with respect to columns selected in SELECT statement which might belong to two or more tables joined. Column aliases is used to name a column in an understandable format. Aliases do not affect performance.

Answer 75

CHAR is a fixed-length character data type and the storage size of the CHAR value is equal to the maximum size for this column. Can use when the data entries in a column are expected to be the same size. VARCHAR is a variable-length character data type.

Answer 76

VARCHAR stores variable-length character data whose range varies up to 8000 bytes; varchar(MAX) stores variable-length character data whose range may vary beyond 8000 bytes and till 2 GB. TEXT datatype is going to be deprecated in future versions, and the usage of VARCHAR(MAX) is strongly recommended instead of TEXT datatypes

Answer 77

NVARCHAR supports Unicode characters, allowing you to use multiple languages in the database. It’s takes twice as much space when compared to VARCHAR. Use NVARCHAR only if you are using foreign languages.

Answer 78

1. The column must be of Unicode data type (NCHAR, NVARCHAR, NTEXT). 2. The value must be prefixed with N while insertion. For example: INSERT INTO table (Hindi_col) values (N’hindi data’)

Answer 79

* Include SET NOCOUNT ON statement * Use schema name with object name * Don’t use the prefix “sp_” in the stored procedure name * Use IF EXISTS (SELECT 1) instead of (SELECT *) * Use the sp_executesql stored procedure instead of the EXECUTE statement * Avoid using SQL Server cursors whenever possible * Keep the transactions as short as possible * Use TRY-Catch for error handling

Answer 80

The TOP operator is used to specify the number of rows to be returned by a query. The TOP operator has new addition in SQL SERVER 2008 that it accepts variables as well as literal values and can be used with INSERT, UPDATE, and DELETE statements.

Babu's Study Deck Flashcards

getting a developer job