Search Results

Search found 37012 results on 1481 pages for 'sql query'.

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

Page 100/1481 | < Previous Page | 96 97 98 99 100 101 102 103 104 105 106 107  | Next Page >

  • Why better isolation level means better performance in MS SQL Server

    - by Oleg Zhylin
    When measuring performance on my query I came up with a dependency between isolation level and elapsed time that was surprising to me READUNCOMMITTED - 409024 READCOMMITTED - 368021 REPEATABLEREAD - 358019 SERIALIZABLE - 348019 Left column is table hint, and the right column is elapsed time in microseconds (sys.dm_exec_query_stats.total_elapsed_time). Why better isolation level gives better performance? This is a development machine and no concurrency whatsoever happens. I would expect READUNCOMMITTED to be the fasted due to less locking overhead.

    Read the article

  • Sql Shorthand For Dates

    - by vigilant
    Is there a way to write a query equivalent to select * from log_table where dt >= 'nov-27-2009' and dt < 'nov-28-2009'; but where you could specify only 1 date and say you want the results for that entire day until the next one. I'm just making this up, but something of the form: select * from log_table where dt = 'nov-27-2009':+1;

    Read the article

  • A question about complex SQL statement

    - by william
    Table A has columns ID and AName, Table B has columns BName and ID. B.ID is foreign key of A.ID. Write a SQL statement to show data like: print column AName and C which describe whether Table B has ID in Table A, if exists 1 or else 0. So if A is: 1 aaa 2 bbb B is: something,2 output is: aaa,0 bbb,1

    Read the article

  • Search & Replace SQL

    - by Shonna
    I am messing around with one of my databases.. is there away for me to search for a string in ALL the tables.. and replace it with another everywhere it occurs? I am looking for SQL

    Read the article

  • sql: trying to select the second biggest element but selects the biggest

    - by matthy
    we want to have the second biggest element. We first use ANY to exclude the biggest one. Then we use all to select the biggest. However when we run this query, it shows the biggest and not the second one. Why? SELECT * FROM bestelling WHERE totaalprijs > ALL ( SELECT totaalprijs FROM bestelling WHERE totaalprijs < ANY ( SELECT totaalprijs FROM bestelling ) ) elements in the table: 157.00 5.00 82.80 15.00 20.00 20.00

    Read the article

  • Execution Plan Optimization when where clause is removed then added back

    - by nmushov
    I have a stored procedure that uses a table valued function which executes in 9 seconds. If I alter the table valued function and remove the where clause, the stored procedure executes in 3 seconds. If I add the where clause back, the query still executes in 3 seconds. I took a look at the execution plans and it appears that after I remove the where clause, the execution plan includes parallelism and the scan count for 2 of my tables drops for 50000 and 65000 down to 5 and 3. After I add the where clause back, the optimized execution plan still runs unless I run DBCC FREEPROCCACHE. Questions 1. Why would SQL Server start using the optimized execution plan for both queries only when I first remove the where clause? Is there a way to force SQL Server to use this execution plan? Also, this is a paramaterized all-in-one query that uses the (Parameter is null or Parameter) in the where clause, which I believe is bad for performance. RETURNS TABLE AS RETURN ( SELECT TOP (@PageNumber * @PageSize) CASE WHEN @SortOrder = 'Expensive' THEN ROW_NUMBER() OVER (ORDER BY SellingPrice DESC) WHEN @SortOrder = 'Inexpensive' THEN ROW_NUMBER() OVER (ORDER BY SellingPrice ASC) WHEN @SortOrder = 'LowMiles' THEN ROW_NUMBER() OVER (ORDER BY Mileage ASC) WHEN @SortOrder = 'HighMiles' THEN ROW_NUMBER() OVER (ORDER BY Mileage DESC) WHEN @SortOrder = 'Closest' THEN ROW_NUMBER() OVER (ORDER BY P1.Distance ASC) WHEN @SortOrder = 'Newest' THEN ROW_NUMBER() OVER (ORDER BY [Year] DESC) WHEN @SortOrder = 'Oldest' THEN ROW_NUMBER() OVER (ORDER BY [Year] ASC) ELSE ROW_NUMBER() OVER (ORDER BY InventoryID ASC) END as rn, P1.InventoryID, P1.SellingPrice, P1.Distance, P1.Mileage, Count(*) OVER () RESULT_COUNT, dimCarStatus.[year] FROM (SELECT InventoryID, SellingPrice, Zip.Distance, Mileage, ColorKey, CarStatusKey, CarKey FROM facInventory JOIN @ZipCodes Zip ON Zip.DealerKey = facInventory.DealerKey) as P1 JOIN dimColor ON dimColor.ColorKey = P1.ColorKey JOIN dimCarStatus ON dimCarStatus.CarStatusKey = P1.CarStatusKey JOIN dimCar ON dimCar.CarKey = P1.CarKey WHERE (@ExteriorColor is NULL OR dimColor.ExteriorColor like @ExteriorColor) AND (@InteriorColor is NULL OR dimColor.InteriorColor like @InteriorColor) AND (@Condition is NULL OR dimCarStatus.Condition like @Condition) AND (@Year is NULL OR dimCarStatus.[Year] like @Year) AND (@Certified is NULL OR dimCarStatus.Certified like @Certified) AND (@Make is NULL OR dimCar.Make like @Make) AND (@ModelCategory is NULL OR dimCar.ModelCategory like @ModelCategory) AND (@Model is NULL OR dimCar.Model like @Model) AND (@Trim is NULL OR dimCar.Trim like @Trim) AND (@BodyType is NULL OR dimCar.BodyType like @BodyType) AND (@VehicleTypeCode is NULL OR dimCar.VehicleTypeCode like @VehicleTypeCode) AND (@MinPrice is NULL OR P1.SellingPrice >= @MinPrice) AND (@MaxPrice is NULL OR P1.SellingPrice < @MaxPrice) AND (@Mileage is NULL OR P1.Mileage < @Mileage) ORDER BY CASE WHEN @SortOrder = 'Expensive' THEN -SellingPrice WHEN @SortOrder = 'Inexpensive' THEN SellingPrice WHEN @SortOrder = 'LowMiles' THEN Mileage WHEN @SortOrder = 'HighMiles' THEN -Mileage WHEN @SortOrder = 'Closest' THEN P1.Distance WHEN @SortOrder = 'Newest' THEN -[YEAR] WHEN @SortOrder = 'Oldest' THEN [YEAR] ELSE InventoryID END )

    Read the article

  • MSSQL add count column near existing select columns

    - by Luis
    Hi, i have a query that returns something like this. ID | Company| Total_Money | no_items | count_Total_Money_For_Company ----------------------------------------------------------- 1 | A | 1000 | 1 | 2001 2 | A | 1001 | 0 | 2001 3 | B | 1001 | 1 | 5010 4 | B | 1002 | 1 | 5010 5 | B | 1003 | 1 | 5010 6 | B | 1004 | 1 | 5010 7 | B | 1000 | 1 | 5010 How can i add that column with the count for that company?

    Read the article

  • Avoiding SQL Injection in SQL query with Like Operator using parameters?

    - by MikeJ
    Taking over some code from my predecessor and I found a query that uses the Like operator: SELECT * FROM suppliers WHERE supplier_name like '%'+name+%'; Trying to avoid SQL Injection problem and parameterize this but I am not quite sure how this would be accomplished. Any suggestions ? note, I need a solution for classic ADO.NET - I don't really have the go-ahead to switch this code over to something like LINQ.

    Read the article

  • SQL WHERE.. IN clause multiple columns

    - by ala
    i need to implement the following query in SQL Server select * from table1 WHERE (CM_PLAN_ID,Individual_ID) IN ( Select CM_PLAN_ID, Individual_ID From CRM_VCM_CURRENT_LEAD_STATUS Where Lead_Key = :_Lead_Key ) but the WHERE..IN clause allows only 1 column. How to compare 2 or more columns with another inner select?

    Read the article

  • how to compare the values inside a table in sql

    - by Ranjana
    how to compare the values of same table(say for eg: Order table) each and every time the record get inserted , if the record with same values get inserted already in same table i should not insert the new record with same values. how to do that exactly in sql server 2008

    Read the article

  • SQL updating a column in a table

    - by tecnodude
    Hi, I have the following table in an access database id VisitNo Weight 1 1 100 1 2 95 1 3 96 1 4 94 1 5 93 Now row 2 and 4 are deleted. So i have... id VisitNo Weight 1 1 100 1 3 96 1 5 93 However what i need is... id VisitNo Weight 1 1 100 1 2 96 1 3 93 What is the SQL query i need to accomplish the above? thanks

    Read the article

  • How to Collect Debug Info for Oracle SQL Developer

    - by thatjeffsmith
    In a perfect world, there would be no software bugs. Developers would always test their code. QA would find any scenarios and bugs the developers hadn’t already thought of. Regression tests would be complete and flawless. But alas, we can only afford to pay mere humans here, so we will have bugs from time to time. Or sometimes you are trying to do something the software wasn’t designed for, or perhaps your machine has exhausted it’s resources trying to build the un-buildable. When you run into problems, you will need help. Developers need your help so they can help you. Surprisingly enough, feedback like this isn’t very helpful: Your program isn’t working. How can I make it work? When you are ready to work with us on the SQL Developer OTN forum, you will most likely be asked to run SQL Developer and capture the output from the command console. In case you need help with this, ere’s a step-by-step process you can follow in Windows 7 (should work in XP too.) Open a windows command window Start – Run – CMD Once it’s open, click on the window icon and select ‘Defaults.’ Change the default buffer size to be something bigger, much bigger. Set the CMD window default buffer size HIGHER Note: you only need to do this once. Navigate to your SQL Developer Installation Folder Instead of running the ‘sqldeveloper.exe’ file in the root directory, we are going to go several sub-directories down. Find the ‘bin’ sub-directory and run the ‘sqldeveloper.exe’ there. When you do this, a CMD window will open, and then you’ll see the SQL Developer application load. The SQL Developer bin directory - run the tool from here and get a logging window Use SQL Developer as normal, until it ‘breaks’ or ‘hangs’ Now, you are ready to grab the nitty-gritty information that MIGHT tell the developer what is going wrong or happening in your scenario. Click back into the CMD window Send a Ctrl+Break or a Ctrl+Pause. If you on a newer laptop that doesn’t have this key, be sure to check the ‘Fn’ subset of keys. If you need to map the BREAK or PAUSE buttons, this article might help. You can also try the on-screen keyboard in windows – just type ‘OSK’ in your START – RUN prompt. Copy the logging information from the command window – all of it We need this information, help us get it! Open a case with Oracle Support or Start a Thread on the Forums Or email me. If you’re on my blog reading this, it’s the least I can do to help Now, before you hit ‘Send’ or ‘Post’ or ‘Submit’ – be sure to add a brief description of what you were doing in the application when you ran into the problem. Even if you were doing ‘nothing,’ let us know how many connections you had open, what windows were active, etc. The more you can tell us, the higher your odds go up to getting a quick fix or at least an answer as to what is happening. Also include the following information: The version of SQL Developer you are running The version of the JDK you are using The OS you are using The version of Oracle you are connected to Now, don’t be surprised if you get asked to upgrade to a supported configuration, say ‘version 3.1 and the 1.6 JDK.’ Supporting older versions of software is fun, and while we enjoy a challenge, it may be easier for you to upgrade your way out of the problem at hand.

    Read the article

  • SQL Server source control from Visual Studio

    - by David Atkinson
    Developers have long since had to context switch between two IDEs, Visual Studio for application code development and SQL Server Management Studio for database development. While this is accepted, especially given the richness of the database development feature set in SSMS, loading a separate tool can seem a little overkill. This is where SQL Connect comes in. This is an add-in to Visual Studio that provides a connected development experience for the SQL Server developer. Connected database development involves modifying a development sandbox database, as opposed to offline development, where SQL text files are modified independently of the database. One of the main complaints of Data Dude (VS DBPro) is that it enforces the offline approach. This gripe is what SQL Connect addresses. If you don't already use SQL Source Control, you can get up and running with SQL Connect by adding a new project to your Visual Studio solution as follows: Then choose your existing development database and you're ready to go. If you already use SQL Source Control, you will need to link SQL Connect to your existing database scripts folder repository, so SQL Connect and SQL Source Control can be used collaboratively (note that SQL Source Control v.3.0.9.18 or later is required). Locate the repository (this can be found in the Setup tab in SQL Source Control). .and create a working folder for it (here I'm using TortoiseSVN). Back in Visual Studio, locate the SQL Connect panel (in the View menu if it hasn't auto loaded) and select Import SQL Source Control project Locate your working folder and click Import. This creates a Red Gate database project under your solution: From here you can modify your development database, and manage your changes in source control. To associate your development database with the project, right click on the project node, select Properties, set the database and Save. Now you're ready to make some changes. Locate the object you'd like to modify in the Solution Explorer, and double click it to invoke a query window or table designer. You also have the option to edit the creation SQL directly using Edit SQL File in Project. Keeping the development database and Visual Studio project in sync is as easy as clicking on a button. One you've made your change, you can use whichever mechanism you choose to commit to source control. Here I'm using the free open-source AnkhSVN to integrate Subversion with Visual Studio. Maintaining your database in a Visual Studio solution means that you can commit database changes and application code changes in the same changeset. This is desirable if you have continuous integration set up as you want to ensure that all files related to a change are committed atomically, so you avoid an interim "broken build". More discussion on SQL Connect and its benefits can be found in the following article on Simple Talk: No More Disconnected SQL Development in Visual Studio The SQL Connect project team is currently assessing the backlog for the next development effort, and they'd appreciate your feature suggestions, as well as your votes on their suggestions site: http://redgate.uservoice.com/forums/140800-sql-connect-for-visual-studio- A 28-day free trial of SQL Connect is available from the Red Gate website. Technorati Tags: SQL Server

    Read the article

  • How to optimize simple linked server select query?

    - by tomaszs
    Hello, I have a table called Table with columns: ID (int, primary key, clustered, unique index) TEXT (varchar 15) on a MSSQL linked server called LS. Linked server is on the same server computer. And: When I call: SELECT ID, TEXT FROM OPENQUERY(LS, 'SELECT ID, TEXT FROM Table') It takes 400 ms. When I call: SELECT ID, TEXT FROM LS.dbo.Table It takes 200 ms And when I call the query directly while being at LS server: SELECT ID, TEXT FROM dbo.Table It takes 100 ms. In many places i've read that OPENQUERY is faster, but in this simple case it does not seem to work. What can I do to make this query faster when I call it from another server, not LS directly?

    Read the article

  • SQL Max Group By Query Help

    - by Hcabnettek
    Hi All, I have a quick question. How do I select the two values I need in one query? Currently I'm doing this, which works fine, but it's obviously running two queries when one should do the trick. I tried MAX(columnA) and GROUP BY ColumnB, but that returns multiple row. I only want one row returned. DECLARE @biID bigint , @dtThreshold DateTime SELECT @biID = MAX(biID) FROM tbPricingCalculationCount WITH (NOLOCK) SELECT @dtThreshold = dtDateTime FROM tbPricingCalculationCount WITH (NOLOCK) WHERE biID = @biID I would like both those variables to be set correctly in one query. How can I do that? Thanks, ~ck

    Read the article

  • Problem with Linq query and date format

    - by Alan T
    Hi All I have a C# console application written using Visual Studio 2008. My system culture is en-GB. I have a Linq query that looks like this: var myDate = "19-May-2010"; var cus = from x in _dataContext.testTable where x.CreateDate == Convert.ToDateTime(myDate) select x; The resulting SQL query generates and error because it returns the dates as "19/05/2010" which it interprets as an incorrect date. For some reason even though my system culture is set to en-GB it looks like it's trying to intrepret it as a en-US date. Any ideas how I get around this? Thanks. Alan T

    Read the article

  • SQL Self Join Query Help

    - by hdoe123
    Hi All, I'm trying to work out a query that self join itself on a table using the eventnumber. I've never done a self join before. What i'm trying to query is when a client has started off in a city which is chester to see what city they moved to. But I dont want to be able to see if they started off in another city. I would also like be only see the move once (So i'd only like to see if they went from chester to london rather then chester to london to wales) The StartTimeDate is the same EndDateTime if they moved to another city. Data example as follows if they started off in the city chester :- clientid EventNumber City StartDateTime EndDateTime 1 1 Chester 10/03/2009 11/04/2010 13:00 1 1 Liverpool 11/04/2010 13:00 30/06/2010 16:00 1 1 Wales 30/07/2010 16:00 the result I would like to see is on the 2nd row - so it only shows me liverpool. Could anyone point in the right direcetion please?

    Read the article

  • SQL Server - Query Short-Circuiting?

    - by Sam Schutte
    Do T-SQL queries in SQL Server support short-circuiting? For instance, I have a situation where I have two database and I'm comparing data between the two tables to match and copy some info across. In one table, the "ID" field will always have leading zeros (such as "000000001234"), and in the other table, the ID field may or may not have leading zeros (might be "000000001234" or "1234"). So my query to match the two is something like: select * from table1 where table1.ID LIKE '%1234' To speed things up, I'm thinking of adding an OR before the like that just says: table1.ID = table2.ID to handle the case where both ID's have the padded zeros and are equal. Will doing so speed up the query by matching items on the "=" and not evaluating the LIKE for every single row (will it short circuit and skip the LIKE)?

    Read the article

  • Optimizing a Soundex Query for finding similar names

    - by xkingpin
    My application will offer a list of suggestions for English names that "sound like" a given typed name. The query will need to be optimized and return results as quick as possible. Which option would be most optimal for returning results quickly. (Or your own suggestion if you have one) A. Generate the Soundex Hash and store it in the "Names" table then do something like the following: (This saves generating the soundex hash for at least every row in my db per query right?) select name from names where NameSoundex = Soundex('Ann') B. Use the Difference function (This must generate the soundex for every name in the table?) select name from names where Difference(name, 'Ann') = 3 C. Simple comparison select name from names where Soundex(name) = Soundex('Ann') Option A seems like to me it would be the fastest to return results because it only generates the Soundex for one string then compares to an indexed column "NameSoundex" Option B should give more results than option A because the name does not have to be an exact match of the soundex, but could be slower Assuming my table could contain millions of rows, what would yield the best results?

    Read the article

facebook twitter digg google delicious technorati stumbleupon myspace wordpress linkedin gmail igoogle windows live tumblr viadeo yahoo buzz yahoo mail yahoo bookmarks favorites email print

< Previous Page | 96 97 98 99 100 101 102 103 104 105 106 107  | Next Page >