Hot answers tagged

11

If a non-clustered index is created on a partitioned table, that index will be partition-aligned by default unless you explicitly specify that it should not be (e.g., use ON [PRIMARY] to specify the file group). In such a case, the SSMS script index functionality will not show the partition scheme used, but you can use sp_help on the table to confirm that ...


5

From xp_cmdshell (Transact-SQL) in the product documentation: The Windows process spawned by xp_cmdshell has the same security rights as the SQL Server service account. When it is called by a user that is not a member of the sysadmin fixed server role, xp_cmdshell connects to Windows by using the account name and password stored in the credential ...


4

Not exactly what you are looking for but could perhaps be of interest to you. The query creates weeks with a comma separated string for the days used in each week. It then finds the islands of consecutive weeks that uses the same pattern in Weekdays. with Weeks as ( select T.*, row_number() over(partition by T.ContractID, T.WeekDays order by ...


3

I couldn't understand the logic behind grouping weeks with gaps, or weeks with weekends (e.g. when there are two consecutive weeks with a weekend, which week does the weekend go to?). The following query produces the desired output except that it only groups consecutive weekdays, and groups weeks Sun-Sat (rather than Mon-Sun). Whilst not exactly what you ...


3

This one uses a recursive CTE. Its result is identical to the example in the question. It was a nightmare to come up with... The code includes comments to ease through its convoluted logic. SET DATEFIRST 1 -- Make Monday weekday=1 DECLARE @Ranked TABLE (RowID int NOT NULL IDENTITY PRIMARY KEY, -- Incremental uninterrupted sequence in the ...


3

I ended up with an approach that yields the optimal solution in this case and I think will do well in general. The solution is quite lengthy, however, so it would be interesting to see if someone else has a different approach that is more concise. Here is a script that contains the full solution. And here is an outline of the algorithm: Pivot the data ...


2

The time is not stored as number of milliseconds. It is a numeric representation of the actual time. For instance, 12:06:59 is represented as 120659. 1:00:02 pm is presented as 130102. 1:15:42 am would be 11542. Instead of calculating that all yourself, simply use the dbo.agent_datetime function. Something like: SELECT ...


2

For the sake of completeness, here is a two-pass gaps-and-islands approach that I tried myself before asking this question. Update: As I was testing it on the real data I found few cases when it was producing incorrect results and fixed it. Here is the algorithm: Generate islands of consecutive dates (CTE_ContractDays, CTE_DailyRN, CTE_DailyIslands) and ...


1

SELECT tbl_A.id FROM tbl_A LEFT JOIN tbl_B ON tbl_A.id = tbl_B.id WHERE tbl_B.id IS NULL This is most likely how I would do something like that. Just a simple join where the column in A does not equal the columns in B. The DBMS should take care of optimizations. As for varchar and char: char is more efficient when using data that is all the same length. ...



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