I have a data set consisting of time-stamped values, and absolute (meter) values. Sometimes the meter values reset to zero, which means I have to iterate through and calculate a delta one-by-one, and then add it up to get the total for a given period. For example: Timestamp Value 2009-01-01 100 2009-01-02 105 2009-01-03 ...
Let's say that I have an article on a website, and I want to track the number of views to the article. In the Articles table, there's the PK ID - int, Name - nvarchar(50), and ViewCount - int. Every time the the page is viewed, I increment the ViewCount field. I'm worried about collisions when updating the field. I can run it in a sp...
In an attempt to make the best index choices for my database, I've noticed some particular behaviour which I'd like to address. Observe the following table and corresponding index (SQL Server 2005): CREATE TABLE demo ( id INT PRIMARY KEY IDENTITY, name NVARCHAR(50) NOT NULL, password BINARY(20) NOT NULL ); CREATE NONCLUSTERED INDEX...
Is it possible to call a user-defined function in TSQL without the scoping qualifier. I want to call select myfunc(var) and not select dbo.myfunc(var) select myschema.myfunc(var) It's possible in every other DB that I've ever worked with (7 others) it has to be possible in tsql too. If I'm signed into my DB/schema I don't have to ...
I am new to Sql Server. One of the things that I am seeing is that I am writing a query in the top window and viewing results in the bottom window. The problem is that the result of the query takes up a lot of space vertically.. some queries take up a lot of space vertically. I want to see both the query and the query result side by...
I have a Select like the one below in a stored procedure (shortened for brevity). @param is a parameter to the stored procedure which can be NULL. SELECT name FROM Table1 WHERE EXISTS (select .... from table2 Where param = @param AND ... AND ...) AND ... AND ... I would like the EXISTS statement (the part in bold) to be used only whe...
I have a table (SQL Server 2005) with the following columns: State: - StateId INT PRIMARY KEY - VehicleId INT - ValidFromDate DATETIME For each VehicleId there can be several rows in this table. I want to select the currently valid states (with a date parameter). There can be at most one valid state for each vehicle (the one with the ...
Hello, So I've got this database that helps organize information for academic conferences, but we need to know sometimes whether an item is "incomplete" - the rules behind what could make something incomplete are a bit complex, so I built them into a scalar function that just returns true if the item is complete and 0 otherwise. The pr...
Can I use a Criteria to execute a t-sql command to select the max value for a column in a table? 'select @cus_id = max(id) + 1 from customers' Ta Ollie ...
When and Why does some one decide that they need to create a View in their database? Why not just run a normal stored procedure or select? ...
I'm trying to schedule a long T-SQL script. However, the script gets cut off when pasted into the text box. I googled the problem and there is a 3200 character limit. What do you recommend I do to solve this? Create a stored procedure and run that as a scheduled job? Is there a better option? If it helps, here is the script. I...
How do you combine multiple select count(*) from different table into one return? I have a similar sitiuation as this post but I want one return. I tried Union all but it spit back 3 separate rows of count. How do you combine them into one? select count(*) from foo1 where ID = '00123244552000258' union all select count(*) from foo2 ...
I'm trying to use a view to create an ADO.NET entity using a view. However, that view does not have a single column that is NOT NULL. One thing that occurred to me was to create a NOT NULL column on the view to use as a 'primary key' for the view. This worked, but the field is still reported as NULL. Is there a way to force or trick SQ...
Hello, I have the following T-SQL Pivot query. SELECT AccountNumber, EventID, CreateDate, [ITEMBOOK] AS ITEMBOOK, [POSTER] AS POSTER FROM (SELECT ProductID, Quantity, EventID,AccountNumber,CreateDate FROM #tmpStartupItems) ps PIVOT ( SUM (Quantity) FOR ProductID IN ( [ITEMBOOK], [POSTER]) ) A...
Using Hierarchy data type on SQL 2008. Nodes in my hierarchy go like this: value node 36 /8/1/ 38 /8/2/ 34 /8/3/ 40 /8/4/ 42 /8/5/ 44 /8/6/ 46 /8/7/ 48 /8/8/ I'd like to rearrange nodes so that /8/3/ and /8/1/ switch places. Any idea on how to do this? Only idea that I have so far is that I load all nodes o...
I have a bunch of NVARCHAR columns which I suspect contain perfectly storable data in VARCHAR columns. However I can't just go and change the columns' type into VARCHAR and hope for the best, I need to do some sort of check. I want to do the conversion because the data is static (it won't change in the future) and the columns are indexe...
I have a query to get duplicate data with some extra condition but I feel that it is not fast enough. Any solution to make this query faster? v_listing contains big information SELECT DISTINCT code, name, comm, address, area FROM v_listing t1 WHERE EXISTS (SELECT NULL FROM v_listing t2 WHERE t1.comm = t2...
I have a table that will have 500,000+ records. Each record has a LineNumber field which is not unique and not part of the primary key. Each record has a CreatedOn field. I need to update all 500,000+ records to identify repeat records. A repeat records is defined by a record that has the same LineNumber within the last seven days of i...
I suppose this is quite a common SP present in socialnetworks and community type websites. I have this SP that returns all of a user's friends on their 'friends' page order by those currently online, then alphabetically. It's taking quite a while to load and I am looking to speed it up. I remember reading somewhere on SO that breaking...
Hi, Stored Procedures in SQL 2005 - with field type NText Im Writing a stored procedure to tidy up some data before importing it into Microsoft CRM. So far all works fine. However i need to do a case statement on a nText Field. It needs to check this field against about 3 or 4 text values and set a new field (already in the destina...