If I have a common table expression for a family with mother & father, how can I increment the 'Generation' counter? A family should have the child as generation zero, parents as generation 1, and the four grandparents as generation 2. But the loop is performed twice, one for each set of grandparents. ;WITH FamilyTree AS ( SELECT ...
Hi All, According to the following section of BOL: How to: Use Source Control with SQL Server Management Studio If you have a source control client installed you should be able to choose it in the plug-in selection. I have both source control clients for Visual Studio 2005 and 2008 (tfs2005) installed and there's no plug-in to choose ...
I want to sort a table in sql server. Condition that i need to fulfill is this I have a table that has some records in it like this Select One None Child Old Neutral .. .. .. i want it to be sorted in such a way that Select One comes up and None comes at the end and remaining gets sorted alphabetically. Select One Child Neutral Old ....
Total records in table are 10. Select count(ID) from table1 where col1 = 1 (Result is 8) Select count(ID) from table1 where col1 = 0 (Result is 2) So its a same table but count is based on different condition. How am i gonna get two results (counts) using one select statement? Also Performance is a big concern here. PS: I am using ...
Hi, Is there a way to get the settings of a SQL Server instance via C# or TSQL? And then apply these settings to another SQL Server? Thanks ...
Given a table that has three columns ID (Primary Key, not-autoincrementing) GroupID SomeValue I am trying to write a single SQL INSERT INTO statement that will make a copy of every row that has one GroupID into a new GroupID. Example beginning table: ID | GroupID | SomeValue ------------------------ 1 | 1 | a 2 | 1 ...
I'm having some serious performance issues with a multi-million row table that I feel I should be able to get results from fairly quick. Here's a run down of what I have, how I'm querying it, and how long it's taking: I'm running SQL Server 2008 Standard, so Partitioning isn't currently an option I'm attempting to aggregate all views f...
I have a varchar field that contains a string like "10,11,12,13". How can I use that CSV string to join to another table with those IDs? Here's the approach I'm taking now: select * from SomeTable a WHERE (',' + @csvString + ',') LIKE '%,' + CONVERT(varchar(25), a.ID) + ',%' Where @csvString is "10,11,12,...". I intend to use this ...
Why isn't RAISERROR spelled RAISEERROR? Where is the second E? I could understand if it were some ancient keyword length constraint, but I wouldn't expect it to be a nine-character limit. Is RAIS or RROR a technical word such that "raise-error" is just a mis-reading? Are its (immediate) origins in a different language? I've search...
I would like to ask if there is a way to include the total number of rows, as an additional column, in the returned result sets from a TSQL query using also the Row_Number (SQL 2005) command. For example, getting the results set from a query against Book table in a form similar to this: RowNum BookId BookTitle TotalRows ------...
I have this table UserID SessionID SessionStart SessionEnd ----------------------------------------------- 1 abc1 2010-1-1 2010-1-2 5 def3 2010-1-5 2010-1-9 1 llk0 2010-1-10 2010-1-11 5 spo8 2010-1-13 2010-1-15 1 pie7 2010-1-16 2010-1-...
In my current project it would be convenient to exclude empty strings from the domain, so that is an empty string is met wherever, it is treated as NULL, for example if I UPDATE [table] SET [Something]='', SQL Server should treat it as SET [Something]=NULL, so that in next SELECT a NULL is returned as [Something] value. Should I use tri...
Hi, In SQL 2000, 2005 and 2008, how can I know when databases were last shrunk on a MS SQL server? I want to do this using TSQL. Regards Manjot ...
I'm trying to piece together a dynamic view that will give a list of all emails that are still being rejected. Table structure: EmailName - varchar(150) - An email address StartRejection - Date - Day to start rejecting email Duration - Small Int - Duration of rejection in days IsIndefinate - Bit - Is the rejection 'non date range spec...
I have this data structure: request ======= building_id lot_code building ======== building_id lot_id lot === lot_id lot_code The request table is missing the value for the building_id column and I want to fill it in from the other tables. So I've tried this: UPDATE request SET building_id = ( SELECT bu.building_id FROM bui...
Imagine to have a table defined as CREATE TABLE [dbo].[Price]( [ID] [int] NOT NULL, [StartDate] [datetime] NOT NULL, [Price] [int] NOT NULL ) where ID is the identifier of an action having a certain Price. This price can be updated if necessary by adding a new line with the same ID, different Price, and a more recent date....
I'm using T-SQL with ASP.NET, and c# and i'm pretty new to SQL. I was wondering how i could combine the results of two queries Query1: SELECT tableA.Id, tableA.Name, [tableB].Username AS Owner, [tableB].ImageUrl, [tableB].CompanyImageUrl, COUNT(tableD.UserId) AS NumberOfUsers FROM tableD RIGHT OUTER JOIN [tableB] INNER JOI...
Let us say I have a table (everything is very much simplified): create table OriginalData ( ItemName NVARCHAR(255) not null ) And I would like to insert its data (set based!) into two tables which model inheritance create table Statements ( Id int IDENTITY NOT NULL, ProposalDateTime DATETIME null ) create table Items...
For eg. I have table T1. There are 4 columns in it c1 c2 c3 and c4. I have c1 as id, c2 contains combine name and address. c3 and c4 are empty. There are multiple rows for given id. Let's say there are 10 rows for id=10. What I want is for all the rows with id=10, I want to read c2, separate values in c2 as name and address and store n...
I have a SQL query - fairly complex, but not too bad. When I run the SQL query in Management Studio, the query runs in about 10 seconds or less. When I put the SQL query directly into a reporting services report, the query runs in about 10 seconds or less. When I put the exact same SQL query into a stored procedure, and cal...