29
votes
Accepted
Is "+" slower than "CONCAT" for large strings?
Is "+" slower than "CONCAT" for large strings?
Looks that way. From looking at the pages allocated and deallocated in tempdb for storage of this LOB data it looks like each ...
- 86.5k
15
votes
Accepted
SQL Server 2017 Enterprise vs Standard - Online Indexing and Online Schema Change
The confusion seems to be with the verbiage - online/offline is sort of confusing in this context.
It refers to whether the operations being performed (a reindex or a table alter) are performed ...
15
votes
Accepted
Is there a reliable way to check if a trigger being fired was the result of a DML action from another *specific* trigger?
You can use TRIGGER_NESTLEVEL with an objectid parameter
IF TRIGGER_NESTLEVEL(@@PROCID) > 0
BEGIN
-- do stuff
END;
The EVENTDATA() function is only relevant for DDL triggers anyway.
- 14.5k
9
votes
Accepted
Is my queue table implementation race condition safe?
The only thing you're asking the database engine to do in this scenario is to enforce the PRIMARY KEY. It will do that under all conditions and isolation levels, of course.
The potential race ...
- 90.3k
7
votes
Accepted
Microsoft SQL Server 2016 Service Pack 1 (SP1) support for TDE (transparent data encryption)
The documentation Editions and supported features of SQL Server 2016 was updated to cover what is, and what is not, added in SQL Server 2016 Service Pack 1.
Transparent Data Encryption is still an ...
- 90.3k
7
votes
Can we downgrade edition from SQL Server 2016 Enterprise to Standard when we have compression feature enabled?
From Microsoft Documentation
Feature
Enterprise
StandardwithAdvancedServices
Web
Express
Express
Columnstore 1, 2
Yes
Yes
Yes
Yes
Yes
In-Memory OLTP 1, 2
Yes
Yes
Yes
Yes 3
Yes
Stretch Database
Yes
...
- 13.6k
7
votes
Accepted
Why does adding an empty columnstore index to my tables consistently make execution plans ignore bitmaps?
how can I prevent the introduction of batch mode removing excellent bitmaps?
Short answer
You can't. You'll need to upgrade from Standard Edition.
Long answer
They might both be called bitmaps, but ...
- 90.3k
6
votes
Can anyone recommend an encryption method for ALL user dbs using SQL Server 2016 STANDARD edition?
There are 3rd party tools which replicate TDE functionality.
I have tested the following during an exercise to evaluate whether we can move from Enterprise to Standard Edition:
https://www.database-...
- 323
6
votes
SQL Server 2017 Enterprise vs Standard - Online Indexing and Online Schema Change
No, you don't need to take your database offline, or even take some object offline (how?) manually before altering a table or creating an index.
On the Standard version, SQL Server will lock your ...
- 868
6
votes
Backup Performance best practices
Firstly, I'd encourage you to stop using maintenance plans for your backups. Ola Hallengren's maintenance scripts are used by a LOT of people. Simply schedule them using the SQL Agent.
I agree that ...
6
votes
Accepted
Read-ahead reads and SQL-Variant fields
There are a few issues with the Tech05_03_TestingInsertionInTempTable.sql file. It uses a function called [dbo].[ConcatenateWithOrder] which isn't defined. I think I understood your intention and ...
- 32.6k
6
votes
SQL Server 2008 R2 Edition Upgrade Process
You will need to use SQL Server 2008 R2 Enterprise Edition media to perform the in-place edition upgrade. I am unaware of any option available that allows you to perform an edition upgrade without a ...
- 9,497
6
votes
Accepted
Distributed AG on SQL Server 2017 Standard Edition?
No, you can't. That is one of the basic availability groups limitations.
Limitations
Basic availability groups can not be part of a distributed
availability group.
- 2,196
5
votes
SQL Server Standard Basic Availability Group - only 10 Listeners limit?
The limitation is only number of replicas.
Limit of two replicas (primary and secondary).
he was able to set up 12 basic availability groups. He was able to set up 10 listeners
You can create many ...
- 62.4k
5
votes
Accepted
SQL Server Editions: Backup restore of Enterprise Edition 2014 on Standard Edition 2017
I believe it is possible to restore with same old answer that subject to condition you are not using any "enterprise specific" features. This you can get from
select * from sys....
- 19.1k
5
votes
Sql Server - How to convert Audit log into a table?
I use the following technique to capture audit logs and store them in a table. Perhaps you can adapt it for your needs. We actually use Enterprise edition, so we're probably able to capture more than ...
5
votes
Accepted
Sql Server - How to convert Audit log into a table?
Capturing DML operations on individual tables is among the auditing features that are not available in Standard Edition.
If you want to trace DML operations, another possible hook is Extended Events....
- 11.3k
5
votes
Developer Edition vs Standard Edition
Mostly no, it isn't dangerous, but it depends on which version of SQL Server you are talking about, what parts you are using (just the DB engine?, SSIS?, SSRS?, ...) and your level of aversion to the ...
- 32.4k
4
votes
Accepted
How to capture all user's query on Oracle database
As per my knowledge, there is no straightforward way to achieve this goal. However, I have tried to compose the following steps to fulfill the requirements.
You can enable trace for all sessions by ...
- 8,650
4
votes
Accepted
Simulate production workload on test environment
You have a few options. There are third party tools that can help simulate a load against your database. There are open simple utilities like SQL Query Stress which can run whatever queries you supply ...
- 18.2k
4
votes
SQL Server 2017 Express faster than Standard Edition
Wait statistics
In the execution plan of the query that performs worse we find the following wait stats:
<Wait WaitType="ASYNC_NETWORK_IO" WaitTimeMs="280" WaitCount="27" />
</...
- 16.5k
4
votes
Configure Always On to use secondary site as DR
Standard Edition availability groups are limited to one replica, which is clearly stated in the documentation at Basic Always On availability groups for a single database. So if you need to maintain ...
- 7,984
3
votes
Accepted
SQL Server 2014 Edition Downgrade
Event though it says its a downgrade, I would say its kind of an upgrade since Developer Edition is equivalent to enterprise in-terms of features.
A straight Start-DbaMigration (from dbatools) will ...
- 62.4k
3
votes
Developer Edition vs Standard Edition
Developer Edition is equivalent to Enterprise, not Standard. So you could run into performance differences in Prod that you didn't have while testing in Dev. This is a good article but basically it'...
- 421
3
votes
Accepted
How to sum up the distinct Total Time of an Event ignoring duplicate overlaps in Times?
All the complexity results from the overlapping intervals.
If we pack the intervals so there are no overlaps, the solution is a straightforward group and sum.
The following code uses Itzik Ben-Gan's ...
- 90.3k
3
votes
How is basic availability group able to auto failover without a cluster?
Just like any Availability Group, having the nodes in a cluster is required for automatic failover, and automatic failover between Failover Cluster instances is not allowed.
- 47.6k
2
votes
Redo log member and control files at Oracle DB Standard Edition
How many redo log members for each group can I define?
Database Limits
MAXLOGMEMBERS
Default: 2
Maximum Value : 5
How many control files can I use by using the standard edition (one)?
I couldn'...
- 41.1k
2
votes
Accepted
How to Store Text Reports in a Database
Okay, I think that I'm beginning to understand the nuance of your question.
Given that space efficiency is a high priority, I think that you can strike the varchar(max) option from your list. Since ...
- 1,144
2
votes
Deleting data from a table containing LOBs did not reduce the amount of data reported INTERNALLY by the table or database
Your table has a lot of LOB data, and the only thing that can free up LOB pages is index reorg (with lob_compaction):
ALTER INDEX PK_LARGE_LOG_TABLE REORGANIZE WITH (LOB_COMPACTION = ON)
Index ...
- 11.2k
2
votes
Can anyone recommend an encryption method for ALL user dbs using SQL Server 2016 STANDARD edition?
Depending on the size you need for your tempdb using a ram disk is an option that should be considered. It wouldn't be written to disk ever and it provides great performance.
https://blogs.technet....
- 285
Only top scored, non community-wiki answers of a minimum length are eligible