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 ...
answered Dec 28 '19 at 16:51
David Browne - Microsoft
19.1k1 gold badge17 silver badges48 bronze badges
Only top voted, non community-wiki answers of a minimum length are eligible
