Saturday, May 25, 2013

[T-SQL] Trigger for truncate

[T-SQL] Trigger for truncate

Trigger for truncate

Posted: 21 Apr 2010 12:18 AM PDT

Hi,Can we simulate a truncate trigger? what i need is from which host, who,when truncated a table even if he/she is a DBA.

Confused selecting Info From Two DB

Posted: 07 Apr 2013 12:12 AM PDT

i have two database AgencyDB(primary DB) and AgencyBackupDB(Deleted Info from AgencyDB by user) and i have this three table in both DB(but there is no relationship in AgencyBackupDB between Tables).this is my question: I want select all info from AgencyBackupDB in tables SabtHazine and Hazine and HazineGroup.i want if HazineID from Hazine table Doesnt Exist in AgencyBackupDB select it From AgencyDB and do same for HazineGroup.this is script of All Tabel With Test Data[code="sql"]CREATE TABLE [dbo].[HazineGroup]( [HazineGroupID] [smallint] IDENTITY(1,1) NOT NULL, [HazineName] [nvarchar](150) NOT NULL, CONSTRAINT [PK_HazineGroup] PRIMARY KEY CLUSTERED ( [HazineGroupID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_HazineGroup] UNIQUE NONCLUSTERED ( [HazineName] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[HazineGroup] ONINSERT [dbo].[HazineGroup] ([HazineGroupID], [HazineName]) VALUES (6, N'برق')INSERT [dbo].[HazineGroup] ([HazineGroupID], [HazineName]) VALUES (1, N'شستشویی')INSERT [dbo].[HazineGroup] ([HazineGroupID], [HazineName]) VALUES (2, N'نظافتی')SET IDENTITY_INSERT [dbo].[HazineGroup] OFF/****** Object: Table [dbo].[Hazine] Script Date: 04/07/2013 17:34:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[Hazine]( [HazineID] [int] IDENTITY(1,1) NOT NULL, [HazineGroupID] [smallint] NOT NULL, [Mablagh] [int] NOT NULL, [HazineName] [nvarchar](250) NOT NULL, CONSTRAINT [PK_Hazine] PRIMARY KEY CLUSTERED ( [HazineID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[Hazine] ONINSERT [dbo].[Hazine] ([HazineID], [HazineGroupID], [Mablagh], [HazineName]) VALUES (1, 1, 20000, N'صابون گل')INSERT [dbo].[Hazine] ([HazineID], [HazineGroupID], [Mablagh], [HazineName]) VALUES (3, 2, 10000, N'بروس')SET IDENTITY_INSERT [dbo].[Hazine] OFF/****** Object: Table [dbo].[SabtHazine] Script Date: 04/07/2013 17:34:59 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[SabtHazine]( [SabtHazineID] [int] IDENTITY(1,1) NOT NULL, [HazineID] [int] NOT NULL, [EndUserName] [nvarchar](20) NOT NULL, [Tedad] [smallint] NOT NULL, [sabtHazineDate] [date] NOT NULL, [Describtion] [nvarchar](500) NULL, CONSTRAINT [PK_SabtHazine] PRIMARY KEY CLUSTERED ( [SabtHazineID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOSET IDENTITY_INSERT [dbo].[SabtHazine] ONINSERT [dbo].[SabtHazine] ([SabtHazineID], [HazineID], [EndUserName], [Tedad], [sabtHazineDate], [Describtion]) VALUES (6, 3, N'SA', 3, CAST(0xC2360B00 AS Date), N'')SET IDENTITY_INSERT [dbo].[SabtHazine] OFF/****** Object: Default [DF_SabtHazine_Tedad] Script Date: 04/07/2013 17:34:59 ******/ALTER TABLE [dbo].[SabtHazine] ADD CONSTRAINT [DF_SabtHazine_Tedad] DEFAULT ((1)) FOR [Tedad]GO/****** Object: ForeignKey [FK_Hazine_HazineGroup] Script Date: 04/07/2013 17:34:59 ******/ALTER TABLE [dbo].[Hazine] WITH CHECK ADD CONSTRAINT [FK_Hazine_HazineGroup] FOREIGN KEY([HazineGroupID])REFERENCES [dbo].[HazineGroup] ([HazineGroupID])GOALTER TABLE [dbo].[Hazine] CHECK CONSTRAINT [FK_Hazine_HazineGroup]GO/****** Object: ForeignKey [FK_SabtHazine_Hazine] Script Date: 04/07/2013 17:34:59 ******/ALTER TABLE [dbo].[SabtHazine] WITH CHECK ADD CONSTRAINT [FK_SabtHazine_Hazine] FOREIGN KEY([HazineID])REFERENCES [dbo].[Hazine] ([HazineID])GOALTER TABLE [dbo].[SabtHazine] CHECK CONSTRAINT [FK_SabtHazine_Hazine]GO[/code]

calculate average time results

Posted: 24 May 2013 04:59 AM PDT

[code="sql"] CREATE TABLE #AmountOfTime ( DURATION TIME ) [/code][code="sql"]INSERT INTO #AmountOfTime (DURATION ) VALUES ('07:22:04.1600000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('07:12:21.5800000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('00:41:52.5170000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('05:24:58.4530000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('09:19:34.9500000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('07:59:44.4000000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('03:00:53.3170000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('06:03:16.6430000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('05:50:43.9300000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('10:10:51.3600000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('07:26:35.8430000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('08:55:28.1970000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('03:24:47.2830000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('02:11:15.0770000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('07:56:58.9070000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('07:28:34.0070000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('06:21:41.2800000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('02:00:09.6170000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('06:45:57.8800000')INSERT INTO #AmountOfTime (DURATION ) VALUES ('09:48:41.8670000')[/code]Hi,The times you see are, it took this many hours, min, sec, ms, to go from point A to point B; thus each record is how long it took for each uniqe trip...I've googled and search here, and cant seem to find or understand how to find out what the average hours, min, sec, ms for these 20 records???Thank you,John

JOIN Issues

Posted: 24 May 2013 10:20 AM PDT

I need to GROUP products by category based on a product rate, but I'm having a hard time joining on multiple tables.Here's the layout...A customer has N number of products that are billed at N number of rates. Each product rate for a customer has a record in the [custrate] table. There are also two other tables that store the rate data...[rateheader] and [rate]. The [rateheader] table is the parent...[rate] and [custrate] are child tables. Now here's where it get's fuzzy... The only table of these three that has the [category_id] is the [rate] table. This table holds the base product rate record and N number of product override rate records. These records share the same [rg_id] but have different [idnum]'s. The base rate record always has an [idnum] = 0 and he product override rate records have [idnum] = [crid]. This [crid] is the primary key in the [custrate] table. So...I can almost join on [idnum] since it has unique [crid] values for the override records...but with the base rate records [idnum] = 0...I can't. Well...I don't know if I can or not...Here are all tables involved...some sample data and my failed query.[code="sql"]USE TestGOCREATE TABLE [dbo].[customer]( [cust_id] [int] NOT NULL, [active] [char](1) NULL, [storeid] [int] NULL, CONSTRAINT [pk_customer] PRIMARY KEY CLUSTERED ( [cust_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO [dbo].[customer]([cust_id], [active], [storeid])SELECT 1, 'Y', 1 UNION ALLSELECT 2, 'Y', 1 UNION ALLSELECT 3, 'Y', 1 UNION ALLSELECT 4, 'Y', 1 UNION ALLSELECT 5, 'Y', 2 UNION ALLSELECT 6, 'Y', 2 UNION ALLSELECT 7, 'Y', 2 UNION ALLSELECT 8, 'Y', 2GOCREATE TABLE [dbo].[custrate]( [crid] [int] NOT NULL, [cust_id] [int] NOT NULL, [rg_id] [smallint] NOT NULL, [lastbilled] [datetime] NULL, [nextbill] [datetime] NULL, CONSTRAINT [pk_custrate] PRIMARY KEY CLUSTERED ( [crid] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO [dbo].[custrate]([crid], [cust_id], [rg_id], [lastbilled], [nextbill])SELECT 111, 1, 2001, '20130504 00:00:00.000', '20130604 00:00:00.000' UNION ALLSELECT 112, 2, 2001, '20130519 00:00:00.000', '20130619 00:00:00.000' UNION ALLSELECT 113, 2, 2002, '20120522 10:13:39.000', '20130522 00:00:00.000' UNION ALLSELECT 114, 2, 2003, '20120211 00:00:00.000', '20120511 00:00:00.000' UNION ALLSELECT 115, 3, 2003, '20130516 00:00:00.000', '20130616 00:00:00.000' UNION ALLSELECT 116, 3, 2004, '20130509 00:00:00.000', '20130609 00:00:00.000' UNION ALLSELECT 117, 4, 2002, '20120821 00:00:00.000', '20120921 00:00:00.000' UNION ALLSELECT 118, 5, 2001, '20130509 00:00:00.000', '20130609 00:00:00.000' UNION ALLSELECT 119, 5, 2001, '20120701 00:00:00.000', '20120801 00:00:00.000' UNION ALLSELECT 120, 6, 2001, '20130521 00:00:00.000', '20130621 00:00:00.000' UNION ALLSELECT 121, 6, 2004, '20121012 00:00:00.000', '20121112 00:00:00.000' UNION ALLSELECT 122, 7, 2001, '20130520 00:00:00.000', '20140520 00:00:00.000' UNION ALLSELECT 123, 8, 2002, '20120704 00:00:00.000', '20120804 00:00:00.000' UNION ALLSELECT 124, 8, 2003, '20120810 00:00:00.000', '20120910 00:00:00.000' UNION ALLSELECT 125, 8, 2004, '20120622 00:00:00.000', '20120722 00:00:00.000'GOCREATE TABLE [dbo].[rateheader]( [rg_id] [int] NOT NULL, [rg_name] [varchar](100) NOT NULL, CONSTRAINT [pk_rateheader] PRIMARY KEY CLUSTERED ( [rg_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO [dbo].[rateheader]([rg_id], [rg_name])SELECT 2001, N'Product1 Rate' UNION ALLSELECT 2002, N'Product2 Rate' UNION ALLSELECT 2003, N'Product3 Rate' UNION ALLSELECT 2004, N'Product4 Rate'GOCREATE TABLE [dbo].[rate]( [id] [int] NOT NULL, [rg_id] [int] NOT NULL, [idnum] [int] NULL, [name] [varchar](80) NOT NULL, [price] [money] NOT NULL, [category_id] [int] NULL, CONSTRAINT [pk_rate] PRIMARY KEY CLUSTERED ( [id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO [dbo].[rate]([id], [rg_id], [idnum], [name], [price], [category_id])SELECT 1, 2001, 0, 'Product1 Base Rate', 100.99, 1001 UNION ALLSELECT 2, 2001, 111, 'Product1 Override Rate', 200.99, 1001 UNION ALLSELECT 3, 2001, 112, 'Product1 Override Rate', 700.99, 1001 UNION ALLSELECT 4, 2001, 118, 'Product1 Override Rate', 20.99, 1001 UNION ALLSELECT 5, 2001, 119, 'Product1 Override Rate', 800.99, 1001 UNION ALLSELECT 6, 2002, 0, 'Product2 Base Rate', 99.99, 1002 UNION ALLSELECT 7, 2002, 123, 'Product2 Override Rate', 100.00, 1002 UNION ALLSELECT 8, 2003, 0, 'Product3 Base Rate', 1.99, 1003 UNION ALLSELECT 9, 2003, 115, 'Product3 Override Rate', 5.99, 1003 UNION ALLSELECT 10, 2003, 124, 'Product3 Override Rate', 6.99, 1003 UNION ALLSELECT 11, 2004, 0, 'Product4 Base Rate', 999.00, 1004 UNION ALLSELECT 12, 2004, 125, 'Product4 Override Rate', 1200.99, 1004GOCREATE TABLE [dbo].[product_category]( [category_id] [int] NOT NULL, [category_name] [varchar](250) NOT NULL, CONSTRAINT [pk_item_category] PRIMARY KEY CLUSTERED ( [category_id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOINSERT INTO [dbo].[product_category]([category_id], [category_name])SELECT 1001, N'Product1' UNION ALLSELECT 1002, N'Product2' UNION ALLSELECT 1003, N'Product3' UNION ALLSELECT 1004, N'Product4'-- Group all product rates by categorySELECT pc.category_name, count(*) as Total-- c.storeid, c.cust_id, rh.rg_name, r.idnum, r.category_id, cr.* FROM customer c WITH(NOLOCK)JOIN custrate cr WITH(NOLOCK) ON cr.cust_id = c.cust_id AND c.active = 'Y' AND c.storeid IN (1,2)JOIN rateheader rh WITH(NOLOCK) ON cr.rg_id = rh.rg_idLEFT JOIN rate r WITH(NOLOCK) ON r.idnum = cr.cridLEFT JOIN product_category pc WITH(NOLOCK) ON r.category_id = pc.category_idGROUP BY pc.category_name[/code]

Looping and variables

Posted: 24 May 2013 03:17 AM PDT

I am unable to understand how variables work:I have created this query that finds all the column names and associated tables that contain "REALS" using a cursor.I want to find the MIN and MAX reals for the "found" columns thus the reason why I need the table names.I figured that while the cursor had gotten the column name and table in the respective variables @ColVal and @TableName , I could just create a SELECT statement to return the MIN or MAX value of that column.EX: SELECT MAX( + @ColVal + ) FROM + @TableName;But it is not working.ANY IDEAS?Here is the query:[b]USE [TankInspections]GO/****** Object: StoredProcedure [dbo].[MINMAXREAL2] Script Date: 05/22/2013 16:27:33 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGO-- =============================================-- Author: David Cunningham-- Create date: 05-22-13-- Description: MIN MAX REALS-- Finds all the column names in all the tables that have data type REAL-- It then loops thru that resulting data (column names and table) and finds the MAX REAL number in that column-- =============================================DECLARE @ColVal varchar(50), @TableName varchar(50), @MaxVal varchar(100);DECLARE data_cursor CURSOR FORSELECT COLUMN_NAME, TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS where (DATA_TYPE = 'real');OPEN data_cursor;-- Perform the first fetch.FETCH NEXT FROM data_cursor INTO @ColVal,@TableName;-- Check @@FETCH_STATUS to see if there are any more rows to fetch.WHILE @@FETCH_STATUS = 0BEGIN -- This is executed as long as the previous fetch succeeds. FETCH NEXT FROM data_cursor INTO @ColVal,@TableName; PRINT 'Column Name: ' + @ColVal + ' Table Name: ' + @TableName; -- BUILD and INSERT NEW SQL STATEMENT TO GET MAX VALUE-- EX: SELECT MAX( + @ColVal + ) FROM + @TableName ENDCLOSE data_cursor;DEALLOCATE data_cursor;GO[/b]_________Any solution or tutorials reference would be great - I am "kind of" new to SQL - exposed to it thru VB, .NET etc..

LEFT JOIN - Table Scan

Posted: 24 May 2013 01:32 AM PDT

Hello,I have a query where two tables are joined using a LEFT JOIN. This logical operation is being implemented using a hash match physical operator. I have an index for the join predicate, but my right table is being accessed using a clustered index scan, but this table has more than 12 millions rows. It would be nice if it would be accessed using an index seek.My question is: is it correct if I assume that SQL Server should have used my index and do an index seek instead of an index scan? I am not sure if it should, because this is a join and the hash match will do the join.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog