Stored Procedure QUOTED_IDENTIFIER and ANSI_NULLS

SET QUOTED_IDENTIFIER (Transact-SQL)

Causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Identifiers delimited by double quotation marks can be either Transact-SQL reserved keywords or can contain characters not generally allowed by the Transact-SQL syntax rules for identifiers.

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. Literals can be delimited by either single or double quotation marks.

Reference: here

SET ANSI_NULLS

Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<>) comparison operators when used with null values.

When SET ANSI_NULLS is ON, a SELECT statement using WHERE column_name = NULL returns zero rows even if there are null values in column_name. A SELECT statement using WHERE column_name <> NULL returns zero rows even if there are nonnull values in column_name.

When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the SQL-92 standard. A SELECT statement using WHERE column_name = NULL returns the rows with null values in column_name. A SELECT statement using WHERE column_name <> NULL returns the rows with nonnull values in the column. In addition, a SELECT statement using WHERE column_name <> XYZ_value returns all rows that are not XYZ value and that are not NULL.

Reference: here

Send Ctrl+Alt+Del to Remote Desktop

In order to change the account password in Windows Server via Remote Desktop, we can use Ctrl+Alt+Del.

How to send this command via Remote Desktop?
Press Ctrl+Alt+END

SQL Store Procedure CURSOR

I just learnt stored procedure with cursor pointer.

Here's an example:

BEGIN
    DECLARE @MyID AS BIGINT
    DECLARE MyIDCursor CURSOR FOR
        SELECT ID FROM MyTable
    OPEN MyIDCursor

    FETCH NEXT FROM MyIDCursor INTO @MyID
    WHILE @@FETCH_STATUS=0 -- this is necessary to loop
    BEGIN
        -- print it out
        print '@MyID'
        print @MyID
        -- to assign to the next value, without this, it will loops forever!
        FETCH NEXT FROM MyIDCursor INTO @MyID 
    END

    CLOSE MyIDCursor
    DEALLOCATE MyIDCursor
END

Actually this method is not recommended, look here.

MSSQL Multiplication and Division Limitation

There was a case at work, the number is not precise enough for multiplication of data type decimal (38,10).

The result will give up to 6 decimal place precision and the rest 4 decimal place follows it will be truncated, replaced with 0.

Here's the sample in SQL Query Analyzer:

DECLARE @CrossRate as decimal(38,10)
DECLARE @ExchRate1 as decimal(38,10)
DECLARE @ExchRate2 as decimal(38,10)

SET @ExchRate1 = 1.3159643631
SET @ExchRate2 = 1.3663000000

SET @CrossRate = @ExchRate1/@ExchRate2
print '@ExchRate1: '
print @ExchRate1
print '@ExchRate2: '
print @ExchRate2
print '@CrossRate: '
print @CrossRate

And here's the result:

@ExchRate1:
1.3159643631
@ExchRate2:
1.3663000000
@CrossRate:
0.9631590000

However for data type decimal (18,10), multiplication result of the two data types will give the same precision - decimal(18,10).

The CrossRate result of decimal (18,10) is
0.9631591620

I am still not yet known how to solve this, as the requirement that we have is for data type decimal (38,10) instead of decimal (18,10). If you have any idea on how to solve this, please let me know. Thanks.

Some updates
My colleague just said that we can use float.
However there are some things to take note:

Float is Approximate-number data type, which means that not all values in the data type range can be represented exactly.

Decimal/Numeric is Fixed-Precision data type, which means that all the values in the data type reane can be represented exactly with precision and scale.

Converting from Decimal or Numeric to float can cause some loss of precision.

Reference: here