Sorry to necro here, but just in case someone else hits this page with a similar question, and since there's so little documentation about this. Short: The regular 'Enterprise' (non-'core') edition caps the cores at 20, period. The 'core' edition has no such cap. This is not a guess, it's a provable certainty. I ran into the specific question 'when do I NEED the Core edition, vs. About a year ago.
The good news: an edition upgrade from non-core to core is utterly painless and very fast. As in it takes longer just to get to the Upgrade button in setup, than to actually perform the upgrade when you hit it. Virtualization side-note: this has nothing to do with cores on the host. Just how many are assigned to the Windows VM.
I'm not sure how or why anyone would give a VM more than 20 cores (at that point your host is already a dedicated server most likely), but if so. Then yeah, this applies to your guest. If your host has 40 cores but you never assign 20+ to any guest, don't worry about it. Explanation: The non-core edition, as others stated, is intended for Server/CAL licensing. Even if you're paying for core licensing, and paying for more than 20 cores on a server, it's still capped at 20. Yes - that means you're wasting a LOT of money. Yes, that sucks - so if your boss is itching for a reason to fire you, be sure to point out that yeah.
Documentation about the 'Core-licensing' edition is extremely lacking. Or be sure you have an unloved teammate you can throw under the bus.:).
I ran into this the hard way, on a server with 60 cores, running for over a year, and because admittedly I was not paying enough attention. Yeah, we were effectively wasting 40 core licenses. I'm lucky though - I have a cool boss, plus three teammates and two lead developers that also had no clue about this, backing me up. You can verify this yourself, the same way I finally discovered it, on a server with more than 20 cores of course. Check sys.dm_os_schedulers, in particular the 'status' column. Cores in-use show 'VISIBLE ONLINE.' If you have more than 20 cores, but the non-core edition, you'll see only 40 rows with that status.
With the Core edition, you'll see twice as many rows as you have cores. '40' and 'twice' are assuming HT is enabled; if not, half those figures. This is also assuming you didn't monkey with processor affinity. In fairness to the community at large, most DBAs don't ever see a server with more than 20 cores unless they're in a large enterprise environment. Even then, if scale- out strategy is usually 'the norm,' you'll normally have not-quite-beast-level servers. 20+ cores means you have a seriously loaded database, a budget that doesn't even flinch at buying a server that costs 6 figures, and multiple levels of bosses that are all on the same page. Not a very common scenario.
Understanding SQL Server. In its simplest definition a clustered index is an index that stores the. And have Enterprise edition do not index TEXT. SQL Effects Clarity is an advanced Sybase ASE database comparison tool. The tool differentiates itself from other database comparison tools by focusing on ease of use, flexible comparison selections and criteria, lightning fast comparison performance, unique GUI review of differences.
Doesn't really excuse MS, even their licensing 'experts' never bothered to point this out in the course of the past nine years, but at least it explains why not many people seem to 'know' about this. I was confused by this too. From it seems that Enterprise Core is in fact just the per-core licensing version of Enterprise. (Contrary to your own answer). As a Microsoft Partner, the 'Enterprise Core' version was all that was available to me in our Partner Download Portal.
I thought it strange that I would be restricted to installing SQL to Windows Core, but as I discovered when I went to install it on full Windows Server 2008 R2, it proceeded without difficulty. Since both entries in your image are the same file size, I'd hazard a guess that the two versions have been mistakenly added to MSDN and are in fact the same thing. Not only do they NOT tell you the difference between Enterprise and Enterprise Core.
They have continued this practice of misnaming the downloads into SQL Server 2014. It is very easy to make a mistake and download the wrong version and wind up with a server with only 1/2 of it's cores working. Microsoft should make the following changes: They should name 'SQL Server 2012 Enterprise Edition with Service Pack 1 (x64) - DVD (English)' as 'SQL Server 2012 Enterprise Edition-Server-CAL-License with Service Pack 1 (x64) - DVD (English)' It would probably be helpful if they changed 'SQL Server 2012 Enterprise Core Edition with Service Pack 1 (x64) - DVD (English)' to 'SQL Server 2012 Enterprise Per Core Licensing Edition with Service Pack 1 (x64) - DVD (English)' This will go a long way and make it more clear to their customers. The lack of clarity is unacceptable.
THIS TOPIC APPLIES TO: SQL Server (starting with 2008) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse Creates a user-defined function in SQL Server and Azure SQL Database. A user-defined function is a Transact-SQL or common language runtime (CLR) routine that accepts parameters, performs an action, such as a complex calculation, and returns the result of that action as a value. The return value can either be a scalar (single) value or a table. Use this statement to create a reusable routine that can be used in these ways: • In Transact-SQL statements such as SELECT • In applications calling the function • In the definition of another user-defined function • To parameterize a view or improve the functionality of an indexed view • To define a column in a table • To define a CHECK constraint on a column • To replace a stored procedure • Use an inline function as a filter predicate for a security policy.
Note Parentheses are required after the function name even if a parameter is not specified. @ parameter_name Is a parameter in the user-defined function. One or more parameters can be declared. A function can have a maximum of 2,100 parameters.
The value of each declared parameter must be supplied by the user when the function is executed, unless a default for the parameter is defined. Specify a parameter name by using an at sign (@) as the first character. The parameter name must comply with the rules for identifiers. Parameters are local to the function; the same parameter names can be used in other functions. Parameters can take the place only of constants; they cannot be used instead of table names, column names, or the names of other database objects. Note ANSI_WARNINGS is not honored when you pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch statement. For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.
[ type_schema_name. ] parameter_data_type Is the parameter data type, and optionally the schema to which it belongs.
For Transact-SQL functions, all data types, including CLR user-defined types and user-defined table types, are allowed except the timestamp data type. For CLR functions, all data types, including CLR user-defined types, are allowed except text, ntext, image, user-defined table types and timestamp data types. The nonscalar types, cursor and table, cannot be specified as a parameter data type in either Transact-SQL or CLR functions. If type_schema_name is not specified, the Database Engine looks for the scalar_parameter_data_type in the following order: • The schema that contains the names of SQL Server system data types. • The default schema of the current user in the current database.
• The dbo schema in the current database. [ = default ] Is a default value for the parameter.
If a default value is defined, the function can be executed without specifying a value for that parameter. Note Default parameter values can be specified for CLR functions except for the varchar(max) and varbinary(max) data types. When a parameter of the function has a default value, the keyword DEFAULT must be specified when the function is called to retrieve the default value. This behavior is different from using parameters with default values in stored procedures in which omitting the parameter also implies the default value.
However, the DEFAULT keyword is not required when invoking a scalar function by using the EXECUTE statement. READONLY Indicates that the parameter cannot be updated or modified within the definition of the function. If the parameter type is a user-defined table type, READONLY should be specified. Return_data_type Is the return value of a scalar user-defined function. For Transact-SQL functions, all data types, including CLR user-defined types, are allowed except the timestamp data type. For CLR functions, all data types, including CLR user-defined types, are allowed except the text, ntext, image, and timestamp data types. The nonscalar types, cursor and table, cannot be specified as a return data type in either Transact-SQL or CLR functions.
Function_body Specifies that a series of Transact-SQL statements, which together do not produce a side effect such as modifying a table, define the value of the function. Function_body is used only in scalar functions and multistatement table-valued functions. In scalar functions, function_body is a series of Transact-SQL statements that together evaluate to a scalar value.
In multistatement table-valued functions, function_body is a series of Transact-SQL statements that populate a TABLE return variable. Scalar_expression Specifies the scalar value that the scalar function returns. TABLE Specifies that the return value of the table-valued function is a table. Only constants and @ local_variables can be passed to table-valued functions.
In inline table-valued functions, the TABLE return value is defined through a single SELECT statement. Inline functions do not have associated return variables.
In multistatement table-valued functions, @ return_variable is a TABLE variable, used to store and accumulate the rows that should be returned as the value of the function. @ return_variable can be specified only for Transact-SQL functions and not for CLR functions. Warning Joining to a multistatement table valued function in a FROM clause is possible, but can give poor performance. SQL Server is unable to use all the optimized techniques against some statements that can be included in a multistatement function, resulting in a suboptimal query plan.
To obtain the best possible performance, whenever possible use joins between base tables instead of functions. Select_stmt Is the single SELECT statement that defines the return value of an inline table-valued function. ORDER () Specifies the order in which results are being returned from the table-valued function. For more information, see the section, 'Guidance on Using Sort Order,' later in this topic. EXTERNAL NAME assembly_name.
Method_name Applies to: SQL Server 2008 through SQL Server 2017. Specifies the assembly and method to which the created function name shall refer.
• assembly_name - must match a value in the name column of SELECT * FROM sys.assemblies. This is the name that was used on the CREATE ASSEMBLY statement.
• class_name - must match a value in the assembly_name column of SELECT * FROM sys.assembly_modules. Often the value contains an embedded period or dot. In such cases the Transact-SQL syntax requires that the value be bounded with a pair of straight brackets [], or with a pair of double quotation marks '.
• method_name - must match a value in the method_name column of SELECT * FROM sys.assembly_modules. The method must be static. In a typical example, for MyFood.DLL, in which all types are in the MyFood namespace, the EXTERNAL NAME value could be: MyFood.[MyFood.MyClass].MyStaticMethod.