I have a question about the fastest way to perform a SQL Server query on a table, TheTable, that has the following fields: TimeStamp, Col1, Col2, Col3, Col4 I don't maintain the database, I just can access it. I need to perform 10 calculations that are similar to: Col2*Col3 + 5 5*POWER(Col3,7) + 4*POWER(Col2,6) + 3*POWER(...
I'm trying to investigate a performance problem at a client site. I've looked through the blocking data for the site and come up with a couple of SPIDs to investigate over a time period. I've then looked at the data from sys.syslockinfo and found that one of the problem SPIDs has a WAIT on an Intent Exclusive Table lock. I tried to find ...
I am trying to search a SQL Server 2008 table (containing about 7 million records) for cites and countries based on a user input type text. The search string that I get from the user can be anything like: "Hotels in San Francisco, US" or "New York, NY" or "Paris sddgdfgxx" or "Toronto Canada" terms are not allways separated by comma an...
I have an entity generated from my database that contains one table. When i make changes in the DB i obviously would like these changes to reflected in my model. My current approach is to delete the item from the designer and then right-click - update model from database. This works for me at the moment. Is there a different approach t...
i have a front end in access and back end is sql server 2008 one of the fields is the account number and here are the rules it is a zipcode like 92111 plus a dash plus a number. so the first one would be 92111-1, the second 92111-2 this has to do with how many clients we have in the zip code i would like this zip code to be automati...
I want to copy the value of one column to another column in SQL Server. This operation needs to be carried out across the whole DB which has 200M rows. My query syntax is: UPDATE [Net].[dbo].[LINK] SET [LINK_ERRORS] = [OLD_LINK_ERRORS] However, very soon I exhaust the transaction log and the query aborts. What's the best way to init...
Does anyone know how to add a description to a SQL Server column by running a script? I know you can add a description when you create the column using SQL Server Management Studio. How can I script this so when my SQL scripts create the column, a description for the column is also added? ...
In Microsoft SQL Server 10, how do I set constraint, that column in table with data type nchar(50) must be unique? ...
Hi all, Having some problems while trying to optimize my SQL. I got 2 tables like this: Names id, analyseid, name Analyses id, date, analyseid. I want to get the newest analyse from Analyses (ordered by date) for every name (they are unique) in Names. I can't really see how to do this without using 2 x nested selects. My try (Dont...
if you create a new trigger in sql server, it gives you this template: --==================================== -- Create database trigger template --==================================== USE <database_name, sysname, AdventureWorks> GO IF EXISTS( SELECT * FROM sys.triggers WHERE name = N'<trigger_name, sysname, table_alter_drop...
I want to display greatest selling product by quantity Product Table ProductID ProductName 1 AA 2 BB 3 CC [Order Details] Table OrderID ProductID Quantity DateOfOrder 1 1 10 SomeDate 2 1 100 ,, 3 2 15 ,, 4 1 15 ,, ...
This is not a duplicate of this post although the title is very similar. I am using EF4 with MSSQL Express 2008 R2 on VS2010. A simplified version of my schema is as follows: Table [Team]: Id (PK) Member1 Member2 Table [Person]: Id (PK) FirstName [Team].Member1 and [Team].Member2 are foreign keys pointing to [Person].Id. When ...
Hello, I have added Full Text Search to my sql server 2008 express database and created an index catalog for two columns in a single table. So now, I have to rewrite one of my stored procedures but I have no idea where to begin. The following is my current SP that I need to convert to take advantage of the full text search capability: A...
Hey I have a table like this: ID BitOne BitTwo BitThree -- ------ ------ -------- 99 NULL 1 NULL 99 1 NULL NULL And I need a user function that returns the string "BitTwo, BitOne" for the ID: 99 - which would be the input parameter for the function. Is that possible? ...
Given a "User" table and a "Login" table in MS SQL 2008: CREATE TABLE [dbo].[User_User]( [UserID] [int] IDENTITY(1000,1) NOT NULL, [UserName] [varchar](63) NOT NULL, [UserPassword] [varchar](63) NOT NULL ) CREATE TABLE [dbo].[Util_Login]( [LoginID] [int] IDENTITY(1000,1) NOT NULL, [User_UserID] [int] NOT NULL, -- FK ...
I have a situation where I have to dynamically create my SQL strings and I'm trying to use paramaters and sp_executesql where possible so I can reuse query plans. In doing lots of reading online and personal experience I have found "NOT IN"s and "INNER/LEFT JOIN"s to be slow performers and expensive when the base (left-most) table is lar...
I am trying to insert a .csv file into SQL Server 2008 R2. The .csv is 300+MB from http://ipinfodb.com/ip_database.php Complete (City), 4.0M records. Here're the top 5 lines, with 1st line = column headers: "ip_start";"country_code";"country_name";"region_code";"region_name";"city";"zipcode";"latitude";"longitude";"metrocode" "0";"RD"...
First Select Statement: SELECT dbo.FG_FILLIN.PartNumber, dbo.DropshipPackinglist.Shiplist_Qty, dbo.DropshipPackinglist.Quantity FROM dbo.FG_FILLIN INNER JOIN dbo.DropshipPackinglist ON dbo.FG_FILLIN.PartNumber = dbo.DropshipPackinglist.PartNumber WHERE (dbo.FG_FILLIN.Batch = 'CIP_HK_6') GROUP BY dbo.FG_FILLIN.Batch, d...
I've an IList with 5 items. I need to insert these, looping one by one, from the list to a SQL Server table using ado.net. I am using IReader(enterprise library) How can I do this? ...
Hi, I am not sure how to make an automaticed script that incrments all dates in a database. I was asked if the date is Friday, the script needs to increment the next business date to Monday, so I will need some logic in the script. This can be easily done in C# or any other programming language. But, I was required that the script must ...