I have a complex query that I need to use in a subsequent query (actually update statement). I have tried both using a CTE and a temp table. The performance using the CTE is horrible vs the temp table approach. Its something like 15 seconds vs milliseconds. To simplify the test instead of joining the CTE/Temp table in the subsequent q...
I have two different queries: SELECT PB_BANK_CODE, PB_BANK_NAME FROM GLAS_PDC_BANKS WHERE PB_COMP_CODE='1' AND PB_BANK_CODE='025' AND PB_BANK_CODE IN ( SELECT DISTINCT PC_BANK_FROM FROM GLAS_PDC_CHEQUES WHERE PC_BANK_FROM ='025' AND ISNULL(PC_DISCD,'X') != 'C' AND PC_DUE_DATETIME BETWEEN '05/05/2008' AN...
I am having a problem with my SQL Server 2005 where stored procs, specifically ones that run Bulk Inserts, are sometimes taking a really long time to execute. This stored proc, let's call it DatabaseUpdate, shold take about 2 secs to complete. However, between 20-40% of the time it takes much more than that and it is very common that i...
Given the following sample of XML and the select statement that shreds the xml into a relation, what I need is the second column of the select to be the ordinal of the category (ie 1 for the directions and 2 for the colours in this case). Note: The literal value 'rank()' in the select is left a placeholder. I was poking around with usin...
I have columns that are of datetype, that I use for things like created, lastmodified etc. I don't want these columns to allow for nulls, is there a best practise in terms of what I should use for their unused, initialized state? e.g. should I use whatever value datetime.minvalue is? ...
I need to create a query that will sum the number of True(1) and False(0) into two separate columns from one bit field. I'm joining 3 tables and need it to be something like: Attribute | Class | Pass | Fail I will be grouping on Attribute and Class. ...
I have a SQLClient.DataSet in VB.NET, and I want to insert the entire thing into a SQL Server table without having to do the following: For Each dr as Datarow in MyDataset Dim sc As New SqlCommand("INSERT INTO MyNewTable " & _ "VALUES (@column1, @column2)", MyDBConnection) sc.Parameters.AddWithValue("@col...
Ok, basically what is needed is a way to have row numbers while using a lot of joins and having where clauses using these rownumbers. such as something like select ADDRESS.ADDRESS FROM ADDRESS INNER JOIN WORKHISTORY ON WORKHISTORY.ADDRESSRID=ADDRESS.ADDRESSRID INNER JOIN PERSON ON PERSON.PERSONRID=WORKHISTORY.PERSONRID WHERE PERSONRID...
So I came across an instance where a stored procedure which handled the Updates on a specific table was doing nothing because the record didn't exist [duh]. So what I decided to do was change the stored procedure to: UPDATE Table SET col1 = @var1 WHERE Type = @type AND UserId = @userId IF @@ROWCOUNT = 0 BEGIN INSER...
Lets say you have a table with some winning numbers in it. Any of these numbers is meant to be only "won" by one person. How could I prevent 2 simultaneous web requests that submit the same numbers from both checking and seeing that the numbers is still available and then giving the prize to both of them before the number is marked as ...
A coworker and I were browsing SO when we came across a question about SQL Injection, and it got us wondering: how do parametrized queries work internally? Does the API you are using (assuming it supports parametrized queries) perform concatenation, combining the query with the parameters? Or do the parameters make it to the SQL engine...
Hi, I've the following sql query: SQL = "SELECT * FROM Product WHERE ProductCategoryId = " & Request.QueryString("CategoryId") This query tells to get all the products from ONE category. I want the ability so products can be from some categories, and not from one category only. So, I changed the [Product].ProductCategoryId field t...
This desktop app written in VB.Net within VStudio 2008 fails when one of our "power users" tries to run the program: Select permission denied on object 'AcctLogon', database 'MDS', owner 'dbo'. The author of the app is out for a few weeks and I am the "fixer" while she's out. Both of our logins for SQL have permissions for db_owner i...
What is the time complexity of a function such as count, sum, avg or any other of the built in "math"-functions in mysql, sql server, oracle and others? One would think that calling sum(myColumn) would be Linear? But count(1) isn't, how come and what are the Real time-complexity? In a perfect world I would want sum, avg and count to b...
I have a stored proc that calls several store procs, each of which insert dummy data into a single table each. It works fine except that for each loop in the cursor a single row of results is dispayed - just showing ClubcardId = 2, ClubcardId = 3 etc. I have used the SET NOCOUNT ON but this doesn't seem to help. I'm looking for this sto...
How can I Select all columns from all tables from the DB, like: Select * From * in SQL Server 2008??? The table list it´s very very big, and have so many columns, is it possible to do it without writing the column names? Or maybe make a select that returns the name of the tables. ...
I'm using SQLCLR in my project and, for the record, I'm very pleased with it. However, I can't find any good sources of information for good data access patterns. Usually I do communication between .net and SQL using stored procedures because I always want an API for my database. However, in this case the .net code is part of the API, s...
Hi, I have read lots of posts about the importance of database version control. However, I could not find a simple solution how to check if database is in state that it should be. For example, I have a databases with a table called "Version" (version number is being stored there). But database can be accessed and edited by developers ...
Anyone have any recommendations for books about performance tuning on either frontend or backend? I'm looking specifically for asp.net or sql (MS-SQL) performance tuning, but anything is good to go. Thanks! ...
So let's say I have two databases, one for production purposes and another one for development purposes. When we copied the development database, the full-text catalog did not get copied properly, so we decided to create the catalog ourselves. We matched all the tables and indexes and created the database and the search feature seems t...