[T-SQL] What is – DML, DDL, DCL and TCL – Introduction and Examples

DML

DML is abbreviation of Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.

Examples: SELECT, UPDATE, INSERT statements

DDL

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

Examples: CREATE, ALTER, DROP statements

DCL

DCL is abbreviation of Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.

Examples: GRANT, REVOKE statements

TCL

TCL is abbreviation of Transactional Control Language. It is used to manage different transactions occurring within a database.

Examples: COMMIT, ROLLBACK statements

[My-Exp] Job Description for DBA/DB Developer

This JD is copied from one of the SQL Server Experts:

Job Description of Database Administrator (DBA) or Database Developer

Develop standards and guidelines to guide the use and acquisition of software and to protect vulnerable information.

Modify existing databases and database management systems or direct programmers and analysts to make changes.

Test programs or databases, correct errors and make necessary modifications.

Plan, coordinate and implement security measures to safeguard information in computer files against accidental or unauthorized damage, modification or disclosure.

Approve, schedule, plan, and supervise the installation and testing of new products and improvements to computer systems, such as the installation of new databases.

Train users and answer questions.

Establish and calculate optimum values for database parameters, using manuals and calculator.

Specify users and user access levels for each segment of database.

Develop data model describing data elements and how they are used, following procedures and using pen, template or computer software.

Develop methods for integrating different products so they work properly together, such as customizing commercial databases to fit specific needs.

Review project requests describing database user needs to estimate time and cost required to accomplish project.

Review procedures in database management system manuals for making changes to database.

Work as part of a project team to coordinate database development and determine project scope and limitations.

Select and enter codes to monitor database performance and to create production database.

Identify and evaluate industry trends in database systems to serve as a source of information and advice for upper management.

Write and code logical and physical database descriptions and specify identifiers of database to management system or direct others in coding descriptions.

Review workflow charts developed by programmer analyst to understand tasks computer will perform, such as updating records.

Revise company definition of data as defined in data dictionary.

[T-SQL] Create and Encrypted Stored Procedure

An answer to a very simple question , how to create a stored procedure any problem if we create it.

Here is the script

USE AdventureWorks
GO
-- Create Encyrpted SP
CREATE PROCEDURE uspEnc
WITH ENCRYPTION
AS
SELECT *
FROM Sales.SalesOrderDetail
UNION
SELECT *
FROM Sales.SalesOrderDetail
GO
-- Execute SP
EXEC uspEnc
GO

Question: if any stored procedure is encrypted can we see its definition in Activity Monitor.
Answer: No

Question: Any tool to decrypt the SP
Answer: SQL Decryptor, Decrypt SQL

Question: Should a DBA recommend it
Answer: Encrypted stored procedures are horrible. You can’t see what queries are executed, you don’t have a clue what the execution plan is. So if users are complaining about the slowness of the system, and everything is encrypted… then it will not be possible to look into it.

[T-SQL] Get Running Query from the Sessions

If you want to get the query running of one of the session then you can get from the query below , just answering that if you want to know the running sessions then you can use sp_who and sp_who2.

Before executing this statement please change the option from result to grid to result to text. For this you can use shortcut CTRL+T.

Here is the script, 55 can be replaced from your desired session id.

DECLARE @sqltext VARBINARY(128)
SELECT @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = 55
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
GO

[T-SQL] Get Numbers from Alpha Numeric Value

Here is the procedure i just saw from one of the sql expert blog that will rreturn Numbers from Alpha Numeric Values.

CREATE PROCEDURE [dbo].[GetNumbers]
@alpha VARCHAR(50),
@decimal DECIMAL(14, 5) OUTPUT
AS BEGIN
SET NOCOUNT ON;
DECLARE @ErrorMsg VARCHAR(50)
DECLARE @Pos INT
DECLARE @CommaPos INT
DECLARE @ZeroExists INT
DECLARE @alphaReverse VARCHAR(50)
DECLARE @NumPos INT
DECLARE @Len INT
-- 1 Reverse the alpha in order to get the last position of a numeric value
SET @alphaReverse = REVERSE(@alpha)
-- 2 Get the last position of a numeric figure
SET @NumPos = PATINDEX('%[0-9]%', @alphaReverse)
-- 3 Get the lenght of the string
SET @Len = LEN(@alpha)
-- 4 Add a comma after the numeric data in case it's no decimal number
SET @alpha = SUBSTRING(@alpha, 1, (@Len - @NumPos + 1))
+ ','
+ SUBSTRING(@alpha, (@Len - @NumPos + 2), 50)
-- Check if there is a zero (0) in the @alpha, then we later set the @decimal to 0
-- if it's 0 after the handling, else we set @decimal to NULL
-- If 0 no match, else there is a match
SET @ZeroExists = CHARINDEX ( '0' , @alpha ,1 )
-- Find position of , (comma)
SET @CommaPos = 1
SET @CommaPos = PATINDEX('%,%', @alpha)
IF (@CommaPos = '') BEGIN
SET @CommaPos = 20
END
SET @Pos = PATINDEX('%[^0-9]%',@alpha)
-- Replaces any aplha with '0' since we otherwice can't keep track of where the decimal
-- should be put in. We assume the numeric number has no aplhe inside. The regular way
-- to solve this is to replace with ”, but then we miss the way to find the place to
-- put in the decimal.
WHILE (@Pos > 0) BEGIN
SET @alpha = STUFF(@alpha, @pos, 1, '0')
SET @Pos = PATINDEX('%[^0-9]%',@alpha)
END
IF (@alpha IS NOT NULL AND @alpha != '') BEGIN
SET @decimal = CONVERT(DECIMAL(14, 5), SUBSTRING(@alpha, 1, (@CommaPos - 1))
+ '.'
+ SUBSTRING(@alpha, (@CommaPos + 1), 20))
END
-- Since we in this case don't want to set 0 if where is no numeric value, we set NULL to be safe
IF (@decimal = 0 AND @ZeroExists = 0) BEGIN
SET @decimal = NULL
END
END
GO

If you run above SP as shown below it will work
DECLARE @myVal DECIMAL(14,5)
EXEC [GetNumbers] 'ABC455,88ghf', @myVal OUTPUT
SELECT @myVal RValue

RValue
---------------------------------------
455.88000

[My-Exp] Query Optimized

Few days back i blogged that my manager said to me to optimize an sp , so here is the story for this:

The sp was not running for one day data even in 45 mins, but now its running in 4 mins for one month data. So here are the tips of what i did.

1. Added "with recompile" in sp.
2. Added "SET NOCOUNT ON;  "
3. Applied Parameter sniffing
4. Created two Non clustered indexes on one of the table.

[T-SQL] Clear Cache and Buffer of Stored Procedure

Use DBCC FREEPROCCACHE to clear the procedure cache. Freeing the procedure cache would cause, for example, an ad-hoc SQL statement to be recompiled rather than reused from the cache. If observing through SQL Profiler, one can watch the Cache Remove events occur as DBCC FREEPROCCACHE goes to work. DBCC FREEPROCCACHE will invalidate all stored procedure plans that the optimizer has cached in memory and force SQL Server to compile new plans the next time those procedures are run.

Use DBCC DROPCLEANBUFFERS to test queries with a cold buffer cache without shutting down and restarting the server. DBCC DROPCLEANBUFFERS serves to empty the data cache. Any data loaded into the buffer cache due to the prior execution of a query is removed.

DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS