Labels

admin (1) aix (1) alert (1) always-on (2) Architecture (1) aws (3) Azure (1) backup (3) BI-DWH (10) Binary (3) Boolean (1) C# (1) cache (1) casting (3) cdc (1) certificate (1) checks (1) cloud (3) cluster (1) cmd (7) collation (1) columns (1) compilation (1) configurations (7) Connection-String (2) connections (6) constraint (6) copypaste (2) cpu (2) csv (3) CTE (1) data-types (1) datetime (23) db (547) DB2 (1) deadlock (2) Denali (7) device (6) dotNet (5) dynamicSQL (11) email (5) encoding (1) encryption (4) errors (124) excel (1) ExecutionPlan (10) extended events (1) files (7) FIPS (1) foreign key (1) fragmentation (1) functions (1) GCP (2) gMSA (2) google (2) HADR (1) hashing (3) in-memory (1) index (3) indexedViews (2) insert (3) install (10) IO (1) isql (6) javascript (1) jobs (11) join (2) LDAP (2) LinkedServers (8) Linux (15) log (6) login (1) maintenance (3) mariadb (1) memory (4) merge (3) monitoring (4) MSA (2) mssql (444) mssql2005 (5) mssql2008R2 (20) mssql2012 (2) mysql (36) MySQL Shell (5) network (1) NoSQL (1) null (2) numeric (9) object-oriented (1) offline (1) openssl (1) Operating System (4) oracle (7) ORDBMS (1) ordering (2) Outer Apply (1) Outlook (1) page (1) parameters (2) partition (1) password (1) Performance (103) permissions (10) pivot (3) PLE (1) port (4) PostgreSQL (14) profiler (1) RDS (3) read (1) Replication (12) restore (4) root (1) RPO (1) RTO (1) SAP ASE (48) SAP RS (20) SCC (4) scema (1) script (8) security (10) segment (1) server (1) service broker (2) services (4) settings (75) SQL (74) SSAS (1) SSIS (19) SSL (8) SSMS (4) SSRS (6) storage (1) String (35) sybase (57) telnet (2) tempdb (1) Theory (2) tips (120) tools (3) training (1) transaction (6) trigger (2) Tuple (2) TVP (1) unix (8) users (3) vb.net (4) versioning (1) windows (14) xml (10) XSD (1) zip (1)

Query logic flow

(7) select (8) distinct
(10) <TOP> <select list>
(1) FROM <left table>
(3) <join type> JOIN <right table>
(2) ON <join condition>
(4) WHERE <where condition>
(5) GROUP BY <group list>
(6) HAVING <having condition>
(9) ORDER BY <order list>

  1. from table
  2. join condition
  3. join
  4. where conditions
  5. group by
  6. having conditions
  7. select
  8. distinct on the select
  9. order by
  10. top x rows (of the select)

Comparing DB objects content

This SP compare betwwen 2 objects content in the DB.

Object can be Stored procedure, function, view or trigger.

The main output is whether the objects are the same, if not what percentage of the differences and what are the differences and whether calls to the object to SP or other functions.

Can be used to compare objects, finding differences between two versions or two objects, identifying errors, etc..

Output example:


SP code:

CREATE PROCEDURE [dbo].[Itai_CompareObjectsContent]
@ObjName1 NVARCHAR(500), 
@Schema1 NVARCHAR(50) = 'dbo', 
@ObjName2 NVARCHAR(500), 
@Schema2 NVARCHAR(50) = 'dbo',
@ShowSelect int = 0, -- show the objects content
@ShowDiffSelect int = 1 -- show the differences betwwen the objects
AS

SET NOCOUNT ON

DECLARE @Obj1 nvarchar(max)
SET @Obj1 = @Schema1 + N'.' + @ObjName1

DECLARE @Obj2 nvarchar(max)
SET @Obj2 = @Schema2 + N'.' + @ObjName2

-------------------------------------------------------------------
-- insert lines of object #1 into temp table:
CREATE TABLE #obj1 (
CodeLineTxt nvarchar(max), 
CodeLineNum int not null identity(1,1)
)
INSERT INTO #obj1 (CodeLineTxt) 
EXEC sp_HelpText @Obj1

