Every time I find out that the performance of data retrieval from my database is slow. I try to figure out which part of my SQL query has the problem and I try to optimize it and also add some indexes to the table. But this does not always solve the problem. My question is : Are there any other tricks to make SQL server performance be...
hello, I am using membership class for my user management, and it created a database called ASPNETDB.MDF.. I decided to use the same database to handle my other data, and so I added some of my own tables in there... When I try to access it: <connectionStrings> <add name="connString" connectionString="Initial Catalog=MyProject...
I've run across multiple situations in the last few months where legacy sql SP's are returning a single table made up mostly of redundant information. Example: Select CustomerID, CustomerEmail, CustomerAddress, InventoryLineItem, ShipQty from ... Returns: 55 [email protected] 723 StreetName InvLineItem#1 45 55 [email protected] 723 StreetName InvLineIt...
I have two tables A and B with A referencing a column in B with foreign key constraint. Now, i am trying to add more columns and constraints to table A by dropping table A and creating the table A again with new columns. SQL Server Mgmt Studio provides the "Drop and Create" option to do this where i alter the create table statement to ad...
Looking at JOIN's this weekend. I was reading up on Join and seeing a ton of combinations of JOIN, LEFT, RIGHT, OUTER, FULL, INNER. I checked the MSDN docs and it looks like the only allowed combinations are in the form: < join_type > ::= [ INNER | { { LEFT | RIGHT | FULL } [ OUTER] } ] [ < join_hint > ] JOIN so from tha...
Assuming the following: /* drop index ix_vouchers_nacsz on dbo.vouchers; drop index ix_vouchers_nacsz2 on dbo.vouchers; create index ix_vouchers_nacsz on dbo.Vouchers( FirstName, LastName, Address, Address2, City, State, Zip, Email ); create index ix_vouchers_nacsz2 on dbo.Vouchers( Email, FirstName, LastName, Address, Address2,...
Hi Does anyone know a query for listing out all foreign keys in a database with "WITH NOCHECK" description applied to it? (removing them will boost performance and stability). ...
This is very straightforward: I have 4 packages, and I would like to define one Error handler for all of them. I already created OnError event handler in one package and now I would like to configure other 3 packages to use the same error handling routine. I don't want to copy and paste. What is the easiest way of doing it? ...
I have two ints: @RecurremceTemp and @WeekDifference The following line errors: PRINT @WeekDifference >= @RecurrenceTemp with Incorrect syntax near '>' And won't execute. Can someone please tell me how to write such a boolean expression to include in a Select statement i.e.: Select * FROM TableX WHERE somevariable = x and @Week...
explain session type =sql server' ...
I've developed a couple of T-SQL stored procedures that iterate over a fair bit of data. The first one takes a couple of minutes to run over a year's worth of data which is fine for my purposes. The second one, which uses the same structure/algorithm, albeit over more data, takes two hours, which is unbearable. I'm using SQL-Server and...
Consider this unique constraint: ALTER TABLE Posts ADD CONSTRAINT UQ_Posts_Name UNIQUE (Name); Does it automatically create index on Name column? ...
Hi Does anyone here know anything about the license for the icons in SQL Server Manager? I would like to use one of them in my application, but have not yet found anything about the licensing for this stuff. Anyone dealt with this before? Maybe I should contact Microsoft and get a written permission .) best regards Ola ...
I am aware of COLUMNS_UPDATED, well I need some quick shortcut (if anyone has made, I am already making one, but if anyone can save my time, I will appriciate it) I need basicaly an XML of only updated column values, I need this for replication purpose. SELECT * FROM inserted gives me each column, but I need only updated ones. somethi...
Hi All, I cannot get my dataset to recognise a parameter supplied to it. I have created a report parameter "ProjectID". (In Report Parameter Properties, Name="ProjectID", Prompt="ProjectID"). In Dataset Properties|Parameters, Parameter Name="ID", Parameter Value="[@ProjectID]". When I click on the Filter button in Query Designer, th...
STRAGG function implemention returns a result as a single column value. Implementation for Oracle seems pretty generic and can be consumed for different tables (and relationships). Could similar behavior be achieved for SQL Server. A search in the web, appears to return only hard coded implementations and not a generic one. Do we have an...
I created a SqlCommand with an SQL query containing parameters. I than add all the parameters to the class. Is there an easy way to see the resulting SQL query before it is sent off to the db? This would be handy for debugging purposes. (e.g. copying the entire query and run it in the management studio to try and pinpoint problems) R ...
I am trying to have a PostBuildEvent in my SSIS project. This is my original .DTProj file from a test project with one test package. <?xml version="1.0" encoding="utf-8"?> <Project xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <ProductVersion>10.0.2531.0</ProductVersion> <Sche...
My app uses sql2000 and a select statement it uses will sometimes fail. Once a week or so the select returns the error 'Error Converting data type varchar to numeric' SQL: sum(case when ISNULL(form_prsn_id, -1) = irpd_prsn_id then convert(dec(11,2), case when valu_value = '' ...
I'm running the following query on my database, which generates a sql query I know returns 0 results and when run in sql management studio takes less than a second to return. var query = (from item in db.Table where item.Field == FieldValue // Field is not the primary key but is indexed from other in item.Assoc...