Rankspike: Your Web Development's one-stop-shop

How to check table size in SQL

Sometimes when you’re having an issue with database timing out or slowdown, it’s always worth to check and investigate the abnormal growth of the table size in your database. Here’s a simple script that may do just that.

USE DatabaseName
 
SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.TABLES t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.Rows
ORDER BY 
    t.Name

 

Hope this helps someone who is currently investigating issues related to the Database.

How to create Rijndael key in ASP.NET c#

The Advanced Encryption Standard (AES) is a specification for the encryption of electronic data established by the U.S. National Institute of Standards and Technology (NIST) in 2001.[4]

AES is based on the Rijndael cipher[5] developed by two Belgian cryptographers, Joan Daemen andVincent Rijmen, who submitted a proposal to NIST during the AES selection process.[6] Rijndael is a family of ciphers with different key and block sizes.

For AES, NIST selected three members of the Rijndael family, each with a block size of 128 bits, but three different key lengths: 128, 192 and 256 bits.

AES has been adopted by the U.S. government and is now used worldwide. It supersedes the Data Encryption Standard (DES),[7] which was published in 1977. The algorithm described by AES is asymmetric-key algorithm, meaning the same key is used for both encrypting and decrypting the data.

In the United States, AES was announced by the NIST as U.S. FIPS PUB 197 (FIPS 197) on November 26, 2001.[4] This announcement followed a five-year standardization process in which fifteen competing designs were presented and evaluated, before the Rijndael cipher was selected as the most suitable (see Advanced Encryption Standard process for more details).

Here’s how to create one on C# can be used as plug and play.

using System.IO;
using System.Security.Cryptography;
 
private static readonly byte[] SALT = new byte[] { 0x26, 0xdc, 0xff, 0x00, 0xad, 0xed, 0x7a, 0xee, 0xc5, 0xfe, 0x07, 0xaf, 0x4d, 0x08, 0x22, 0x3c };
 
public static byte[] Encrypt(byte[] plain, string password)
{
    MemoryStream memoryStream;
    CryptoStream cryptoStream;
    Rijndael rijndael = Rijndael.Create();
    Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(password, SALT);
    rijndael.Key = pdb.GetBytes(32);
    rijndael.IV = pdb.GetBytes(16);
    memoryStream = new MemoryStream();
    cryptoStream = new CryptoStream(memoryStream, rijndael.CreateEncryptor(), CryptoStreamMode.Write);
    cryptoStream.Write(plain, 0, plain.Length);
    cryptoStream.Close();
    return memoryStream.ToArray();
}
 
public static byte[] Decrypt(byte[] cipher, string password)
{
    MemoryStream memoryStream;
    CryptoStream cryptoStream;
    Rijndael rijndael = Rijndael.Create();
    Rfc2898DeriveBytes pdb = new Rfc2898DeriveBytes(password, SALT);
    rijndael.Key = pdb.GetBytes(32);
    rijndael.IV = pdb.GetBytes(16);
    memoryStream = new MemoryStream();
    cryptoStream = new CryptoStream(memoryStream, rijndael.CreateDecryptor(), CryptoStreamMode.Write);
    cryptoStream.Write(cipher, 0, cipher.Length);
    cryptoStream.Close();
    return memoryStream.ToArray();
}

How-to randomize in SQL

Few weeks ago I was task to populate dummy data into a table, I have to come up with a price and randomize the ID from 80-90, I have to populate this table with 25000 records!

So for solution, I turned to Rand(), an SQL function that randomizes integer, for quick reference here’s the simple syntax:

Rand [Seed]

a quick example would be :

Then I came up with my own

 

DECLARE @maxValue tinyint = 80
, @minValue tinyint = 90
, @randomId int
, @hid INT
, @amtadd INT;
 
SET @hid=0;
SET @amtadd=20;
WHILE @hid < 2000
BEGIN
 
SET @randomId = Cast(((@maxValue + 1) - @minValue) 
* Rand() + @minValue AS tinyint);
 
INSERT INTO tblDetail (PaymentId,PaymentDate, PaymentAmount,SeqNumber,IsActive,CreateDate,UpdateDate,UpdateUser)
VALUES (@randomId,'2013-10-17',@amtadd,3,1,getdate(),getdate(),'Alf')
 
SET @hid = @hid + 1;
SET @amtadd = Cast(((380 + 1) - 120) 
* Rand() + 120 AS int);
END

 

And there it is!