Hot answers tagged

12

Downsides of Transparent Data Encryption compared to Always Encrypted: Only protects data at rest - backups and data files are "safe" but data in motion or in memory is vulnerable Whole database only Backup compression can take longer and be counter-productive tempdb also inherits encryption – stays even after disabling TDE Data always accessible to ...


11

Does this do what you need? select coalesce(city + ', ' + state, city + ', ' + country, state + ', ' + country, country, state, city) as location If you are 100% sure that the following holds, too (if there are constraints that make it certain): I will add that if a ...


5

Without using REVERSE... It's always fun to use a recursive solution ;) (I did mine in SQL Server 2012, earlier versions might have limitations on recursion) create function dbo.IsPalindrome (@s varchar(max)) returns bit as begin return case when left(@s,1) = right(@s,1) then case when len(@s) < 3 then 1 else ...


5

This is too long for a comment so I'll chime in with another case where one might want to turn off auto update stats. I've worked with databases supporting high-volume OLTP workloads and a stringent query performance SLA in milliseconds. Nearly all queries were trivial with a lot of attention to query and index tuning detail and some of the tables were ...


4

To avoid CROSS APPLY the simplest way I can think of is to call sys.dm_os_volume_stats with explicit parameters for database_id and file_id. This means executing a single-row result for every db/file combo. First, create a #temp table to hold results: CREATE TABLE #x(dt datetime, srv nvarchar(520), logical_name sysname, physical_name sysname, FileSizeMb ...


4

Generally I would say that having auto update statistics on is beneficial. But like any setting, there are reasons you can turn it on or off. One is that some tables have a lot of churn, and perhaps queries are not very sensitive to accurate statistics. Think ETL or other bulk scenarios where you're changing a lot of data, but either not reading it from ...


4

You are correct, I also believe that in most cases the Auto Update statistics should be set to true we should allow SQL Server to decide when to update stats and believe me it does good job. When this is set to true it make sure stats are updated about distribution of data in the field which would eventually help optimizer to prepare better plan. The ...


4

If your table is test, try this: SELECT t1.* ,New_FileName = t1.FileName + CASE WHEN t2.seq > 1 THEN '_' + ltrim(str(seq)) ELSE '' END FROM test t1 JOIN ( SELECT ID ,Memeber_Number ,FileName ,seq = ROW_NUMBER() OVER ( PARTITION BY FileName ,Memeber_Number ORDER ...


2

Simply put, TDE is data encrypted at rest (On disk) and AE is data encrypted on the wire.


2

select author from article where author IS NOT NULL and author <> '' except select name from author since any comparison the null is false I think below works select author from article where author <> '' except select name from author


1

Below will help you declare @sql xml set @sql = '<row> <ID_Cota>162986</ID_Cota> <ID_Taxa_Plano>1000</ID_Taxa_Plano> <ID_Plano_Venda>1020</ID_Plano_Venda> <ID_Pessoa>18522</ID_Pessoa> </row>' select s.n.value('(ID_Cota)[1]', 'int') as ID_Cota ,s.n.value('(ID_Taxa_Plano)[1]', ...


1

You can execute Dynamic SQL on that Linked Server by executing sp_executesql on that remote server using the 4-part name. Then you just assign that to a variable marked as OUTPUT: DECLARE @RemoteName sysname, @LoginID INT; SET @LoginID = 267; EXEC [LinkedServerName].master.dbo.sp_executesql N'SET @tmpRemoteName = SUSER_NAME(@tmpLoginID);', ...


2

As your data looks a bit like hierarchyId I thought about using that. Initial versions did not scale well up to 1 million rows, however a few indexing choices on the main temp table have helped. However issues could also be due to my test data, so can you tell me a bit more about your hierarchy? For example how many top-level parents are there, how many ...


2

Use CROSS APPLY and the .nodes method of the XML data-type, eg something like this: IF OBJECT_ID('dbo.yourTable') IS NOT NULL DROP TABLE dbo.yourTable GO CREATE TABLE dbo.yourTable ( rowId INT IDENTITY PRIMARY KEY, ST_Registro_Del XML ) GO INSERT INTO dbo.yourTable ( ST_Registro_Del ) SELECT '<row> <ID_Cota>162986</ID_Cota> ...



Only top voted, non community-wiki answers of a minimum length are eligible