CopyPaste.Net: July 2014

declare @intA int = 2, @intB int = 5

select @intA / @intB * 100.00 -- = ?

select @intA * 100.00 / @intB -- = ?

Why are the results different?

In the first select - the cast to decimal number is done after the first operation - that it int:

2/5 = 0.4 = 0 (int!) * 100.00 = 0.00 (cast the zero to decimal)

In the first select - the cast to decimal is in the first operation, so the other operations will be decimal too:

2 * 100.00 = 200.00 (decimal!) / 5 = 40.00

The keyboard combination of CTRL+R show/hide the query results pane.

Sometimes, CTRL + R is not working in SSMS 2012.

Solution:

Go to the menu, Tools --> Options --> Environment --> Keyboard.

On the right, press the "Reset" button.

HASHBYTES('<hash algorithm>', <string text or column>)
http://msdn.microsoft.com/en-us/library/ms174415.aspx

<algorithm>::= MD2 | MD4 | MD5 | SHA | SHA1 | SHA2_256 | SHA2_512

Hashing vs Encryption:

Encryption is a two way process.

Hashing is unidirectional.

In order to find the source value of hashed text,

we need to hash all the optional strings (for example: hash a column data from a table), and to compare to the hashed string.

APPLY causes the right-side query to execute once per result in the left-side query.

CROSS only considers matching rows like INNER JOIN.

OUTER considers all rows in left-side query.

REVERT - Switches the execution context back to the caller of the last EXECUTE AS statement.

SELECT SUSER_NAME(), USER_NAME(); -- sa, dbo

EXECUTE AS USER = 'myUser';

SELECT SUSER_NAME(), USER_NAME(); -- myUser, myUser

REVERT;

SELECT SUSER_NAME(), USER_NAME(); -- sa, dbo

EXEC sp_MSforeachdb @command

EXEC sp_MSforeachdb 'USE [?]; .....'

The command should include USE [?]; - the ? will be replace by the DBs names.

For example:

DECLARE @command varchar(500)

SELECT @command = 'USE ? SELECT * FROM sys.objects WHERE xtype = ''U'''

EXEC sp_MSforeachdb @command

Labels