SQL Server

Microsoft SQL Server 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 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. ALTER COLUMN IDENTITY

    Provide an ALTER COLUMN syntax to add or remove the IDENTITY property of a column. This will allow an IDENTITY to be changed without dropping and recreating the table.

    This is not only a convenience but is actually important for some scenarios:
    1. Suppose the SELECT INTO was used to create the table. SELECT INTO is fast, but subsequently recreating the table just to setup the IDENTITY negates a lot of SELECT INTO's speed.


    1. If the table is involved in replication, it is not possible to drop and recreate it.

    A similar suggestion in Microsoft Connect involves the ability to…

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

    We’ll send you updates on this idea

    1 comment  ·  Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 27

    <=-=Jan 28 2008 6:27PM=-=>

    Hello

    Thank you for your feedback. The ability to modify the identity property of an existing column will certainly benefit the scenarios you’ve described. We’ll look into ways of enabling these scenarios in a future release of SQL Server.

    SQL Server Engine Team

    <=-=Jan 28 2008 6:28PM=-=>

    Hello

    Thank you for your feedback. The ability to modify the identity property of an existing column will certainly benefit the scenarios you’ve described. We’ll look into ways of enabling these scenarios in a future release of SQL Server.

    SQL Server Engine Team

    <=-=Jan 1 2011 2:09PM=-=>

    Any update on this? It seems pretty ridiculous that the only way of changing a column to be an identity column is either to add a new column and drop the existing one (necessitating an update to every row in the table, wasting space from the dropped column…

  2. SSRS: Export tooltips to Excel as comments

    It would be nice if exporting a report to Excel resulted in the tooltips travelling with it and appearing in Excel as comments.

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

    We’ll send you updates on this idea

    3 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 56

    <=-=Jan 13 2010 10:03AM=-=>

    Hello,

    Thanks for writing in with this feature request. While this feature isn’t available yet, we’ve flagged it for consideration for a future release of SQL Server Reporting Services.

    Best regards,
    Chris B.
    SSRS Team

    <=-=Dec 13 2012 7:57AM=-=>

    How shit that you can’t do this already!! How did they never bloody think of this? Multi-billion dollar company, and they can’t think of anything innovative. Even Apple are better than Microsoft.

    <=-=Apr 4 2013 12:42PM=-=>

    Having worked with multiple companies over the years in the BI space there are two primary export/save as scenarios for the displayed report, Excel or PDF. When exporting to Excel, business clients invariably want all the richness of the report preserved to the extent possible. Lost tooltips is one example, charts rendered as bitmaps with no data behind is another.

    We hope SSRS is actually moving to the…

  3. Option to defer foreign key constraint checking until transaction commit

    There are various "chicken and egg" scenarios where it would be desirable to defer the checking of referential integrity constraints until commit time on a transaction.

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

    We’ll send you updates on this idea

    1 comment  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 106

    <=-=Apr 20 2006 7:33PM=-=>

    As a further example, consider flushing the contents of an ADO.NET dataset to the database server. Currently, the order of the updates is critical. It would simplify code design if you could just a) start a transaction b) send all the updates c) commit them.

    <=-=Oct 13 2006 1:35PM=-=>

    Hello Greg,

    Thanks for the feedback. We are aware of this and looking into it for a future release.

    Sameer Verkhedkar
    SQL Engine
    [MSFT]

    <=-=Apr 9 2008 5:07AM=-=>

    This is a duplicate of feedback id 124624.

    The SQL ANSI-92 standard is to defer constraint checking until the commit phase – only 16 years ago now.

    It is not a question of performance or faster dev, it is a fundamental to any database to be able to change a record from one consistent state to another consistent state within a transaction,…

  4. CREATE OR ALTER is not fully supported in Transactional Replication DDL commands

    We've been having trouble with some objects not replicating correctly after a DDL change.

    When a published function or proc is altered, the following appears to happen:
    A database trigger is called: trMStranalterschemaonly
    Which calls a proc called: sys.spMStranddlrepl - This proc will output debug IF MSrepldebugDDL exists (create anything called this!)
    which calls a function: master.sys.fn_replgetparsedddlcmd, which is designed to return the ddl after the object's name, to be used for generating the command to be applied 
    at the subscribers.

    SQL Server BOL states that ALTER is supported (https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/replicate-schema-changes?view=sql-server-2016)

    However, this doesn't…

    8 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 →
  5. SQL Server should not raise illogical errors

    You already have tons of these bug reports, and I bet that you have closed them all "by design" and told people to use the CASE statement. But, no, you are wrong. This is a bug, and should be fixed. Although the fix is to add new functionality, hang on.

    Consider this silly repro:

    create table albert(a int NOT NULL,

                        b varchar(23) NOT NULL)
    

    create table stina (a int NOT NULL)

    go
    insert albert (a, b)

       values  (1, &#39;99&#39;), 
    
    (2, &#39;Gurka&#39;),
    (3, &#39;89&#39;)

    insert stina (a) values(1), (3), (9), (12)
    go
    SELECT a.a, a.b + 100

    FROM albert a
    JOIN…

    8 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: 82

    <=-=Mar 3 2010 10:44AM=-=>

    Hi Erland,
    Thanks for your feedback. We are aware of this request and hope to address it in a future version of SQL Server.


    Umachandar, SQL Programmability Team

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

    This bug cost me and others about 8 hours of troubleshooting this week. And the query was working until statistics were updated. That’s the spooky part, that a new join order can cause an error in a previously-working query. This was compounded by the fact that the query was in a UDF and 1) error messages don’t list the line in the UDF but rather the calling SP and 2) I know of no way to see the execution plan of a UDF, which would have helped because I could have seen in the plan the conversion to float before the JOIN to the filtering table.

    <=-=Mar 9 2011 12:08PM=-=>

  6. SQL Server 2008 Database Audit on INSERT UPDATE and DELETE actual SQL and not parameter values

    If a database Audit Specification is created for a table object on SELECT, INSERT UPDATE and DELETE and the Transact SQL to perform the SELECT, INSERT UPDATE or DELETE contains a parameter, then the parameter is shown in the Audit Log instead of the parameter value.

    For example, executing the following TSQL statement:

    INSERT INTO [config] VALUES ('test','1234')

    creates an audit entry:
    ..
    object_name:config
    statement:INSERT INTO [config] VALUES ('test','1234')
    ..

    However, executing the TSQL:

    DECLARE @stat1 varchar(10)='test'
    DECLARE @stat2 varchar(10)='1234'
    INSERT INTO [config] VALUES (@stat1,@stat2)

    Creates the audit entry:
    ..
    object_name:config
    statement:INSERT INTO [config] VALUES (@stat1,@stat2)
    ..

    In order for…

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

    We’ll send you updates on this idea

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

    Upvotes: 54

    <=-=Nov 29 2010 8:49AM=-=>

    Hi,

    First of all, thank you for your feedback!

    This is a great suggestion to improve the SQL Server Audit experience and we will try to consider this in our future release planning.

    Thank you and regards,
    Don Pinto
    SQL Server Engine

    <=-=Jul 29 2011 9:58AM=-=>

    I can’t believe there aren’t more comments on this subject. I assume it’s simply because not many are currently using the auditing system, likely one reason is due to the limitation described here.

    It seems like a logical step to include parameters within the additional_information field (which appears completely unused with regard to table level events). Perhaps this was overlooked due to performance issues?

    While I’m on the topic, and understanding that CDC is NOT necessarily to be used for auditing, it seems odd to me that the information regarding WHO did something is not included with the…

  7. Allow OPTION(HASH GROUP) with SQLCLR UDAs

    Currently, it appears that when SQLCLR UDA are used with a GROUP BY clause, a StreamAggregate operation must always be used. Attempting to specify OPTION(HASH GROUP) on a query like this:

    SELECT dbo.SomeUDA(col1) FROM someTable GROUP BY someColumn OPTION(HASH GROUP)

    produces an "Msg 8622, Query processor could not produce a query plan..."

    I only have a single test case, but because there is no special considerations in the grouping column (someColumn) in this case, I can only guess its because you'd need to allocate N UDA instances where N is the number of groups.

    This should be permitted (indeed a…

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

    We’ll send you updates on this idea

    1 comment  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 24

    <=-=Jan 4 2011 4:40PM=-=>

    Hi Bob,

    Thanks for the feedback. We’ll consider fixing this in a future release. Can you tell me more about the scenario? What UDAgg were you implementing and why? Feel free to contact me by email if you want.

    Best regards,
    Eric Hanson
    Program Manager, SQL Server Query Processing
    eric.n.hanson@microsoft.com

    <=-=Jan 4 2011 5:15PM=-=>

    Sure Eric, there are a few that I can think of. This actually came about because of the following forum question:
    http://social.technet.microsoft.com/Forums/en-US/sqlnetfx/thread/957a5b94-c7d0-49d8-928d-7cccff14b0c6. I realized that the sort was required because of choice of stream aggregate. And he can’t put on every index possible to get rid of the sort.

    Second is that the spatial aggregates in Denali would need this funtionality. Related to that is that there’s a vendor product that consists of a library of UDAs, Fuzzy Logix (http://www.fuzzyl.com/in-database_analytics.php#) that could benefit from this flexibility as…

  8. The Deployment Utility (Microsoft.AnalysisServices.Deployment.exe) should return a non-zero exit code on error

    It would be extremely helpful if the Deployment Utility/Microsoft.AnalysisServices.Deployment.exe http://msdn.microsoft.com/en-us/library/ms162758%28v=SQL.105%29.aspx would return a non-zero exit code on error.

    A little bit of background on what we're trying to do:
    We've automated the generation of the Deployment Script as a part of our build processes using MSBuild:

    <Exec Command='"$(AnalysisServiceDeployTool)" Some.asdatabase /s:somelogfile.log /o:SomeOutput.xmla /d'

          WorkingDirectory=&#39;$(AnalysisServicesTemp)&#39;/&gt;
    

    The Exec Task relies on the Exit Code* to determine if the task failed or not, however it appears that the Deployment Utility will not give a non-zero exit code on failure, resulting in the Exec task assuming that it was successful.

    *This is not a 100%…

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

    We’ll send you updates on this idea

    1 comment  ·  Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 14

    <=-=Sep 9 2012 9:55PM=-=>

    Thank you for your feedback. We will be investigating your proposed suggestion as part of the planning efforts for the next major release. If we have more questions as the planning process progresses we will get back to you

    Thanks
    The Analysis Services Team

    <=-=Nov 17 2012 1:33PM=-=>

    This is extremely disappointing and very important. You guys know any win32 process should NEVER return 0 on failure. Please make this a priority, it is critical to continuous delivery and automated builds with tabular and multidimensional models. We currently have no way of knowing whether this step of the build was successful or not without inspecting the build log manually on every build!!!

    <=-=Sep 22 2016 10:00AM=-=>

    Four years later – has anything changed? That this utility simply exits without an error indication when something goes wrong is troubling.

  9. Plain Text Password in Maintenance Plans

    The user and password of the user that create a Maintenance Plans using the MSSMS is save in plain text, you can use the following query to access this informaci�n.

    SELECT CAST(CAST(packagedata AS VARBINARY(max)) AS VARCHAR(max)) FROM sysssispackages
    WHERE name LIKE 'MaintenancePlansName'

    This will show the xml of Maintenance Plans including the user and password.

    8 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 →
  10. Populate has_default_value in sys.parameters

    As we all know, T-SQL stored procedure parameter defaults are not stored in sys.parameters, allparameters, and systemparameters. They are also not exposed through spsproccolumns, sys.columns, or spprocedureparams_rowset.

    However, Management Studio has no problems at least indicating whether or not a default value exists (though not the actual value) for a specific parameter (this is new in 2005; Query Analyzer did not show this). So I know that the information can be retrieved somehow, whether it is parsing the text in sys.sqlmodules.definition, or using sptsproccolumnsodbc_view, or some internal mechanism invisible to…

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

    We’ll send you updates on this idea

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

    Upvotes: 62

    <=-=Nov 13 2006 2:23PM=-=>

    HI Tim,

    As posted by Tibor Karaszi, BOL document that “SQL Server only maintains default values for CLR objects in this catalog view; therefore, this
    column has a value of 0 for Transact-SQL objects. To view the default value of a parameter in a Transact-SQL object, query the definition column of the sys.sql_modules catalog view, or use the OBJECT_DEFINITION system function.”

    We dont store even the bit that indicating parameter is of default value in Yukon.

    Thanks for the feedback, we will look into addressing this more completely in future releases.

    Best Regards
    Andrew
    [MSFT]

    <=-=Nov 13 2006 2:56PM=-=>

    Hi Andrew, this is Aaron not Tim. If you are not gong to fix these omissions from sys.parameters, then could you at least share/publish the code that Management Studio uses to parse syscomments.text, since it is fully capable of showing…

  11. Support POSIX-style regular expressions in T-SQL

    Regular expressions is a very powerful way to match data, far more powerful
    than the LIKE operator in SQL. Regular expressions comes in a number of flavours:
    POSIX defines one standard, .Net have their own twist (I think), and Perl is
    probably the most elaborate of them all. But they all share some common
    traits: . is a wildchard charcted, * means previous pattern zero or more times etc.

    ANSI/ISO have tried to catch up, and SQL:1999 introduced the SIMILAR TO
    operator, but it's obvious that they are on the wrong track. They still use % and
    _ as wildcard…

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

    We’ll send you updates on this idea

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

    Upvotes: 22

    <=-=Aug 12 2008 2:12PM=-=>

    Of course, there should also a be a replace function that can work with regular expression. Both for the pattern to find – and for the replacement string.

    <=-=Aug 13 2008 7:16AM=-=>

    Hi Erland,

    I’ve resolved this as a duplicate of Connect #261342 and made sure your comments are reflected in that issue. I can tell you that this is under serious consideration right now. If you haven’t already voted on that item, it would be worth it to do so.

    Thanks,
    -Isaac

  12. View Any Database

    The instance level View Any Database permission takes precedence over assigning more granular securables; this permits a user to view all databases in an instance, not just those databases for which the user has permission.

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

    We’ll send you updates on this idea

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

    Upvotes: 11

    <=-=Nov 5 2010 7:16AM=-=>

    I agree that we need more granularity. Servers are holding greater numbers of databases, and the importance of masking groups of databases for admin purposes is growing. Currently, if the “ANY” permission is not granted, the principal must be the database owner (or highly privileged).

    A database level VIEW DATABASE permission would enhance manageability.

    <=-=Dec 13 2010 5:02AM=-=>

    Hi Grant,

    Thanks for the suggestion. This is something that we have been thinking about and we’ll consider this for a future version of SQL Server.

    Thanks,
    il-Sung.

    <=-=Aug 30 2011 11:38AM=-=>

    Hi Grant,

    Thank you for your feedback. We will consider this for a future version of SQL Server.

    Regards,
    Don Pinto
    SQL Server Engine

    <=-=Feb 11 2013 2:38PM=-=>

    Thank you for your feedback. We will consider this for a future version of SQL Server.

    <=-=Jun 25 2015 12:35PM=-=>

    agree with Michael Smith.…

  13. Allow for outer joins in indexed views

    Currently there is a restriction for indexed views to only contain inner joins. This effectively restricts the use case to non-null fk relations. Nullable fk relations are impossible to materialize.

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

    We’ll send you updates on this idea

    1 comment  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 3

    <=-=Feb 7 2011 2:05PM=-=>

    Why not a filtered index?

    <=-=Feb 8 2011 10:31AM=-=>

    Hi,

    Thanks for the feedback. We’ll consider this for a future release. The columnstore index feature coming in the Denali release provides such excellent performance that many data warehouse and data mart customers won’t need indexed views any more. So that may provide an adequate solution for you. See http://download.microsoft.com/download/8/C/1/8C1CE06B-DE2F-40D1-9C5C-3EE521C25CE9/Columnstore%20Indexes%20for%20Fast%20DW%20QP%20SQL%20Server%2011.pdf

    Best regards,
    Eric Hanson
    Program Manager
    SQL Server Query Processing

    <=-=Feb 8 2011 10:51AM=-=>

    Please don’t leave out OLTP customer… Indexed views are great for eliminating joins for mostly read data. Consider the following schema:

    create table forumthreads (id, userid null, title)
    create table users (id, name)

    and the query:
    select top 10 * from forumthreads left join users order by users.name

    No single-table index can satisfy the sort order so the entire result set must be materialized and top-n sorted. It would be much…

  14. Page Compression for Out-of-Row LOB Storage

    Disappointingly page compression does not apply to out-of-row LOB storage for data types such as VARCHAR(MAX) and NVARCHAR(MAX) [and also TEXT and NTEXT].

    Would you consider adding this in a future release?

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

    We’ll send you updates on this idea

    2 comments  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 10

    <=-=Mar 8 2011 9:34AM=-=>

    Thanks for contacting SQL Server team with the feedback. We will consider this in a future release. At this time, you can compress Out-of-row BLOBs by storing them as FileStream on a compressed volume. But I do understand that this is not your scenario as the data types you mentioned are not suitable for Filestream

    Thanks
    Sunil

    <=-=Mar 8 2011 1:30PM=-=>

    Sunil, thanks for the feedback. I had considered this option, but we’re using database mirroring as a resiliency solution which, unfortunately, doesn’t support Filestream.

    Thanks
    Chris

    <=-=Jan 15 2012 11:47AM=-=>

    Chris, I updated the title of your request. You are not really saying PAGE compression for out of row or LOB data. What you are saying is that we need the ability to compress this data as well. Also, I added a variance of UNICODE compresion as well.

    thanks
    Sunil

    <=-=Dec 1 2014…
  15. Cardinality Estimation Error With Pushed Predicate on a Lookup

    In SQL Server 2005, a filtering condition on a column retrieved from a Key Lookup is applied in an explicit Filter operator after an apply join.
    In SQL Server 2008 onward, this filter can be pushed down to the lookup during copy out, but cardinality estimates are not updated correctly.
    The post-filter cardinality estimate is applied to the inner side of the lookup join, rather than the result of the join as a whole.

    -- SQL Server 2005 AdventureWorks
    SELECT

    th.ProductID,
    
    th.TransactionID,
    th.TransactionDate

    FROM Production.TransactionHistory AS th
    WHERE

    th.ProductID = 1 
    
    AND th.TransactionDate BETWEEN &#39;20030901&#39; AND &#39;20031231&#39;;

    Estimate from index…

    6 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: 76

    <=-=Oct 26 2012 11:30AM=-=>

    Thanks for the feedback, we are looking into this request

    <=-=Sep 17 2013 2:44PM=-=>

    Thank you for submitting this feedback. After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. This is because the fix is risky to implement.
    Thanks again for reporting the product issue and continued support in improving our product.

    <=-=Dec 2 2015 2:45AM=-=>

    This is something we are investigating, and needs thorough testing. This is so we do not introduce wide-spread regressions, like in cases when we had the CE underestimate, and then we would not have had not enough memory granted, leading to spills otherwise.

    <=-=Jan 3 2017 9:52AM=-=>

    Tested and confirmed fixed in SQL Server 2016 Service Pack 1 (build 13.0.4001.0)

    <=-=Jan 3 2017 10:23AM=-=>

    Actually, no. A related item…

  16. Fully support position() in xquery

    Right now position() can only be used in predict in xquery. However, in lots of scenarios, the position of the xml data is significant.

    DECLARE @x xml;
    SET @x = N'<SelectAnswers xmlns="http://MyNamespace">
    <AnswerType Type="selectOne" IsRequired="true">
    <SelectAnswer>Once a month</SelectAnswer>
    <SelectAnswer>Two or three times a month</SelectAnswer>
    <SelectAnswer>Usually every week</SelectAnswer>
    <SelectAnswer>More than once a week</SelectAnswer>
    </AnswerType>
    <AnswerType Type="MultipleChoice" IsRequired="true">
    <SelectAnswer>This is my first time</SelectAnswer>
    <SelectAnswer>Hardly ever/special occasions only</SelectAnswer>
    <SelectAnswer>Less than once a month</SelectAnswer>
    </AnswerType>
    </SelectAnswers>'

    -- Suppose you need to get the postion of AnswerType in the xml as AnswerTypeID and the position of SelectAnswer in each AnswerType as AnswerID, you…

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

    We’ll send you updates on this idea

    1 comment  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 39

    <=-=Dec 3 2008 4:28PM=-=>

    Thanks for the feedback. The following shows how we would have to approach this.

    The nodes() method returns N rows one for each node selected by the expression inside the nodes() method. Each of that node is being set as the context item for the expressions inside the value/query/exist/nodes methods. The definition of the position() function is

    http://www.w3.org/TR/2007/REC-xpath-functions-20070123/#func-position

    16.1 fn:position
    fn:position() as xs:integer
    Summary: Returns the context position from the dynamic context.

    where context position is defined as:

    [Definition: The context position is the position of the context item within the sequence of items currently being processed.] It changes whenever the context item changes. When the focus is defined, the value of the context position is an integer greater than zero. The context position is returned by the expression fn:position(). When an expression E1/E2 or E1[E2] is evaluated, the context position in the inner…

  17. SQL Server doesn't have an ANSI INTERVAL data type

    SQL Server does not have an ANSI INTERVAL data type. When we create effectively dated tables we have to simulate a time period by using 2 DATETIME columns (StartDate, EndDate). However, often we don't want to allow 2 rows to have overlapping date ranges and many times we don't want to allow gaps in there either. All of these validations have to be done in SQL. It is cumbersome and probably doesn't perform as well as a data type would.

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

    We’ll send you updates on this idea

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

    Upvotes: 76

    <=-=Apr 21 2009 9:51AM=-=>

    Thank you for your feedback. This is something that we would like to add to SQL Server and we will definitively be keeping it in mind for future releases!

    Again, thanks for your feedback!
    -Tobias

    <=-=Nov 4 2009 1:15PM=-=>

    Please implement ANSI SQL date math.
    DATEDATE —> INTERVAL (equivalent for datetime, datetime2, datetimeoffset)
    DATE + INTERVAL —> DATE (equivalent for datetime, datetime2, datetimeoffset)
    TIMETIME —> INTERVAL (range specified by application)
    TIME + INTERVAL —> TIME
    DATE + TIME —> TIMESTAMP (i.e., datetime or datetime2 in SQL Server; NOT timestamp; equivalent for datetimeoffset)

    There is no way (without conversion) to add values of the new date and time types.
    DATEADD is very limiting (only one datepart at a time).
    Conversions so often necessary now to work with date/time data clutter code and impact performance.

    <=-=Dec 18 2009 9:25AM=-=>

    This sounds like…

  18. Query hints not allowed after or inside IF EXISTS()

    Hugo Kornelis came up with an interesting problem when combining IF EXISTS() and certain query hints (e.g. to limit parallelism or to suppress warning messages).

    Microsoft is already aware of the issue internally, but I thought it would be beneficial to post it here both for visibility of the bug itself, and to expose the workaround I came up with (and any you may come up with, also).

    Basically, the problem is this. If I want to take this query, which works fine:

    SELECT *
    
    FROM sys.objects
    WHERE [name] LIKE &#39;%foo%&#39;
    OPTION (FORCE ORDER);

    And put it inside an IF…

    6 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 →
  19. Module-level table expressions

    There are some situations where you perform a lot of operations on a very
    well-defined subset of a table in an SQL module. A typical case is a process-
    keyed table. That is a table that is keyed by @@spid or some other process
    key. see http://www.sommarskog.se/share_data.html#prockeyed for a longer
    discussion. If you have a procedure that operates on such table, the
    procedure may have a bunch of:

    SELECT/UPDATE/DELETE ... WHERE processkey = @processkey

    This is somewhat error-prone. If the procedure has 20 such operations,
    you may fail to observe that the condition on processkey is missing in
    one of…

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

    We’ll send you updates on this idea

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

    Upvotes: 50

    <=-=May 9 2008 7:48PM=-=>

    I browsed the SQL-2003 standard to see if there was something like this, and nothing jumped out. That makes me suspect there’s a good reason. In some respects, this feature could be like Dynamic SQL, only worse.

    Macro-like abilities are very handy, but they can also be tricky to implement and define. Perhaps you would learn to use the feature effectively, but the parser/preprocessor/optimizer would have to handle whatever this feature allowed, and the results might be surprising. For example (and I think there are many examples), suppose you began a long procedure with

    declare @sessionRows query as select T.id, T.c from T where T.spid = @@spid;

    If @sessionRows is expanded before query execution, these two seemingly-identical statements are completely different:

    — Expression A
    with T1 as (
    select spid, id, c
    from Nisse
    where status = ‘Censored’
    )
    update Trades set…

  20. Allow literals and read only table data to be represented as enums

    It would be really useful to represent system values to be used in code as constants rather than having to hard code integer values.

    This means that lookup tables can use nice tinyints as the surrogate PKs but when referenced in code the code can use the textual representation.

    Essentially data driven enums.

    I am happy for the restriction that this only applies to read only data.

    an example where this wouldbe useful is the system message types/contracts in service broker. Currently you have to have strings like 'http://schemas.Microsoft.com/SQL/ServiceBroker/DialogTimer&#39; in your code. which means you are comparing against…

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

    We’ll send you updates on this idea

    1 comment  ·  Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 236

    <=-=Jan 29 2007 11:51AM=-=>

    Thanks for the feedback. There are a few alternatives to enable this, we’ll consider it for future release.

    - Christian Kleinerman

    <=-=Aug 9 2007 10:55AM=-=>

    Ageed, this would make dev work much simpler, not to mention maintaining the system afterwards.

    <=-=Aug 27 2007 9:05PM=-=>

    I agree with the call for enums, but I do not believe it should be implemented as a secondary lookup table table, or at least, the lookup table should be handled by the system, and should not require the developer or DBA to create it up front.. I think any implementation should be functionally equivalent to the MySQL column data type. It works and is widely adopted in the MySQL crowd, and would provide better portability to/from other systems.

    <=-=Aug 27 2007 9:35PM=-=>

    Some type of Enum capability would certainly make development much easier. Having such a feature may also…

  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base