I have a "history" table where I log each request into a Web Handler on our web site. Here is the table definition: /****** Object: Table [dbo].[HistoryRequest] Script Date: 10/09/2009 17:18:02 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[HistoryRequest]( [HistoryRequestID] [uniqueidentifier] N...
If I have an update statement like this: UPDATE Message SET Subject = Subject ...will SQL Server actually perform an update or is it smart enough to recognize that no update is really needed? ...
Hi, I have a table: Message (MessageID int, Subject nvarchar(100), Body nvarchar(max)) After a message is being updated on UI, I call a stored proc to update that table. In some cases user might update just subject, in other cases just body. I want this stored proc to only update what has changed, so I'm also passing flags showing wh...
I have the following table: memberid 2 2 3 4 3 ...and I want the following result: memberid count 2 2 3 1 ---Edit by gbn: do you mean 2? 4 1 I was attempting to use: SELECT MemberID, COUNT(MemberID) FROM YourTable GROUP BY MemberID ...but now I want find which record which ...
Are there any differences between following two indexes? IDX_IndexTables_1 IDX_IndexTables_2 If there are any, what are the differences? create table IndexTables ( id int identity(1, 1) primary key, val1 nvarchar(100), val2 nvarchar(100), ) create index IDX_IndexTables_1 on IndexTables (val1, val2) GO create index IDX_IndexTab...
How do you look up a list of language IDs available within SQL Server? I am specifically looking if there are any views within sys schema. ...
I have this query: SELECT COUNT (DISTINCT CUSTOMER_ACCOUNT.ID) AS NUMBER_OF_ACCOUNTS FROM CUSTOMER_ACCOUNT INNER JOIN ACCOUNT ON CUSTOMER_ACCOUNT.ACCOUNT_ID=ACCOUNT.ID INNER JOIN CUSTOMER_ACCOUNT ON LOAD_ACCOUNT_DETAIL_0.ID = CUSTOMER_ACCOUNT.ID WHERE Convert(datetime, convert(char(10), [CUSTOMER_ACCOUNT].CREATED_ON, 101)) BETWEEN ...
I have this query in SQL Server 2005: SELECT J.JobID, dbo.tblCustomers.Name AS CustomerName, J.CustomerJobNumber, J.JobName, (CASE WHEN [tblCustomers].[CoreCust] = 0 THEN 'AUXILIARY' ELSE 'CORE' END) AS Department, J.JobStatusID, dbo.tblJobTypes.JobType FROM dbo.tblJobs (NOLOCK) AS J IN...
I have a tables like the following CREATE TABLE Company ( Id INT ) CREATE TABLE CompanyNumbers ( CompanyId INT, NumberText VARCHAR (255) ) What I want as an output is this in pseudo code Give me all the numbers for company A as a single comma separated string, if the string contains more than 150 numbers output another row with next...
In SQL Server is there a way to have an output parameter return the total number of records (for paging) in a parameterized query? ...
I need to write a valid T-SQL query version of the following pseudo-code: select * from newTable where [name] like in ( select [name] from oldTable ) I'm not sure how to go about this. Any help (even directing me to an existing question) would be great. Thanks! Edit: Per some comments I will clarify this particular case. The t...
I'm just doing some stat collection on multiple servers, and as a test I'm working with my machine (Machine A) and another machine (Machine B) on the local network. My Machine (A) is collecting all the information in the staging table from the other Machine (B). I have a sp that runs and dynamically creates something like this: exec (...
I need to calculate all Employees that have X number of consecutive absences within a date range in SQL. We have an Absences Table with 1 record for each day an employee is absent and a Calendar Table with the work days for the year. tblAbsences EmployeeID int AbsenceDate datetime tblCalendar WorkDay datetime Does anyone have any ...
I have data formatted "2009-07-17T00:00:00-05:00" in varchar variable. How can I convert this data to datetime field in MS SQL server using query or TSQL? ...
I have a TSQL Query that does something like this: SELECT SUM(s.Amount) as TotalSales, p.ProductName FROM SALES s INNER JOIN Product p ON s.ProductID = p.ID GROUP BY p.ProductName The resulting output is TotalSales Product ----------------------- 123.45 Apples 234.56 Oranges 345.67 Grapes What I would like to do is...
I have a MS SQL Query that is pulling data via from a remote server. The data that I'm pulling down needs to be filtered by a date that is determined at run time.. When I run the query like this: SELECT * FROM SERVER.Database.dbo.RemoteView WHERE EntryDate > '1/1/2009' then the filter is applied remotely... However, I don't actua...
There is a view in my DB that someone defined with a * from one table. I just added a new column to that table and I want the view to reflect the new column. Besides re-executing the view creation script, is there another way to rebuild the view? I am looking for something similar to how sp_recompile will recompile a stored procedure ...
Last night I was writing a simple T-SQL program something like this DECLARE @ROLEID AS INT SELECT @ROLEID = [ROLE ID] FROM TBLROLE ;WITH CTE AS ( SELECT * FROM SOMETABLE ) IF (@ROLEID = 1) BEGIN //SOMECODE END ELSE IF(@ROLEID = 2) BEGIN //SOMECODE END ELSE BEGIN //SOMECODE END I found after compilation th...
Here's my scenario: I have two tables A, B which (for the sake of this question are identical): Table X (PK) ID 1 2 Table A: ID FKID Value Sort 1 1 a 1 2 1 aa 2 3 1 aaa 3 4 2 aaaa 1 5 2 aaaaa 2 Table B: ID FKID Value Sort 1 1 b 1 2 1 bb 2 3 2 bbb 1 4 2 bbbb 2 5 2 bbbbb ...
I have a table of values like this: CREATE TABLE ( Name1 VARCHAR (50), Name2 VARCHAR (50), Sequence INT ) In this table I have rows like this 'Bob', 'Jones', 1 'James','Ant', 2 I want the best way to UPDATE (UPDATE SET) sequence based on the order of say the Name2 column, so when re-sequenced the values are: 'James','...