Saturday, May 4, 2013

[SQL Server 2008 issues] Need Computed Column / Trigger / Function to insert data from another table

[SQL Server 2008 issues] Need Computed Column / Trigger / Function to insert data from another table

Need Computed Column / Trigger / Function to insert data from another table

Posted: 23 Apr 2013 06:10 AM PDT

Hi,I've got the following Problem, which I'd like to solve in SQL Server 2008:I got a Table 'Products', each product with a range of up to 3 different areas, which contains Information about the Total Amount of items for this specific product.So the first product contains of a Total of 10 different Parts, whereas the 2nd product contains out of 5 and so on … PROD_ID : ACA : CW : DIVE : BR_ID1 : 5 : 1 : 4 : 102 : 0 : 0 : 5 : 113 : 1 : 0 : 2 : 124 : 2 : 1 : 2 : 10I got a second table named 'Breakdown' which defines the commissions paid for each different Product-Part ... BR_ID : ACA : CW : DIVE10 : 85 : 425 : 21311 : 0 : 0 : 29012 : 850 : 0 : 325This table is fix and used to calculate the Comms for each Individual – it's NOT editable by the USER.My third table 'Orders' contains all the ORDERS placed by the Customers ...ORD_ID : PROD_ID : Date 100 : 1 : 01-Apr 101 : 1 : 01-Apr 102 : 2 : 02-Apr 103 : 3 : 02-Apr 104 : 1 : 03-Apr In each of the 3 tables the first column is IDENTITY and as such definitely not entered twice.Here my problem:In my fourth table 'Comms' I need to get all the Commissions correctly entered and it's done by the Receivers themselves AFTER the Product has been sold and PAID- obviously it's unlikely that Comms are not entered at all seeing that people want to collect those - but it's more likely that people enter those multiple times/multiple people enter the Comms twice ... What I try to achieve is a Calculated Field to validate the entries in that table on SAVE Action, with multiple criterias.I want to only allow Users to enter the maximum allowed Amount of Prod.-Parts from the first table (ACA/CW/DIVE) for a single ORDER_ID from Table 3, so for example if Order_ID 100 refers to PROD_ID 1 (which contains 5 Parts ACA), then a Maximum of total Entries of ACA could not exceed 5 in ALL rows entered for this ORDER_ID or an error message is thrown …My Table 4 'Comms' looks like this so far:CREATE TABLE [dbo].[Comms]( [Comm_ID] [int] IDENTITY(65000,1) NOT NULL, [Cert_Date] [datetime] NOT NULL, [Ins_ID] [int] NOT NULL, [ORD_ID] [int] NOT NULL, [Amount_Aca] [int] NOT NULL, [Amount_Confined] [int] NOT NULL, [Amount_Dives] [int] NOT NULL,What I would like now is a Computed Column of the style like "Compare the Records for this Order_ID, find the Product and Breakdown-Values for it from Table 1 & 2 and then search for existing Records with that Order_ID in the 'Comms' Table itself. If no entry has been made, return the Maximum Parts as defined in the 'Products' Table. If someone entered for example already 2 ACA for PROD_ID 1 under ORD_ID 101, then return the remaining Value as 5 (MAX ITEMS ACA) - 2 = 3 ...A simple View can do this, but unfortunately I couldn't find a way to Create it with Computed Columns (as they can't refer to other Tables) or Functions/Triggers, as well as it has to be done more or less on Record Creation.The ID-Fields on all Tables are set IDENTITY, and FK are set in between them.On a positive side: This application is not for millions of Transactions at the same time, so it's a chance of about one million to one, that two users will enter their Comms at the same time, so double entries/engine performance shouldn't be a problem. ...Any help with this would be really appreciated, I'm new to this stuff, so any extra information that could help I'd love to provide. Thanks a lot already! :)

Log EndUser Activity

Posted: 03 May 2013 05:35 PM PDT

I have a table for user in my DB(UserID,UserName,FirstName,..).i want to log users that do insert,edit,delete and date of that on all table in my DB.how can i do that????i want clear way to perform this

Matching records

Posted: 02 May 2013 11:06 PM PDT

Greetings everybody,This is my first time posting.I'm trying to think through a problem and can't figure it out.There are two tables, Subscriptions and Payments, that share a number of fields: [shared] CustomerId varchar(15)[shared] Amount money[shared] TransactionDate datetime[not shared] UniqueId bigint The records from Subscriptions need to be matched to those in Payments to ensure that all payments are recorded in the subscriptions.Is there a SQL statement that would identify the payments that match for all customers at the same time?For example:One customer has 3 payments but only 2 were processed into subscriptions.Sample data:PaymentsUniqueId CustomerId Amount Date1 ABCD 25 1/1/137 ABCD 25 1/1/1325 ABCD 25 1/1/13SubscriptionsUniqueId CustomerId Amount Date22 ABCD 25 1/1/1329 ABCD 25 1/1/1354 ABCD 25 1/1/13The query should return the unique id of the record that match from each record, so the results should be:Resultset:PaymentId SubscriptionId1 227 29This could easily be done using T-SQL and a cursor, but there must be a better way to accomplish this without looping through the records?

index on the underlying table, not the index on the schemabound view is in the execution plan

Posted: 03 May 2013 08:39 AM PDT

I have a view on one underlying table with schemabinding. I created a clustered index of five columns on itThe underlying table has a clustered index on it also, but its not on a join column, its on a date, the joining column is 3rd in the order, if that matters anymore.I thought putting a clustered index on the view would let me preserve the underlying's schema during data loading and allow a clustered index on the view with different columns without affecting the underlying table, but when I look at the execution plan, the optimizer is using the clustered index from the underlying table!Surprise!What am i missing or what should i be reading?...i thought the clustered index on the view would be independent of and have precedence over the clustered index on the table, but it doesn't look that way.thanks a lotdrew

Connecting to SQL Server or Oracle using ODBC drivers

Posted: 03 May 2013 09:00 AM PDT

Hi All,what odbc drivers should we install to connect to sql server. My scenario is that my source server will be always same and destination may be SQL Server or oracle depending upon the request. can i set up SSIS pkg in such a way that i can change destination to either oracle or sql server by just changing connection string ?

Index Text

Posted: 03 May 2013 03:41 AM PDT

Hello EveryoneI was suggested that I use a different way of generating some IDs. This column is not any part of a Pri Key, only a way to identify an item, kind of like a part number.The other person suggested that I use 'FakeID_00001', 'FakeID_00002', 'FakeID_0000n' etc.... There are other IDs that have already been generated that are all numbers, even though the column is text. So I wanted to use an all number ID, even though it is a text data type column, on the idea that one day, someone may want to convert the IDs to an actual numeric value. Mine are '548921412345', '154879612345', '889789712345', etc....The other person said his would be indexed better than mine since the repeating values were at the beginning of the text string. And my repeating values are at the end of the string. Is that true? I would think that it would not matter, since the index is going to use the entire value of the string.Thank you in advance for all your suggestions, comments and assistanceAndrew SQLDBA

Is this is necessary to install SSIS or SSRS on production box.

Posted: 03 May 2013 06:19 AM PDT

Hi All,Is this is necessary to install SSIS or SSRS on production to do run SSIS packages or generatle reports.?

Is SQL right for this?

Posted: 03 May 2013 02:44 AM PDT

My daughter is in a school program where they have to complete community service hours. I'm on the parent advisory board of the program, and the program organizer told me about some software where they can submit their service hours and keep track of them online. Unfortunately the software is quite expensive and doesn't really fit into the budget for the academic program, so the program organizer came to me asking if there was anything that we (by that she meant me) could set up so that the kids could submit their hours and have them recorded. Now I'm a network engineer, I've done a little work with databases but I'm no wiz to put it lightly.What I would need is to have an interface where students could set up a username and password, have a form that would go to the program organizer to have their service hours approved, then that would come back to the students letting them know it was approved at which point they could submit a second form letting the program organizer know that they had completed the activity for community service hours, and a screen totaling all of their hours. Would SQL be correct for this? If not would you be able to suggest something that might work well? If there is already open source software or something cost-effective that we could implement I would be down with that! Any help is greatly appreciated.

Move 2008 R2 Files to 2008 instance?

Posted: 03 May 2013 04:27 AM PDT

Hi Everyone,We have an application which originally wrote to a SQL Server 2008 database. Just recently we moved it to 2008 R2 by taking a backup on 2008 and restoring it into 2008 R2 instance. The application has been performing badly since and we'd like to move it back. I know I can't take an R2 backup and restore it into 2008 since the backup would be a newer version but can I detach the data/log files, move them back to the 2008 server and then attach them back to the 2008 instance?

DB Query

Posted: 02 May 2013 10:51 PM PDT

Last Night their were some DB's which were not functioning as the Transaction LOg was Full.. the Drive where the Logs are kept were full..as always one technician executed shringing of log.. all went well & the DB's were good.The same thing happened with different server..while shrinking of Logs .. One DB gave errorError - Cannot shrink db_Test Log as it is under transition...and so all DB's LOG files were Shrunk except the db_Test..Now when he used the query in query analyserUse db_Testerror - No entry found with that name..i mean the DB does not exist :w00t:

VIEW vs. DynamicSQL

Posted: 29 Apr 2013 08:04 PM PDT

Hello,I have few questions, I would like remove DSQL, but our design table is not good, but redesign table is for future, because few programs ( korean source, sometimes we dont have source... long story) still use direct statement in source.So now we have separate table, but there is column line_code I dont know who designed this - Korean :Dso my questions is for someone who understand about query optimazing ...here is declare of one table , there is different just end of table for example CPM01, CPM02, according to help by opc.three I added check constraint for line code [quote]USE [MCS_MESDB]GO/****** Object: Table [dbo].[TB_MASTER_CPM01] Script Date: 4/30/2013 10:39:11 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [dbo].[TB_MASTER_CPM01]( [ORDER_DATE] [char](8) NOT NULL, [COMMIT_NO] [char](4) NOT NULL, [BODY_NO] [varchar](12) NOT NULL, [CAR_CODE] [varchar](3) NOT NULL, [LINE_CODE] [char](5) NOT NULL, [STATION_ID] [char](5) NOT NULL, [WORK_SEQ] [int] NOT NULL, [POINT_SEQ] [int] NOT NULL, [POINT_SER] [int] NOT NULL, [WORK_TYPE] [varchar](2) NOT NULL, [ITEM_CODE] [char](5) NULL, [ALC_CODE] [varchar](4) NULL, [WORK_CODE] [varchar](6) NOT NULL, [WORK_POS] [char](1) NOT NULL, [WORK_QTY] [int] NOT NULL, [LIMITV_LOW] [decimal](5, 2) NULL, [LIMITV_HIGH] [decimal](5, 2) NULL, [OPTION_VALUE] [varchar](20) NULL, [MASTER_DESC] [varchar](50) NULL, CONSTRAINT [PK_TB_MASTER_CPM01] PRIMARY KEY CLUSTERED ( [ORDER_DATE] ASC, [COMMIT_NO] ASC, [BODY_NO] ASC, [CAR_CODE] ASC, [LINE_CODE] ASC, [STATION_ID] ASC, [WORK_SEQ] ASC)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY]GOSET ANSI_PADDING OFFGOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_POINT_SEQ] DEFAULT ((1)) FOR [POINT_SEQ]GOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_POINT_SER] DEFAULT ((1)) FOR [POINT_SER]GOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_WORK_QTY] DEFAULT ((0)) FOR [WORK_QTY]GOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_LIMITV_LOW] DEFAULT ((0)) FOR [LIMITV_LOW]GOALTER TABLE [dbo].[TB_MASTER_CPM01] ADD CONSTRAINT [DF_TB_MASTER_CPM01_LIMITV_HIGH] DEFAULT ((0)) FOR [LIMITV_HIGH]GOALTER TABLE [dbo].[TB_MASTER_CPM01] WITH CHECK ADD CONSTRAINT [check_CPM01] CHECK (([LINE_CODE]='CPM01'))GOALTER TABLE [dbo].[TB_MASTER_CPM01] CHECK CONSTRAINT [check_CPM01]GOEXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ORDER DATE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'TB_MASTER_CPM01', @level2type=N'COLUMN',@level2name=N'ORDER_DATE'GO[/quote]here is view [quote]USE [MCS_MESDB]GO/****** Object: View [dbo].[VIEW_MASTER] Script Date: 4/30/2013 10:55:50 AM ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE VIEW [dbo].[VIEW_MASTER] WITH SCHEMABINDING AS SELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVENUNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVF1UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVF2UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVFAUNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVFCUNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_AGVRCUNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_CPM01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_CPSB1UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_ENG01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAST1UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAST2UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAX01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FAX02UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FCM01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_FEM01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_RCM01UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRCP1UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRCP2UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRFE3UNION ALLSELECT [ORDER_DATE],[COMMIT_NO],[BODY_NO],[CAR_CODE],[LINE_CODE],[STATION_ID],[WORK_SEQ] ,[POINT_SEQ],[POINT_SER],[WORK_TYPE],[ITEM_CODE],[ALC_CODE],[WORK_CODE],[WORK_POS] ,[WORK_QTY],[LIMITV_LOW],[LIMITV_HIGH],[OPTION_VALUE],[MASTER_DESC] FROM dbo.TB_MASTER_TRSB1GO[/quote]And here I have few testing queries [quote]DECLARE @LINE_CODE VARCHAR(5)='CPM01'DECLARE @COMMIT_NO varchar(4)='0042'DECLARE @ORDER_DATE varchar(8)='20130301'DECLARE @STATION_ID varchar(5)='CP-08'DECLARE @sql varchar(2000)DECLARE @nsql nvarchar(2000)DECLARE @StartTime datetimeSET @StartTime = GETDATE()select TOP 1 * from [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID OPTION(RECOMPILE);SELECT ExecutionTimeInMSVieWVariableOPt = DATEDIFF(millisecond, @StartTime, getdate()) SET @StartTime = GETDATE()select TOP 1 * from [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID ;SELECT ExecutionTimeInMSVieWVariable = DATEDIFF(millisecond, @StartTime, getdate()) SET @StartTime = GETDATE()select TOP 1 * from [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE='CPM01' AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID ;SELECT ExecutionTimeInMSVieWwithoutVar = DATEDIFF(millisecond, @StartTime, getdate()) SET @StartTime = GETDATE()set @nsql='SELECT TOP 1 * FROM [VIEW_MASTER] WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID'EXECUTE sp_executesql @nsql, N'@ORDER_DATE varchar(8), @LINE_CODE varchar(5), @COMMIT_NO varchar(4), @STATION_ID varchar(5)' , @ORDER_DATE, @LINE_CODE, @COMMIT_NO, @STATION_IDSELECT ExecutionTimeInSP_EXECUTESQLView = DATEDIFF(millisecond, @StartTime, getdate())SET @StartTime = GETDATE()set @nsql='SELECT TOP 1 * FROM TB_MASTER_'+@LINE_CODE+' WHERE ORDER_DATE=@ORDER_DATE AND LINE_CODE=@LINE_CODE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_ID'EXECUTE sp_executesql @nsql, N'@ORDER_DATE varchar(8), @LINE_CODE varchar(5), @COMMIT_NO varchar(4), @STATION_ID varchar(5)' , @ORDER_DATE, @LINE_CODE, @COMMIT_NO, @STATION_IDSELECT ExecutionTimeInSP_EXECUTESQL = DATEDIFF(millisecond, @StartTime, getdate()) SET @StartTime = GETDATE()set @sql='SELECT TOP 1 * FROM TB_MASTER_'+@LINE_CODE+' WHERE ORDER_DATE='''+@ORDER_DATE+''' AND COMMIT_NO='''+@COMMIT_NO+''' AND STATION_ID='''+@STATION_ID+''''execute (@sql)SELECT ExecutionTimeInMSDynamic = DATEDIFF(millisecond, @StartTime, getdate())SET @StartTime = GETDATE()select TOP 1 * from TB_MASTER_CPM01 WHERE ORDER_DATE=@ORDER_DATE AND COMMIT_NO=@COMMIT_NO AND STATION_ID=@STATION_IDSELECT ExecutionTimeInMSQuery = DATEDIFF(millisecond, @StartTime, getdate())[/quote]FOR TEST you can add to table just one row with parameteres of variable, another columns you can add random..And next I used this command for better result [quote]CHECKPOINT; GO DBCC DROPCLEANBUFFERS; DBCC DROPCLEANBUFFERSDBCC FREEPROCCACHEGOselect plan_handle, creation_time, last_execution_time, execution_count, qt.textFROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text (qs.[sql_handle]) AS qt[/quote]but the background of this I dont understand very well.very thank you for response

Drop and recreate Constriants

Posted: 03 May 2013 01:39 AM PDT

HiI need to change the datatype of one of the table which has got lot of dependencies.Its in the prod so I want a query which can drop all the constraints (Primary Key,Foreign Key,Composite unique Key,unique key and default) in the database and recreate it.Kindly help!!:w00t:

SQL 2008 - Database users disabled - How to resolve and find reason

Posted: 03 May 2013 01:47 AM PDT

After upgrading to 2008, some of the users in master are shown disabled. Wht could be the reason and how to fix it, other than deleting and recreating the users?

Migration from SQL 2005 to SQL 2008 R2 using mirroring concept

Posted: 03 May 2013 01:11 AM PDT

I just have 1.5 yrs exp as a dba. The requirement is as below:Test environment - We have 15 user databases to be migrated from SQL server 2005 to SQL server 2008 R2 on a different machine.The plan is basically to use the concept of mirroring and do the activity (This is cos at the later point of time same has to be implemented on production box which is having 500 Db's)I have never configured mirroring too .. Can any one please help how to begin with..

Locking Question

Posted: 02 May 2013 10:31 PM PDT

Hi,I need to check if Row Level Locking is set for a sepcific server on which My SQL Server database resides.Is there a way I can check this please?

Need help with CASE

Posted: 02 May 2013 10:45 PM PDT

Hi, I have a select statement and i am selecting the results of the select statement as @clientNameNow I want to do a like on the @clientName, so if @clientName is LIKE _P_ then return 3 I am not sure if the following code is correct[code="sql"]DECLARE @clientName VARCHAR (60) SELECT DISTINCT @clientname = ClientName FROM tblClient WHERE ClientNumber IN (SELECT ClientNumber FROM tblSystem WHERE SystemNum = 30461) CASE WHEN @clientname like ('_p_') THEN RETURN 3 ELSE 1[/code]

how to check lastest firmware in windows Server 2003?

Posted: 02 May 2013 07:59 PM PDT

how to check latest firmware in windows Server 2003?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog