I am creating sql queries and I have read in one book that when using NOT operator or LIKE operators Indexes do not work. How much true this statement is. How can we avoid this if the statement is true. How a query should be made to do the same work avoiding these operators. What all other areas are there in sql server where Indexes are...
If I place DISTINCT keyword i get an error other wise it is working fine. ERROR: Msg 145, Level 15, State 1, Procedure SP_Products_GetList, Line 15 ORDER BY items must appear in the select list if SELECT DISTINCT is specified. ALTER PROCEDURE [dbo].[SP_Products_GetList] @CatID int, @CatName int, @IsNew bit, @InActive bit, @SortBy ...
I have data as Employee: id Name -------- 1 xyz 2 abc 3 qaz Employee_A: (Eid - employee table, title - title table) eid active type title ------------------------------ 1 1 1 1 1 1 2 2 1 1 4 3 2 0 3 4 2 1 2 2 2 0 ...
Please consider the following scripts. Create Table: IF OBJECT_ID('Colortable') IS NOT NULL DROP TABLE ColorTable CREATE TABLE Colortable (Color VARCHAR(32)) GO Insert some values: SET NOCOUNT ON INSERT Colortable SELECT 'red' INSERT Colortable SELECT 'orange' INSERT Colortable SELECT 'blue' INSERT Colortable SELEC...
Is there a simple way to view the SQL Queries actually generated by SSRS other than running profile traces to capture them? Is there some way from within the BIDS editor to see this? ...
I have to write a delete script to delete rows form a database table. However the table has a lot of children tables (foreign keys) and those children tables have children tables too. There are foreign keys for all relationships and I'd like to use this info to get the list of tables where I'll have to deletes, in the correct order (le...
The grouping is done on from and toloc and one group is been indicated by usrid Table : from toloc usrid a b 1 c d 1 --- group 1 e f 1 ------------------- a b 2 c d 2 --- group 2 e f 2 ---------------------- a b 3 c d 3 ...
I have basic stored procedure that performs a full text search against 3 columns in a table by passing in a @Keyword parameter. It works fine with one word but falls over when I try pass in more than one word. I'm not sure why. The error says: Syntax error near 'search item' in the full-text search condition 'this is a search item' SEL...
I've been using the sp_MSforeachtable built-in stored procedure to determine the row count of each table in our database, using COUNT(*). I've realized, though, that I just want a 0 or 1, depending on whether there are any rows at all in the table. Is there something else I can use that's faster/cheaper than COUNT(*)? ...
Hello, sorry for the poorish description it is really hard to explain what I am trying to do. But this is some pseudo: foreach (row in Table1) insert Table2 select * from getValuesTable('text', row.Column1) I'm not too sure how to get that initial join together because it will not allow me to alias the returned table from getValue...
I receive a denormalized text file that must be loaded into a normalized table. Denormalized table: CustomerID -- Category -- Category2 -- Category3 -- Category4 1 -- A -- B -- C -- D When this is normalized, it should look like: CustomerID -- Category 1 -- A 1 -- B 1 -- C 1 -- D What is the best way to write a T-SQL statement to ac...
I have the pivot PIVOT ( MIN(DateId) FOR Stage IN ( [Start], [Alpha], [Beta], [Release] ) ) as p I only want the MIN for the [Start] column and Max for every other column. How would I go about achieving this? ...
Hi guys, I'm not clear about the security-related catalog views in SQL Server 2005 or 2008. I want to list all logins, their server roles, their correspond users in all database, all database roles in one query. How can I write the query? I know there are some catalog views to use, but I'm not familiar with their relation. These catalo...
Hi, I've got this strange problem which I'm sure is well known - When I insert a date like '20/08/2010' I mean it to be as 'dd/mm/yyyy' where MSSQL expects it to be 'dd/mm/yyyy'. How can it be changed for MSSQL to expect 'dd/mm/yyyy' as the field format. Thanks! ...
I have a table that has the following columns Id ForeignKeyId AttributeName AttributeValue Created Some of the data may look like this: 1, 1, 'EmailPreference', 'Text', 1/1/2010 2, 1, 'EmailPreference', 'Html', 1/3/2010 3, 1, 'EmailPreference', 'Text', 1/10/2010 4, 2, 'EmailPreference', 'Text', 1/2/2010 5, 2, 'EmailPreference', 'Htm...
I wish to do the following: select * into tmptbl from sometable EXCEPT 'sometable' is a stored procedure that returns a result set AND editing the stored procedure to suit my goal is not an option. ALSO i may or may not know the columns and types of what the procedure returns. Basically i am looking for a proper way of doing this: ...
How would I select all rows distinct on Forename and Surname and where there's a duplicate select the one with the higher SomeDate, then Id if still duplicates e.g. For: | Id | Forename | Surname | SomeDate | ---------------------------------------- | 1 | Bill | Power | 2011-01-01 | | 2 | James | Joyce | 2011-02-01 | | ...
I'm having an issue on the query below. The issue is: Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int The issue is located in this line of the code below. INNER JOIN Test t ON a.AccountNO <> t.AccountNo Any ideas? WITH TEST AS ( SELECT DISTINCT AccountNo, SUBSTRING(APa...
I have two indexed views, v_First and v_Second. These views work great when I have a WHERE clause that filters only based on one of these views, however as soon as I have filter criteria based on both views I get two clustered index scans and poor performance as a result. My query is: SELECT * FROM dbo.v_First (NOEXPAND) JOIN dbo.v_Se...
Hi All, I have a need to build a schema structure to support table of contents (so the level of sections / sub-sections could change for each book or document I add)...one of my first thoughts was that I could use a recursive table to handle that. I want to make sure that my structure is normalized, so I was trying to stay away from deo...