Use LEFT and RIGHT arrow keys to navigate between flashcards;
Use UP and DOWN arrow keys to flip the card;
H to show hint;
A reads text to speech;
11 Cards in this Set
- Front
- Back
|
What is user defined functions in sql? |
User-defined functions are * routines * accept parameters * perform an action( complex calculation) * return the result of that action as a value. * Return value can either be a scalar value or a result set(table) |
|
|
What is a stored procedure in sqlserver |
A stored procedure is a group of T-sql statements that you can save, so the code can be reused over and over again. |
|
|
What is trigger in sqlserver? |
A trigger is a special type of stored procedure that automatically fires in response to a DML action in the database. DML events are INSERT, UPDATE, or DELETE. |
|
|
What is an index? |
An index is a physical structure, - index provides efficient method for locating a particular row or set of rows.--common used to enforce uniqueness constraints- - Used to speed retrieval of rows from the table or view. - The keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently |
|
|
What is a view |
In SQL, a view is a virtual table based on the result-set of a SQL statement. A view contains rows and columns, just like a real table. The column in a view are columns from one or more real tables in the database. |
|
|
Partition function |
SQL Server Partition Functions. A partition function is a function in the current database. - Maps(assigned) the rows of a table or index into partitions - based on the values of a specified column. |
|
|
ER diagram |
--An entity-relationship (ER) diagram is a specialized graphic that illustrates the interrelationships between entities in a database. --ER diagrams often use symbols to represent three different types of information. Boxes are commonly used to represent entities. Diamonds are normally used to represent relationships and ovals are used to represent attributes.
|
|
|
Statistics |
Statistics are something the query optimizer will use to decide what type of index (tablescan or index scan) to be used to search data. - Statistics change according to inserts and updates on the table,
-In short “Index statistics” are notsame in all situations. So DBA has to run statistics again and again after certain interval to ensure that the statistics are up-to-date with the current data.
|
|
|
Referential integrity |
Referential integrity ensures that relationships between tables remain consistent. --When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key(child) unless there is a corresponding record in the linked table (parent). --It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are reflected in the primary table. |
|
|
Diffrence between Function and SP |
The main differences are :- 1. Functions must return a value - procedure may or may not return any value. 2. Function can return only a single value at a time - Procedure can return one, many or none. 3. Function can be called from sql statements while - procedures can't. 4. You can join UDF - You can not join SP 5. Functions Cannot be used to change server configuration - while Procedures Can be used to change server configuration 6)Cannot be used with XML FOR clause in function while Can be used with XML FOR clause |
|
|
Subquery |
--A sub-query or inner query or nested query is a query within a query. - It is a SELECT statement that is nested within another T-SQL statement. - used by the DBMS to determine the results of the higher-level query that contains the sub-query. -Appear within the WHERE or HAVING clause of another SQL statement. -- Inner Query is independent of Outer Query. Inner Query is executed first and the results are stored· Outer Query then runs on the stored results--Used as a parameter to a functional call.--comparision operators like =, <, >, >=, <= etc--Usually return one record but can also return multi record when useed with operator like IN,NOT IN |
