create trigger Prevent_AlterDrop
on database
for drop_table, alter_table
as
  print 'table can not be dropped or altered'
  rollback transaction

  CREATE TABLE ddl_log 
  (
     EventType nvarchar(100), 
 PostTime datetime,
 SPID nvarchar(100),
 ServerName nvarchar(100),
 LoginName nvarchar(100),
 UserName  nvarchar(100),
 DatabaseName  nvarchar(100),
 ObjectName  nvarchar(100),
 ObjectType  nvarchar(100),
 DefaultSchema nvarchar(100),
 [SID]  nvarchar(100),
 TSQLCommand nvarchar(2000));

CREATE TRIGGER [Log] ON DATABASE
 FOR DDL_DATABASE_LEVEL_EVENTS
 AS
 DECLARE @data XML
 SET @data = EVENTDATA()
 INSERT INTO ddl_log
 VALUES (
   @data.value('(/EVENT_INSTANCE/EventType)[1]',   'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/PostTime)[1]',   'datetime'),
   @data.value('(/EVENT_INSTANCE/SPID)[1]',   'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/ServerName)[1]',   'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/LoginName)[1]',   'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/UserName)[1]',   'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/DatabaseName)[1]',   'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/ObjectName)[1]',   'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/ObjectType)[1]',   'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/DefaultSchema)[1]',   'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/SID)[1]',   'nvarchar(100)'),
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]',   'nvarchar(2000)'));

Server Level

DDL_SERVER_LEVEL_EVENTS

 DDL_LINKED_SERVER_EVENTS

 DDL_LINKED_SERVER_LOGIN_EVENTS

 DDL_REMOTE_SERVER_EVENTS

 DDL_EXTENDED_PROCEDURE_EVENTS

 DDL_MESSAGE_EVENTS

 DDL_ENDPOINT_EVENTS

 DDL_SERVER_SECURITY_EVENTS

 DDL_LOGIN_EVENTS

 DDL_GDR_SERVER_EVENTS

 DDL_AUTHORIZATION_SERVER_EVENT Database Level

Database Level

DDL_TABLE_VIEW_EVENTS

DDL_TABLE-EVENTS

DDL_VIEW_EVENTS

DDL_INDEX_EVENTS

DDL_STATISTICS_EVENTS

DDL_DATABASE_SECURITY_EVENTS

DDL_CERTIFICATE_EVENTS

DDL_USER_EVENTS

DDL_ROLE_EVENTS

DDL_APPLICATION_ROLE_EVENTS

DDL_SCHEMA_EVENTS

DDL_GDR_DATABASE_EVENTS

DDL_AUTHORIZATION_DATABASE_EVENTS

DDL_FUNCTION_EVENTS

DDL_PROCEDUER_EVENTS

DDL_TRIGGER_EVENTS

DDL_PARTITION_EVENTS

DDL_PARTITION_FUNCTION_EVENTS

DDL_PARTITION_SCHEME_EVENTS

DDL_SSB_EVENTS

DDL_MESSAGE_TYPE_EVENTS

DDL_CONTRACT_EVENTS

DDL_QUEUE_EVENTS

DDL_SERVER_EVENTS

DDL_ROUTE_EVENTS

DDL_REMOTE_SERVICE_BINDING_EVENTS

DDL_XML_SCHEMA_COLEECTION_EVENTS

DDL_FULLTEXT_CATALOG_EVENTS

DDL_DEFAULT_EVENTS

DDL_EXTENDED_PROPERTY_EVENTS

DDL_PLAN_GUIDE_EVENTS

DDL_RULE_EVENTS

DDL_SYNONYM_EVENTS

DDL_EVENT_NOTIFICATION_EVENTS

DDL_ASSEMBLY_EVENTS

DDL_TYPE_EVENTS

فرض کنید یک وب سایت حرفه‌ای خبری یا علمی-پژوهشی داریم که قابلیت دریافت نظرات کاربران را در مورد هر مطلب مندرج در سایت یا نظرات داده شده در مورد آن مطالب را دارا می‌باشد. یعنی هر کاربر علاوه بر توانایی اظهار نظر در مورد یک خبر یا مطلب باید بتواند پاسخ نظرات کاربران دیگر را نیز بدهد. اولین راه حلی که برای طراحی این مطلب در پایگاه داده به ذهن ما می‌رسد، ایجاد یک زنجیره با استفاده از کد sql زیر می‌باشد:

