New answers tagged t-sql
0
This may be better addressed with an outer apply. (thus eliminating the need for the group by *)
If you are confident of an 1:n cardinality:
SELECT A.*, B.CountFoo
FROM TABLE1 A
outer apply (select count(B.Foo) as CountFoo from TABLE2 B where A.PKey = B.FKey) B
If Table1 has duplicates on PKey (though depending on your use, you start to have a wash in terms ...
0
RENAME is not transactional ie begin transaction .. rename .. will throw an error. So any attempt to gain an exclusive lock on a table before renaming it is futile as the transaction acquiring the lock must end (and release the lock) before RENAME can run.
Your best bet is to schedule no access during the swap. If access must happen it must be written ...
0
Please provide an example of the queries you're currently using, and your table definitions, in your post. As it stands now, your question isn't very clear, especially on how loops are even relevant here.
That being said, with the current information you provided, it sounds like you guys could possibly benefit from dynamic SQL by leveraging the system stored ...
2
If I read your question correctly, when you are doing this in SSMS you are creating the database in the context of your account (using integrated security), then after creating the database you are connecting with the limited credentials to query the database. This works fine because your account has permission to create databases but the limited credential ...
1
Follow the documentation for define a column filter
https://docs.microsoft.com/en-us/sql/relational-databases/replication/publish/define-and-modify-a-column-filter?view=sql-server-ver15
-2
Save your data to a CSV file, import to a table, and then join.
4
I want to find which are the extent pages [...]
It tells you in the error, Extent (1:896320), so from page 1:896320 to 1:896327.
The thing here is that you have an allocation error where the extent is marked as allocated but no other structure shows it as such. Is it really part of something? Is is not?
[...] and then to see what's the data in these pages.
...
answered Oct 19 at 11:26
Sean Gallardy - Retired User
21.2k2 gold badges34 silver badges65 bronze badges
0
This is an old question, but you could also send the results to a temp table and not actually SELECT them, like this:
DECLARE @Start datetime
DECLARE @End datetime
SELECT @StartTimeWA=GETDATE()
DROP TABLE IF EXISTS #STAT
SELECT
[id]
,[database_id]
,[proc_name]
,[exec_t]
INTO #STAT
from
[DB].[dbo].[STAT]
SELECT @End=GETDATE(...
0
The first thing you need to do is understand what is happening when the maintenance task is "stalled". Use sp_whoisactive to determine the state of the session running the maintenance. Look for things like blocking, and take a look at the wait type listed for the session.
Determining what is causing the "stall" will allow you to define ...
4
I think in comments you mentioned using maintenance plans. Its been a long time, almost over 6 years since i have used maintenance plans to perform database maintenance. But per my knowledge those are tracked under SQL server agent job history , so you can query msdb database to get those metrics.
However i personally recommend doing this via ola hallengren ...
0
Try an approach like this.
First identify the rows that have changed by using LAG.
The do a running total on those change points.
Once you know that you can do the Group By but include the groups.
DROP TABLE IF EXISTS #Source
CREATE TABLE #Source (RowNum_Helper INT, ItemID CHAR(1), Classification CHAR(1), Start_Date DATETIME2, End_Date DATETIME2)
INSERT ...
1
SQL cannot return data that does not exist... if there is no monday in your data, SQL won't "guess" that you are expecting something for monday... (The case does not what you are expecting here).
If you want SQL to show you data for each day, then you need a lookup table that would contain all days and then, you could simply do a left join on your ...
7
if I have a clustered index on [column_a], [column_b], and [column_c] and run the same query from above, will the data ALWAYS come back sorted based on that order since that's the order that the clustered index was created on?
No.
SQL Server does not guarantee that it will return data in any order unless you specify the order. It is easy to prove things can ...
2
You need to CAST() (CONVERT()) the hard-coded value in your query as a VARBINARY(MAX) because by default a value in single quotes in a query is natively treated as a VARCHAR data type.
You could also use the following CROSS JOIN query (without hard-coding the value) to completely eliminate the need to cast anything since your source and destination fields in ...
1
You SUM up all the sizes, then do the multiplication (then division), but along the way if the numbers get too big, you can overflow the int limit. There are a few ways you can work around that.
Primarily, you want to convert your numbers to a bigger datatype before doing the math to get the larger scale. Then convert again for your display formatting, if ...
1
SUM(size) will return an int because the size column is int. Convert the column to a larger type, either with an explict CAST or an implicit conversion to avoid the overflow.
Below is an example that multiplies size by numeric constant 1.0 so the expression returns a numeric(13,1), resulting in numeric(38,1) after the SUM function:
SELECT
sys.databases....
0
You need to CAST size to BIGINT first. The max size for a BIGINT is quite large. (9,223,372,036,854,775,807) LINK
SELECT
sys.databases.name,
CONVERT(VARCHAR,SUM(TRY_CAST(size AS BIGINT)) * 8/1024) +' MB' AS [Total_Size]
FROM sys.databases
JOIN sys.master_files
ON sys.databases.database_id=sys.master_files.database_id
...
10
The SQL Standard specifies that the result of an update must be the same as if it had been executed in three separate and non-overlapping phases:
A read-only search determines the records to be changed and the new column values.
Changes are applied to affected records.
Database consistency constraints are verified.
This is known as update phase separation: ...
1
Doesn't look like it. The time stats for that operator don't show it running for long,
<RunTimeCountersPerThread Thread="0" ActualRows="2302947" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="5417" ActualCPUms="5406" />
...
answered Sep 29 at 18:02
David Browne - Microsoft
36.1k3 gold badges38 silver badges79 bronze badges
0
Here's an example of an SQL Query that would achieve what you are expecting:
create table #school (school_name varchar(20), school_area varchar(20));
insert into #school values ('someschool1', 'schoolarea1'),
('someschool2', 'schoolarea1'),
('someschool3', 'schoolarea1'),
('soomschool4', 'schoolarea2'),
('soomschool5','schoolarea2')
select ROW_NUMBER() ...
Top 50 recent answers are included