SQL Server Query Plan Analysis: The 5 Culprits That Cause 95% of Your Performance Headaches

SQL Server Query Plan Analysis: The 5 Culprits That Cause 95% of Your Performance Headaches

Simple DAX on Power BI

DAX

Sum with applying a filter

Total for Non Duplicate = 
CALCULATE(
SUM('InvoiceDetails'[Total]),
'InvoiceDetails'[DuplicateInvoice] IN { FALSE }
)

Total for Non Duplicate 2 =
 SUMX( 
FILTER(InvoiceDetails,InvoiceDetails[DuplicateInvoice] = FALSE ),

InvoiceDetails[Total])


Handle null value in SUM/ COUNT


Exception Value = 
VAR total=SUM(InvoiceStatusView[EXCEPTION]) 

RETURN IF(ISBLANK(total),0,total)


Exception Count = 
VAR total=COUNT(InvoiceStatusView[EXCEPTION]) 

RETURN IF(ISBLANK(total),0,total)

All Tables Row Count

All Tables Row Count

SELECT T.name AS [TABLE NAME], 
       I.rows AS [ROWCOUNT] 
FROM   sys.tables AS T 
       INNER JOIN sys.sysindexes AS I 
               ON T.object_id = I.id 
                  AND I.indid < 2 
ORDER  BY I.rows DESC 

Time Dimension T-SQL

Generating Time Dimention

with cte1 as(

select 1 as a from( values(1),(2)) as A(a)

cross join ( values(1),(2)) as B(b)

),

cte2 as( select 1 as x from cte1 as a cross join cte1

),cte3 as( select 1 as x from cte2 as a cross join cte2

),cte4 as( select 1 as x from cte3 as a cross join cte3

),CTE as( select ROW_NUMBER() over(order by (select 1)) as x from cte4)

select x,CONVERT (date, getdate()-x ) from CTE

Hashing password with Salt

Hashing password with Salt

https://docs.microsoft.com/en-us/sql/t-sql/functions/hashbytes-transact-sql

Generate hash in Sql

SELECT HASHBYTES('SHA2_512', 'password-plaintext');

Generate hash in .Net

SHA512 hash = SHA512Managed.Create();

saltedPassword=password+Guid.NewGuid().ToString().ToUpper();

byte[] passwordHash = hash.ComputeHash(Encoding.UTF8.GetBytes(saltedPassword));

salt used is Guid

Points to Note to match HASH generated in SQL  and .Net 

1. Guid is SQL is in UPPER Case while .Net Guid is in lower case
2. string equivalent in .Net is varchar and NOT nvarchar, so use CAST(N'xxxxxx' as varchar(50)) in case its variable is of type nvarchar

can use below script to generate salt and hash

 

1. update dbo.Tenant set TenantPasscodeSalt=NEWID();
2. update dbo.Tenant set TenantPasscodeHash= HASHBYTES( 'SHA2_512',CAST(TenantPasscode as VARCHAR(50))+CAST(TenantPasscodeSalt as VARCHAR(50)))

byte[] passwordHash;

salt =Guid.NewGuid();

            using (SHA512 hash = SHA512Managed.Create())

            {

                string saltedPassword = password + salt.ToString().ToUpper();

                passwordHash = hash.ComputeHash(Encoding.UTF8.GetBytes(saltedPassword));

            }

SqlParameter paramTenantPasscodeHash = cmd.Parameters.Add("@TenantPasscodeHash", SqlDbType.VarBinary, 128);

                        paramTenantPasscodeHash.Value = passwordHash;

CODE GEN

using System.ComponentModel;

using System.IO;

using Microsoft.CSharp;

using System.CodeDom;

public class ClassGen<T>

    {

        public static void Generate()

        {

            Type type = typeof(T);

            PropertyDescriptorCollection properties =TypeDescriptor.GetProperties(typeof(T));

            string classtemplate =

                @" class {0} {{

                        {1}

                       }}

                 }}";

            string template = @"public {0}  {1} {{get; set;}}";

            string content = "";

            using (var provider = new CSharpCodeProvider())

            {

                foreach (PropertyDescriptor prop in properties)

                {

                    var typeRef = new CodeTypeReference(prop.PropertyType);

                    string typeName = provider.GetTypeOutput(typeRef);

                    content += string.Format(template, typeName, prop.Name) + "\n";

                }

            }

            Console.WriteLine(content);

            content = string.Format(classtemplate, type.Name, content);

            File.WriteAllText("../" + type.Name + ".cs", content);

}

ClassGen<A>.Generate();

ClassGen<B>.Generate();

SQL prime number

This is a solution for www.hackerrank.com
problem

Write a query to print all prime numbers less than or equal to . Print your result on a single line, and use the ampersand () character as your separator (instead of a space).

For example, the output for all prime numbers  would be:

2&3&5&7

declare @primes as varchar(max);
;
with strip as(
select  * from (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) as X(n)  --tens
),
seed as(
select ROW_NUMBER() over(order by (select null)) as n from strip as A
cross join strip as B
cross join strip as C
)
,cte_prime as(
select cte_numerator.n as n1,cte_denominator.n as n2,  
(case cte_numerator.n % cte_denominator.n when 0 then 0 else 1 end) as mod  --1 if has remainder, 0 if its divisable
from seed as cte_numerator 
inner join seed as cte_denominator on cte_numerator.n > cte_denominator.n
)
 