CREATE TABLE Comments (
comment_idSERIAL PRIMARY KEY,
parent_idBIGINT UNSIGNED,
comment TEXT NOT NULL,
FOREIGN KEY (parent_id) REFERENCES Comments(comment_id)
);

البته همان طور که پیداست بازیابی زنجیره‌ای از پاسخ‌ها در یک پرس‌وجوی sql کار سختی است. این نخ‌ها معمولا عمق نامحدودی دارند و برای به دست آوردن تمام نخ‌های یک زنجیره باید پرس‌وجوهای زیادی را اجرا نمود.

ایده‌ی دیگر می‌تواند بازیابی تمام نظرها و ذخیره‌ی آن‌ها در حافظه‌ی برنامه به صورت درخت باشد. ولی این روش برای ذخیره هزاران نظری که ممکن است در سایت ثبت شود و علاوه بر آن مقالات جدیدی که اضافه می‌شوند، تقریبا غیرعملی است.

1.2 هدف: ذخیره و ایجاد پرس‌وجو در سلسله‌مراتب

وجود سلسله مراتب بین داده‌ها امری عادی محسوب می‌گردد. در ساختار داده‌ای درختی هر ورودی یک گره محسوب می‌گردد. یک گره ممکن است تعدادی فرزند و یک پدر داشته باشد. گره اول پدر ندارد، ریشه و گره فرزند که فرزند ندارد، برگ و گره‌ای دیگر، گره‌های غیربرگ نامیده می‌شوند.

مثال‌هایی که از ساختار درختی داده‌ها وجود دارد شامل موارد زیر است:

Organizational chart: در این ساختار برای مثال در ارتباط بین کارمندان و مدیر، هر کارمند یک مدیر دارد که نشان‌دهنده‌ی پدر یک کارمند در ساختار درختی است. هر مدیر هم یک کارمند محسوب می‌گردد.

Threaded discussion: در این ساختار برای مثال در سیستم نظردهی و پاسخ‌ها، ممکن است زنجیره‌‌ای از نظرات در پاسخ به نظرات دیگر استفاده گردد. در درخت، فرزندان یک گره‌ی نظر، پاسخ‌های آن گره هستند.

در این فصل ما از مثال ساختار دوم برای نشان دادن Antipattern و راه حل آن بهره می‌گیریم.

2.2 Antipattern : همیشه مبتنی بر یکی از والدین

راه حل ابتدایی و ناکارآمد  

اضافه نمودن ستون parent_id . این ستون، به ستون نظر در همان جدول ارجاع داده می‌شود و شما می‌توانید برای اجرای این رابطه از قید کلید خارجی استفاده نمایید. پرس‌وجویی که برای ساخت مثالی که ما در این بحث از آن استفاده می‌کنیم در ادامه آمده است:

 CREATE TABLE Comments (  comment_idSERIAL PRIMARY KEY,
parent_idBIGINT UNSIGNED,
bug_idBIGINT UNSIGNED NOT NULL,
author BIGINT UNSIGNED NOT NULL,
comment_dateDATETIME NOT NULL,
comment TEXT NOT NULL,
FOREIGN KEY (parent_id)REFERENCES Comments(comment_id),
FOREIGN KEY (bug_id)         REFERENCES Bugs(bug_id),
FOREIGN KEY(author)          REFERENCES Accounts(account_id)
);

لیست مجاورت :

لیست مجاورت خود می‌تواند به عنوان یک antipattern در نظر گرفته شود. البته این مطلب از آنجایی نشأت می‌گیرد که این روش توسط بسیاری از توسعه‌دهندگان مورد استفاده قرار می‌گیرد ولی نتوانسته است به عنوان راه حل برای معمول‌ترین وظیفه‌ی خود، یعنی ایجاد پرس‌وجو بر روی کلیه فرزندان، باشد.

• با استفاده از پرس‌وجوی زیر می‌توان فرزند بلافاصله‌ی یک "نظر" را برگرداند: 

SELECT c1.*, c2.*
FROM Comments c1 LEFT OUTER JOIN Comments c2
ON c2.parent_id = c1.comment_id;