-- insert lines of object #2 into temp table:
CREATE TABLE #obj2 (
CodeLineTxt nvarchar(max), 
CodeLineNum int not null identity(1,1)
)
INSERT INTO #obj2 (CodeLineTxt) 
EXEC sp_HelpText @Obj2

-- show objects content
IF @ShowSelect = 1
BEGIN
select CodeLineTxt, CodeLineNum from #obj1
select CodeLineTxt, CodeLineNum from #obj2
END

-------------------------------------------------------------------
-- replace spaces and empty lines 
-- in order not to get differences of them
--REPLACE spaces
UPDATE #obj1 SET CodeLineTxt = REPLACE(CodeLineTxt, N' ', N'')
UPDATE #obj2 SET CodeLineTxt = REPLACE(CodeLineTxt, N' ', N'')
--REPLACE char(9) (horizontal tab)
UPDATE #obj1 SET CodeLineTxt = REPLACE(CodeLineTxt, char(9), N'') 
UPDATE #obj2 SET CodeLineTxt = REPLACE(CodeLineTxt, char(9), N'')
--REPLACE char(10) (new line)
UPDATE #obj1 SET CodeLineTxt = REPLACE(CodeLineTxt, char(10), N'')
UPDATE #obj2 SET CodeLineTxt = REPLACE(CodeLineTxt, char(10), N'')
--REPLACE char(13) (carriage return)
UPDATE #obj1 SET CodeLineTxt = REPLACE(CodeLineTxt, char(13), N'')
UPDATE #obj2 SET CodeLineTxt = REPLACE(CodeLineTxt, char(13), N'')
--REPLACE char(32) (space)
UPDATE #obj1 SET CodeLineTxt = REPLACE(CodeLineTxt, char(32), N'')
UPDATE #obj2 SET CodeLineTxt = REPLACE(CodeLineTxt, char(32), N'')
-- delete empty rows
DELETE FROM #obj1 WHERE CodeLineTxt = N''
DELETE FROM #obj2 WHERE CodeLineTxt = N''

-------------------------------------------------------------------
-- replace schemas to dbo
-- in order not to get differences of schemas names
if @Schema1 <> 'dbo'
UPDATE #obj1 SET CodeLineTxt = REPLACE(CodeLineTxt, '[' + @Schema1 + ']', '[dbo]')
if @Schema2 <> 'dbo'
UPDATE #obj2 SET CodeLineTxt = REPLACE(CodeLineTxt, '[' + @Schema2 + ']', '[dbo]')

-------------------------------------------------------------------
-- raw count of #lines in object #1:
DECLARE @cnt1 int
SELECT @cnt1 = COUNT(*) FROM #obj1

