I'm sorry if the title question isn't very clear but i don't think i can expalain my problem in a single sentance. I have a table with a number of different types of events in it all recorded against a date. I'm querying the table and grouping based on a subset of the date (month and year). SELECT DATENAME(MONTH, event_date_time) + ' ...
Ok I have a sql server database view with a list of bookings (employee, date, charge). What i need to do is create a PerformancePoint chart that basically lists the employees and totals all their charges for a particular period. e.g. Karen 01/01/2010 £50 Jim 01/01/2010 £20 Karen 02/01/2010 £30 Tim 03/01/2010 £70 Chart for J...
I decided to edit my post since people didn't quite get what I'm trying to do (I suppose it was not irrelevant to say why I needed it after all): Ok, there we go. The thing is, I have a HUGE SQL script which many clients use and it has to be ran thoroughly every time a client executes the "database management" functionality that our sof...
I am attempting to populate a table based on 2 and 3 week intervals for a semi-monthly pay period in TSQL. The table should populate, 2 week date 2 week date 3 week date 2 week date 2 week date 3 week date ..based on the first date I supply, subsequently adding 2 or 3 weeks to the last date supplied. I should be able to supply a star...
Hello all, I want to do some calculations when my table data is changed. However, I am updating my table manually and copy pasting about 3000 rows in once. That makes my trigger work 3000 times, but I want it to do the trigger only once. Is there a way to do that ? Thanks. ...
I have a stored procedure running on sql server 2008 looping over a table of ~50 million rows, deleting records one day at a time (approx. 25,000 records a day). I want to be able to watch this occur as the process runs via print statements to the messages window. I'd like to be able to see a message each time a day's worth of deletions ...
Hi I am currently using TableAdapter that returns a dataTable, which is fine to use when result table should have more than one row like : MyItemsDataTable myItemsDataTable = Adapter.GetAllItems(); but if we need only one row as a result, say an item, which has a particular ID MyItemsDataSet.MyItemRow itemRow = Adapter.GetItemByID...
The Locks performance object on SQL Server provides three counters that I'm interested in. X = Average Wait Time (ms) Y = Lock Wait Time (ms) Z = Lock Waits/sec From their descriptions I'd have imagined that X = Y / Z. However, while this is true for SQL2005, it does not seem to be true for SQL2000. I have some code that forces a dea...
Is it a good idea to store audio files (mp3, wav) in SQL Server as BLOBs? One of the merits appears to be saving Hard disk space. Does this have any impact on scalability or performance? ...
I'd like to find the hits and misses in a table using only a list of items without having to create a new table to contain the list items and without using any scripting. I do many ad-hoc queries throughout the day so this would be useful. Here's an example of what I'm using now: SELECT custid, name, email FROM customers WHERE cust...
I have searched this site extensively but cannot find a solution. Here is the example of my query: SELECT ActivityID, Hours = (CASE WHEN ActivityTypeID <> 2 THEN FieldName = (Some Aggregate Sub Query), FieldName2 = (Some other aggregate sub query) WHEN A...
I have access to an installation of SQL 2005 Express. I probably installed it myself some time ago. But haven't used it in a while. I tried to access this instance via an 'sa' password I typically use for such local installs. However this password did not work. I double checked that mixed mode authentication is setup and that the sa u...
Hello all, I have a big query and I am tring to improve it part by part, however due to the caching mechanism, and simplicity of t-sql codes, I don't have a reliable environment for testing the speeds. The queries that I am tring to improve about speeds are all last about 1 or 2 seconds, so I can't see the difference clearly. And creati...
SQL Server 10, .NET, C# This is similar to this question but not exactly the same. When a user creates new event on my event-scheduling web site, a unique sort-of easy to remember code is to be generated, so this code can be communicated (via the phone, let's say) to the participants. This generated code looks something like: JohMon...
I need to change the values of a PK/FK (add 10000) on 2 tables. How do I tell the two tables involved that they should not care about referential integrity during the update, but to care after. I don't want to have to drop and recreate the relationships if I don’t have to. ...
I'm doing a relatively complex CTE query with an expensive user defined scalar function call. The where clauses of the CTE filters most of the large table data, and returns relatively small result, so calculating an expensive field is not a problem, if calculated on the resultset. SQL server is doing a good job if not evaluating the ex...
In the query below is it better for performance to move the case statement to a UDF? If so why? SELECT id, name, case when v1 = 'Y' then 'something' when v2 = 'K' then 'something else' when v3 is null then 'dont know' else 'default' end from table ...
I have an mdb which used to contain a bunch of linked tables. These links point to tables in another Access mdb. As part of a controlled migration, I'm changing these link table to point to an SQL server instance instead, by iterating through all linked tables and updating the connect string to an ODBC one, then calling RefreshLink on ...
Set database name dynamically in SQL SErver Stored procedure? ...
I've been trying to solve various deadlocks we are seeing in production. We've enabled deadlock tracing. The traces show lots of blocking on KEYLOCKs like this: 01/15/2010 08:25:07,spid15s,Unknown,keylock hobtid=72057594047758336 dbid=2 objectname=tempdb.dbo.MyTable indexname=IX_MyTable id=lock36977900 mode=X associatedObjectId=720575...