Original Rcds Vs IntutiveRecords
OrName,OrAmt,IntName,IntAmt
ABC,100,ABC,110
for this reason i use
Select A.name,A.Amt,B.name,B.Amt
from A
leftOuterjoin B -- There are many reasons why i do a left outer join
because i may not have the record in the b side at all
but this is causing a very big performence issue as this view is very
huge.31,000 records on each side is taking 2 minits to work.
suresh writes "Hi This is suresh, I want to know some information about Dynamic sql what its performense wise? Difference bitween the proc and dynamic sql? Advantages of dynamic sql?"
SELECT REC.RECSKU, REC.RECPRL, max(REC.RECKEY) AS latest_reckey FROM dbo.REC, dbo.LATESTREC_V WHERE REC.RECSKU = LATESTREC_V.RECSKU AND REC.RECPRL = LATESTREC_V.RECPRL AND sysdb.ssma_oracle.to_char_date(REC.RECSDTR, 'YYYYMMDD') + REC.RECSTM = LATESTREC_V.LATEST_DATE_TIME GROUP BY REC.RECSKU, REC.RECPRL Note that it refers to view LATESTREC_V, which is defined below ( the SQL ):
SELECT REC.RECSKU, REC.RECPRL, max(sysdb.ssma_oracle.to_char_date(REC.RECSDTR, 'YYYYMMDD') + REC.RECSTM) AS latest_date time FROM dbo.REC GROUP BY REC.RECSKU, REC.RECPRL Now, there already is a key on the REC table, columns RECSKU and RECPRL. Adding that index sped things up quite a bit. But this setup is still very enefficient. I cannot apply the indexed view concept to LATESTRECKEY_V or LATESTREC_V because they use "max". I suppose I could apply it to the first view, but since that view depends on the other 2 views, I am not convinced it will improve my performance. Any ideas on improving such a situation? Thanks for your time.
--Table 1 "Employee" CREATE TABLE [MyCompany].[Employee]( [EmployeeGID] [int] IDENTITY(1,1) NOT NULL, [BranchFID] [int] NOT NULL, [FirstName] [varchar](50) NOT NULL, [MiddleName] [varchar](50) NOT NULL, [LastName] [varchar](50) NOT NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [EmployeeGID] ) GO ALTER TABLE [MyCompany].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_BranchFID] FOREIGN KEY([BranchFID]) REFERENCES [myCompany].[Branch] ([BranchGID]) GO ALTER TABLE [MyCompany].[Employee] CHECK CONSTRAINT [FK_Employee_BranchFID]
-- Table 2 "Branch" CREATE TABLE [Mycompany].[Branch]( [BranchGID] [int] IDENTITY(1,1) NOT NULL, [BranchName] [varchar](50) NOT NULL, [City] [varchar](50) NOT NULL, [ManagerFID] [int] NOT NULL, CONSTRAINT [PK_Branch] PRIMARY KEY CLUSTERED ( [BranchGID] ) GO ALTER TABLE [MyCompany].[Branch] WITH CHECK ADD CONSTRAINT [FK_Branch_ManagerFID] FOREIGN KEY([ManagerFID]) REFERENCES [MyCompany].[Employee] ([EmployeeGID]) GO ALTER TABLE [MyCompany].[Branch] CHECK CONSTRAINT [FK_Branch_ManagerFID]
--Foreign IDs = FID --generated IDs = GID Then I try a simple single row DELETE
DELETE FROM MyCompany.Employee WHERE EmployeeGID= 39
Well this might look like a very basic error: I get this Error after trying to delete something from Table €œEmployee€?
The DELETE statement conflicted with the REFERENCE constraint "FK_Branch_ManagerFID". The conflict occurred in database "MyDatabase", table "myCompany.Branch", column 'ManagerFID'.
Yes what I€™ve been doing is to deactivate the foreign key constraint, in both tables when performing these kinds of operations, same thing if I try to delete a €œBranch€? entry, basically each entry in €œbranch€? and €œEmployee€? is child of each other which makes things more complicated.
My question is, is there a simple way to overcome this obstacle without having to deactivate the foreign key constraints every time or a good way to prevent this from happening in the first place? Is this when I have to use €œON DELETE CASCADE€? or something?
Is there a format function in MDX which will help me solve the following scenario:
One of my measures is Net Sales and I have tried all different formats in Analysis Services so that it shows without the decimal places i.e. instead of showing the Net Sales for any selected combination of Dimensions as 123,456.04, I want to show it as 123,456
When I browse the cube (in Analysis Services), I can meet my requirement. However, in my front-end (Microsoft Data Analyzer), I am still getting it as 123,456.04 (even when the same in Analysis services, i.e. when I browse the cube, is being shown as 123,456).
I do not have much choice at the moment and am stuck with Microsoft Data Analyzer and unfortunately have not been able to solve this :(
Can someone think of a solution/workaround/use of MDX which will help me get the results being displayed without the decimal. Is there a format function that I can use in MDX and how to use it???
Hello,What is uniqueidentifier as a data type?Also what is the data type for setting unique STRINGS ((nchar,nvarchar), for example to be used for emails and user names in a userregistration system).SQL Server does not allow me set primary keys for columns where datatypes are not INT.Thanks in advance.
I've been working on a performance review web application (i.e., employee's annual reviews done via the web). In the process of creating the application I've been teaching myself .NET - maybe not the best way to do it but I've been learning a lot. However, I still feel like I'm not doing something right.On each Page_Load I'm doing database work with a data reader: Reading the data in, displaying it, letting the user add, edit, or delete it, etc. So every Page_Load code behind looks like this: string sql = "SELECT UserID, Passwd, RecID, Name FROM UserList";
SqlConnection myConn = new SqlConnection("Server=BART; Database=WSSD; User ID=sa; Password=wss1231"); SqlCommand cmd = new SqlCommand(sql, myConn); SqlDataReader dr;
myConn.Open(); dr = cmd.ExecuteReader(); And so forth and so on. Now since I re-use this code again and again - I imagine it's a good idea to implement my connection code in a class that I can re-use easily. But I have no idea where to start on something like that. What can I say? I'm a newb. A push in the right direction would be great.
I have the following tablestblUserdatausercode username firstname lastname5 peter peter smith11 john433 john doe15 simonsays Simon SmithtblEventsID postedbycode title eventtext createdate1 5 woodstock 'oldies' 12/12/20082 11 love parade 'dance all night 1/1/20083 11 spring break 'great party' 2/2/2006tblEventVisitorsusercode eventid5 15 311 111 211 3As you can see User John433 is going to 3 events.But I only want to select the one that has the first upcoming startdate bigger than now: getdate()Desired output would be:username firstname lastname eventid title eventtext eventdatepeter Peter Smith 1 woodstock 'oldies' 12/12/2008john433 john doe 2 love parade 'dance all night 1/1/2008simonsays Simon Smith NULL NULL NULL NULLHow can I make such a selection? (perhaps see this thread for similar info: http://forums.asp.net/t/1201266.aspx)Thanks!
I tired the above tips but it appears that if I try to do this in my DTS (which appends data),the logic of the DTS will change. A single arrow also gets added whichI think represents a simple mapping/transformation rather than a append. To clarify my point, please note the attached image which represents that the data is being appended (due to the many sided arrows pointing to the source and destination - visible under the Transformations tab of my DTS).
Sincerely hoping that my post is clear, can someone help me find how to make changes in a DTS (which appends data) and ensuring that thelogic remains the same i.e. it should append data.
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4) RETURNS text AS $BODY$ DECLARE rec record; str text; comstr text; BEGIN str := ''; comstr := ''; FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP str := str || comstr || rec.classname; comstr := ','; END LOOP; RETURN str; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
I am backing up the database using the Database Maintenance Plan.
Everything is in place. I want to save the file name in the format yyyy-mm-dd_finbck.bak. how can i assign the date from here itself ? this should be done through the DB maintenance plan only.
is it possible ? can we assign the date to the backup file through the DB maintenence plan ?
Could anyone guide me to prepare for the Interview in SQL SERVER 2005.
I have studied the relevant things from background knowledge from the SQL Server books. But is there any specific areas in which I have to concentrate for the sake of Interview?
We had been running SQL Server without any control of security (sincethe company is very small -100 employees). All of us know the adminpassword and has been accessing the database as admin. Our databaseserver crashed due to hardware failure twice last month and we lost alot of important data. Now the management is taking the control ofserver access seriously.SQL Enterprise manager is installed on many PCs and any one can deleteany database with a right click.My question is:1. Can the enterprise manager be installed on client's PC with alimited right (or as a user not as admin)?We need to limit the user's access of using the Enterprise Manager.In other words, how can we set this up for different users.2. How can we keep running SQL Server if one server fails?Clustering or Replication or Mirroring? OI would highly appreciate if you could direct me to any website orresources on how to set up security of SQL Server (2000 with the latestservice pack).Thanks a million in advance.Best regards,Mamun
Any idea the cause of this error. I got it while trying to execute a stored proc (in sql 2005). I had just restored database and below error is stucking me to move ahead. Any help please
Error invoking method 'ExecuteQuery' for transaction (Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding)
I have a function written in postgresql that I want to create in sql server (UDF). After effort of full day I am seding this request to please help me, here is the function:
CREATE OR REPLACE FUNCTION fn_comma_env(int4) RETURNS text AS $BODY$ DECLARE rec record; str text; comstr text; BEGIN str := ''; comstr := ''; FOR rec IN SELECT class.classname FROM hostenv, class WHERE hostenv.classid = class.classid AND (hostenv.hostid = $1) LOOP str := str || comstr || rec.classname; comstr := ','; END LOOP; RETURN str; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE;
I have a data including "year", "month" and "day" columns and I'm using these columns as parameter to create a report. I'm using the parameters successfully in my report. I import all possible year-month-day combinations but in my data there are some unmatched combinations. (For ex, for 2007 there's just last 3 months of the year, and some days are missing) To make the report more affective, I wanna make the parameters dynamic; when I choose 2007 in year-combobox I wanna see just the related months (not all 12) and the related days after I choose the month. Is it possible in reporting services?
On the web form I have textboxes for the Purchase No., date and part description, and a drop down list for the machine name. How do I insert them into the different tables?
I've just start learning ASP.NET and I am using Web Matrix for this. The examples I've seen so far only shows how to insert into a single table.
Locally I have a table named 'countrydetails' in sql server which is created by dbo. I create asp.net page trying to show the details of the country. I write a query in vb.net page and build the project and try to run it, its giving me results.
The same vb.net project is uploaded and put online and when I try to see the details of the country online inspite of having records i m getting no results. In this case the table which was created is having username other than dbo.
I want my query written in vb.net page to run irrespective of the different users who have the database table. The page should run for both dbo and other users also. How do I achieve this? Please advise.
Here is the first table:CREATE TABLE "Games"("ID" int,"Title" varchar(30),"Popularity" int)Here is the second table:CREATE TABLE "Related"("ID" int,"rID" int)Here is the data for the first table:INSERT INTO Games (id, title, popularity) VALUES (1, 'Tag Dodgeball', 10)INSERT INTO Games (id, title, popularity) VALUES (2, 'Freeball', 10)INSERT INTO Games (id, title, popularity) VALUES (3, 'Doctor Dodgeball', 10)INSERT INTO Games (id, title, popularity) VALUES (4, 'Kickball', 8)INSERT INTO Games (id, title, popularity) VALUES (5, 'Fooseball', 8)INSERT INTO Games (id, title, popularity) VALUES (6, 'Basketball', 7)INSERT INTO Games (id, title, popularity) VALUES (7, 'Knockout', 6)Here is the data for the second table:INSERT INTO Related (ID, rID) VALUES (1,2)INSERT INTO Related (ID, rID) VALUES (2,1)INSERT INTO Related (ID, rID) VALUES (1,3)INSERT INTO Related (ID, rID) VALUES (2,3)INSERT INTO Related (ID, rID) VALUES (3,1)INSERT INTO Related (ID, rID) VALUES (3,2)INSERT INTO Related (ID, rID) VALUES (6,7)INSERT INTO Related (ID, rID) VALUES (7,6)Now, lets say a person wants to grab the top three results:SET ROWCOUNT 3Select * from Game ORDER BY PopularityResult Set:1 Tag Dodgeball 102 Freeball 103 Dr. Dodgeball 10But, what if this person doesn't want to play three related games in a row? This is my dilemma. How do I get the following result set:1 Tag Dodgeball 102 Kickball 83 Fooseball 8The related table tells which games are related to which, but how do I get the select statement to realize that it should not select any games that are related once it already has one in the result set?I can't use distinct, b/c if I do, any games which don't have a relationship are eliminated, as their rID is null.Pseudo-Code might look something like this:SET ROWCOUNT 3Select * from Game ORDER BY Popularity (where ID !=rID)Please help. I've banging my head against this one for quite some time.Respectfully,David.
Hello EveryOne,I have a program which insert the Data into table Fine. But the problem is that whenever expecption is raised the auto increament id is increases..i want that user enter the record and without error the auto increament id is 1 and when second user enter the record on table and exception raised and next time he entered again then his record auto increament id is 3 why? But record is not entered only the expecption is raised.How to resolve this problem to make cosistency in record of other tables.? Help me
Plz help me out making a SELECT for my imagegallery. Table one: [albums] contains the fields [albumId], [albumTitle] and [albumDesc] - the name says it all. Table two: [images] contains the fields [imageId], [imagePath] and forign key [albumId} In my albumspage i would like to show all albums listed in [albums] (the easy part) AND for every album i would like to count how many images there's related to the particular album AND a random retrieve a random [imagePath] To extract the albums something like this works: SELECT * FROM [albums] ORDER BY [albumTitle] To count the images in a sertain album this works: SELECT COUNT(*) AS imagesCount WHERE [albumId] = ...the albumId from the album-select And to retrieve a random imagePath this works fine: SELECT TOP 1 [imagePath] FROM [images] WHERE [imageId] = NEWID() -But how do i combine them so that i can retrieve the data in one request? Suppose theres going to be some JOIN and group but i cant figure it out. Please help me out.
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed. This is the error i get every now and then when i attempt to browse to a page. It seems that i am getting this error as its trying to connection to a sql express database??? Only i have set my project up to use sql server 2000 and i am using that DB but every now and then i get this error. Im not sure what else i need to do? Any ideas?
I've created a DTS package with 2 connections to 2 servers. The operation transfers a db in server1 to server2. I have to set the sa password for each of the server (when setting up the connections). Our policy is to change the server password regularly. This creates problem as I have to change the password setting at each of the connections. If there are 100 connections, I have to change it 100 times. Is there a way to avoid this so that we can avoid this tedious work. TIA
Hi, I don't use SQL much but I need to know how to form the following query against a SQL table. I have a single table with two columns - CurrentPage and LastPage.
I need to write SQL to perform a number of iterations of a particular insert into another table, depending upon the count from CurrentPage to LastPage i've put the following Pseudocode together, but need help implementing this.
Declare counter Set counter = currentpage If counter > lastpage Goto task exit loop Else perform task - (insert string into table) Increment counter and loop
I've been playing / reading up, but i'm getting nowhere fast -
DECLARE @Count int DECLARE @Current string DECLARE @Last string
SET @Current= dbSL.tblPaging_colCurrentPage SET @Count= dbSL.tblPaging._colPagingCount SET @Last= dbSL.tblPaging._colLastPage WHILE @Count <= dbSL.tblPaging._colLastPage
BEGIN PRINT 'Hello World' SET @Count = @Count +1 END
Can a table be created within a trigger? I looked in books online and found the list of transact-sql statements that are not allowed in a trigger. Create table is not one of them, so I assume that it can be done within a trigger. Correct? Thanks for your help.
I have diferent jobs scheduled in the system but I can´t find which Package is launched by the job. I only have this information: (double click over the job, steb tab, modify button for any job step, in the command text window)
