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.

Do you have a comment or suggestion to improve SQL Server? We’d love to hear it!

(thinking…)

Enter your idea and we'll search to see if someone has already suggested it.

If a similar idea already exists, you can support and comment on it.

If it doesn't exist, you can post your idea so others can support it.

Enter your idea and we'll search to see if someone has already suggested it.

  • Hot ideas
  • Top ideas
  • New ideas
  • My feedback
  1. xp_cmdshell

    Hi, I have discovered a very strange problem in MS SQL Server 2005. When we reset the credentials of

    "##xp_cmdshell_proxy_account##" through the command

    EXEC sp_xp_cmdshell_proxy_account 'SHIPPING\KobeR','sdfh%dkc93vcMt0'

    it works always but when you try doing it from Enterprise manager it doesn't works

    In fact in my situation the ##xp_cmdshell_proxy_account## was already running under an accound which had sysadmin privilages on sql server and also Administrative privilages on OS but when I tried running it through another user which was a non sysadmin, the xp_cmdshell failed with error code 1385.

    Here is the step I took
    1) In sql server server property…

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

    We’ll send you updates on this idea

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

    Upvotes: 2

    <=-=Aug 12 2008 7:33PM=-=>

    Hi,

    Thank you for your feedback. We will look into your reported issue and reply shortly.

    <=-=Aug 13 2008 8:33AM=-=>

    The message 1385 returned from xp_cmdshell indicates that the proxy account does not have permission to connect to the local machine. Using the “net helpmsg 1385” command in a command window results in:

    Logon failure: the user has not been granted the requested logon type at this computer.

    The sp_xp_cmdshell_proxy_account procedure will make sure the proxy account has the SeBatchLogonRight while management studio will only directly manipulate the CREDENTIAL DDL. In the case where only the DDL is used the SeBatchLoginRight will need to be manually assigned to the new proxy account.

    Thanks for identifying this issue. We are investigating to see if this can be resolved in a future release.

    <=-=Aug 17 2008 10:41PM=-=>

    Hi you are very much correct on the…

  2. iSeries RUNRMTCMD doesn't work with SQLCMD but only with OSQL

    We have to run SQL Server querys in SQL 2005 by calling a windows batch file from a As400 / iSeries CL program with RUNRMTCMD.
    This batch file uses SQLCMD command to run an TSQL statement. But it doesn't work. It works when the windows batch file is called directly on the PC not using runrmtcmd.
    The only way I found was not to use SQLCMD but to use OSQL, which works.
    If OSQL is not supported in future versions of SQL Server, how to solve this problem in the future?

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

    We’ll send you updates on this idea

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

    Upvotes: 1

    <=-=Aug 27 2008 5:25PM=-=>

    Hello Gego,

    Thank you for reporting the problem. This may be an issue specific to the 3rd party utility you are using. However if you have configured you sql server for SQL Authentication, you could try using SQL security instead of Windows security. In the command line parameters to sqlcmd / osql try replacing -E with -U and -P options.

    Thanks,
    Karthik

    <=-=May 24 2012 1:31PM=-=>

    Thanks for your feedback. SInce we have not heard back from you on this issue, we are treating this as closed.

    <=-=Jun 4 2013 7:26AM=-=>

    I am fighting this right now, and the solution I find on all the blogs is to use OSQL. (A tool that has been marked for removal). I want SQLCMD to work if it is the current tool. What does SQLCMD do different than OSQL. I really need a work around…

  3. SSAS Using translations for dimensions causes duplicate attribute hierarchies

    Hello,

    I have encountered a problem where using translations for dimensions seems to cause my attribute hierarchy list to duplicate on the translation tab.

    Here is what my screen looks like when in the error state:

    The primary key shows up first then I have the apparent duplicate attributes. Next I have as set of attributes that have attribute data translations used. On these I have an icon to the left of the attribute name that has six blue boxes. These appear at the bottom of my list. There are no icons like these on the apparent duplicate attributes.

    I…

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

    We’ll send you updates on this idea

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

    Upvotes: 0

    <=-=Oct 13 2008 11:50AM=-=>

    Thanks srwoehrle for reporting the problem.

    BI Dev studio is using OWC ( Office Web Components) and the problem might be with that.
    We are going to take a look at this for the future release of Analysis Services. Hope this problem is not critical for your customers.

    The Analysis Services Team.

    <=-=Apr 22 2010 10:38AM=-=>

    Hi, we are currently investigating the issue you have reported regarding creation of duplicate attribute hierarchies when using translations for SSAS. Can you help specify where you see these duplicated? Is it in then dimension or the cube browser? In addition, it would be very helpful if you would provide a screenshot. Thanks in advance

    <=-=Apr 30 2010 1:24PM=-=>

    Hello,

    The problem is seen in BI Dev studio only. I can provide a screenshot next week if that would be helpful. It seems like your last comment…

  4. SQL Server 2008 gives error when attempting to open project in SourceSafe

    When using SQL Server 2008 SQL Server Management Studio to open a database project under Visual SourceSafe control I get the following error:
    "Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG))

    SQL Server 2005 SQL Server Management Studio can open the same project on the same workstation with no problem.

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

    We’ll send you updates on this idea

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

    Upvotes: 0

    <=-=Aug 13 2008 11:37AM=-=>

    When I attempt to create a new database project on my XP x64 workstation SQL does not create the .ssmssln file. Only the the .ssmssqlproj file.

    I installed SQL Server 2008 Developer Edition on a x86 XP SP2 workstation and was able to successfully create and access database projects.

    <=-=Aug 14 2008 4:49AM=-=>

    I uninstalled both SQL Server 2005 and SQL Server 2008 Developer Editions on my workstation. I then reinstalled ONLY SQL Server 2008 Developer Edition. Everything works correctly now. There must be some conflict between the two editions that was causing this problem.

    <=-=Aug 27 2008 10:02AM=-=>

    RLKittrell,

    Thank you for reporting this issue. I will request product team to further detail bug fix / enhancement with source control features along with this issue.

    Regards,
    Eric

    <=-=Dec 2 2008 9:29AM=-=>

    Hello R L Kittrell,

    Side by side installation of SSMS 2005 and …

  5. Intellisense & Line Wrap Issues

    This is a minor issue, but it is annoying when you type fast. It requires line wrapping to be on (with or without markers).

    When typing near the wrap point at the line end, if the Intellisense statement doesn't complete before the wrapped point, the auto-completion will insert and then punt the cursor some distance right in the text stream, past the wrap point to the next line that had wrapped. It appears that the punt is the number of characters that were auto-inserted, plus one, but it is unclear since it is hard to count the exact characters with…

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

    We’ll send you updates on this idea

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

    Upvotes: 1

    <=-=Aug 27 2008 10:04AM=-=>

    FBuchan,

    Thank you for reporting this issue. We will request further investigation on this issue to the product team.

    Regards,
    Eric

    <=-=Jun 28 2011 11:20AM=-=>

    Thanks for your feedback. This particular problem should be fixed in the next release of SQL Server.

  6. SQL 2008 Management Studio Breaks SQL 2005 Solution Explorer

    After installing SQL 2008 side-by-side with SQL 2005, SQL 2005 Management Studio can no longer open SQL Solution files.

    Note that SQL 2008 SSMS can open the solution.

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

    We’ll send you updates on this idea

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

    Upvotes: 18

    <=-=Aug 27 2008 4:32AM=-=>

    Hello SQLCraftsman,

    Thank you for reporting the problem to us. If the error message displays the dll name please let us know. We will try to address the problem in a future release of SQL Server.

    Thanks,
    Karthik

    <=-=Oct 14 2008 5:04AM=-=>

    I have the same problem on a german windows 2008 Server System (x64). After installing SQL Server 2008 (Express) Side by Side i cannot open any projects in SSMS 2005 :-((

    <=-=Oct 14 2008 5:15AM=-=>

    That solved my problem with SSMS 2005: http://www.sqlcoffee.com/Troubleshooting010.htm
    I can’t say if this affected SSMS 2008 in any way…

    <=-=Dec 2 2008 9:50AM=-=>

    Side by side installation of SSMS 2005 and SSMS 2008 will cause this problem with SQL Server Project files. The current workaround is to install SSMS 2005 and SSMS 2008 on different machines.

    Thanks,
    -Karthik

    <=-=Nov 10 2009 7:18PM=-=>

    I’m a developer and the …

  7. Calling sqlcmd.exe from a Scheduled Task will leak handles in sqlservr.exe and lsass.exe

    I have a case where I am running SQL Express 2005 SP2 and am using Windows Scheduled Tasks to run sqlcmd.exe to run certain SQL scripts at regular intervals. I discovered that if the task logins in with an account that is not interactively logged in to the server (like the account in which my SQL Server instance runs), then every time the task runs both sqlservr.exe and lsass.exe will use a few extra handles that they never release. It's harder to track with lsass but sqlservr will consume 1 handle for each task that is run and never release…

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

    We’ll send you updates on this idea

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

    Upvotes: 3

    <=-=Aug 27 2008 4:54AM=-=>

    Hello Ron Michael

    Thank you for reporting the problem to us. Could you please attach a sample script you ran in your setup with the non-interactive login.

    Thanks,
    Karthik

    <=-=Aug 27 2008 5:53AM=-=>

    Hello Karthik. This is a sample of the type of command I run in my scheduled task:

    “c:\program files\microsoft sql server\90\tools\binn\SQLCMD.EXE” -s localhost -E -i “mytasks.sql”

    mytasks.sql will contain various T-SQL – usually I’m executing a stored procedure, but it doesn’t matter what I do.

    By the way, this is running on a domain controller on a small network – it’s the client’s one and only server.

    <=-=Mar 4 2009 5:19AM=-=>

    I am seeing the same exact issue. Any update on this bug? Eventually the server does run out of memory…

  8. Update status bar colour when changing connections.

    In SQL Server Management Studio 2008, there is an option to assign status bar colors to different connections. When new query windows are opened. The color of the status bar takes the custom color that was set in the server registration's connection properties (or the default if none was set).

    If the connection is changed subsequently. The color of the status bar remains unchanged. See attached wmv.

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

    We’ll send you updates on this idea

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

    Upvotes: 65

    <=-=Aug 26 2008 8:41AM=-=>

    Hi mjswart,

    Thank you for reporting the problem to us. Can you please clarify your point “6. Change connection and specify a different server”

    By this did you mean you changed the connection information “Server name” in the server registration or you meant you created a new server registration without any changes to the Custom color properties?

    Thanks,
    Karthik

    <=-=Aug 26 2008 8:01PM=-=>

    Karthik, Can you not see the wmv files I’ve attached? I do my best to be clear.

    When I say change connection, I mean click the “Change connection” icon in the task bar and specify a new server name. After clicking okay, the current query window is now connected to a different server.

    <=-=Nov 26 2008 6:24AM=-=>

    Any updates?

    <=-=Oct 19 2009 8:30AM=-=>

    A relevant blog post by Buck Woody:
    http://blogs.msdn.com/buckwoody/archive/2009/10/19/color-me-informed.aspx

    <=-=Nov 30 2009 3:05AM=-=>

    I seemingly don’t have the ability…

  9. SQL Server 2005 produces inefficient query plan when ANSI_PADDING OFF

    I have a problem with SQL Server 2005 producing an inefficient query plan. On SQL Server 2000 the plan is very efficient but porting our product from 2000 to 2005 has produced performance problems for a type of query which we use extensively.

    The query plan on 2005 can be made to match the 2000 plan by setting ANSI_PADDING ON when the table is created.
    On 2000 the plan is always good, regardless of the ANSI_PADDING setting.

    All the tables in our product are created with ANSI_PADDING OFF, so we get the inefficient plan on 2005.

    The difference between these…

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

    We’ll send you updates on this idea

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

    Upvotes: 1

    <=-=Aug 26 2008 10:29AM=-=>

    Hello,

    Thank you for reporting the issue! Indeed there was a change in code for SQL Server 2005 that caused the behavior you have described. You will also observe a similar behavior in SQL Server 2008.

    Note that ANSI_PADDING option is on deprecation list (http://msdn.microsoft.com/en-us/library/ms143729.aspx) and is recommended to be ON and will be assumed ON in the future releases (http://msdn.microsoft.com/en-us/library/ms187403.aspx).

    Unfortunately, there are no easy workarounds in this case. One other option you may consider to set ANSI_PADDING option to ON without recreating the table is to run ALTER TABLEALTER COLUMN with the same data type which sets the ANSI_PADDING option to ON.

    E.g. alter table MyTable_AP_OFF alter column Code char(8)

    Please let us know if this is an acceptable workaround.

    Thank you and best regards,
    Boris.

  10. UDF Incorrect Errors Generated (Syntax, Missing BOL, Fails to execute scenarios)

    In a number of scenarios, UDFs are not performing correctly, with error messages being reported which are completly inaccurate.

    All the demo examples here use database: AdventureWorks

    In a number of scenarios that are demonstrated SQL BOL is missing information, or the UDFs do not work as documented, or are inconsistent (e.g can use exec to call a UDF without user owner , but have to use user owner to call UDF in a select statement - which is contary to all other calls in the system, i.e. selecting from table/view).

    Version: Microsoft SQL Server 2005 - 9.00.3068.00 (Intel X86)…

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

    We’ll send you updates on this idea

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

    Upvotes: 0

    <=-=Aug 14 2008 5:40AM=-=>

    Example 2/4/5 Does not apply – Syntax different than select statement.
    Ignore Examples 2/4/5 (Found documentation hidden away – in some very small print).

    Example 1 – is an edge condition (bugs)
    1 – Incorrect Error Msg (Bad error) “Invalid object name ‘dbo.ufnGetStock’”

    I believe it would be better to return a better error msg , than object does not exist – when it does.

    <=-=Aug 19 2008 12:36PM=-=>

    Hi,
    Thanks for your suggestion. We will consider it for the next version of SQL Server. We are currently looking at scenarios where we could improve the error messages / feedback to developers and we will consider this request as part of it.


    Umachandar, SQL Programmability Team

    <=-=Mar 8 2011 3:11PM=-=>

    This has been resolved as duplicate of connect item below:

    https://connect.microsoft.com/SQLServer/feedback/details/650174/cleanup-error-messages


    Umachandar, SQL Programmability Team

  11. SSMS : inconsistent behavior of "parse" functionality

    In a query window, we have a button called parse, which is designed to check the correctness of queries. In a job step command window, we also have a button called parse, but it behaves differently. The Parse button in a query window just checks syntax, but does not check semantics. The Parse button in a job step command window behaves similarly to SET NOEXEC ON or display estimated execution plan, where type validation and binding actually do take place. I find this inconsistent and gives the user a strange feeling of what "Parse" actually means. As I suggested in…

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

    We’ll send you updates on this idea

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

    Upvotes: 11

    <=-=Aug 22 2008 11:23AM=-=>

    Aaron, thanks for the bug report. We’ll take a look at what’s going on here and get back to you.

    -Richard Waymire

    <=-=Oct 14 2009 12:27PM=-=>

    So the root of this problem is the Agent UI does not use the same TSQL parser as the query window.

    In our next release we plan to start using the same parser to provide a more consistent experience.

    I have resolved this bug as a duplicate of the work item tracking that work.

    Thanks,

    Amy Lewis

    <=-=Jul 28 2014 8:33AM=-=>

    It is really necessary to have a way to parse query in order to check also invalid object names.
    If there is invalid object (column or table name) while creating a trigger, the parse button should show there is something wrong.
    Validate only syntax is not enought. If we want a consistent database, we should have a…

  12. 0 votes
    Sign in
    (thinking…)
    Sign in with: oidc
    Signed in as (Sign out)

    We’ll send you updates on this idea

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

    Upvotes: 3

    <=-=Aug 20 2008 10:03AM=-=>

    Hi Bill,
    Do you have SSMS configured in a Tab or MDI format? You can check this by selecting Tools → Options and looking at this setting on the General area. Perhaps a screen shot will also help.

    Thanks,
    Dan

    <=-=Aug 20 2008 11:22AM=-=>

    Tab (the default)
    I already attached a screen shot.
    Start SSMS and reduce the size to a non-full-screen window. Start a new query which opens a new window in the center area. Maximize SSMS. Note the Query window does not grow.

    <=-=Jul 5 2010 1:36PM=-=>

    I am also having what i believe is the same problem, and it’s extremely frustrating. It only happens when i have tablet drivers (i.e. Wacom) installed and the Microsoft Tablet PC Input Service running, Wacom tell me to stop the MS Tablet service and that does help but i don’t like the answer.

    If…

  13. sp_start_job error handling

    Hi,
    I have
    1. created a job in SQL Server Agent,
    2. when stopped SQL Server Agent,
    3. and then executed statement to start job inside try catch block.

    Catch block never gets fired, however I can see a red error.

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

    We’ll send you updates on this idea

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

    Upvotes: 20

    <=-=Aug 19 2008 11:38AM=-=>

    Hi,
    Thanks for reporting this issue. This is a known restriction in the TRYCATCH error handling for extended SPs. Extended SPs in general can throw any error so we cannot use our internal error handlers to change the error action. The severity of the error is also meaningless since XP writer can set it to any value. We are looking at introducing some error policy for errors thrown in XPs and/or change the severity information for errors thrown from XPs.
    We already have a workitem tracking this request so I will resolve your request as duplicate of that one. You can see the associated workitem at:

    http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=249967


    Umachandar, SQL Programmability Team

    <=-=Aug 19 2008 12:32PM=-=>

    Hi,
    As indicated before, I have resolved this request as a duplicate of another one in our bug tracking system.


    Umachandar

    <=-=May 16 2009 11:07AM=-=>

  14. SQL Server 2008 RTM - In SSMS Ctrl+N and/or F8 shortcuts not available

    Sometimes it happens that there is no Ctrl+N and/or F8 shortcuts available in SSMS. They are not visible in main menu (but the appropriate options: File - New - Query with Current Connection and View - Object Explorer ara available) and do not work.

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

    We’ll send you updates on this idea

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

    Upvotes: 25

    <=-=Aug 26 2008 9:24AM=-=>

    Hi Brejk,

    Thank you for reporting the problem. If the table designer was open then the F8 may not have opened the Object Explorer. The current workaround in that case would be use the View→ Object Explorer menu option. CTRL+N consistenly opened the Query Editor window.

    Can you please update the cases when CTRL+N and F8 were not working as expected?

    Thanks,
    Karthik

    <=-=Sep 5 2008 4:26AM=-=>

    the proposed workaround of changing keyboard scheme in Tools→Options→Keyboard really worked for me

    <=-=Sep 5 2008 10:50AM=-=>

    Hello Brej K,

    Can you try the following: From the Tools→Options go to Environment→Keyboard and change the keyboard scheme to SQL Server 2000. This should bring back the back the CTRL+N behaviour to launch a new query editor.

    Thanks,
    Karthik

    Program Manager | SQL Server

    <=-=Sep 5 2008 11:26AM=-=>

    Hi Karthik,

    I have already given this workaround here. However, can…

  15. Report Builder Search does not search Description

    In Report Builder. The Search button above the list of Entities does not search any descriptions of fields. This should search this field so that a entity reference can be included in the Description which can be searched on. Also the search should include the reference field in the Serach Results for easier identification of the correct field.

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

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  16. SSMS find and replace alert (informational messages) settings cannot be accessed

    There is a problem concerning restoring settings in SSMS. When using the find functionality to find some text in a document in SSMS (both 2005 and 2008), when the last occurrence of the text is found in the document an alert window appears, notifying the user that "Find reached the starting point of the search". This alert window has a checkbox ("Always show this message"). If one unticks this checkbox, there seems to be no way to restore the original behaviour, i.e. to have an alert notifying the user that the end of the document has been reached.

    In Visual…

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

    We’ll send you updates on this idea

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

    Upvotes: 2

    <=-=Sep 29 2008 12:25PM=-=>

    Dear Andras Belokosztolszki,

    Thank you for reporting this problem. The Find and Replace Option setting is not present in SSMS to popup a dialog box indicating that the end of file has been reached after the checkbox is unchecked. However the status bar highlights the message after each time search reaches the starting point of the file. We will try to address this issue in a future release of SQL Server.

    Thanks,
    Karthik

    <=-=Aug 11 2011 12:27AM=-=>

    Hi

    Thanks for your request on SSMS to introduce an option to control display of informational messages for the “Find” operation.

    Given the work that would be involved in implementing this work, and triaging against our current set of deliverables, we do not think, we would be able to get to this in the near future.

    Having said that, we value your suggestions, and would like to…

  17. SQLGetTypeInfo ODBC API result set change for SQL 2008 ?

    It appears that the result set returned from SQLGetTypeInfo for the SQL_WVARCHAR type has changed between SQL 2005 and SQL 2008. Under SQL 2005, only "nvarchar" and "sysname" were returned; however, under SQL 2008, various date and time types are returned in addition to the nvarchar and sysname types.

    These extraneous types are breaking my app, and could cause problems for other applications that are not equipped to parse out non-character related types.

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

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  18. Costing issue with many-to-many merge joins

    The cost for the many-to-many merge joins that have large numbers of distinct values is to high. The optimizer chooses other algorithms which are slower in this case.

    Checked on SQL Server 2005 SP2 Dev and SQL Server 2008 Dev.

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

    We’ll send you updates on this idea

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

    Upvotes: 1

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

    I agree that the cost estimates seem off – the merge join operator has a very high I/O estimate (313 in my case), whereas there is 0 I/O cost estimated for the hash join. But the I/O statistics don’t seem to show increased I/O for the merge join.

    While this is a costing bug, I think, the repro isn’t too compelling. The actual costs are close, and there is little impact from the wrong plan choice (the chosen plan was about 5% slower in my case). In general, choosing a hash join over a merge join is probably not a disaster (wrongly choosing a loop join could be more serious). If I find a modified repro that shows a real impact, I’ll post it.

    For the record, I also tried this with a unique clustered index on (c,rn) instead of the nonclustered index you…

  19. Default values of procedure/function parameters are not stored in catalog views

    Default values for parameters of stored procedures and functions are not stored in catalog views.

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

    We’ll send you updates on this idea

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

    Upvotes: 16

    <=-=Aug 21 2008 11:07AM=-=>

    Hi,
    Thanks for your suggestion. We will consider it for a future version of SQL Server. Currently, the definitions for procedures, functions, triggers and views are stored as is in the catalog tables. They are used at the time of compilation/execution directly. So there is no way to get the default value for parameters of SPs, and functions other than parsing the text.


    Umachandar, SQL Programmability Team

    <=-=Aug 21 2008 1:15PM=-=>

    Hi,
    But when I use the function like this:

    select dbo.fnTest(default)

    SQL Server has to parse it to get the default value. I see no reason why SQL Server could not parse the definition during object creation and put the proper default values into system tables.

    Regards,
    Pawel Potasinski, SQL Server MVP

    <=-=Aug 22 2008 10:00AM=-=>

    Hi,
    I did not say that we cannot do the parsing and persist the default value…

  20. 2008 RTM, SSMS/Engine: Table designer doesn't script WHERE clause in filtered indexes

    When using the table designer to make changes to a table that would cause a recreate, the script will not include the WHERE clause portion of a filtered index.

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

    We’ll send you updates on this idea

    0 comments  ·  Bugs  ·  Flag idea as inappropriate…  ·  Admin →
  • Don't see your idea?

SQL Server

Feedback and Knowledge Base