What more can I do to optimize this query? SELECT * FROM (SELECT `item`.itemID, COUNT(`votes`.itemID) AS `votes`, `item`.title, `item`.itemTypeID, `item`. submitDate, `item`.deleted, `item`.ItemCat, `item`.counter, `item`.userID, `users`.name, TIMESTAMPDIFF(minute,`submitDate`,NOW()) AS 'timeMin' , ...
System Specifications... Microsoft SQL Server Management Studio 9.00.4035.00 Microsoft Analysis Services Client Tools 2005.090.4035.00 Microsoft Data Access Components (MDAC) 2000.085.1132.00 (xpsp.080413-0852) Microsoft MSXML ...
'Order by' returns this result below 05 05/1-1 05/1-2 05/1-3 05/1-4 05/1-5 05/1-6 05/1-7 05/1 05/2-1 05/2-2 05/2-3 05/2-4 05/2 05/3 05/4 and this order below is OK 05 05/1 05/1-1 05/1-2 05/1-3 05/1-4 05/1-5 05/1-6 05/1-7 05/2 05/2-1 05/2-2 05/2-3 05/2-4 05/3 05/4 Is there a way to do this? ...
I have a query that takes too long and frequently times out. It's a proximity based zip code search table-value function. Is there anyway to index based on the query, so it doesn't have to recalculate all of these values every time? The postal code and zip code list combined is over a million rows. Here is the table function. Create F...
Hi, Currently I got this type of query generated by programmation (c#) INSERT INTO TableName (Field1, Field2, Field3) SELECT Field1, Field2, Field3 FROM TableName2 The problem is that the SELECT can have a result of many records (like a million), so it's take many times and the result is an connection timeout. Also, if I separate al...
We currently run a specific SQL script as part of our ANT deploy process. What we'd like to do is change this so we run all SQL scripts in a given directory. We can't figure out how to get this directory listing in ANT and iterate through the list and run each SQL script. Does anyone know how to do this? Note: we currently run the sq...
I have been tasked with moving a cube which is in SQL 2000 to SQL 2005. It would appear that the Dimensions and Measures are all coming from one table. Is this possible in SQL 2005 or do I need to restructure the data into multiple Dim/Fact tables, and is there any way to easily move a SQL 2000 cube into SQL 2005? ...
I have two tables involved in this query I need to create, and I'm not exactly sure how to join these two tables in order to update. I have a ITEM and CONSUMER_ITEMS table. The ITEM table has a distinct code for each item and a UPC code. I need to concatenate a string with the ITEM.UPC_CODE to CONSUMER_ITEMS.NEW_ITEM_CODE where CONSUMER...
I have the following SQL statement INSERT INTO A_Table (field1, field2) SELECT field1, field2 FROM B_Table WHERE field1 NOT IN ( SELECT field1 FROM A_Table); basically, I need to copy all the data from B_Table to A_Table, with a constrain: field1 from B_Table not existing in A_Table. I need to add this constrain since field1 ...
I've heard that "everyone" is using parameterized SQL queries to protect against SQL injection attacks without having to vailidate every piece of user input. How do you do this? Do you get this automatically when using stored procedures? So my understanding this is non-parameterized: cmdText = String.Format("SELECT foo FROM bar WHERE...
What are the differences between the classic transaction pattern in linq to sql like: using( var context = Domain.Instance.GetContext() ) { try { context.Connection.Open(); context.Transaction = context.Connection.BeginTransaction(); /*code*/ context.Transaction.Commit(); catch(Exception e){ con...
I am a noob when it comes to SQL syntax. I have a table with lots of rows and columns of course :P Lets say it looks like this: AAA BBB CCC DDD ----------------------- Row1 | 1 A D X Row2 | 2 B C X Row3 | 3 C D Z Now I want to create an advanced select statement that gives me this combined (pseudo SQLish here...
So, I have a table where StartDate and EndDate's are used to determine the activeness of a record. I thought of using NULLs to relieve the maintainers from having to manufacture some crazy dates for some of the records. For example, if NULL had defined as positive infinite when used in EndDate, the data maintainers wouldn't need to come ...
I have a problem writing a SQL statement for both Oracle and MS SQL server. I'd like to write the SQL so that it works in both. I don't want to have to mess around with setting a database type variable and switching I have a column of course names that have values like this: 9RA923.2008W 1223.200710 P0033330.200901 I w...
What is wrong with the statement below? I keep getting the following error message.... Server: Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'THEN'. update oildatasetstatus set oildatasetstatusid = case WHEN 5 THEN 16 WHEN 6 THEN 17 WHEN 7 THEN 18 WHEN 8 THEN 18 ...
For Example, is there any way to do something like the following: INSERT INTO table2 VALUES(SELECT x FROM table1 WHERE x > 5 LIMIT 4) ...
I have a database table that looks like: ForeignId: int Key: varchar Value: varchar Where ForeignId and Key constitute a unique primary key I can easily determine the total set of keys defined for a given set of document with SELECT DISTINCT [Key] FROM [Table] WHERE [ForeignId] IN (...) What I would like to do however is to further...
When using SQL, are there any benefits of using = in a WHERE clause instead of LIKE? Without any special operators, LIKE and = are the same, right? ...
I'm trying to learn advanced sql and how to use system queries (sql server). The below query is a little confusing. CREATE PROC dbo.ShowHierarchy ( @Root int ) AS BEGIN SET NOCOUNT ON DECLARE @EmpID int, @EmpName varchar(30) SET @EmpName = (SELECT EmpName FROM dbo.Emp WHERE EmpID = @Root) PRINT REPLICATE('-', @@NEST...
Hi all I have a horrid database I gotta work with and linq to sql is the option im taking to retrieve data from. anywho im trying to reuse a function by throwing in a different table name based on a user selection and there is no way to my knowledge to modify the TEntity or Table<> in a DataContext Query. This is my current code. publi...