WITH Encryption clause in SQL Server

This is quite an old feature, however, a very useful one in SQL Server.

Applies to: SQL Server 2005 and above.

Why do we need this or its Usages?

“WITH ENCRYPTION” clause can be used for procedures and function in SQL Server.
I personally used this clause in many places, where I wanted to hide my procedure definition. When we need to manage multiple environments and different people have access to environments, its possible, the procedure definition gets changed by others. So, if we need to have to keep the same definition across all environments, we need to get the definition encrypted which would prevent the users to change the definition.

Usually, DBAs will use this option to prevent any changes or hide the logic implemented for certain purposes.Sometimes business critical logic are also encrypted using this option to hide from others.

How to Implement?

The implementation is very simple to provide the clause at the time of creation or alter.


CREATE OR ALTER procedure Test_Encryption
WITH ENCRYPTION
AS
Begin
	
	Select 'This is an encrypted procedure.'
		
End

Let us try to get the definition using sp_helptext, we will be getting a message “The text for object ‘Test_Encryption’ is encrypted.”

If we try to get the definition through SSMS, we will get the below error message.


And you can see a lock symbol to identify the encrypted procedures.

Gotchas:
1. Once the procedure is encrypted, you will not be ale to see the execution plan for the procedure.
2. It is always important to save the definition safe, you will never be able to retrieve the procedure definition once its encrypted.
3. CLR procedures and CLR User Defined Functions cannot be encrypted.
4. Encrypted procedures or functions cannot be replicated.

Advertisements

Python Dictionaries

A dictionary is a set of unordered key, value pairs. In a dictionary, the keys must be unique and they are stored in an unordered manner.

In this tutorial you will learn the basics of how to use the Python dictionary.

Creating a Dictionary:

dict1

Accessing Items:
You can access the items of a dictionary by referring to its key name, inside square brackets:

dict2

Updating Dictionary:
You can update a dictionary by adding a new entry or a key-value pair, modifying an existing entry, or deleting an existing entry as shown below in the simple example −

dict3

Loop Through a Dictionary:
You can loop through a dictionary by using a for loop.When looping through a dictionary, the return value are the keys of the dictionary, but there are methods to return the values as well.

dict4.JPG

Check if Exists:

You can test the presence of a key using ‘in’ or ‘not in’

dict5

Restrictions on Dictionary Keys:
Almost any type of value can be used as a dictionary key in Python. As an example,  integer, float, and Boolean objects are used as keys:

dict6

However, there are a couple restrictions that dictionary keys must abide by.

First, a given key can appear in a dictionary only once. Duplicate keys are not allowed. A dictionary maps each key to a corresponding value, so it doesn’t make sense to map a particular key more than once.
You could see below that when you assign a value to an already existing dictionary key, it does not add the key a second time, but replaces the existing value:

dict7

Restrictions on Dictionary Values:
By contrast, there are no restrictions on dictionary values. Literally none at all. A dictionary value can be any type of object Python supports, including mutable types like lists and dictionaries, and user-defined objects.There is also no restriction against a particular value appearing in a dictionary multiple times.

Hope you have enjoyed the post. Keep reading

How to identify table rows and size in a database in SQL Server

Problem Statement:

At times, we may need to understand the size of the tables in a database for various reasons. I often query these details to understand the growth of the data and clean up data for lower environments.

Code:
Here is a simple script to understand the rows, size of the table in a database.
You can further make changes to filter for any tables/group the results based on the indexes/partition etc.


SELECT  sc.name + '.' + t.NAME AS TableName,  
        SUM(p.[Rows]) NumerOfRecords,
        -- p.partition_number, i.index_id,i.name,
        ( SUM(a.total_pages) * 8 ) / 1024 AS TotalReservedSpaceMB, 
        ( SUM(a.used_pages) * 8 ) / 1024 AS UsedDataSpaceMB,  
        ( SUM(a.data_pages) * 8 ) / 1024 AS FreeUnusedSpaceMB  
FROM    sys.tables t  
        INNER JOIN sys.schemas sc ON sc.schema_id = t.schema_id  
        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  
WHERE   t.type_desc = 'USER_TABLE'  
        -- Replace with valid table name if you want to filter for a table
        --AND t.NAME='YourTableName' 
GROUP BY sc.name + '.' + t.NAME,  
        i.[object_id]
        --,i.index_id, i.name, p.[Rows], p.partition_number
ORDER BY ( SUM(a.total_pages) * 8 ) / 1024 DESC  

Find Max and Min value of dates in a row in SQL Server

Problem Statement:
How to identify the Max and Min date value in a row in SQL Server?

Code:
Here is a solution to identify Max and Min date value with the help of CROSS APPLY in SQL Server.


create Table FindMaxMindate
(
    ID int, 
    Firstdate datetime,
    Seconddate datetime, 
    ThirdDate datetime
)

Insert into FindMaxMindate 
Values
(1,'2018-01-26 19:14:07.587','2018-04-26 19:14:07.587','2018-12-26 19:14:07.587'),
(2,'2018-12-26 19:14:07.587','2018-01-26 19:14:07.587','2018-03-26 19:14:07.587')

Select ID,Min(Dates) MinDate,Max(Dates) MaxDate
From FindMaxMindate A
Cross apply (values (Firstdate),(SecondDate),(ThirdDate))B (Dates)
Group by ID

