tsql

How to get distinct values from columns?

I have a query that returns a result set similar to the one below A | B | C | D -----|----|----|----- 1 abc | d0 | e0 | true 2 def | d0 | e1 | true 3 ghi | d0 | e2 | true 4 jkl | d1 | e1 | true 5 mno | d2 | e2 | false In Column A each value is unique. but column B,C,D have some duplicate values. ...

SQL Server 2008 Empty String vs. Space

I ran into something a little odd this morning and thought I'd submit it for commentary. Can someone explain why the following SQL query prints 'equal' when run against SQL 2008. The db compatibility level is set to 100. if '' = ' ' print 'equal' else print 'not equal' And this returns 0: select (LEN(' ')) It appears to b...

Database transaction only partially committing

I've got a T-SQL stored procedure running on a Sybase ASE database server that is sometimes failing to commit all of its operations, even though it completes without exception. Here's a rough example of what it does. BEGIN TRANSACTION UPDATE TABLE1 SET FIELD1 = @NEW_VALUE1 WHERE KEY1 = @KEY_VALUE1 IF @@error <> 0 OR @@rowcount <> 1 BE...

T-SQL: is it possible to specify condition in Count()?

Is it possible to specify a condition in Count()? I would like to count only the rows that have for example "Manager" value in Position column. Edit: please read carefully, I want to do it IN count statement, not using WHERE; I'm asking about it because I need to count both Managers and Other in the same select (something like Count(Pos...

VB6 SQL 2005 Database Index Question

I have a VB app that accesses a sql database. I think it’s running slow, and I thought maybe I didn’t have the tables propery indexed. I was wondering how you would create the indexes? Here’s the situation. My main loop is Select * from Docrec Order by YearFiled,DocNumb Inside this loop I have two others databases hits. Select *...

Adding a Column Programmatically to a SQL Server database

I've taken over an ASP.NET application that needs to be re-written. The core functionality of this application that I need to replicate modifies a SQL Server database that is accessed via ODBC from third party software. The third-party application creates files that represent printer labels, generated by a user. These label files dire...

SQL Conditional on Bit

Why does this.. DECLARE @SkyBlue Bit SET @SkyBlue = 1 IF @SkyBlue Select 'the sky is blue!' ELSE Select 'the sky is not blue!' Produce this "An expression of non-boolean type specified in a context where a condition is expected, near 'Select'." And is there a Boolean type in SQL2008? ...

Creating SQL UPDATE statements on the fly

I am in the midst of updating data in multiple tables. Currently I have a table that has one field, "sources", that is just a list of all tables that include the field "itemid". I also have a table that has 2 fields, "itemid" and "olditemid". In TSQL, I would like to iterate through the sources and create the update statements on the ...

How to delete records in SQL 2005 keeping transaction logs in check

I am running the following stored procedure to delete large number of records. I understand that the DELETE statement writes to the transaction log and deleting many rows will make the log grow. I have looked into other options of creating tables and inserting records to keep and then Truncating the source, this method will not work for...

Set Variable Values

I'm sure this is something simple, but I can't seem to figure it out. Why doesn't this code work? DECLARE @FirstSaturday DATETIME DECLARE @ENDDATE DATETIME SELECT @FirstSaturday = min(RED1.DATE) FROM REDFRIDAYDATES..TBLREDFRIDAYALLDATES RED1 WHERE Period = 9 AND year = 2009 SELECT CASE WHEN getdate() < @FirstSaturday THEN set @ENDD...

TSQL inter-table updates

I have two tables, a contacts table and an addresses table. The addresses table contains a contact id, and 4 address lines. I wish to update the contacts table with the information from the addresses table. For simplicity's sake let the tables be as follows: addresses( . contact int not null, . address1 varchar(32) not null, . address2 ...

what is the difference (system resource wise) between using views and temporary tables?

what is the difference (system resource wise) between using views and temporary tables? I have a complex report that will be built from numerous tables. I am trying to determine if I should use a series of views or temp tables (SQL 2008). Is there a difference? ...

I need a slow query on AdventureWorks (SQL 2005)

Hi everyone, As an exercise (read:interview question) in index optimisation, I need a query which is slow on the standard AdventureWorks database in SQL2005. All the queries I've tried take about 1 second and I would prefer to have a query which takes multiple seconds so that it can be optimised effectively. Can anyone here create such...

Problem with SQL subquery using Top() on Linked Server

Hi, I am using SQL Server 2008 and I have the following SQL script: Select o.CustomerId as CustomerNoId, OrderValue, OrderDate From dbo.Orders as o Inner Join ( Select Top (10) CustomerId From dbo.Customers where Age < 60 ) As c On c.CustomerId = o.CustomerId This works as desired when used with dbo.Customers and dbo.Orde...

SQL "select where not in subquery" returns no results

Disclaimer: I have figured out the problem (I think), but I wanted to add this issue to Stack Overflow since I couldn't (easily) find it anywhere. Also, someone might have a better answer than I do. I have a database where one table "Common" is referenced by several other tables. I wanted to see what records in the Common table were o...

SQL Aggregrates without a GROUP BY

I'm trying to determine if there is a way to accomplish this elegantly. I have a query: SELECT TASK.*, PROJCOST.act_cost, PROJCOST.target_cost FROM task LEFT OUTER JOIN projcost ON task.task_id = projcost.task_id I just found out that PROJCOST.target_cost and act_cost are not 1 to 1 and need to be summed. I understand how to use SUM ...

Conditional insert in SqlServer Compact Edition

Is there a way to do a conditional insert in the compact edition? I've tried two ways that I think would work on SqlServer: INSERT INTO CUSTQUOTE (QTE_ID) VALUES (1) WHERE EXISTS(SELECT * FROM JOB WHERE JOB_NUMBER = 'EW090800345') There was an error parsing the query. [ Token line number = 2,Token line offset = 1,Token in error = WHERE...

Best way to represent a color in a SQL Database?

If I am using .Net and SQL Server 2008, what is the best way for me to store a color in the database, should I use ToString or convert it to an integer, or something else? Edit: All I want from the colour is to be able to retrieve it and draw something on the screen in the specified colour. I don't need to be able to query against it. ...

Check constraint on table lookup

Hi, I have a table, department , with several bit fields to indicate department types One is Warehouse (when true, indicate the department is warehouse) And I have another table, ManagersForWarehouses with following structure: ID autoinc WarehouseID int (foreign key reference DepartmentID from departments) ManagerID int (foreign key r...

Approximating Page Views Per Tag (or Tag Group) Per Month with Limited Data?

Using the Stack Overflow public data dump, I've created three simple tables: Questions (Question_Id, View_Count, Creation_Date) Tags (Tag_Name) QuestionTags (Question_Id, Tag_Name) The Questions table has hundreds of thousands of rows with Creation_Date spanning from a year ago to today. Looking over the data, there are two notable t...