-- count of #lines in object #1 that match in object #2:
DECLARE @cnt1a int
SELECT @cnt1a = COUNT(*) 
FROM #obj1 T1 
WHERE EXISTS (SELECT NULL FROM #obj2 T2 WHERE T2.CodeLineTxt = T1.CodeLineTxt)

-- raw count of #lines in object #2:
DECLARE @cnt2 int
SELECT @cnt2 = COUNT(*) FROM #obj2

-- count of #lines in object #2 that match in object #1:
DECLARE @cnt2a int
SELECT @cnt2a = COUNT(*) 
FROM #obj2 T1 
WHERE EXISTS (SELECT NULL FROM #obj1 T2 WHERE T2.CodeLineTxt = T1.CodeLineTxt)

-------------------------------------------------------------------
-- show differences betwwen the objects
IF @ShowDiffSelect = 1
BEGIN
SELECT tt1.ROW_NUMBER_ as ROW_NUM, 
tt1.CodeLineNum as obj1_CodeLineNum, 
tt1.CodeLineTxt as obj1_CodeLineTxt, 
tt2.CodeLineTxt as obj2_CodeLineTxt, 
tt2.CodeLineNum as obj2_CodeLineNum
FROM ( SELECT CodeLineTxt , CodeLineNum, ROW_NUMBER_
FROM (select *, ROW_NUMBER() OVER (ORDER BY CodeLineNum) as ROW_NUMBER_ from #obj1) T1 
WHERE NOT EXISTS (SELECT NULL FROM #obj2 T2 WHERE T2.CodeLineTxt = T1.CodeLineTxt)
) tt1
FULL OUTER JOIN
( SELECT CodeLineTxt , CodeLineNum, ROW_NUMBER_
FROM (select *, ROW_NUMBER() OVER (ORDER BY CodeLineNum) as ROW_NUMBER_ from #obj2) T3 
WHERE NOT EXISTS (SELECT NULL FROM #obj1 T4 WHERE T4.CodeLineTxt = T3.CodeLineTxt)
) tt2
ON tt1.ROW_NUMBER_ = tt2.ROW_NUMBER_
ORDER BY isnull(tt1.ROW_NUMBER_,isnull(tt1.CodeLineNum,tt2.CodeLineNum))
END

DECLARE @CallsCount int

PRINT 'object #1: ' + @Obj1
PRINT 'object #2: ' + @Obj2
PRINT ''

SELECT @CallsCount = COUNT(1) FROM #obj1
PRINT 'Rows number, object #1: ' + cast(@CallsCount as nvarchar(50))
SELECT @CallsCount = COUNT(1) FROM #obj2
PRINT 'Rows number, object #2: ' + cast(@CallsCount as nvarchar(50))
PRINT ''

PRINT 'Percentage match between the objects: '
+ LTRIM(STR(100.0 * (@cnt1a + @cnt2a) / (1.0 * @cnt1 + @cnt2), 10, 2)) + '%'
PRINT 'Percentage of object #1 found in object #2: '
+ LTRIM(STR(100.0 * @cnt1a / (1.0 * @cnt1), 10, 2)) + '%'
PRINT 'Percentage of object #2 found in object #1: '
+ LTRIM(STR(100.0 * @cnt2a / (1.0 * @cnt2), 10, 2)) + '%'
PRINT ''

SELECT @CallsCount = COUNT(1) FROM #obj1 where CodeLineTxt like '%EXEC%'
Print 'Calls to SPs from object #1: ' + cast(@CallsCount as nvarchar(10))
SELECT @CallsCount = COUNT(1) FROM #obj2 where CodeLineTxt like '%EXEC%'
Print 'Calls to SPs from object #2: ' + cast(@CallsCount as nvarchar(10))

declare @LikeSchema nvarchar(50), @LikeSchemaOr nvarchar(50)
set @LikeSchema = '%dbo.%'
set @LikeSchemaOr = '%[[]dbo].%'
SELECT @CallsCount = COUNT(1) FROM #obj1 where CodeLineTxt like @LikeSchema or CodeLineTxt like @LikeSchemaOr
SET @CallsCount -= 1 -- don't count CREATE ... [dbo].....
Print 'Calls to Functions from object #1: ' + cast(@CallsCount as nvarchar(10))
SELECT @CallsCount = COUNT(1) FROM #obj2 where CodeLineTxt like @LikeSchema or CodeLineTxt like @LikeSchemaOr
SET @CallsCount -= 1 -- don't count CREATE ... [dbo].....
Print 'Calls to Functions from object #2: ' + cast(@CallsCount as nvarchar(10))

PRINT ''

DROP TABLE #obj1
DROP TABLE #obj2

SET NOCOUNT OFF

GO

Adding a serial number to the select

SELECT col2, ROW_NUMBER() OVER (ORDER BY col1) 
FROM TABLENAME

WITH RESULT SETS

WITH RESULT SETS Available only from SQL Server 2011 (Denali).

This command change the names and/or data types of the returning result set of a stored procedure.

EXEC ExampleSP 
WITH RESULT SETS
(
 ( 
  ID INT,
  ModifyNameVARCHAR(150),
  ModifyType VARCHAR(15)
 ) 
GO

Get columns data

SELECT OBJECT_NAME(object_id) as TableName, 

     TYPE_NAME(system_type_id) as system_type_name,
FROM sys.columns
--WHERE name = 'COLUMN_NAME'
--WHERE name LIKE '%abcd%'
--WHERE OBJECT_NAME(object_id) = 'TABLE_NAME'
-- and more.....
ORDER BY OBJECT_NAME(object_id)

Select the recent queries on the server

SELECT deqs.last_execution_time AS [Time],
dest.TEXT AS [Query],
decp.query_plan
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS decp
ORDER BY deqs.last_execution_time DESC

Note: This select returns only queries from the memory cache.

SQL Source Control

Source/Version Control goals are:
  • Control of the versions of objects that are deployed in the various environments.
  • Change and difference tracking.
  • Labeling of objects.
  • Recovery and troubleshooting.

While the application code is always managed with Source Control tools, the DB is usually not managed in any way, from 2 main reasons:
  • No one think about it...
  • SQL source control is less structured and clear process than code source control.

But, SQL source control is not complicated more that code source control.


SQL Source Control principles:
  1. Re-runnable. Each script should be re-runnable.
  2. Not depend on specific database/server/etc. you should be able to run the same script for any environment, server, …
  3. Pay attention not to damage your data. Data scripts should be written carefully.
  4. Execution order: is important in database upgrade. See below.

This is the recommended SQL Source Control process:
(As I like generally – I "keep it simple").

1. Create he baseline schema
Generate a baseline schema. This is the starting point for the database versioning.
The baseline schema includes:
  • DB creation (DB, files, etc.).
  • Users and permissions.
  • Base schemas and tables.
  • Other base objects (depends on the status of the database when you start manage it in a source control).
The baseline schema can be saved as:
  1. Scripts – recommended.
  2. Backup / MDF files.

2. Create a SQL file for each DB object (SP, functions, etc.)
The file should be built in a way that you can update an exists object and create the object using the same script.

2.a. SPs/Functions/Views
Those objects actually can always be dropped and re-created.
BUT – it's not recommended – from performance issues (cached plans etc.).

So, the best way to write the scripts to those objects is Create+Alter:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SP_NAME]') AND type in (N'P', N'PC'))
   EXEC (N'CREATE PROCEDURE [dbo].[SP_NAME] AS RETURN')
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FUNCTION_NAME]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
   EXEC (N'CREATE FUNCTION [dbo].[FUNCTION_NAME]() RETURNS @ret TABLE (ID INT) AS BEGIN RETURN END')
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Trg_dbo_AAAAAA]') AND type IN (N'TR'))
       EXEC (N'CREATE TRIGGER [dbo].[Trg_dbo_AAAAAA] ON [dbo].[AAAAAA] AFTER UPDATE AS RETURN');
GO
Etc…

2.b. Tables
Tables can't be dropped because of the data.
Tables should be created only in the first time, and than check each change:
IF NOT EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TABLE_NAME]') AND type in (N'U'))
       CREATE TABLE [dbo].[TABLE_NAME]
.....

Table schema changes:
IF NOT EXISTS ( SELECT * FROM syscolumns where id=object_ID(N'TABLE_NAME') and name='COLUNM_NAME')
       ALTER TABLE TABLE_NAME ALTER COLUMN COLUNM_NAME int NULL
GO
And so on (cinstaints, indexes, etc.)

2.c.  Data
New data insertion - very importrnt that:
  1. Will be re-runnable.
  2. Won't damage current data
  3. Will be runnable to any enviromrnt - not "hard-code" like a specific enviromrnt/database.
  4. Be very very very careful when deleting!

IF NOT EXISTS ( SELECT * FROM TABLE_NAME WHERE CHANGED_COLUMN_NAME='NEW VALUE')
BEGIN
       INSERT INTO TABLE_NAME .....
END
ELSE
BEGIN
       UPDATE TABLE_NAME SET ....
END


3. Each change should be updated in the script. 
In order not to delete other DB developer changes, it's important to check the changes between the last version to the new one, and make sure that the only changes are the knowen changes of the last development.

4. Labeling and tagging
Labeling and tagging will be done like standard code source control labeling and tagging.


Database Upgrade Execution order:
1.     Database objects and users,
2.     Schemas.
3.     Tables.
a.     Tables also have internal ordering: depend on Triggers, FKs and other constraints.
b.    You can also create all tables and after it create all Triggers, FKs and constraints.
4.     Basic Initial Data.
5.     Types.
6.     Functions.
7.     Views.
8.     Stored procedures.
9.     Data.

But - and envirement can have specific properties that can make this order to be changed. Think and make adjustments to your envirement.


SQL Source Control tools:
There are few tolls of SQL source control. But, if a code source control is already in use - it is good enough to the DB also.

That all - basically.
Take those principles and build your structure.
If you need and want - you can comment beloa and/or email me.

Alter schema

ALTER SCHEMA newSchema TRANSFER orgSchema.StamTable

orgSchema.StamTable --> newSchema.StamTable