SQL Server

Microsoft SQL Server 2017 powers your entire data estate by supporting structured and unstructured data sources. It builds on previous versions of SQL Server, which have been industry leading for four years in a row and a leader in TPC-E. It scales to petabytes of data and allows customers to process big data through PolyBase using T-SQL over any data. SQL Server has also been the least vulnerable database during the last seven years. SQL Server 2017 brings data insights with business intelligence capabilities that provide analytics at a fraction of the cost on any device along with advanced analytics with support for R and Python.

More details about SQL Server are available in the SQL Server documentation.
If you have a technical issue, please open a post on the developer forums through Stack Overflow or MSDN.

  • Hot ideas
  • Top ideas
  • New ideas
  • My feedback
  1. Characters and linebrakes are disappering in a Job with powershell that holds an \

    Characters and linebrakes are disappering in a Job with powershell that holds an \

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 2

    <=-=Jul 24 2008 9:11AM=-=>

    Eric Verschoor,

    Thanks for the report – we will investigate this issue.

    -Richard Waymire

    <=-=Dec 1 2011 11:47AM=-=>

    We took a look at this bug along with several others recently.
    Unfortunately, triaging it against other critical bugs, I do not think we would get to investigating this in the near future.
    However, we have taken note of this internally, and when we revisit this functionality in the future, we will try and get this resolved.

    Thank you
    Alex Grach[MSFT]

  2. Transaction log file size will not grow exactly 4GB when filegrowth=4GB.

    When I set filegrowth to 4GB , 4096MB or 8GB for transaction log file of any database, after the log fills up, the autogrowth will kick in and then I monitor file growth of the log file. It will not grow exactly 4GB or 8GB. It grows in much smaller chunks. Something like, 248KB.
    If I set log filegrowth to 3GB, 5GB, 4095MB, 4097MB or anything that's not exactly 4GB, it will grow exactly the set size for filegrowth.
    This also happens on SQL2005 Developer Edition and Standard Edition on x86.

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 5

    <=-=Aug 12 2008 1:22PM=-=>

    Thank you for your report. This does seem like an interesting anomaly. We will see if we can reproduce this in the current product and addess it in ongoing development.

    <=-=Aug 4 2009 9:44AM=-=>

    I’m seeing behavior similar to this now with VLF and log files on the latest CUs of SQL 2005 and 2008.
    I was attempting to create a 40GB log file in 5 steps of 8GB each and I don’t arrive at 16 × 5 VLFs of the same size. I’m going to do some more testing before I log a bug.

  3. Many to many dimension query returns incorrect value

    I've just been shown a repro of what looks like a bug where a query with a many-to-many dimension and the expression MyM2MDim.MyHier.Children in the Where clause returns an incorrect value. It looks like a case where an incorrect assumption has been made for query optimisation purposes. I can repro on AS2005 SP2 and AS2008 RC0.

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 2

    <=-=Jul 24 2008 8:50PM=-=>

    Thanks a lot Chris for reporting the problem.

    Unfortunately it is too late to look at this in 2008 release of Analysis Services.
    We will take a look at this in the next release.

    Edward Melomed

    <=-=Aug 1 2008 12:56AM=-=>

    I think that a good solution would be a different one than simply changing the behavior of the query optimizer. I would add a property to measures (or measure groups) to specify if the evaluation of that specific measure/measure group has to always consider the related m2m dimension and its intermediate measoure group or if this passage can be optimized. This would solve the issue I described in my m2m paper (http://www.sqlbi.eu/manytomany.aspx) related to the distinct count alternative measure.

  4. Reporting Visual Studio: ShowGrid is not working

    In Visual Studio
    Tools -> Option -> Windows Forms Designer -> General the Settings are
    LayoutMode=SnapToGrid
    ShowGrid=True
    SnapToGrid=True

    But there is no visible Grid.

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 79

    <=-=Jul 22 2008 1:26PM=-=>

    Thank you for providing this feedback. With SQL Server 2008 we replaced grid lines with object snapping and snap lines. We will consider this as feedback that you would like grid lines back. We will consider adding them back in a future release. If this feature is brought back, however, it would not be enabled by this VS option, the Designer would have it’s own command to display or hide grid lines. The VS option you mention is for designing WinForms apps, not directly related to the Report Designer.

    <=-=Jul 23 2008 2:04AM=-=>

    Many thanks for your reply!

    Greetings
    Webfred

    <=-=Oct 23 2008 12:55PM=-=>

    I would also like the SnapToGrid, DrawGrid, and GridSpacing options back. Report development is so much more cumbersome with the object snapping and snap lines. I could actually see how things lined up when the grid was there. Now I’m…

  5. Cannot install 08-040 for SQL 2000 on Vista

    According to
    http://www.microsoft.com/downloads/details.aspx?familyid=8316BC5E-8C2D-4710-8ACC-B815CCC81CD4&displaylang=en
    this hotfix is supported on Vista. However if try to run the install on
    Vista, it fails with "There is not enough storage for this
    command".

    I have x64, but I have reports that it fails on x86 as well.

    Since SQL 2000 is not supported on Vista, the error may be on the download page.

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  6. Edit Menu -> Advanced -> Tabbify / Untabbify Selected Lines option is not working

    I select 10 lines of code and then selected "Tabbify Selected Lines" menu from "Edit->Advanced", but its not working, similary "Untabbify Selected Lines" as well.

    FYI, The uppercase and lowercase converts everything into uppercase, if we have some option to convert only keywords to uppercase then it would make the code look good and also please assign some shortcuts for "Tabbify Selected Lines" and "Untabbify as well, because those are options which we regularly use, so using the menu is time consuming so if we have shortcut it would save lot of time and easy to use as well

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 0

    <=-=Aug 27 2008 9:30AM=-=>

    arunraj_c,

    Thank you for reporting this issue. We will mark it as a product issue and request dev team to investigate it.

    Regards,
    Eric

    <=-=Jun 20 2011 12:31AM=-=>

    Hi Arun,

    This is regarding the connect issue 357143. Kindly note that the function “tabify” converts all tab length white spaces to a tab. So there is no visual indication and tabify does not mean to move selected text by a tab. Say there are 4 white spaces in a tab, then tabify would look for 4 consequent white spaces in the selected text and replace them by a single tab character. Similarly untabify replaces a tab character with equivalent number of white space characters.

    I hope this explains the behavior. We are closing the issue as by-design.

    thank you

    Seshagiri
    PM, SSMS

  7. Data Collection: Disk Usage Summary

    Incorrect Figure in Disk Space Used

    Workbench is a SQL Server 08 RC0

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 3

    <=-=Jul 11 2008 7:02PM=-=>

    Hi Nitya,
    thank you for your bug submission regarding the problem with the disk usage report. we are aware of the problem already and will fix this in a future release.
    Thank you,
    Bill Ramos

    <=-=Jun 18 2009 8:28AM=-=>

    Just a note – I needed to do some hosecleaning to duplicate this issue to the active connect item. This is now linked to http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=356518.
    Thank you,
    Bill Ramos

  8. Multiple Filters using hinting a spatial index should work

    The following query produces this error message when I do not believe its appropriate to:

    Msg 8635, Level 16, State 10, Line 2
    The query processor could not produce a query plan for a query with a spatial index hint. Reason: Could not find required comparison predicate. Try removing the index hints or removing SET FORCEPLAN.

    The BOL topic: "Geometry Methods Supported by Spatial Indexes" doesn't require just one filter.

    The queries in question:

    drop table dbo.shapeParts
    go
    create table dbo.shapeParts(

       shapePartID tinyint not null identity(1,1) primary key,
    
    shapeID tinyint not null,
    shapeQuad tinyint null,
    shape geometry not null);

    go …

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 1

    <=-=Jul 24 2008 1:04PM=-=>

    Thank you for the bug submission. Spatial indexes match only WHERE/ON clauses made of single atomic predicates or ANDs thereof — not ORs. We also do not infer that STIntersects here is redundant.

    <=-=Jul 28 2008 10:25AM=-=>

    We we have not done a good job (yet!) documenting how exactly Katmai spatial indexing works; capabilities, limitations, recommendations. This is a sore spot and it’s overdue. Meanwhile, “index union” support will go on the list of possible enhancements for next release.

    <=-=May 25 2011 9:52AM=-=>

    This is resolved as duplicate of 670505 connect item.
    https://connect.microsoft.com/SQLServer/feedback/details/670505/cross-feature-support-for-spatial-types-should-improve

  9. SQLServer Exception with SPs that start with Sys, contain TVP, and accessed via ADO.NET

    A fatal exception occurs, and SQL Server terminates the session, if you call a stored procedure from C#, and that stored procedure (1) contains a TVP and (2) begins with the letters "sys".

    For example, the following causes the problem:
    ALTER PROC [dbo].[SystemConfiguration_StoreContact]

    @ItemList contactDetail READONLY
    

    but these do not:
    ALTER PROC [dbo].[ystemConfiguration_StoreContact]

    @ItemList contactDetail READONLY
    

    ALTER PROC [dbo].[SystemConfiguration_StoreContact]

    @ItemList int
    

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 1

    <=-=Jul 28 2008 9:25AM=-=>

    Hi Brian,

    Thankyou for this bug report. After investigation, the situation that triggers this bug is:

    A CLR routine calls a stored proc that 1) contains a TVP and 2) has a name beginning “sys”.
    (yes, I know – we are probably thinking the same thoughts :-)

    Anyhow, we have a fix for this bug, pending review and checkin.

    Meantime, a workaround is to rename your stored proc to not begin “sys”.

    Thanks,

    Jim

    <=-=Jun 22 2010 1:57PM=-=>

    Ran into this bug today on a sql server 2008 instance and it took some time to diagnose. Is the fix available? When was it released? Where can I get it?

    <=-=Jan 4 2011 2:55PM=-=>

    Hi,
    We have fixed this issue for an upcoming cumulative update for SQL Server 2008 R2. Thanks again for reporting it.


    Umachandar, SQL Programmability Team

  10. Intellisense does not display properly without Use statement

    When typing select statements using Intellisense without prefixing the query in the query window with a Use statement, the select statement does not show the appropriately available table names.

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 1

    <=-=Aug 11 2008 7:15AM=-=>

    Hi,

    Thank you for reporting this problem. Intellisense tries to determine the context mentioned in the query to display the list and not from the connection instance. However if you launch the query editor on the current database making it the default context, this problem does not occur. I can understand that this can sometimes be a pain when using the feature. We will try to address the issue in a future release of SQL Server.

    Thanks,
    Karthik

    <=-=Mar 10 2011 6:25AM=-=>

    My 2 cents: The context chosen by Intellisense is perfectly OK to me – seems logical (on 2008 R2 CU3). However, the choice for a default context for new query windows is less perfect than it was in 2005. Sorry, haven’t yet understood the specifics, but I know I’m suprised to find ‘master’ up there twice a day.

    <=-=Jun 28 2011 6:19PM=-=>

  11. SSRS2008 RC0 row headers visible and repeated bug

    Checking the options:
    Repeat header rows on each page
    Header should remain visible while scrolling
    in the Tablix properties page has no effect.
    The headers do not repeat, and they don't remain visible while scrolling.

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 1

    <=-=Jul 29 2008 10:52AM=-=>

    Thank you for using SQL Server Reporting Services
    We are investigating this issue for the next release. Were the reports your working with upgraded from SSRS 2005?
    Thank you for your time.

    <=-=Jul 29 2008 11:12AM=-=>

    No, they weren’t upgraded. I found my answer in
    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=356434&wa=wsignin1.0
    Thanks.
    Because the header row for the Table template is a Fixed Row and not a group header, it is not affected by the options I selected. I tried checking those options for a matrix, and it worked fine. It would be better if the options worked with a table template as well.

    <=-=Jul 31 2008 11:49AM=-=>

    Thank you for the closing notes.

  12. Crash on Startup

    This is from the Application event log:
    Faulting application ssmsee.exe, version 2005.90.3042.0, stamp 45cd6a6a, faulting module mscorwks.dll, version 2.0.50727.3082, stamp 492b82c1, debug? 0, fault address 0x0000c98e.

    Just yesterday I installed a few MS updates.

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 0

    <=-=Dec 13 2012 11:46AM=-=>

    Let us know if you see this issue in SQL 2012 SP1.

    Thanks
    Sethu Srinivasan [MSFT]
    SQL Server

    <=-=Dec 14 2012 3:49PM=-=>

    Please reactivate this issue if you have more information about the crash

    Thanks
    Sethu Srinivasan

  13. Fuzzy lookup triggers SQLDUMPER, even with very small dataset with Run64BitRuntime set to false

    Fuzzy lookup running small, published package, triggers SQLDUMPER, even with very small dataset, with Run64BitRuntime set to false. Runs fine with it set to true.

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 28

    <=-=Sep 24 2009 1:22PM=-=>

    Thank you for your submission. We will be reviewing your information and providing you feedback on our findings and progress. Thanks for your interest and support of SSIS.

    <=-=Oct 1 2009 2:20PM=-=>

    I get the same issue running on Vista Business Edition (x64).

    <=-=Nov 22 2009 5:39PM=-=>

    I ran into the same problem. Simple Fuzzy Lookup which hangs: doesn’t pass any records downstream and remains yelllow. No error messages, it just hangs. And the log says that the package was cancelled (even though the Data Flow on the Control Flow tab, and the transforms on the Data Flow up until and including the Fuzzy Lookup are all still yellow. Doesn’t seem to be anything unusual about the Fuzzy Lookup transform.

    I have gotten other Fuzzy Lookups to work on the same server without this bug.

    <=-=Dec 9 2009 8:30AM=-=>

    same issue on Microsoft …

  14. ReportViewerControl Crash When Printing To XPS DocumentWriter

    When printing to the XPS document writer printer from a ReportViewerControl on a WinFrom app, the application will crash.

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 0

    <=-=Sep 28 2009 11:29AM=-=>

    Thank you for your feedback. The issue that you have reported is already tracked by another work item in our database. We are resolving this issue as “Duplicate”.

    Thank you.

    Stella Chan
    SQL Server Reporting Services

    <=-=Nov 30 2009 4:22PM=-=>

    Is it possible for you to tell me what item it is a duplicate of and whether there is likely to be a fix? I have no way of tracking the status of this bug now.

  15. Some times the password is not get saved in SQL Server 2005

    I am using SQL Server 2005, Always i will check the option "Remember password" to save my password while connecting to the Database Engine. But the problem is, it is again asking for the password when i next login to the same server.

    This is happening in all the systems of my office. Please fix this bug as soon as possible

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 2

    <=-=Sep 14 2009 5:48AM=-=>

    I am using SQL Server 2005, Always i will check the option “Remember password” to save my password while connecting to the Database Engine. But the problem is, it is again asking for the password when i next login to the same server.

    This is happening in all the systems of my office. Please fix this bug as soon as possible

    <=-=Sep 14 2009 11:38AM=-=>

    This has been reported several times already. See the following feedback items:

    124842
    163877
    222848
    330055
    332722
    339592

    <=-=Aug 27 2010 10:24AM=-=>

    Still a problem. No matter how often it gets reported, it seems microsoft doesn’t care.

    <=-=May 22 2011 3:02AM=-=>

    Hi:

    We were tracking this issue through another bug. Hence we are closing this as a duplicate. I quote the workaround to avoid this problem here:

    Refer to:
    http://connect.microsoft.com/SQLServer/feedback/details/124842/remember-password-option-is-not-remembered-in-registered-server-properties

    The only change that we suggest is, please do…

  16. Updates such as changes in Role membership are slow or timeout in large databases Initial Connection is Slow

    Updates such as role membership changes or calculated member changes are extremely slow or timeout with large databases with many partitions, and substantial aggregations.
    As a hack workaround we have been able to update the the MDXScript file and bounce the SSAS service to make changes in a fraction of the time it takes to update via BIDS or Mgt. Studio. In addition, any intial connection to a sever with one or more large databases is very slow as metadata is loaded. It appears that a single aggregation design which is defined once in a measure group and reused for…

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  17. logging of ssis tasks are incomplete when run by sql agent job

    when a logging-enabled package (stored in msdb) is run by a sql server agent job, the log data is not complete.
    If it is run in BIDS, it logs all data like I would expect it to do.

    i have reproduced this on two sql environments - both sql2008 and win2003srv

    is this an issue with the sql agent job (dtexec)?
    or with the property of LoggingMode 'UseParentSetting'?

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 10

    <=-=Sep 24 2009 1:01PM=-=>

    Thank you for your submission. We will be reviewing your information and providing you feedback on our findings and progress. Thanks for your interest and support of SSIS.

    <=-=Oct 5 2009 12:57AM=-=>

    can anybody confirm this issue? it takes just some minutes to follow the steps. I can also give more information, if neccessary.

    <=-=Dec 15 2009 11:29AM=-=>

    See http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/02874b24-0e09-4b71-bf45-2db26d1310bc/ for more people encountering this issue. No word in two and a half months?

    <=-=Dec 21 2009 5:46AM=-=>

    I made up a thread here: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/511502a6-99d6-4693-94da-2c317c3f59c6

    feedback from MS: it will be fixed in next major release => SQL11 (not SQL10 R2) …
    … i’m disappointed – because it’s a common functionality without any customiziation

    <=-=Dec 13 2011 10:30AM=-=>

    I created several new packages from scratch with SSIS 2008 R2 and I experienced the same problem with most of the events including OnInformation. The…

  18. Space usage after disabling change tracking

    After disabling change tracking, space used for change tracking is not released.

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 1

    <=-=Oct 5 2009 9:38AM=-=>

    We will take a further look at this issue. But I believe change tracking is cleaned by checkpoints in a lazy fashion. So if this space is never recovered even after few hours please let us know.

    <=-=Oct 19 2009 8:10PM=-=>

    I have this installation on 7 servers. After waiting 1 month, still none of them was cleaned.

    <=-=Jan 5 2010 10:27AM=-=>

    Could you please try with SQL 2008 sp1 CU4? We believe this issue is resolved in this hotfix update. You can contact customer support to get hold of hotfixes. Thanks

    <=-=Jan 11 2010 3:04PM=-=>

    Closing this issue as resolved. If the issue still persists after SP1 CU4, please let us know.

  19. SQL Server 2008 SP1 - Reporting Services Rounds Parameters of Data Type Datetime2(7) to Datetime

    Reports executed via Report Manager convert parameters of data type DATETIME2(7) to data type DATETIME and truncates any fractional seconds. For instance, a value of 9/30/09 23:59:59.9999999 is rounded to 9/30/09 23:59:59.000.

    Reports executed via subscription convert parameters of data type DATETIME2(7) to data type DATETIME and round to the nearest value which may be higher or lower than the supplied value. For instance, a value of 9/30/09 23:59:59.9999999 is rounded to 10/01/09 00:00:00.000.

    The behavior between the execution methods should be consistent at a minimum. Also, it would be nice if it could support DATETIME2(7) instead of rounding to…

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  20. Query optimizer generates incorrect plan with a deffered filter operator

    If you execute both queries below, in SQL Server 2008 (with or without SP1), the second query is more optimized than the first one.

    select SalesOrderID, SalesOrderNumber
    from Sales.SalesOrderHeader
    where OrderDate = '20010702'

    select SalesOrderID, SalesOrderNumber
    from Sales.SalesOrderHeader with(index(1))
    where OrderDate = '20010702'
    go

    The first one has this execution plan:
    StmtText

    SELECT [SalesOrderID],[SalesOrderNumber] FROM [Sales].[SalesOrderHeader] WHERE [OrderDate]=@1
    |--Filter(WHERE:([AdventureWorks2008].[Sales].[SalesOrderHeader].[OrderDate]='2001-07-02 00:00:00.000'))

       |--Compute Scalar(DEFINE:([AdventureWorks2008].[Sales].[SalesOrderHeader].[SalesOrderNumber]=[AdventureWorks2008].[Sales].[SalesOrderHeader].[SalesOrderNumber]))
    
    |--Compute Scalar(DEFINE:([AdventureWorks2008].[Sales].[SalesOrderHeader].[SalesOrderNumber]=isnull(N&#39;SO&#39;+CONVERT(nvarchar(23),[AdventureWorks2008].[Sales].[SalesOrderHeader].[SalesOrderID],0),N&#39;*** ERROR ***&#39;)))
    |--Clustered Index Scan(OBJECT:([AdventureWorks2008].[Sales].[SalesOrderHeader].[PK_SalesOrderHeader_SalesOrderID]))

    The second one , in which I force to use the cluster index, the query optimizer chooses to apply the filter condition while doing the cluster index scan, which is the…

    0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 3

    <=-=Oct 5 2009 12:08PM=-=>

    Dear Customer,

    Thanks for taking the time to file this issue. I’ve tried but failed to create a repro by examining the query and plans that you provided. Please could you provide the table and clustered index definitions? This looks like something that could be tightened.

    thank-you,
    Campbell Fraser, SQL Development

    <=-=Oct 14 2009 4:59AM=-=>

    Hi Campbell.
    I�m using the default configuration from AdventureWorks2008, here is a resume of the table structure:

    Columns

    SalesOrderID int
    RevisionNumber tinyint
    OrderDate datetime
    DueDate datetime
    ShipDate datetime
    Status tinyint
    OnlineOrderFlag Flag
    SalesOrderNumber nvarchar
    PurchaseOrderNumber OrderNumber
    AccountNumber AccountNumber
    CustomerID int
    SalesPersonID int
    TerritoryID int
    BillToAddressID int
    ShipToAddressID int
    ShipMethodID int
    CreditCardID int
    CreditCardApprovalCode varchar
    CurrencyRateID int
    SubTotal money
    TaxAmt money
    Freight money
    TotalDue money
    Comment nvarchar
    rowguid uniqueidentifier
    ModifiedDate datetime

    Indexes

    AK_SalesOrderHeader_rowguid nonclustered, unique located on PRIMARY rowguid
    AK_SalesOrderHeader_SalesOrderNumber nonclustered, unique located on PRIMARY SalesOrderNumber
    IX_SalesOrderHeader_CustomerID nonclustered located on …

  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base