70-461 Sql Server Data Types

Front 1

What is the use of MERGE statement?

Back 1

With the MERGE statement, you can MERGE data from a source table or table expression into a target table.

MERGE INTO AS TGT

USING table/ expression/select query AS SRC

ON

WHEN MATCHED [AND ]

THEN

WHEN NOT MATCHED [BY TARGET] [AND PREDICATE]

THEN INSERT...

WHEN NOT MATCHED BY SOURCE [AND PREDICATE] THEN ;.>

MERGE clause solves the issue of non deterministic update found with the JOIN clause.

Front 2

Numeric vs Decimal?

Back 2

Both are functionally similar. Both are declared as DECIMAL(m,d) and NUMERIC(m,d).

where m is the total number of digits and d is the number of digit after the decimal.

In both cases, m defaults to 18 and d to 0.

Front 3

What is the problem with REAL and FLOAT?

Back 3

REAL and FLOAT should be used in areas like scientific calculations for very large/small numbers but shouldn't be used in those cases where precision is needed. In other words, numeric/decimal data types should be used in the latter case.

Front 4

CAST , CONVERT and PARSE?

Back 4

SELECT CAST(1 as varchar);

SELECT CONVERT(DATE, '1/1/2012', 101);

SELECT PARSE('1/1/2012' AS DATE USING 'en-US');

Front 5

CONCAT() vs string concatenation( + operator)

Back 5

The +opeartor returns null on NULL input whereas CONCAT() replaces NULL input with an empty string.

Front 6

FORMAT()

Back 6

Returns a value formatted with the specified format and optional culture in SQL Server 2016. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT.

DECLARE @d DATETIME = '10/01/2011';

SELECT FORMAT ( @d, 'd', 'en-gb' ) AS 'Great Britain English Result' ;

//returns 01/10/2011

FORMAT(Now(),'YYYY-MM-DD')

Front 7

NULLIF()

Back 7

NULLIF(col1, col2) returns null if and b are equal otherwise returns a NULL.

Front 8

IIF()

Back 8

IIF(, , . it's similar to COALESCE or CASE expressions.

Front 9

CHOOSE()

Back 9

CHOOSE(, , , ) returns the expression at the specified the position.

CHOOSE(2, 'x','y','z') returns 'y'.

Front 10

ISNULL ()

Back 10

ISNULL ( check_expression , replacement_value ) - Replaces NULL with the specified replacement value.

Similar to COALESCE function, but the COALESCE () is the standard.

Front 11

SUBSTRING

Back 11

SUBSTRING ( expression ,start , length ). numbering is 1 based. Returns part of a character, binary, text, or image expression in SQL Server.

Front 12

STUFF()

Back 12

STUFF ( character_expression , start , length , replaceWith_expression ). The STUFF function inserts a string into another string at the specified position by start and length values.

Front 13

LEFT()

Back 13

LEFT ( character_expression , integer_expression ) - Returns the left part of a character string with the specified number of characters.

Front 14

REPLACE()

Back 14

REPLACE ( string_expression , string_pattern , string_replacement )

Front 15

REPLICATE

Back 15

REPLICATE ( string_expression ,integer_expression )

Front 16

CHARINDEX ()

Back 16

Searches an expression for another expression and returns its starting position if found.

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

Front 17

PATINDEX

Back 17

PATINDEX ( '%pattern%' , expression )

Front 18

ROUND

Back 18

ROUND ( numeric_expression , length [ ,function ] ) . Returns a numeric value, rounded to the specified length or precision.

When length is a positive number, numeric_expression is rounded to the number of decimal positions specified by length. When length is a negative number, numeric_expression is rounded on the left side of the decimal point, as specified by length.

Front 19

RAND

Back 19

Returns a pseudo-random float value from 0 through 1, exclusive.

RAND ( [ seed ] )

Front 20

ABS

Back 20

ABS ( numeric_expression ). ABS returns the absolute (positive) value of the specified numeric expression.

Front 21

SIGN

Back 21

SIGN ( numeric_expression ) . Returns the positive (+1), zero (0), or negative (-1) sign of the specified expression.

Front 22

DATEADD()

Back 22

DATEADD (datepart , number , date )

Front 23

DATEDIFF()

Back 23

DATEDIFF ( datepart , startdate , enddate )

Front 24

DATEFROMPARTS ()

Back 24

DATEFROMPARTS ( year, month, day ) - Returns a date value for the specified year, month, and day.

SELECT DATEFROMPARTS ( 2010, 12, 31 ) --returns 2010-12-31

Front 25

EOMONTH ()

Back 25

EOMONTH ( start_date [, month_to_add ] ). Returns the last day of the month that contains the specified date, with an optional offset.

Front 26

WHERE EXISTS ( subquery ) ,

WHERE IN[/NOT IN(subquery),

WHERE (subquery) OR

Back 26

Returns true if subquery returns any rows. If the subquery doesn't contain any row, it's considered as false. it's different from IN operator which returns Unknown when the result of subquery is NULL(hence producing unexpected results).

OR can also be used in place of IN but it's bit slower and verbose.

Front 27

ANY, SOME or ALL operators.

Back 27

SOME is an ISO standard equivalent for ANY.

=ANY operator is equivalent to IN

but <>ANY is not same as NOT IN, but <>ALL is the same as NOT IN .

Front 28

spatial data in sql server?

Back 28

GEOMETRY is for planar spatial data (that is, data on a flat surface).

GEOGRAPHY is for terrestrial spatial data (that is, data on the (curved) surface of the Earth)

Front 29

Window analytic functions : CUME_DIST , Percentile ranking functions ?

Back 29

PERCENT_RANK and CUME_DIST. Calculates the relative rank of a row within a group of rows .Use PERCENT_RANK to evaluate the relative standing of a value within a query result set or partition. PERCENT_RANK is similar to the CUME_DIST function.

PERCENT_RANK( ) OVER ( [ partition_by_clause ] order_by_clause )

Front 30

Sql server 2008 vs 2012

Back 30

SQL Server 2008: The Maximum number concurrent connections to SQL Server 2008 is 32767, while it is unlimited in sql server 2012.

TRY_CONVERT() and FORMAT() functions are not available in SQL Server 2008 ORDER BY Clause does not have OFFSET / FETCH options as in SQL Server 2012 .

Sequence is not available in SQL Server 2008.

The SQL Server 2008 uses 27 bit precision for spatial calculations. The SQL Server 2012 uses 48 bit precision for spatial calculations.