,
res as(
select n from seed
where 1= ALL(select mod from cte_prime where n1=seed.n and n2<>1  ) --always has remainder!!
and seed.n > 1
)
SELECT @Primes = COALESCE(@Primes +'&','') +CAST(n as varchar(3)) from res
print @primes

Azure Blob Storage

Install nuget pakages

1. WindowsAzure.Storage
2. Microsoft.WindowsAzure.ConfigurationManager

here is the extract from packages.config

<packages>
  <package id="Microsoft.WindowsAzure.ConfigurationManager" 

           version="3.2.3" targetFramework="net40" />
  <package id="WindowsAzure.Storage" 

           version="8.1.1" targetFramework="net40" />
</packages>

web.config

<add key="storage:account:name" value="d8view"/>
<add key="storage:account:key" value="7o1-----A=="/>
<add key="storage:account:connection" value="DefaultEndpointsProtocol=https;AccountName=d8view;AccountKey=7o1-----==" />
<add key="storage:account:PDFContainer" value="patients"/>

using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;

declare the CloudBlobClient!

 CloudBlobClient blobClient;

 public constructor()
 {
            // Retrieve storage account from connection string.
            CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
            ConfigurationManager.AppSettings["storage:account:connection"]);
            // Create the blob client.
            blobClient = storageAccount.CreateCloudBlobClient();
 }

Retrive the blob with policy url

public string GetAzureBlobReference(string strContainer, string prefix)
        {
            // Retrieve reference to a previously created container.
            CloudBlobContainer container 
                               = blobClient.GetContainerReference(strContainer);
            var readPolicy = new SharedAccessBlobPolicy()
            {
                Permissions = SharedAccessBlobPermissions.Read,
                SharedAccessExpiryTime = DateTime.UtcNow + TimeSpan.FromMinutes(5)
            };
            var docref = container.GetBlobReference(prefix);
            return  docref.Uri.AbsoluteUri 
                    + docref.GetSharedAccessSignature(readPolicy) };
        }

download as byte[]

//download document templete from Azurebolb
 public byte[] DownloadTemplate(string templateName)
 {
     byte[] templateInByteArray;
     try
     {
         //string containerName = "template";
         string fileName = templateName;
 
         var accountName = ConfigurationManager.AppSettings["storage:account:name"];
         var accountKey = ConfigurationManager.AppSettings["storage:account:key"];
         var containerName = ConfigurationManager.AppSettings["storage:WordTemplate:Location"];
 
         var storageAccount = new CloudStorageAccount(new StorageCredentials(accountName, accountKey), true);
         CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
 
         // Retrieve reference to a previously created container.
         CloudBlobContainer container = blobClient.GetContainerReference(containerName);
 
         // Retrieve reference to a blob
         CloudBlockBlob blockBlob = container.GetBlockBlobReference(fileName);
 
         using (MemoryStream ms = new MemoryStream())
         {
             blockBlob.DownloadToStream(ms);
             templateInByteArray = ms.ToArray();
         }
     }
     return templateInByteArray;
 }

upload blob

//update generated pdf Azurebolb
        public bool UploadPDFToAzureBlob(byte[] pdf, string fileName)
        {
            try
            {
                //string containerName = "generatedpdf";
 
                var accountName = ConfigurationManager.AppSettings["storage:account:name"];
                var accountKey = ConfigurationManager.AppSettings["storage:account:key"];
                var containerName = ConfigurationManager.AppSettings["storage:PDFExport:Location"];
 
                var storageAccount = new CloudStorageAccount(new StorageCredentials(accountName, accountKey), true);
 
                CloudBlobClient blobClient = storageAccount.CreateCloudBlobClient();
 
                CloudBlobContainer container = blobClient.GetContainerReference(containerName);
 
 
                // Retrieve reference to a blob named "myblob".
                CloudBlockBlob blockBlob = container.GetBlockBlobReference(fileName);
                blockBlob.Properties.ContentType = "application/pdf";
                // Create or overwrite the "myblob" blob with contents from a local file.
                using (var stream = new MemoryStream(pdf, writable: false))
                {
                    blockBlob.UploadFromStream(stream);
                }
            }

            return true;
        }

Delete blob

var accountName = ConfigurationManager.AppSettings["storage:account:name"];
var accountKey = ConfigurationManager.AppSettings["storage:account:key"];
var containerName = ConfigurationManager.AppSettings["storage:PDFExport:Location"];
 
var storageAccount = new CloudStorageAccount(new StorageCredentials(accountName, accountKey), true);
var deleteFolderPath = patientId+"/"+"Episode" + episodeId;
 
CloudBlobContainer container = storageAccount.CreateCloudBlobClient().GetContainerReference(containerName);
var folderDelete = container.GetDirectoryReference(deleteFolderPath);
//((CloudBlob)folderDelete).DeleteIfExists();