Hot answers tagged

3

From comments: The built-in STRING_SPLIT() function was added in SQL Server 2016, not 2014. And for me, I go the SQLCLR route because I am not using Azure Single DB or AWS RDS and thus have no reason not to. I use the SQLCLR library that I created, SQL#, which contains String_Split and String_SplitInts (optimized for splitting a list of BIGINT / INT / ...


3

This uses a Dynamic SQL query. It excludes system databases. Checks for is_auto_create_stats_on or is_auto_update_stats_on 0 OFF and change it to ON declare @sql nvarchar(max); ; with db as ( select name, is_auto_create_stats_on, is_auto_update_stats_on from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb') ...


2

DECLARE @SQL NVARCHAR(MAX) = '' -- getting the commands list SET @SQL = ( SELECT '' + x.cmd FROM ( select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET AUTO_CREATE_STATISTICS ON;' AS cmd from sys.databases WHERE database_id > 4 AND is_auto_create_stats_on = 0 UNION ALL select 'ALTER DATABASE ' +...


2

Ok, here is my revised approach, using NTILE. It actually gives roughly the same as your proposed output. declare @ships table ( shipnr int, portnr varchar(50) ); insert into @ships (shipnr, portnr) values (1, 'A'), (2, 'A'), (3, 'A'), (4, 'A'), (5, 'A'), (6, 'B'), (7, 'B'), (8, 'B'), (9, 'B'), (10, 'C'), (11,'D'), (12, 'D'), (13, 'E'), (14, 'E'), (15, '...


1

You need to parse the string to separate the alpha + numeric part. Assuming that it is always alpha first follow by numeric and not the other way round the 2 cross apply split the alpha and numeric part from the string select * from yourtable t cross apply ( select alpha = LEFT(your_col, PATINDEX('%[^0-9]%', your_col)) ...


1

Base on your input data, I will do something like this: select I.X.value('(key/text())[1]', 'varchar(50)') as ItemID, I.X.value('(value/text())[1]', 'varchar(50)') as Store from @XML.nodes('values/Nationality') as O(X) cross apply O.X.nodes('item') as I(X); ouptput ItemID Store UK def IE def


1

So, you are looking for the difference in Years between the two dates, correct? And you want to show one decimal place in the calculation, correct? Then a simple DATEDIFF() will not work for you. Try this instead: SELECT ROUND(CAST(Datediff(Day, emp.hiredate, GETDATE()) as decimal(9,1))/ 365, 1) This is also imprecise (because of leap years) but matches ...


1

You can also group it in inner query. SELECT emp.ID, emp.fname, emp.lname, emp.address1 , emp.address2, emp.city, emp.state, emp.zip, emp.phone, s.Sales FROM ( SELECT emp_id, SUM(sales) AS Sales FROM empPerformance WHERE saledate BETWEEN @startdate AND @enddate GROUP BY emp_id ) s JOIN employeeData emp ON emp.Id = s.emp_id


1

I don't believe it's possible, or useful to CREATE MASTER KEY in the master database in Azure SQL Database/Azure Synapse Analytics (formerly SQL Data Warehouse). And the error you're seeing is a bit misleading, as you get that error whenever you attempt an operation in master that is allowed in normal SQL Server, but prohibited in Azure SQL Database. The ...


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