Search Results

Search found 32641 results on 1306 pages for 'sql constraint and keys'.

Page 363/1306 | < Previous Page | 359 360 361 362 363 364 365 366 367 368 369 370  | Next Page >

  • Adding more OR searches with CONTAINS Brings Query to Crawl

    - by scolja
    I have a simple query that relies on two full-text indexed tables, but it runs extremely slow when I have the CONTAINS combined with any additional OR search. As seen in the execution plan, the two full text searches crush the performance. If I query with just 1 of the CONTAINS, or neither, the query is sub-second, but the moment you add OR into the mix the query becomes ill-fated. The two tables are nothing special, they're not overly wide (42 cols in one, 21 in the other; maybe 10 cols are FT indexed in each) or even contain very many records (36k recs in the biggest of the two). I was able to solve the performance by splitting the two CONTAINS searches into their own SELECT queries and then UNION the three together. Is this UNION workaround my only hope? Thanks. SELECT a.CollectionID FROM collections a INNER JOIN determinations b ON a.CollectionID = b.CollectionID WHERE a.CollrTeam_Text LIKE '%fa%' OR CONTAINS(a.*, '"*fa*"') OR CONTAINS(b.*, '"*fa*"') Execution Plan (guess I need more reputation before I can post the image):

    Read the article

  • using NEWSEQUENTIALID() with UPDATE Trigger

    - by Ram
    I am adding a new GUID/Uniqueidentifier column to my table. ALTER TABLE table_name ADD VersionNumber UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWSEQUENTIALID() GO And when ever a record is updated in the table, I would want to update this column "VersionNumber". So I create a new trigger CREATE TRIGGER [DBO].[TR_TABLE_NAMWE] ON [DBO].[TABLE_NAME] AFTER UPDATE AS BEGIN UPDATE TABLE_NAME SET VERSIONNUMBER=NEWSEQUENTIALID() FROM TABLE_NAME D JOIN INSERTED I ON D.ID=I.ID/* some ID which is used to join*/ END GO But just realized that NEWSEQUENTIALID() can only be used with CREATE TABLE or ALTER TABLE. I got this error The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression. Is there a workaround for this ? Edit1: Changing NEWSEQUENTIALID() to NEWID() in the trigger solves this, but I am indexing this column and using NEWID() would be sub-optimal

    Read the article

  • SQL query multi table selection

    - by nemiss
    I have 3 tables, - Section table that defines some general item sections. - Category table - has a "section" column (foreign key). - Product table - has a "category" column (foreign key). I want to get all products that belong to X section. How can I do it? select from select?

    Read the article

  • Fill in missing values in a SELECT statement

    - by benjamin button
    If i have a table with two fields.customer id and order. let's say i have in total order ID 1,2,3,4 all the customer can have all the four orders.like below 1234 1 1234 2 1234 3 1234 4 3245 3 3245 4 5436 2 5436 4 you can see above that 3245 customer doesnt have order id 1 and 2. how could i print in the query output like 3245 1 3245 2 5436 1 5436 3 EDIT: i dont have order table but i have list of order's like we can hard code it in the query(1,2,3,4) i dont have an orders table.

    Read the article

  • How I can move table to another filegroup ?

    - by denisioru
    Hello, I have MSSQL 2008 Ent and OLTP database with two big tables. How I can move this tables to another filegroup without service interrupting? Now, about 100-130 records inserted and 30-50 records updated each second in this tables. Each table have about 100M records and six fields (including one field geography). I looking for solution via google, but all solutions contain "create second table, insert rows from first table, drop first table, bla bla bla". Can I use partitioning functions for solving this problem? Thank you.

    Read the article

  • sqlserver how to set job priority

    - by Buzz
    Is there any way to set one job priority higher then other, In my case there are two jobs those are working on same set of tables,first JOB-A which is running every 12 hr and other JOB-B is every 10 minutes , i think at some time when they run simultaneously JOB-B is getting in to deadlock and get failed, i google the topic and found sqlgoverner is helpful in such cases does anyone know how to resolve?

    Read the article

  • SQL to have one specific record at the top, all others below

    - by superdario
    Hey all, I am trying to put together a query that will display one specific record (found by the record's primary ID) at the top, and display all other records below it, sorted by date (I have "date_added" as one of the fields in the table, in addition to primary ID). I could do this with a UNION (first select would locate the record I want, and the other select would display all other records), but I'm wondering if is there perhaps a better way? I'm using Oracle, by the way.

    Read the article

  • Database normalization and duplicate values

    - by bretddog
    Consider a Parent / Child / GrandChild structure in a database table schema, or even a deeper hierarchy. These being in the same aggregate. One table DAYS keeps a single row per day, and has a "Date" field. This is the root table, or maybe a child of the root. No row can ever be deleted in this table. In this case, however complex my table schema looks like, however far away in the hierarchy any other table is, is there any reason why any other table would hold a Date value? Can't it instead just have a FK to the DAYS table. I obviously assume that the creation of these date fields happen not before such datefield exist in the DAYS table. I'm now thinking just about the date part to be relevant, not the time part. Not sure if all databases can store these individually. That's maybe relevant, but not really the focus of the question.

    Read the article

  • Sum two rows in one - My Sql

    - by user303832
    I have found some similar posts, but I didn't find them useful. But I didn't know how to group them. I would like to Sum 'No' and 'Not Set' to one row, and to lose 'Not Set' row. So : 'No' = 'No' + 'Not Set' I have something like this : TEST TestCount Month 'Yes' 123 March 'No' 432 March 'Not Set' 645 March 'Yes' 13 April 'No' 42 April 'Not Set' 45 April 'Yes' 133 May 'No' 41 May 'Not Set' 35 May .... And I would like something like this : TEST TestCount Month 'Yes' 423 March 'No' 410 March 'Yes' 154 April 'No' 192 April 'Yes' 130 May 'No' 149 May .... Can anybody help me with this, tnx in advance

    Read the article

  • SQL query sample

    - by qods
    Hi, my table is look like this order.. I would like count the TermID's with these conditions; If it's possible also want to get resultset with these headers - TermID must have close or open status - TermID Status must turn to Open (close to open) - Status Date (open status) must be bigger date than close status date with my regards, ID TermID Status Date 103990107 103641 Close 28/05/2010 104000600 103641 Open 31/05/2010 103980976 106458 Close 26/05/2010 103990045 106458 Open 27/05/2010 103939537 106475 Open 18/05/2010 103908130 117220 Open 13/05/2010 103929653 117220 Open 13/05/2010 103999017 117360 Open 31/05/2010 103834814 117402 Open 05/05/2010 103993973 117420 Open 28/05/2010 103849795 119377 Close 05/05/2010 103955266 119377 Close 21/05/2010 103995229 119377 Close 29/05/2010 103979873 119704 Open 25/05/2010 103832225 119767 Open 04/05/2010 103925429 123229 Close 15/05/2010 103986986 123932 Open 27/05/2010

    Read the article

  • Connecting to an MS SQL Server from Silverlight?

    - by cam
    Normally, I would use a PHP webservice to do this, but since the front-end is hosted on a linux box, I need another way to do this (so I don't have to go through the trouble of installing FreeTDS, etc. I will if I have to). Is there a better way to do this? I'm not a web guy, but I'm trying my best.

    Read the article

  • FreeText Query is slow - includes TOP and Order By

    - by Eric P
    The Product table has 700K records in it. The query: SELECT TOP 1 ID, Name FROM Product WHERE contains(Name, '"White Dress"') ORDER BY DateMadeNew desc takes about 1 minute to run. There is an non-clustered index on DateMadeNew and FreeText index on Name. If I remove TOP 1 or Order By - it takes less then 1 second to run. Here is the link to execution plan. http://screencast.com/t/ZDczMzg5N Looks like FullTextMatch has over 400K executions. Why is this happening? How can it be made faster?

    Read the article

  • Is there a way to split the results of a select query into two equal halfs?

    - by Matthias
    I'd like to have a query returning two ResultSets each of which holding exactly half of all records matching a certain criteria. I tried using TOP 50 PERCENT in conjunction with an Order By but if the number of records in the table is odd, one record will show up in both resultsets. Example: I've got a simple table with TheID (PK) and TheValue fields (varchar(10)) and 5 records. Skip the where clause for now. SELECT TOP 50 PERCENT * FROM TheTable ORDER BY TheID asc results in the selected id's 1,2,3 SELECT TOP 50 PERCENT * FROM TheTable ORDER BY TheID desc results in the selected id's 3,4,5 3 is a dup. In real life of course the queries are fairly complicated with a ton of where clauses and subqueries.

    Read the article

  • SQL where clasue to work with Group by clasue after performing a count()

    - by Matt
    Tried my usual references at w3schools and google. No luck I'm trying to produce the following results. QTY is a derived column | Position | QTY -------------------- 1 Clerk 2 2 Mgr 2 Here's what I'm not having luck with: SELECT Position, Count(position) AS 'QTY' FROM tblemployee Where ('QTY' != 1) GROUP BY Position I know that my Position is set up as varchar(255) Count produces a integer data and my where clasue is accurate so that leads me to believe that that Count() is jamming me up. Please throw up an example so I can reference later. Thanks for the help!

    Read the article

  • SQL - get latest records from table where field is unique

    - by 89stevenharris
    I have a table of data as follows id status conversation_id message_id date_created 1 1 1 72 2012-01-01 00:00:00 2 2 1 87 2012-03-03 00:00:00 3 2 2 95 2012-05-05 00:00:00 I want to get all the rows from the table in date_created DESC order, but only one row per conversation_id. So in the case of the example data above, I would want to get the rows with id 2 and 3. Any advice is much appreciated.

    Read the article

  • Setting custom SQL in django admin

    - by eugene y
    I'm trying to set up a proxy model in django admin. It will represent a subset of the original model. The code from models.py: class MyManager(models.Manager): def get_query_set(self): return super(MyManager, self).get_query_set().filter(some_column='value') class MyModel(OrigModel): objects = MyManager() class Meta: proxy = True Now instead of filter() I need to use a complex SELECT statement with JOINS. What's the proper way to inject it wholly to the custom manager?

    Read the article

  • Keeping Linq to SQL alive when using ViewModels (ASP.NET MVC)

    - by Kohan
    I have recently started using custom ViewModels (For example, CustomerViewModel) public class CustomerViewModel { public IList<Customer> Customers{ get; set; } public int ProductId{ get; set; } public CustomerViewModel(IList<Customer> customers, int productId) { this.Customers= customers; this.ProductId= productId; } public CustomerViewModel() { } } ... and am now passing them to my view instead of the Entities themselves (for example, var Custs = repository.getAllCusts(id) ) as it seems good practice to do so. The problem i have encountered is that when using ViewModels; by the time it has got to the the view i have lost the ability to lazy load on customers. I do not believe this was the case before using them. Is it possible to retain the ability of Lazy Loading while still using ViewModels? Or do i have to eager load using this method? Thanks, Kohan.

    Read the article

  • computed column calculate a value based on different table

    - by adnan
    i've a table c_const code | nvalue -------------- 1 | 10000 2 | 20000 and i've another table t_anytable rec_id | s_id | n_code --------------------- 2 | x | 1 now i want to calculate the x value with computed column, based on rec_id*(select nvalue from c_const where code=ncode) but i get error "Subqueries are not allowed in this context. Only scalar expressions are allowed." how can i calculate the value in this computed column ? thanks.

    Read the article

  • Assign the results of a stored procedure into a variable in another stored procedure

    - by RHPT
    The title of this question is a bit misleading, but I couldn't summarize this very well. I have two stored procedures. The first stored procedure (s_proc1) calls a second stored procedure (s_proc2). I want to assign the value returned from s_proc2 to a variable in s_proc1. Currently, I'm calling s_proc2 (inside s_proc1) in this manner: EXEC s_proc2 @SiteID, @count = @PagingCount OUTPUT s_proc2 contains a dynamic query statement (for reasons I will not outline here). CREATE dbo.s_proc2 ( @siteID int, @count int OUTPUT ) AS DECLARE @sSQL nvarchar(100) DECLARE @xCount int SELECT @sSQL = 'SELECT COUNT(ID) FROM Authors' EXEC sp_ExecuteSQL @sSQL, N'@xCount int output', @xCount output SET @count = @xCount RETURN @count Will this result in @PagingCount having the value of @count? I ask because the result I am getting from s_proc1 is wonky. In fact, what I do get is two results. The first being @count, then the result of s_proc1 (which is incorrect). So, it makes me wonder if @PagingCount isn't being set properly. Thank you.

    Read the article

  • SQL how to avoid duplicate insert in a table

    - by user1624531
    how to avoid duplicate insert in a table? I use below query to insert in to table: insert into RefundDetails(ID,StatusModified,RefundAmount,OrderNumber) select O.id,O.StatusModified,OI.RefundAmount,O.OrderNumber from Monsoon.dbo.[Order] as O WITH (NOLOCK) JOIN Monsoon.dbo.OrderItem as OI WITH (NOLOCK)on O.Id = OI.OrderId WHERE o.ID in (SELECT OrderID FROM Mon2QB.dbo.monQB_OrderActivityView WHERE ACTIVITYTYPE = 4 AND at BETWEEN '10/30/2012' AND '11/3/2012') AND (O.StatusModified < '11/3/2012')

    Read the article

  • Is INT the correct datatype for ABS(CHECKSUM(NEWID()))?

    - by Chad Sellers
    I'm in the process of creating unique customers ID's that is an alternative Id for external use. In the process of adding a new column "cust_uid" with datatype INT for my unique ID's, When I do an INSERT into this new column: Insert Into Customers(cust_uid) Select ABS(CHECKSUM(NEWID())) I get a error: Could not create an acceptable cursor. OLE DB provider "SQLNCLI" for linked server "SHQ2IIS1" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. I've check all data types on both tables and the only things that has changed is the new column in both tables. The update is being done on one Big @$$ table...and for reasons above my pay grade, we would like to have new uid's that are different form the one's that we currently have "so users don't know how many accounts we actually have." Is INT the correct datatype for ABS(CHECKSUM(NEWID())) ?

    Read the article

< Previous Page | 359 360 361 362 363 364 365 366 367 368 369 370  | Next Page >