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. Job Output to File permissions

    We have a system with over 700 jobs. We have 3 environments Dev, MO,
    Production.

    The developers create and test the jobs in development and then the DBAs
    promote the jobs to MO and Production via a script.

    In 2005 if the owner of a job is not a sysadmin they cannot write output
    files from the job.

    This is causing us huge headaches because now the DBAs have to manually add
    output files when moving the jobs to MO and Production.

    BOL says that this behavior is by design and I have since discovered this
    is due to a…

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

    <=-=Nov 14 2006 3:41PM=-=>

    Thanks for letting us know about the difficulties you have writing logs from agent jobs executing under highly priviledged proxy accounts.

    You make a good argument for a change in the product design. Could you help us make your case by summarizing the scenario and describing the business impact you suffer as a result of the current design?

    Thanks,

    jkh

    <=-=Nov 15 2006 4:19AM=-=>

    In this environment, we are required to follow Sarbanes-Oxley standards to keep all environments (Development, Model Office, and Production) in a consistent state. In our Model Office and Production environments, all jobs are owned by sa, and developers do not have direct access to any jobs. With these security requirements, we are required to push job output to file system output files so that the development & support personnel can troubleshoot or verify jobs in the higher environments.

    The developers…

  2. LinkMember variation to only search matching level

    LinkMember([Date].[Calendar].[January 2004],[Ship Date].[Calendar])

    That MDX does not return [Ship Date].[Calendar].[January 2004] as you may have expected. Instead it returns H1 CY 2004 because the keys match and because it got to H1 CY 2004 first.

    0 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 →
  3. Huge memory requirements due to drillthrough architecture

    We are a Microsoft partner developing BI solutions for a number of customers, and we now have all our customers running on the AS2005 platform.

    The drillthrough architecture in AS2005 is quite different than in AS2000, which at first hand seems OK. However, the consequences of the new design is that much more memory is needed to process the cubes.

    For one specific customer a cube with drillthrough enabled with a fact table consisting of only 12 million rows could not be processed in the Standard Edition within the 3 GB memory limit. This customer is now forced to upgrade…

    0 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: 0

    <=-=Sep 10 2008 1:00PM=-=>

    Hi,

    Thanks for reporting this issue — I apologize for the delayed response. As you mention, there are tradeoffs that have been made with the new drillthrough architecture. Your concerns are very valid and we will be looking at what can be done to improve this feature in the next release.

    In general, it is still feasible to use a ROLAP fact dimension and ROLAP partitions to achieve similar functionality as in AS 2000, but with less control over the exact SQL queries that are generated.

    Regards,
    Akshai

  4. should be able to mark an MDX sproc as deterministic

    I'm told that using an MDX sproc in a calculation (such as in the calc script) will disable caching. I believe that's because you can't mark a sproc (a function in a .NET assembly which is registered with SSAS) as deterministic.

    0 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: 9

    <=-=Nov 14 2006 9:39AM=-=>

    Thanks for reporting this issue, we will investigate this for the next release.

    <=-=Nov 26 2009 7:53AM=-=>

    What is the status for this point? Implemeted in SSAS 2005 sp or 2008 or 2008 R2 ?

    I encounter the problem when I call a .NET assembly fucntion in a calculated member.
    FY, the .net assemby recode an the Excel mround() function.
    I would like declare it as deterministic to use the SSAS cache.

    Best Regards

  5. cannot pass a Dimension object as a parameter to an MDX sproc

    Build an MDX sproc (a .NET assembly registered in SSAS) with the following signature:

    public static string TestMe(Microsoft.AnalysisServices.AdomdServer.Dimension d)

    That function cannot be called from MDX:

    MyNamespace.TestMe([Date])

    Also, the MDXValue object doesn't have a ToDimension method. I suspect these two problems are related

    0 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 →
  6. AdomdServer doesn't expose all of the properties

    There are a number of places where the AdomdServer object is incomplete. Let me list a few.

    The Level.LevelType property is a restricted list, not an exact match with the UDM... for instance FiscalYears isn't an option and any level that's of that type will show as LevelType.Regular in AdomdServer. This makes it difficult inside an MDX sproc to determine if a dimension attribute is a FiscalYears attribute.

    Another example is that AdomdServer.Member doesn't have a PrevMember property which makes traversing hierarchies challenging, to say the least.

    Also, AdomdServer.MDX doesn't expose all the MDX functions, just a few.

    0 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 →
  7. Need Context.CurrentTuple property in AdomdServer for MDX sprocs

    There is no inexpensive way to determine the complete current context of the query. The only way of doing that is by looping through all the attributes in all the dimensions and checking whether CurrentMember is different than DefaultMember. And that's REALLY expensive.

    0 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: 5

    <=-=Nov 7 2006 2:08PM=-=>

    Thank you for this suggestion – it will be considered in AdomdServer for a future product release.

    <=-=Jan 15 2007 1:32PM=-=>

    While you’re at it, adding a Context.CurrentQuery property would be helpful, too.

    <=-=Sep 22 2010 3:51AM=-=>

    Regarding: http://msdn.microsoft.com/en-us/library/ms175329.aspx there should be a CurrentTuple available, but the CurrentTuple is the only Member which is not mentioned in the other documentations:
    http://technet.microsoft.com/en-us/library/microsoft.analysisservices.adomdserver.context_members.aspx

  8. AdomdServer.Level and Hierarchy are very slow

    Build a .NET assembly with the following two functions then register it in SSAS:

        public static string PerformanceTest1(string sLevelUniqueName)
    
    {
    return sLevelUniqueName;
    }

    public static string PerformanceTest2(Level level)
    {
    return level.UniqueName;
    }

    Now run the following MDX queries in Management Studio against the sample Adventure Works cube and notice the performance difference:

    --executes in about 5 seconds
    with member [Measures].[PerformanceTest] as ASSP.PerformanceTest1([Customer].[Customer].CurrentMember.Level.UniqueName)
    select [Measures].[PerformanceTest] on 0,
    [Customer].[Customer].[Customer].Members on 1
    from [Adventure Works]

    --executes in about 370 seconds
    with member [Measures].[PerformanceTest] as ASSP.PerformanceTest2([Customer].[Customer].CurrentMember.Level)
    select [Measures].[PerformanceTest] on 0,
    [Customer].[Customer].[Customer].Members on 1
    from [Adventure Works]

    0 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: 3

    <=-=Nov 7 2006 1:51PM=-=>

    Also, doing something like looping over Context.CurrentCube.Dimensions is really, really slow. I should have mentioned that, too. It would be nice if there were an alternate set of all AdomdServer objects which perform faster if only simple properties in each object are used.

    <=-=Nov 14 2006 9:38AM=-=>

    Thanks for reporting this issue, we will investigate this for the next release.

  9. AdomdUserDefinedAggregate

    In SSAS you can build a .NET assembly which can be called from MDX as a user defined function. However, these calls to the .NET assembly are just singleton calls, so if you're calling the same function 100 times in one MDX query, you end up duplicating work 100 times. It would be nice if it were possible to call some sort of initialization routine that could be run once per query saving you from having to duplicate that work in each of the 100 calls to the function.

    0 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 →
  10. Mutiple aggregates in PIVOT

    Currently the PIVOT operator is:

    PIVOT ( aggregatefunction ( valuecolumn )

            FOR pivot_column IN ( &lt;column_list&gt; )
    
    [ AS ] table_alias

    I would like to see this enhanced to:

    PIVOT ( aggregatefunction ( valuecolumn ) [ ,...n ]

            FOR pivot_column IN ( &lt;column_list&gt; )
    
    [ AS ] table_alias [ ( column_alias [ ,...n ] ) ]

    The resulting columns are ordered such that all of the aggregation function applications for a single pivot column value are consecutive.

    This would permit both multiple aggregation functions on a single value column (e.g. both MIN and MAX) or application…

    5 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: 12

    <=-=Nov 13 2006 6:46PM=-=>

    Thank you for submitting this feature request. This is now under consideration for upcoming version of SQL Server.

    —G2

    <=-=Mar 10 2011 6:15PM=-=>

    Hello Mark,

    I have resolved your request as duplicate of one below:

    http://connect.microsoft.com/SQLServer/feedback/details/127071

    We will track your feature request as part of that one.


    Umachandar, SQL Programmability Team

    <=-=Mar 10 2011 11:49PM=-=>

    I do not agree that this request is a duplicate of feedback 127071. That feedback is for a dynamic column list (dynamic IN) , where the internal query determines the shape of the output result set. This query is to support multiple aggregates; the shape of the output result set remains static. Specifically I am not suggesting any change in the IN () clause, which is the target of 127071.

    A concrete examples that we have face is when dealing with currencies – we need to simultanously aggregate…

  11. Report Server should export csv in plain ASCII so it can be easily consumed by other MS products.

    When report data is exported to a csv file it is not recognized by Excel when creating a data source off of the file. It appears the file is exported in UNICODE when it should be just a plain ASCII text file.

    0 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: 1

    <=-=Nov 8 2006 5:23PM=-=>

    I assume you are primarily talking about importing into Excel, which has trouble with Unicode CSV files. There is actually a workaround in that you can set the CSV export to ASCII by setting the DeviceInfo values in the Report Server configuration file. See http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1162991_tax301328,00.html?adg=301324&bucket=ETA. The reason that we can’t do this in a general sense is that we would silently lose data for people that had Unicode in their report.

    <=-=Jun 11 2010 6:56AM=-=>

    How about making it an available OPTION, to export to ASCII?

    (You don’t have to change the default behavior.)

  12. Report Builder to support a default member with relational data sources

    Currently, the Report Builder doesn't support defaulting the value of a given attribute with relational data sources and this could be useful enhancement. For example, a Date table may have a Financial Period column. When the user filters on the Financial Period attribute (implicit default will be even better), the user may want the dropdown to default to the row which has CurrentPeriod flag set to true in the Date table. Or, at least default the dropdown to the last period.

    0 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 →
  13. 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…

  14. Allow Median as a Semi-addictive Measure in Analysis Services

    It would be very useful to expand the list of Semi-addicitive functions available in SQL Server 2005 to include the Median function, so it can be used in the same way as the Min/Max function, and hopefully the Average function.

    This function can not be easily expressed as a calculated field, so it can used in the same way as other Semi-addictive measures

    0 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 →
  15. Create query optimizer role for SQL wide showplan permissions

    If you are not a sysadmin, you need SHOWPLAN permissions on every DB your query runs. If you have a temp table, you need it in the tempdb.

    However, every time you restart SQL, tempdb will flush everything out. It would be nice to have a query optimizer role.

    0 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 →
  16. Denying Access to a Whole Dimension

    In AS2K it was possible to deny access to an entire dimension (and not just the members on the dimension) using a role. This is no longer possible in AS2005 - all you can do is use dimension security to hide all the members and then create a perspective where the dimension is hidden for users of that role - but this is hardly satisfactory. See http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=843443&SiteID=1 for more background.

    0 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

    <=-=May 23 2008 8:56AM=-=>

    Thanks Christopher for reporting this.

    We are going to take a look at this for the future version

    Edward Melomed

    <=-=Feb 2 2011 5:58AM=-=>

    Did the look you took result in anything?

    <=-=Mar 23 2011 4:02PM=-=>

    Chris,

    This is not something we’ll be able to deliver for SQL11, but are considering for a future version.

    regards
    ash

    <=-=Sep 3 2013 2:37AM=-=>

    Any updated regarding this case? It is a very important feature for large environments, because perspectives cannot be used for handling access to the dimensions.

    Kind regards,
    Nils Rottgardt

    <=-=Oct 21 2013 11:16AM=-=>

    I have to say this would be a nice feature to bring back. There are cases where this is highly needed, adding an extra layer of security, which cannot be implemented right now.

    <=-=Feb 26 2015 2:41AM=-=>

    Any news in this matter?

  17. Why is it so hard to reduce a database disk filesize?

    Today it is quite hard and many steps are involved in shrinking the disk-footprint of a database and often when you try the options in SQL management studio the effect is now what you desired.

    In the SQL manager there's two options, Shrink file and shrink database..... and within the two options there's plenty of different options. and some options are a bit unclear/fuzzy what they mean and how they actually do work.

    Sure, that's great with options for the "power-user", but why do I need to shrink each file individually? that's alot of unecessary clicks....

    So, my point is…

    0 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: 0

    <=-=Nov 1 2006 7:15AM=-=>

    Graphical presentation of freespace and frag could be handy. As regards “shrink everything”, that seems to covered by DBCC SHRINKDATABASE. I think the idea of a wizard would have very limited appeal because shrinking would/should be a very rare occurence in nearly every case.

    <=-=Nov 1 2006 5:52PM=-=>

    Thanks for the feedback. We will look into this issue for the next release of SQL Server. We will see if we could improve shrink functionlity and supportability and/or GUI.

  18. localize rfeport parameters

    To make a multilingual Portal with Report Services the Parameter Prompts have to allow expressions like the rest of a report. A secound idea would be a manual override of the language settings in the report manager. Because if you are on a foreign Computer by a client or a public station you cant adjust the language settings of the IE.

    0 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: 3

    <=-=Nov 9 2006 6:49AM=-=>

    We will be addressing this issue (parameter prompts as expressions) in the next major release of Reporting Services.

    <=-=Nov 9 2007 8:21AM=-=>

    I am VERY happy to hear that this will be resolved. This is a huge issue on European market with countries were several languages are used. Basicly we had to build a tool that creates translated versions of rdl files. Everything else on SSRS2005 we could localize using custom assemblies, and in case of Report Manager, some creative use of .NET reflection (because one can’t create new satellitte assemblies for different languages for Report Manager). Unfortunately this method of reflection did not work for report parameter prompts.

  19. New Compressed DataType

    Hi

    When storing large amount of text/binary data in the database it is stored very inefficient and disk space-inefficient.

    0 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: 4

    <=-=Oct 30 2007 6:41PM=-=>

    Katmai does not support it. Work-arounds for SQL Server 2008 are

    (1) store LOB as filestream on a compressed disk

    (2) compress LOB in the application

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

    closing it as a duplicate of another connect item with feedback id 649501

    thanks
    Sunil

  20. RPC:Completed event should show both input and output value when used with output params

    Currently, the RPC:Completed trace event shows only the output value when used with output parameters:


    declare @P1 int
    set @P1 = 5
    exec mySP @myParam = @P1 output

    select @P1

    In this case, "5" would be the output value, rather than the input value -- so this script really does not make sense. To be as useful as possible, the event should output BOTH the output and the input values.

    0 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: 2

    <=-=Oct 23 2006 11:28AM=-=>

    Hi
    Thanks for the suggestion. We will consider this for implementation in future release of SQL Server.

    - Christian Kleinerman

    <=-=Jan 18 2013 4:29PM=-=>

    Hello Adam,
    I am closing out this old request. We will not be looking at this.


    Umachandar, SQL Programmability Team

  • Don't see your idea?

SQL Server

Categories

Feedback and Knowledge Base