ضعف پرس‌وجوی فوق این است که فقط می‌تواند دو سطح از درخت را برای شما برگرداند. در حالیکه خاصیت درخت این است که شما را قادر می‌سازد بدون هیچ گونه محدودیتی فرزندان و نوه‌های متعدد (سطوح بی‌شمار) برای درخت خود تعریف کنید. بنابراین به ازای هر سطح اضافه باید یک join به پرس‌جوی خود اضافه نمایید. برای مثال اگر پرس‌وجوی زیر می‌تواند درختی با چهار سطح برای شما برگرداند ولی نه بیش از آن: 

SELECT c1.*, c2.*, c3.*, c4.*
FROM Comments c1                         -- 1st level
LEFT OUTER JOIN Comments c2
ON c2.parent_id = c1.comment_id  -- 2nd level
LEFT OUTER JOIN Comments c3
ON c3.parent_id = c2.comment_id  -- 3rd level
LEFT OUTER JOIN Comments c4
ON c4.parent_id = c3.comment_id; -- 4th level

این پرس‌وجو به این دلیل که با اضافه شدن ستون‌های دیگر، نوه‌ها را سطوح عمیق‌تری برمی‌گرداند، پرس‌وجوی مناسبی نیست. در واقع استفاده از توابع تجمیعی ، مانند COUNT() مشکل می‌شود.

راه دیگر برای به دست آوردن ساختار یک زیردرخت از لیست مجاورت برای یک برنامه، این است که سطرهای مورد نظر خود را از مجموعه بازیابی نموده و سلسه‌مراتب مورد نظر را در حافظه بازیابی نماییم و از آن به عنوان درخت استفاده نماییم:

   SELECT * FROM Comments WHERE bug_id = 1234;

INSERT INTO Comments (bug_id, parent_id, author, comment)
VALUES (1234, 7, 'Kukla' , 'Thanks!' );

UPDATE Comments SET parent_id = 3 WHERE comment_id = 6;

SELECT parent_id FROM Comments WHERE comment_id = 6; -- returns 4
UPDATE Comments SET parent_id = 4 WHERE parent_id = 6;
DELETE FROM Comments WHERE comment_id = 6;

  WITH CommentTree (comment_id, bug_id, parent_id, author, comment, depth)
AS (
SELECT *, 0 AS depth FROM Comments
WHERE parent_id IS NULL
UNION ALL
SELECT c.*, ct.depth+1 AS depth FROM CommentTreect
JOIN Comments c ON (ct.comment_id = c.parent_id)
)
SELECT * FROM CommentTree WHERE bug_id = 1234;

SELECT * FROM Comments
START WITH comment_id = 9876
CONNECT BY PRIOR parent_id = comment_id;

  CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY,
path VARCHAR(1000),
bug_id BIGINT UNSIGNED NOT NULL,
author BIGINT UNSIGNED NOT NULL,
comment_date DATETIME NOT NULL,
comment TEXT NOT NULL,
FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
FOREIGN KEY (author) REFERENCES Accounts(account_id)

  SELECT * FROM Comments AS c
WHERE '1/4/6/7/' LIKE c.path || '%' ;

  SELECT * FROM Comments AS c
WHERE c.path LIKE '1/4/' || '%' ;

  CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY,
nsleft INTEGER NOT NULL,
nsright INTEGER NOT NULL,
bug_id BIGINT UNSIGNED NOT NULL,
author BIGINT UNSIGNED NOT NULL,
comment_date DATETIME NOT NULL,
comment TEXT NOT NULL,
FOREIGN KEY (bug_id) REFERENCES Bugs (bug_id),
FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

  SELECT c2.* FROM Comments AS c1
JOIN Comments as c2
ON c2.nsleft BETWEEN c1.nsleft AND c1.nsright
WHERE c1.comment_id = 4;

SELECT c2.*
FROM Comments AS c1
JOIN Comment AS c2
ON c1.nsleft BETWEEN c2.nsleft AND c2.nsright
WHERE c1.comment_id = 6;

  SELECT parent.* FROM Comment AS c
JOIN Comment AS parent
ON c.nsleft BETWEEN parent.nsleft AND parent.nsright
LEFT OUTER JOIN Comment AS in_between
ON c.nsleft BETWEEN in_between.nsleft AND in_between.nsright
AND in_between.nsleft BETWEEN parent.nsleft AND parent.nsright
WHERE c.comment_id = 6
AND in_between.comment_id IS NULL;

