Is it a good idea to index varchar columns only used in LIKE opertations? From what I can read from query analytics I get from the following query: SELECT * FROM ClientUsers WHERE Email LIKE '%niels@bosmainter%' I get an "Estimated subtree cost" of 0.38 without any index and 0.14 with an index. Is this a good metric to use for anlayzi...
I want to create one proc like below but it has error on syntax. Could anyone pointing out the problem? Create PROCEDURE [dbo].[my_proc] AS BEGIN DISABLE TRIGGER dbo.tr_name ON dbo.table_name -- some update statement ENABLE TRIGGER dbo.tr_name ON dbo.table_name END ** Error Message : Incorrect syntax near 'ENABLE'. ...
How do I alter a primary clustered index to become a non-clustured index. (Being a "secondary" table I want to use the clustured index for the foreign key column of the "header" table.) This doen't work for me (error seems reasonable :) DROP INDEX ClientUsers.PK_ClientUsers CREATE UNIQUE CLUSTERED INDEX IDX_ClientUsers_Id ON ClientUser...
Hi, I have sql server procedure, please see below. ALTER PROCEDURE [dbo].[uspInsertDelegate] ( @CourseID int, @CPUserID int, @StatusID int, @CreateUser varchar(25) ) AS SET NOCOUNT OFF; INSERT INTO tblDelegate ( CourseID, CPUserID, StatusID, CreateUser ) VALUES ( ...
I have a N to N table with columns: Id (primary key) (I need this for various reasons) ClientId FeatureId Queries use all combinations of ClientId and FeatureId and need to be quick for all cases. (ClientId+FeatureId, ClientId, FeatureId) My idea is to create a covered clustured index on ClientId and FeatureId and additional noncovere...
Hi, I'm just wondering if anyone has had any problems having too many subscribers for a report in reporting services. I had 11 people in the "to" address and the 11th person never received anything. On one of the reports I moved the 11th name to the "cc" field and they occasionally received it, but on another report with the same parame...
In Oracle you can use &&VAR_NAME in a script and then the script will ask you for that value when you run it. In SQLSERVER you can use $(VAR_NAME) and reference a property file using: :r c:/TEMP/sqlserver.properties And in the property file you have something like: :setvar VAR_NAME_some_value Can you do the equivalent of &&V...
Hello everybody! I am facing the problem that I cannot properly map my foreign key table values to an asp.net mvc edit view. I have placed a Html.DropDownList and made an IENumerable/IQueryable helper function returning: from item in db.items select item.name which populates my Html.DropDownList with all the Names in the foreign ke...
I've 2 columns called record time and unload time which is in time format AM/PM and I require a new column called total time where I need to find difference between unload time and record time... for example here is my table record time unload time 11:37:05 PM 11:39:09 PM 11:44:56 PM 1:7:23 AM For this I require a new column which...
I have a problem regarding SQL batches in SQL Server. Assume I execute a SqlCommand using the following code: private void Example(SqlConnection connection, SqlTransaction transaction) { using (SqlCommand cmd = new SqlCommand("select * from T1; EXECUTE('update T2 set 1=2')", connection, transaction)) { SqlDataReader ...
Hello everyone, i have the following sql query and i want to filter the results where the alias imagefile is null, but i can't get it to work. it's kinda basic sql... sorry for that! SELECT Categorie.CategorieID, Categorie.Highlight, CategorieTaal.CategorieNaam, (SELECT TOP (1) ImageFile FROM Artikel WHERE (Categ...
Has somebody measured performance differences between "real" SQL Server database and a SQL Server Express user instances (attached .mdf database file)? What was the conclusion? Was the .mdf database file fast enough for your application? ...
One of the applications I develop gets installed locally on the customers site. This means that we have to support MySQL,MSSQL and Oracle as not all the customers use the same database engine. I'm writing a patch for the application, part of which involves executing a 5000 line sql script to make modifications to the database. The scrip...
Hello, Does anyone know of a resource that will tell me the sequence of locks that will be taken out on a table/page/row/index during a select/insert/update/delete in SQL Server 2005 AND how different table hints and isolation levels will impact the locks taken? I know I am asking a lot here, but surely this information must be documen...
I have an ASP.NET web application build using VWD 2008 and using SQL Server Express. When I start my application from within VWD 2008 (i.e., using the ASP.NET development server), it works fine. However, when I close VWD 2008 and try to load the application using IIS 5.1, it fails with this error: An attempt to attach an auto-named d...
I have a couple of LINQ to SQL queries that I feel take a lot of time. What tool and techniques can I use to first find out: How long they take and if there is room for improvement? Find out what I can change and easily test if those changes are effective? Both tools, techniques and reading resources would be appreciated (ergo up-vot...
Anyone know how to extract the date from a datetime stamp as part of the where clause? eg. select * from tableA where date between '01/08/2009' and '31/08/2009' (Date is a timestamp!) Many thanks, Fiona ...
Hi, I'm having to return ~70,000 rows of 4 columns of INTs in a specific order and can only use very shallow caching as the data involved is highly volatile and has to be up to date. One property of the data is that it is often highly repetitive when it is in order. I've started to look at various methods of reducing the row count in o...
Select col1 FROM mytable WHERE ValidUntil > 7/9/2009 8:45:30 pm and the error is Incorrect syntax near '8'. So what is wrong with this sql statement? I tried this directly on SQL management studio... ...
I am seeing some odd behavior in SQL Server AVG calcuation. On manual calculation, you get 49.277588 but SQL Server is reporting that the average is 50.9914 as shown below. Question: Can someone explain the difference and why this is happening? You can try out the query on AdventureWorks2008 Database with following query select C.P...