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.
...
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...
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...
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...
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 *...
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...
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?
...
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 ...
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...
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...
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? 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?
...
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...
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...
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...
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 ...
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...
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.
...
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...
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...