DBMS(Database management system)
Advantage: Common Table Expression (CTE) improve the performance of the pagination query in SQL Server as it takes only needed data first then do the calculation as above query.
Full Outer Join: A combination of both left and right outer joins. Results in every row from both of the tables , at least once. Assigns NULL for unmatched fields.
Functions cannot return values of type text, ntext, image & timestamps were as procedures can
AFTER (default) - The trigger executes after the statement that triggered it completes. If the statement fails with an error, such as a constraint violation or syntax error, the trigger is not executed. AFTER triggers cannot be specified for views.
INSTEAD OF -The trigger executes in place of the triggering action. INSTEAD OF triggers can be specified on both tables and views. You can define only one INSTEAD OF trigger for each triggering action (INSERT, UPDATE, and DELETE). INSTEAD OF triggers can be used to perform enhance integrity checks on the data values supplied in INSERT and UPDATE statements. INSTEAD OF triggers also let you specify actions that allow views, which would normally not support updates, to be updatable.
Joins can be categorized as:
LEFT JOIN or LEFT OUTER JOIN -The result set of a left outer join includes all the rows from the left table specified in the LEFT OUTER clause, not just the ones in which the joined columns match. When a row in the left table has no matching rows in the right table, the associated result set row contains null values for all select list columns coming from the right table.
RIGHT JOIN or RIGHT OUTER JOIN - A right outer join is the reverse of a left outer join. All rows from the right table are returned. Null values are returned for the left table any time a right table row has no matching row in the left table.
FULL JOIN or FULL OUTER JOIN - A full outer join returns all rows in both the left and right tables. Any time a row has no match in the other table, the select list columns from the other table contain null values. When there is a match between the tables, the entire result set row contains data values from the base tables.
clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf nodes of a nonclustered index do not consist of the data pages. Instead, the leaf nodes contain index rows.
Note: Rollback will happened when Transaction at Parent Level
Ex: Transaction at procedure level then Rollback will apply on inside Trigger DMLs
Create in 3 ways
SYSDATETIME () - Returns a datetime2(7) value. The time zone offset is not included
SYSDATETIMEOFFSET () - Returns a datetimeoffset(7) value. The time zone offset is included
SYSUTCDATETIME () - Returns a datetime2(7) value. The date and time is returned as UTC time (Coordinated Universal Time).
CURRENT_TIMESTAMP - Returns a datetime value. The time zone offset is not included.
GETDATE () - Returns a datetime value. The time zone offset is not included.
GETUTCDATE ()- Returns a datetime value. The date and time is returned as UTC time (Coordinated Universal Time).
DATENAME (datepart, date)- Returns a character string that represents the specified datepart of the specified date.
DATEPART (datepart, date) - Returns an integer that represents the specified datepart of the specified date.
DAY (date) - Returns an integer that represents the day day part of the specified date.
MONTH (date) - Returns an integer that represents the month part of a specified date.
YEAR (date) - Returns an integer that represents the year part of a specified date.
DATEFROMPARTS (year, month, day) - Returns a date value for the specified year, month, and day.
DATETIME2FROMPARTS (year, month, day, hour, minute, seconds, fractions, precision)- Returns a datetime2 value for the specified date and time and with the specified precision.
DATETIMEFROMPARTS (year, month, day, hour, minute, seconds, milliseconds)- Returns a datetime value for the specified date and time.
DATETIMEOFFSETFROMPARTS (year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision)- Returns a datetimeoffset value for the specified date and time and with the specified offsets and precision.
SMALLDATETIMEFROMPARTS (year, month, day, hour, minute)- Returns a smalldatetime value for the specified date and time.
TIMEFROMPARTS (hour, minute, seconds, fractions, precision)- Returns a time value for the specified time and with the specified precision.
DATEDIFF (datepart, startdate, enddate) - Returns the number of date or time datepart boundaries that are crossed between two specified dates.
DATEADD (datepart, number, date) - Returns a new datetime value by adding an interval to the specified datepart of the specified date.
EOMONTH (start_date [,month_to_add])- Returns the last day of the month that contains the specified date, with an optional offset.
SWITCHOFFSET (DATETIMEOFFSET , time_zone)- SWITCHOFFSET changes the time zone offset of a DATETIMEOFFSET value and preserves the UTC value.
TODATETIMEOFFSET (expression , time_zone) - TODATETIMEOFFSET transforms a datetime2 value into a datetimeoffset value. The datetime2 value is interpreted in local time for the specified time_zone.
ISDATE (expression)- Determines whether a datetime or smalldatetime input expression is a valid date or time value.
ASCII ( character_expression )- Returns the ASCII code value of the leftmost character of a character expression.
CHAR ( integer_expression ) - Converts an int ASCII code to a character.
CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] ) - Searches an expression for another expression and returns its starting position if found.
CONCAT ( string_value1, string_value2 [, string_valueN ] ) - Returns a string that is the result of concatenating two or more string values.
DIFFERENCE ( character_expression , character_expression ) - Returns an integer value that indicates the difference between the SOUNDEX values of two character expressions.
FORMAT (value, format [, culture ]) - Returns a value formatted with the specified format and optional culture in SQL Server 2012. Use the FORMAT function for locale-aware formatting of date/time and number values as strings.
LEFT (character_expression, integer_expression) - Returns the left part of a character string with the specified number of characters.
LEN (string_expression) - To return the number of bytes used to represent an expression
DATALENGTH ( expression ) - Returns the number of bytes used to represent any expression.
LOWER ( character_expression ) - Returns a character expression after converting uppercase character data to lowercase.
LTRIM ( character_expression ) - Returns a character expression after it removes leading blanks.
NCHAR ( integer_expression ) - Returns the Unicode character with the specified integer code, as defined by the Unicode standard.
PATINDEX ( '%pattern%' , expression ) - Returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
QUOTENAME ( 'character_string' [ , 'quote_character' ] ) - Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.
REPLACE ( string_expression , string_pattern , string_replacement ) - Replaces all occurrences of a specified string value with another string value.
REPLICATE ( string_expression ,integer_expression ) - Repeats a string value a specified number of times.
REVERSE ( string_expression ) - Returns the reverse order of a string value.
RIGHT ( character_expression , integer_expression ) - Returns the right part of a character string with the specified number of characters.
RTRIM ( character_expression ) - Returns a character string after truncating all trailing blanks.
SOUNDEX ( character_expression ) - Returns a four-character (SOUNDEX) code to evaluate the similarity of two strings.
SPACE ( integer_expression ) - Returns a string of repeated spaces.
STR ( float_expression [ , length [ , decimal ] ] ) - Returns character data converted from numeric data.
STUFF ( character_expression , start , length , replaceWith_expression ) - The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
SUBSTRING (expression ,start , length ) - Returns part of a character, binary, text, or image expression in SQL Server 2012.
UNICODE ('ncharacter_expression') - Returns the integer value, as defined by the Unicode standard, for the first character of the input expression.
UPPER (character_expression) - Returns a character expression with lowercase character data converted to uppercase.
CHOOSE (index, val_1, val_2 [, val_n ]) - Returns the item at the specified index from a list of values in SQL Server 2012.
IIF (boolean_expression, true_value, false_value) - Returns one of two values, depending on whether the Boolean expression evaluates to true or false in SQL Server 2012.
CAST ( expression AS data_type [ ( length ) ] )
CONVERT ( data_type [ ( length ) ] , expression [ , style ] ) - Converts an expression of one data type to another in SQL Server 2012.
PARSE ( string_value AS data_type [ USING culture ] ) - Returns the result of an expression, translated to the requested data type in SQL Server 2012.
TRY_CAST ( expression AS data_type [ ( length ) ] ) - Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
TRY_CONVERT ( data_type [ ( length ) ], expression [, style ] ) - Returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.
TRY_PARSE ( string_value AS data_type [ USING culture ] ) - Returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server 2012. Use TRY_PARSE only for converting from string to date/time and number types.
First Normal Form (1NF):
Second Normal Form (2NF):
Third Normal Form (3NF):
4th Normal Form (4NF):
5th Normal Form (5NF)(PJNF):
The term, collation, refers to a set of rules that determine how data is sorted and compared.
Windows collations are collations defined for SQL Server to support the Windows system locales available for the operating system on which SQL Server instances are installed.
SQL Server Collations
SQL Server collations are a compatibility option to match the attributes of common combinations of code-page number and sort orders that have been specified in earlier versions of SQL Server. Many of these collations support suffixes for case, accent, kana, and width sensitivity, but not always.
A collation ties together:
2) Sort Order for Unicode data types.
3) Sort order for code page characters
For example, the Latin1_General_CI_AS collation is a Latin code page that is Case Insensitive (CI) and Accent Sensitive (AS).
A linked server configuration enables SQL Server to execute commands against OLE DB data sources on remote servers. 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. Linked servers offer the following advantages: 1. Remote server access. 2. The ability to issue distributed queries, updates, commands, and transactions on heterogeneous data sources across the enterprise. 3. The ability to address diverse data sources similarly. 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.
41. Diff between Primary and Foreign Key?
By default Primary Key will generate Clustured Index
Primary Key is a combination of Unique and NOT NULL Constraints (so it can’t have duplicate values or any NUll)
A table can have only one PK but It can have any number of UNIQUE Key.
ROW_NUMBER() OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking DENSE_RANK() function uses, if two or more rows tie for a rank in the same partition, each tied rows receives the same rank, however leaving no gaps in the ranking where there are ties..
View: Virtual table/window to a table
Simple: Created on single table
Select * from Party P1 where pt.partyIncome>(Select Avg(P2.partyIncome) from Party P2 where P1.DID=P2.DID
Select Col1+Substring(Col2,1,10), Substring(Col2,11,5) from Table1
Exists faster than IN
Group by is faster than distinct
By Distinct or Group by
Join: Multi table operation and return multiple columns from multiple tables (get the data from more than one table, its work under the condition)
ACID (an acronym for Atomicity Consistency Isolation Durability) is a concept when evaluating databases and application architectures. For a reliable database all this four attributes should be achieved. Atomicity is an all-or-none proposition. Consistency guarantees that a transaction never leaves your database in a half-finished state. Isolation keeps transactions separated from each other until they’re finished. Durability guarantees that the database will keep track of pending changes in such a way that the server can recover from an abnormal termination. Note: Above four rules are very important for any developers dealing with databases.
E.F. Codd, the famous mathematician has introduced 12 rules for the relational model for databases commonly known as Codd's rules. The rules mainly define what is required for a DBMS for it to be consideredrelational, i.e., an RDBMS. There is also one more rule i.e. Rule00 which specifies the relational model should use the relational way to manage the database. The rules and their description are as follows:-
Rule 0: Foundation Rule: A relational database management system should be capable of using its relational facilities (exclusively) to manage the database.
Note that based on these rules there is no fully relational database management system available today. In particular, rules 6, 9, 10, 11 and 12 are difficult to satisfy.
Insert a new ID which we'll call @Id UPDATE myTable SET ID = ID +1 WHERE ID > (@Id -1) INSERT INTO myTable (ID, otherColumns) VALUES (@Id, otherValues)