Given the following table in SQL Server 2005: ID Col1 Col2 Col3 -- ---- ---- ---- 1 3 34 76 2 32 976 24 3 7 235 3 4 245 1 792 What is the best way to write the query that yields the following result (i.e. one that yields the final column - a column containing the minium...
I would like to log changes made to all fields in a table to another table. This will be used to keep a history of all the changes made to that table (Your basic change log table). What is the best way to do it in SQL Server 2005? I am going to assume the logic will be placed in some Triggers. What is a good way to loop through all th...
Here is my query: Select Top 10 CS.CaseStudyID, CS.Title, CSI.ImageFileName From CaseStudy CS Left Join CaseStudyImage CSI On CS.CaseStudyID = CSI.CaseStudyID And CSI.CSImageID in( Select Min(CSImageID) -- >not really satisfactory From CaseStudyImage Group By CaseStudyID ) Order By CS.CaseStudyID ASC Instea...
I am trying to convert some of my stored procedures to Linq and am having problems with the following Transact-Sql statement: Select Year(p.StartDate) As Year, (Select Sum(t.Units) From Time t Where Year(t.TransactionDate) = Year(p.StartDate)) As Hours, (Select Sum(i.Price) From Invoice i Where Year(i.CreatedDate) = Year(p....
If UNION ALL is an "addition" in T-SQL. What is the equivalent of subtraction? For example, if I have a table PEOPLE and a table EMPLOYEES. And I know if I remove EMPLOYEES records from PEOPLE I will be left with my companies "CONTRACTORS". Is there a way of doing this that is similar to UNION ALL? One where I don't have to specify any ...
How can I get a query which uses an OR in the WHERE clause to split itself into two queries with a UNION during compilation? If I manually rewrite it, the query using the UNION is 100x faster than the single query, because it can effectively use different indices in each query of the union. Is there any way I can make the optimizer use...
I have the following table and data in SQL Server 2005: create table LogEntries ( ID int identity, LogEntry varchar(100) ) insert into LogEntries values ('beans') insert into LogEntries values ('beans') insert into LogEntries values ('beans') insert into LogEntries values ('cabbage') insert into LogEntries values ('cabbage') insert...
Are there any programs that will allow you to follow a sql transaction through to it's end? For instance, say I've inherited a rather complex sql database with a data dictionary. The data dictionary is pretty good, but not as good as say, SQL Doc. I've taken a look at Red Gate's Dependency Tracker and, while that does a very good job ...
If I have the following nvarchar variable - BTA200, how can I extract just the BTA from it? Also, if I have varying lengths such as BTA50, BTA030, how can I extract just the numeric part? ...
I have two fields in a table. One contains values such as BTA, BEA, REA. The other contains values such as 1,2,63,103. I want to combine the 2 fields so they look like this BTA001, BTA002, BTA063, BTA103. Notice how if the numbers are not 3 characters in length, I want to pad some 0's to the left of the number to make it equal to 3. ...
I need a simple SQL to accomplish the below: Problem: When a petrol bunk runs out of fuel, the admin makes note of the DateTime (RunOutDate) when it ran out of fuel and notes also the DateTime (ResupplyDate) when the fuel supply was back on. I need to create a report on how many days the bunk ran out of fuel. eg. 1/1/1 10:10 to 1/1/...
I work at a company that has some very non-standardized SQL conventions (They were written by Delphi Developers years ago). Where is the best place that I can find SQL industry standard convention definitions that are most commonly used? ...
This code involves a recursive Stored Procedure call and a "not so great" method of avoiding cursor name collision. In the end I don't care if it uses cursors or not. Just looking for the most elegant approach. I'm mainly going to use it as a simple method to track down Stored Proc hierarchies (without buying a product). I tried cursors ...
Below is a stored procedure to check if there is a duplicate entry in the database based upon checking all the fields individually (don't ask why I should do this, it just has to be this way). It sounds perfectly straightforward but the SP fails. The problem is that some parameters passed into the SP may have a null value and therefore...
I have a table that represents a series of matches between ids from another table as follows: CREATE TABLE #matches ( asid1 int, asid2 int ) insert into #matches values (1,2) insert into #matches values (1,3) insert into #matches values (3,1) insert into #matches values (3,4) insert into #matches values (7,6) insert into #matches v...
I have a column that has fields such as C5, C6, C3, CC, CA, CD, etc. I want to be able to find all the columns that have a letter and then a number such as C5 and C6. I know if I do: SELECT * FROM TABLE WHERE FIELD LIKE 'C%' It will return CC,CA,CD as well, but I don't want that, I want to just return the ones numbers. I also wan...
Can someone explain the use of DBCC DROPCLEANBUFFERS together with the CHECKPOINT operator and provide an example? I understand it is useful to test performances running before your queries but I don't fully grasp it. Also I have no clue about the use in combination with the CHECKPOINT operator. If I execute it before my queries they t...
I was looking at sql inner queries (bit like the sql equivalent of a C# anon method), and was wondering, can I return more than one value from a query? For example, return the number of rows in a table as one output value, and also, as another output value, return the distinct number of rows? Also, how does distinct work? Is this based...
print("select CustomerNo, CustomerName, Address, City, State, Zip, Phone, Fax, ContactName, Email from Customers where CustomerName like '%field%'"); Hi all. This is a simple question but I wasn't able to figure since I'm pretty new to tsql and sql in general. I use the above store procedure to do search. My question is for ...
I am using .NET 2.0 and SQL Server 2005. For historical reasons, the app code is using SQLTransaction but some of the stored procedures are also using T-SQL begin/commit/rollback tran statements. The idea is that the DBTransaction can span many stored procedures, which each individual sproc controls what's happening in its scope - in eff...