Encrypting a stored procedure will hide the definition of the procedure from users and it definition will not be displayed in the activity monitor.
The procedures can be encrypted by using “WITH ENCRYPTION” clause after the create statement.
ex: Read more…
Categories: Functions and DMVs, Procedures, Procedures, SQL Queries, SQL Server, Stored Procedures, T-SQL, T-SQL Tags: encryption, function, replication, SQL Server, stored procedure, T-SQL, Transact SQL
What are the advantages and disadvantages of Surrogate Key ?
Pros:
- Business Logic is not in the keys.
- Small 4-byte key (the surrogate key will most likely be an integer and SQL Server for example requires only 4 bytes to store it, if a bigint, then 8 bytes).
- Joins are very fast.
- No locking contentions because of unique constraint (this refers to the waits that get developed when two sessions are trying to insert the same unique business key) as the surrogates get generated by the DB and are cached – very scalable.
Cons:
- An additional index is needed. In SQL Server, the PK constraint will always creates a unique index, in Oracle, if an index already exists, PK creation will use that index for uniqueness enforcement (not a con in Oracle).
- Cannot be used as a search key.
- If it is database controlled, for products that support multiple databases, different implementations are needed, example: identity in SS2k, before triggers and sequences in Oracle, identity/sequence in DB2 UDB.
- Always requires a join when browsing the child table(s).
Read more…