Drop Table FindMaxMinDate

How to identify Process Name of a Process Id in SQL Server

We had an immediate requirement to identify process names of process ids in SQL Server. The first reaction to the requirement was to refer the program name. However, program name is customized through application, it was not the expected result.

I tried multiple ways to identify a simple solution within SQL Server, but failed to identify a straight forward solution.(You can share if you have any simpler solution.)

With the help of Powershell script, developed a small script to get the process name as below. Hope, this will be helpful for you in the similar situation.

Code:

create Table #TblProcessName(ProcessName varchar(max))

Insert into #TblProcessName
Exec xp_cmdshell 'powershell.exe "Get-Process | Select ProcessName,Id | % {$_.ProcessName + " "- + $_.Id}"' 

;With cte as
(
Select Substring(Processname,0,charindex('-',Processname)) as ProcessName, Substring(Processname,charindex('-',Processname)+1 , Len(processname)) as ProcessID 
From #TblProcessName A
)
Select * From cte A
Inner join sys.sysprocesses B on A.ProcessID = B.hostprocess

DROP Table #TblProcessName

Output:

Tuple in Python

Tuples in Python are immutable sequences of random objects. Once created, the objects within them cannot be replaced or removed,and new elements cannot be added.

Tuples have a similar syntax to lists except that they are delimited by parentheses rather than square brackets.Here’s a literal tuple containing a string, a float, and an integer.
We can access the elements of a tuple by zero-based index using square brackets, and we can determine the number of elements in the tuple using the built-in len function. We can iterate over tuples using the for loop, and we can concatenate tuples using the plus operator.

ex1

Sometimes a single element tuple is required. To write this, we can’t just use a simple number in parentheses. This is because Python pauses that as an integer enclosed in the president’s controlling parentheses of a math expression. To create a single element tuple, we make use of the trailing comma separator, which we’re allowed to use when specifying literal tuples, lists, and dictionaries.

A single element with a trailing comma is passed as a single element tuple. This leaves us with the problem of how to specify an empty tuple. In actuality the answer is simple.
We just use empty parentheses. here is the demo:

ex2

In many cases, the parentheses of literal tuples may be omitted. This feature is often used when returning multiple values from a function.

Here we make a function to return the minimum and maximum values of a series, the hard work being done by the two built-in functions min and max. Returning multiple values as tuple is often used in conjunction with a wonderful feature of Python called tuple unpacking.Tuple unpacking is a destructuring operation, which allows us to unpack data structures into named references. For example, we can assign the result of our minmax function to two new references like this.

ex3

Tuple unpacking works with arbitrarily nested tuples, although not with other data structures. Should you need to create a tuple from an existing collection object such as a list, you can use the tuple constructor, here also shown for strings.

ex4

Hope you have enjoyed the post. Please share your comments

List in python

Python lists such as those returned by the string’s split method are sequences of objects. Unlike strings, lists are mutable in so far as the elements within them can be replaced or removed, and new elements can be inserted or appended.
Literal lists are delimited by square brackets, and the items within the list separated by commas. Here is a list of three numbers and a list of three strings. We can retrieve elements by using square brackets with a zero-based index, and we can replace elements by assigning to a specific element. See how lists can be heterogeneous with respect to the types of the objects. We now have a list containing a string, an integer, and another string.

ex1

It’s often useful to create an empty list, which we can do using empty square brackets. We can modify the list in other ways.Let’s add some floats to the end of the list using the append method.

ex2

See also how we’re allowed to use an additional comma after the last element, an important maintainability feature.

ex3

Slicing is a form of extended indexing which allows us to refer to portions of a list.To use it, we pass the start and stop indices of a half-open range separated by a colon as the square brackets index argument.

Here we slice three words from the list by passing the start index 1 and the stop index 4. This facility can be combined with negative indexing. For example, to take all the elements except the first and last, slice between 1 and -1. Both the start and stop indices are optional. To slice all elements from the second to the end of the list, supply only 2: as the argument to the index operator.

ex4

we can copy one list object to another list object in the following ways:

ex6

You must be aware, that all of these techniques perform a shallow copy. That is, they create a new list containing the same object references as the source list, but don’t copy the referred to objects.

List repetition:

As for strings and tuples, lists support repetition using the multiplication operator. It’s simple enough to use. Here we repeat a list containing the integers 21 and 37 four times

ex7

To find an element in a list, use the index method passing the object you’re searching for. The elements are compared for equivalence or value equality until the one you’re looking for is found and its index returned. Here we create a list W containing few words , Searching for fox using the index method returns the integer 3, which of course allows us to get ahold of that element. If you search for a value that isn’t present like unicorn, you will receive a ValueError. Another means of searching is to count matching elements using the count method. Here we count occurrences of the word the. If you just want to test for membership, you can use the in operator or for nonmembership using the not in operator.

ex8

Elements from a list can be removed using del or remove as shown below.

ex9

 Before we move on from lists, let’s look at two operations which rearrange the elements in place, reversing and sorting. A list can be reversed in place simply by calling its reverse() method. Similarly, a list can be sorted in place using the sort() method. The sort() method accepts two optional arguments, key and reverse. The latter is self-explanatory, and when set to true gives a descending sort

 
ex10
 
Hope you have enjoyed the post.Please share your comments