Ok, I'm trying to make an indexed view that is against a simple table that stores the results of what people think is good/bad for a post. This is the results of a thumbs up / thumbs down, voting on posts. So here's my pseduo fake table :- HelpfulPostId INT IDENTITY(1,1) NOT NULL PRIMARY KEY, PostId INT NOT NULL, IsHelpful BIT NOT NULL...
I have the following set of dates (dd/MM/yyyy) matching events in my database: eventId startDate endDate 1 02/05/2009 10/05/2009 2 08/05/2009 12/05/2009 3 10/05/2009 12/05/2009 4 21/05/2009 21/05/2009 5 25/05/2009 NULL 6 01/06/2009 03/06/2009 The ev...
hey everyone, Im trying to create a view out of quite a complex select query and it wont let me put a clustered index on it because i have to use subqueries and some aggregate functions. I have to get a clustered index on it otherwise the queries that use the view will take forever. Apparently sql server will only store the result set ...
Hi, we faced very strange issue (really strange for such mature product): how to get number of characters in unicode string using Transact-SQL statements. The key problem of this issue that the len() TSQL function returns number of chars, excluding trailing blanks. the other variant is to use datalength (which return number of bytes) and...
It's easier with an example. I have two tables: Books and Chapters. Both have Title and Id columns. Chapters also has a Book column which is a foreign key to Books.Id. Books.Title is unique (I don't want two books with the same title). Now my problem is defining Chapter.Title uniqueness. I want it to be unique as long as Books.Id is the...
I have a result like this from a sql query Month Day Customer Item ------------------------------------------ January 1 John Pencil January 1 Jack --- January 1 Steve Stapler January 2 John --- January 2 Jack --- January 2 Steve VisitingCard January 3 John...
This should be simple enough, but somehow my brain stopped working. I have two related tables: Table 1: ID (PK), Value1 Table 2: BatchID, Table1ID (FK to Table 1 ID), Value2 Example data: Table 1: ID Value1 1 A 2 B Table 2: BatchID Table1ID Value2 1 1 100 2 1 101 3 1 102 1 ...
Ok, so suppose I am doing an insert or an update on a table. So in the BEGIN CATCH/END CATCH I can define a variable to ERROR_MESSAGE() and get back my error message: Cannot insert the value NULL into column 'columnname', table 'Table'; column does not allow nulls. INSERT fails. Is there any way I could return say the primary key of th...
Hi there, I wonder if anyone has a solution to the following requirement. I have a stored procedure which returns a result set of for example 1000 rows. Now I need to limit this to 100 rows at a time. So I will pass in a start and end index value and I only want the records between the start index rowcount and the end index rowcount ...
Is the nocount in SQL for each connection or each execution? Consider the following -- Proc to return data. -- nocount is set to on (no row count returned) create procedure procOne as set nocount on select * from myTable If I have an application that calls procOne and then performs a SQL call such as: DELETE from myTable where ...
Hi, I'd like to list all objects of a particular database in SQL Server 2005. I created a query as shown below: select name, type_desc from sys.objects WHERE type in ( 'C', 'D', 'F', 'L', 'P', 'PK', 'RF', 'TR', 'UQ', 'V', 'X' ) union select name, type_desc from sys.indexes order by name However, this query list all objects of ALL d...
Is there a way to run a query and then have SQL Server management studio or sqlcmd or something simply display the datatype and size of each column as it was received. Seems like this information must be present for the transmission of the data to occur between the server and the client. It would be very helpful to me if it could be dis...
I have a SQL Server query that I need to convert to MySQL. I've never used SQL Server/T-SQL before, so I have no experience with FOR XML PATH. There's surprisingly little documentation on this sort of thing. If I remove the FOR XML PATH statement, MySQL returns the error "Operand should contain at least 1 column(s)." It seems like the n...
I'm using a SELECT statement in T-SQL on a table similar to this: SELECT DISTINCT name, location_id, application_id FROM apps WHERE ((application_id is null) or (application_id = '4')) AND ((location_id is null) or (location_id = '3')) This seems to work fine when searching for one application_id or one location_id, but what if I want...
I have two tables (1) MonthlyTarget {SalesManCode, TargetMonthYear, TargetValue}; this table has 1966177 rows. (2) MonthlySales {SalesManCode, SaleDate, AchievedValue}; this table has 400310 rows. I have to make a query that produces a result like the following table: {SalesManCode, JanTar, JanAch, FebTar, FebAch,....., DecTar, De...
I have two tables (1) MonthlyTarget {SalesManCode, TargetValue}; (2) MonthlySales {SalesManCode, SaleDate, AchievedValue}; I have to make a query that produces a result like the following table: {SalesManCode, JanTar, JanAch, FebTar, FebAch,....., DecTar, DecAch} What should be the query? ...
In my database, I have 3 tables that have 2 similar columns, Year and Month. These tables aren't linked by anything. What i want to do is select the distinct year and months from these tables. So where table 1 contains: 2009 MAY (multiple times) 2008 NOVEMBER (multiple times) 2007 MAY (multiple times) and table 2 and 3 contains: 20...
I have a basic SQL query, starting with: SELECT top 20 application_id, [name], location_id FROM apps Now, I would like to finish it so that it does this (written in Pseudocode) if @lid > 0 then WHERE location_id IN (@lid) else WHERE location_id is all values in location_id column As requested, here is an example applicat...
I'm refactoring an old database and removing columns no longer in use. The DB used to have full text indexing, so, some column are marked for full text. How can I remove them? Notes: DB is MS SQL Server Express 2008 Full text search service is no longer installed Edit: I have tried ALTER FULLTEXT INDEX ON tableName DROP (Colu...
I'm working on a stored proc that executes some dynamic sql. Here's the example I found on 4GuysFromRolla.com CREATE PROCEDURE MyProc (@TableName varchar(255), @FirstName varchar(50), @LastName varchar(50)) AS -- Create a variable @SQLStatement DECLARE @SQLStatement varchar(255) -- Enter the dynamic SQL statement i...