user353gre3
  • Member for 8 years, 4 months
  • Last seen more than a week ago
How can I design a query to pause / sleep / wait for a specified amount of time?
Accepted answer
20 votes

If you are looking for a function to block the execution batch for a specified amount of time, you can use WAITFOR in SQL Server 2000. This function is similar to SLEEP function in Oracle. Usage ...

View answer
SQL Query with optional WHERE clause parameter for date range?
4 votes

Assuming that your start date and end date are compared against DlvryDt in DeliveryOrder table, I suggest to use a very low date value for start date and a very high date value for end date in cases ...

View answer
Using CREATE TYPE within Stored Procedure Call
4 votes

Ownership chaining from procedure does not work with DDL statements and TRUNCATE statement. You can use execute with impersonation (execute as) in your case. The impersonation example for creating a ...

View answer
Need help with SQL Server PIVOT
Accepted answer
4 votes

Try the following: select office_id, [1] as value_1, [2] as value_2, [3] as value_3, [4] as value_4 from ( select office_id, setting_value, row_number() over(partition by office_id order by ...

View answer
Performance hit with column alias
Accepted answer
4 votes

A view, being just a query persisted in the database, should not theoretically have any performance hit. Same is true for column or table aliases. The performance hit, if any, will be barely ...

View answer
Confused on how a value gets added with the DEFAULT constraint
Accepted answer
4 votes

If your column definition does not have default constraint and it is nullable, the nullable field will have null mark, when you add a row even when you specify DEFAULT keyword for your insert value. ...

View answer
Generic script for stored procedure rollback
Accepted answer
3 votes

You can capture the event data with EVENTDATA() function and use a ddl trigger to persist the information into a version table, which can be used as rollback script, if needed. That way you will have ...

View answer
Security Question
Accepted answer
3 votes

Removing a principal from sysadmin role does not remove the principal from public role. You cannot use deny on members of sysadmin role or the object owners. Using sp_dropsrvrolemember system stored ...

View answer
Please explain how this query generates a calendar table in MySQL
Accepted answer
2 votes

I don't know what you want in the explanation, but the last part of the query does a cross join (Cartesian join) to generate an ordered sequence of numbers from 0 to 11322 using the digits table named ...

View answer
How to get the dependent entities (referencing entities) for an User Defined Function (UDF)
Accepted answer
2 votes

You can find out the referencing entity dependencies for your function or any other database entities by using DMV dm_sql_referencing_entities. An example statement is as below. Select ...

View answer
How to retrieve data of two columns from two tables?
Accepted answer
2 votes

Try following. select T1.M_ID, (select T2.T_NAME from TEAM_MASTER as T2 where T1.TEAM1_ID = T2.T_ID) as t1_Name, (select T2.T_NAME from TEAM_MASTER as T2 where T1.TEAM2_ID = T2....

View answer
Backup and Restore Database Method
Accepted answer
2 votes

If your database is in the full recovery model, you should immediately start taking log backups along with the full backups. For simple backup recovery model, you will not need log backups. The ...

View answer
Return every instance of a string contains search
Accepted answer
2 votes

Use following query if you want to count the number of occurrence of the string 'first' in the TextColumn each row. SELECT (LEN(TextColumn) - LEN(REPLACE(TextColumn, 'first', '')))/LEN('first') as ...

View answer
Reset Key Names to Defaults?
1 votes

You can use sp_rename systems stored procedure to change the name of the keys. sp_rename 'dbo.PK_TableName_OldName', 'PK_TableName_NewName';

View answer
Convert date and time
1 votes

For SQl Server 2008 you can use convert function like below. --For output format mm-dd-yyyy declare @d datetime = getdate() select convert(varchar(10), @d, 110) --For output format yyyy-mm-dd ...

View answer
convert update field second to time
Accepted answer
1 votes

Assuming your are using SQL Server database, you can use CONVERT function with style 114 to get the time portion from your seconds value in integer. Please refer to following code implementation. ...

View answer
2 Date Ranges - How many days from one range excluding weekends overlap
1 votes

Create date tables or table variables with your ranges and do the join query. Assuming SQL Server as the database, my sample solution is given below. Declare @Range1Begin date = '10/16/2014', ...

View answer
Get the most present elements from many fields
Accepted answer
1 votes

You can use derive table to aggregate all your column values in a single column and do ordering and counting in the derived table. Example below. Select item0 as item, count(*) as cnt from (SELECT ...

View answer
RaiseError on SQL Server stored procedures
1 votes

If you want to raise error and exit from the procedure, you may want to try return. Example below. alter PROCEDURE sp1 @PersonID int as begin IF (@PersonId is null) --you can check 0, if that is ...

View answer
SQL Server: CONVERT From ISO8601 To DateTime Within Query
1 votes

Try type casting to datetimeoffset type as below: CAST(datetime as DATETIMEOFFSET) For instance your query will become something like following: SELECT * FROM dbo.RebroadcastSmoothStreaming WHERE (...

View answer
Get only future records
1 votes

Looks like you want your current date to be before or on last show date. If that is the case try following HAVING clause. HAVING MAX(CAST(CONCAT(shows.show_date, ' ', shows.show_time) AS DATETIME) &...

View answer
database size limitations
1 votes

Database size is limited in the express edition only. In other editions the size is limited only to your storage subsystem restrictions. If it is not express edition, ask your client if it is the full ...

View answer
Remove duplicates from different columns
0 votes

I used Jeff Moden split string function modified by Aaron Bertrand, and modified it further to return concatenated string for all columns in a sorted order. I cross applied the function with the table ...

View answer
Setting Select Permission on 100+ tables in a database
0 votes

Use GRANT statement with tables delimited by comma like below. Grant select on tbl_1, tbl2, tbl3, tbl4, ...., tb1100 to [MyRole];

View answer
Dynamic Pivot with overlapping years
0 votes

You can use PIVOT with dynamic sql to solve your problem. Below is a simple implementation to solve the issue. The query will support any number of months you may have. Make sure the length of @...

View answer
Reading Data from SQL Server database
0 votes

Assuming #T is the original data table, you can use dynamic pivot to get your solution. Code below. As the code uses dynamic pivot, you can have any numbers in your table, not just 1, 2, 3 and 4; and ...

View answer
Remove a period if it is the first character in a varchar column
0 votes

Try to minimize using too many scalar functions, which could be performance hog if you are dealing with large amount of data. Following example uses STUFF function on a subset of data with union all ...

View answer
How to deal with circular foreign key
0 votes

I would not use a circular reference to model your case. I would rather use a function to check if your current step belongs to the Steps table and add check constraint to the Games table on ...

View answer
Custom calendar for calculating workdays per month
0 votes

I would create a table (holidays in the example below) to store all your company holidays, then write the calendar logic as a function, which calculates all weekdays minus the holidays in your holiday ...

View answer
How to query for 'First showed up as'
0 votes

Workable mysql solution is as below. SELECT Date, Trd, Code as 'First showed up as code', InDate as 'In Date', TDate as 'T-Date' from ( SELECT s.f_date as 'Date', t.trd as 'Trd', t.code as 'Code', ...

View answer