-- make space for NS values 8 and 9
UPDATE Comment
SET nsleft = CASE WHEN nsleft >= 8 THEN nsleft+2 ELSE nsleft END,
nsright = nsright+2
WHERE nsright >= 7;

-- create new child of comment #5, occupying NS values 8 and 9
INSERT INTO Comment (nsleft, nsright, author, comment)
VALUES (8, 9, 'Fran' , 'Me too!' );

  CREATE TABLE Comments ( comment_id SERIAL PRIMARY KEY,
bug_id BIGINT UNSIGNED NOT NULL,
author BIGINT UNSIGNED NOT NULL,
comment_date DATETIME NOT NULL,
comment TEXT NOT NULL,
FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
FOREIGN KEY (author) REFERENCES Accounts(account_id)
);
CREATE TABLE TreePaths (
ancestor BIGINT UNSIGNED NOT NULL,
descendant BIGINT UNSIGNED NOT NULL,
PRIMARY KEY(ancestor, descendant),
FOREIGN KEY (ancestor) REFERENCES Comments(comment_id),
FOREIGN KEY (descendant) REFERENCES Comments(comment_id)
);

   SELECT c.*  FROM Comments AS c
JOIN TreePaths AS t ON c.comment_id = t.descendant
WHERE t.ancestor = 4;

SELECT c.*
FROM Comments AS c
JOIN TreePaths AS t ON c.comment_id = t.ancestor
WHERE t.descendant = 6;

  INSERT INTO TreePaths (ancestor, descendant) SELECT t.ancestor, 8
FROM TreePaths AS t
WHERE t.descendant = 5
UNION ALL
SELECT 8, 8;

   DELETE FROM TreePaths WHERE descendant = 7;

CREATE TABLE [Test1](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Price] [int] NOT NULL,
 CONSTRAINT [PK_Test1] PRIMARY KEY CLUSTERED 
(
[Id] ASC
));

 Insert into Test1 values (1000000000),(1000000000),(1000000000),(1000000000),(1000000000),(1000000000),(1000000000)

select sum(price) from Test1

Arithmetic overflow error converting expression to data type int.

select sum(cast(price as bigint)) from Test1

DECLARE @f AS FLOAT = '29545428.0211111';
SELECT CAST(@f AS NUMERIC(28, 14)) AS value;

CREATE TABLE MoneyTest (
 Mon1 money,
 Mon2 AS Mon1*Mon1,
 Mon3 AS Mon1*Mon1*Mon1,
 Dec1 decimal(19,4),
 Dec2 AS Dec1*Dec1,
 Dec3 AS Dec1*Dec1*Dec1,
 MonDec AS Mon1*Dec1,
 DecMon AS Dec1*Mon1);

CREATE TABLE [MoneyTest](
[Id] [int] IDENTITY(1,1) NOT NULL,
decimalMoney decimal(19,4),
moneyMoney money
 CONSTRAINT [PK_MoneyTest] PRIMARY KEY CLUSTERED 
(
[Id] ASC
));

INSERT INTO MoneyTest
VALUES
(12321423442.3456,12321423442.3456),
(1111111.1919,1111111.1919)

SELECT * FROM MoneyTest

SELECT SUM(decimalMoney) AS [sumDecimal],
   SUM(moneyMoney) AS [sumMoney]
FROM MoneyTest

DECLARE @moneyPer money,
  @decimalPer decimal(19,4)
SET @moneyPer = (SELECT moneyMoney FROM MoneyTest WHERE id = 2)/((SELECT moneyMoney FROM MoneyTest WHERE id = 1))
SET @decimalPer = (SELECT decimalMoney FROM MoneyTest WHERE id = 2)/((SELECT decimalMoney FROM MoneyTest WHERE id = 1))
SELECT @moneyPer AS[moneyPer], @decimalPer AS [decimalPer];

DECLARE @M MONEY = 1234, @D DECIMAL(6,2) = 1234
SELECT @M/$1000000 AS [MONEY] ,
 @D/$1000000 AS [DECIMAL]

DECLARE @dOne DECIMAL(19,4) = 1,
  @dThree DECIMAL(19,4) = 3,
  @mOne MONEY = 1,
  @mThree MONEY = 3

SELECT (@dOne/@dThree) * @dThree AS DecimalResult,
  (@mOne/@mThree) * @mThree AS MoneyResult