Alter Encrypted Stored Procedure

How to Alter Encrypted Stored Procedure ?

Yesterday i wrote about how to write a Stored procedure in a encrypted manner. If Stored Procedure or User Defined Function are created WITH ENCRYPTION keyword then  is not possible to decrypt it using SQL Server commands. It is always advised to save a copy of the script used to create the SP or UDF on other media source than SQL Server.

Encrypted Stored Procedure

How  to write a encrypted Stored Procedure?

Sometimes we  need to write a stored procedure so that alternative user cannot see written text within  Stored Procedure.

Even you may be ineffective to see the text of the encrypted Stored Procedure in SQL Server  Activity Monitor moreover as by using Sp_helptext SpName methodology.

So what's the command to write encrypted stored procedure.

Let See

UNION VS UNION ALL

UNION VS UNION ALL

UNION

The UNION command is employed to pick connected info from 2 tables, very similar to the be a part of command. However, when using the UNION command all selected columns have to be compelled to be of constant knowledge kind. With UNION, solely distinct values are selected.


UNION ALL

The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values.

The distinction between Union and Union all is that Union all won't eliminate duplicate rows, instead it simply pulls all rows from all tables fitting your question specifics and combines them into a table.

A UNION statement effectively will a pick DISTINCT on the results set. If you recognize that each one the records came back are distinctive from your union, use UNION ALL instead, it offers faster results.

Stored Procedure and Transactions

Stored Procedure and Transactions

I simply overheard the subsequent statement – “I don't use Transactions in SQL as i exploit Stored Procedure“.

I simply realized that there are numerous misconceptions regarding this subject. Transactions has nothing to try and do with Stored Procedures. Let me demonstrate that with a straightforward example.

USE tempdb
GO
-- produce three check Tables
CREATE TABLE TABLE_1 (ID INT);
CREATE TABLE TABLE_2 (ID INT);
CREATE TABLE TABLE_3 (ID INT);
GO
-- produce SP
CREATE PROCEDURE TestSP
AS
INSERT INTO TABLE_1 (ID)
VALUES (1)
INSERT INTO TABLE_2 (ID)
VALUES ('a')
INSERT INTO TABLE_3 (ID)
VALUES (3)
GO
-- Execute SP
-- SP can error out
EXEC TestSP
GO
-- Check the Values in Table
SELECT *
FROM TABLE_1;
SELECT *
FROM TABLE_2;
SELECT *
FROM TABLE_3;
GO

Now, the most purpose is: If Stored Procedure is transactional then, it ought to roll back complete transactions when it encounters any errors. Well, that doesn't happen during this case, that proves that Stored Procedure doesn't solely give simply the transactional feature to a batch of T-SQL.

Unable to connect to Sql Server

If you are unable to connect your SQL Server from outside or from Asp.net web.config
then  you must check this setting.

open your Sql Server and right click on your connected server and go to properties.
click security from left hand side and ensure
Sql server and windows authentication mode is enable.

:))

Tips to Speed up SQL Query

Almost Every website data is stored in a database and served to visitors upon request. Databases are very fast, but there is lots of things that we need to  enhance  speed and make sure not to waste any server resources. In this article, I am suggesting you 10  tips to optimize and speed up your Sql query.

1. Do not Select column that we do not need.

    A very common practice is to use Select * from tablename.
    It's better to select column which you need in output.

2. Avoid using Cursor , use while loop instead of Cursor.

3. Avoid using Sql statement in a loop.It's takes a lot of resource.

4.Use Join instead of Subqueries.

   

SELECT a.id,
    (SELECT MAX(created)
    FROM posts
    WHERE book_id = a.id)
AS latest_post FROM books a

However subqueries are useful, they often can be replaced by a join, which is definitely faster to execute.

SELECT a.id, MAX(p.created) AS latest_post
FROM books 
a
INNER JOIN posts p
    ON (a.id = p.book_id)
GROUP BY a.id