Category: DBMS

Database Management Systems

Use CRYPT_GEN_RANDOM() to Create a Cryptographic, Random Number in SQL Server

Posted on by Ian

In SQL Server, you can use the CRYPT_GEN_RANDOM() function to return a cryptographic, randomly-generated number. The number is generated by the Cryptographic Application Programming Interface (CAPI).

CAPI is a Microsoft Windows platform specific application programming interface included with Microsoft Windows operating systems that provides services to enable developers to secure Windows-based applications using cryptography.

The CRYPT_GEN_RANDOM() function accepts two arguments: the length (required), and a seed (optional).

The return value is varbinary(8000).

Continue reading →

Make an Object DIE in SQL Server

Posted on by Ian

In SQL Server, if you try to drop an object that doesn’t exist, you’ll get an error. If you don’t want to get an error, you’ll need to add some extra code to check for the existence of the object.

Prior to SQL Server 2016, you needed to add an IF statement that queried the relevant system objects to find out whether or not the object existed.

From SQL Server 2016, you can now use a new, cleaner method to check if an object exists. We’ll call it DROP IF EXISTS (otherwise known as “DIE”).

Continue reading →

SET TEXTSIZE Not Working in SQL Server? Check This.

Posted on by Ian

Have you used SET TEXTSIZE to limit the data returned by a query, but found that it just doesn’t seem to work? You’re finding that your SELECT queries continue to return the full amount of text, regardless of the TEXTSIZE value?

Read on. You could be making this one obvious mistake.

When I first encountered the TEXTSIZE option, I decided to run a quick test. But it didn’t work. I tried several times with various TEXTSIZE values, but nothing seemed to work.

“Why isn’t it working?” I wondered.

But then it clicked. I re-read the documentation and realised that I wasn’t using the correct string size for my data types.

Continue reading →