New answers tagged t-sql
0
This is an XY problem. You want to fix the VLFs in your log file, not fill a transaction log so it overflows into a second log file, freeing up the first log file.
There are definitely ways to stabilize VLFs, even in an AG, even in full recovery, and without disrupting your log chain. Here is a long-winded but easily reproducible example.
First, let's make a ...
0
Two aspects:
1: Why not just empty the log (BACKUP LOG or (temporarily) switch to simple recovery model) and shrink the log file to an extremely small size. Then expand it (once or a few times, based on what version of SQL server you are on and how many VLFs you want) to the size you want. That is IMO the easiest way to manage large number of VLFs.
2: ...
-2
I think you are talking about oracle redo log files. In order to quickly fill in redo log files, you need to pick a very large table, for example, 100 million of rows, and if the table has Date type column, below update will create very large redo:
update big_table set date_column = sysdate where rownum < 500000;
0
The query you have has a semi-join,
DELETE A FROM table1 WHERE
EXISTS
(
SELECT 1
FROM
table2 B WITH
WHERE
B.id = A.id
)
Maybe you would have written it as:
DELETE A
FROM table1
inner join table2 B on B.id = A.id
A semi-join can be more efficient. You can look up what a semi-join is here and here, for performance check out this article.
0
We can formulate your query in the following way:
We want all time intervals, for which there are no other time intervals that overlap AND also have a higher priority.
We can define overlapping as: the start time of one interval is between the start and end time of another.
Assuming here that two intervals starting and finishing coterminus are NOT ...
1
It really depends on ... what fields, how big index etc....
The ratio is not great but ...
Is it a simple logging table with insert only?
how often do you insert vs how often do you query.
If you query only once a week but do a big select on a multimilion wide table then even the 10 index scans can be ok.....
To summarize,
I would ask what select is running ...
3
What I would do is
SET STATISTICS IO ON
and then I would test the SELECT statement that produces the seek and the UPDATE statement.
Finally, I would compare the number of pages read by the SELECT with the number of pages written by the UPDATE.
But in general this ratio between seek and updates seems to suggest that your nonclustered index is not valuable. ...
-3
I would prefer :
CREATE PROC P_LONG_RUNNING_QUERY @DELAY_SECOND SMALLINT
AS
SET NOCOUNT ON;
-->testing
IF NOT EXISTS(SELECT *
FROM sys.dm_exec_requests
WHERE DATEDIFF(second, start_time, getdate()) > @DELAY_SECOND)
RETURN;
--> collecting
DECLARE @MSG NVARCHAR(max) =
CAST((
SELECT Query.session_id as [SPID],
...
0
Based on your description and example, i think what you are looking for is this:
;with prep as (
select distinct [From] as timeValue from Records
union
select distinct [To] from Records)
,intervals as (
select prep.timeValue as newFrom, lead(prep.timeValue) over (order by timeValue asc) as newTill
from prep )
select *
from intervals as intervals
...
1
If you intend to be able to update/insert through the view, it will likely not work. Please see my post here:
http://nedotter.com/archive/2017/07/dml-for-memory-optimized-tables-in-partitioned-views/
Rows cannot transition to/from the memory-optimzed side by changing the key/partitioning value. Depending on the version of SQL you are running, it will either ...
0
So if I understand your examples correctly it looks like you want the unique list of NumerCzesci values from both tables, except when Zlozenie.NumerCzesci contains "ZŁOŻENIE". Then you want to use PodZlozenie.NumerCzesci instead. You should be able to achieve that with a FULL JOIN and a DISTINCT clause like so:
SELECT DISTINCT
CASE
...
1
Some of the permissions you're trying to change don't exist in the way you're trying to use them.
For example, to deny CREATE TABLE and most other DDL statements, for a particular schema, the correct syntax and permission would be DENY ALTER ON SCHEMA::dbo TO MyRole.
This should cover the equivalent of the db_ddladmin role's permissions. (I know it's a ...
1
There are 4x different RDBMS products under the Sybase name ... Adaptiver Server Enterprise (ASE), SQLAnywhere, IQ and Advantage. The 4x products do not share a common SQL language/syntax.
The question references a link for the create trigger syntax in ASE.
The sample code in the question appears to also be for ASE.
However, from the comments we've ...
2
I try to answer to all your questions:
If I create indexes on the CentralDB's table, will the views use it?
Yes for sure, if the indexed columns are used in the views, like the Department Code in your example.
Are Columnstore Indexes better choice?
Yes and No, it depends on numbers of rows and the usage of the central table.
It's something you have to ...
1
Fisrt, you cannot create indexed views when the tables are spread over different databases.
Second, SQL Server have special DMVs with name beginning as dm_db_missing index_* that will be filled with demands of creating indexes needed for the executed queries. So let it works a lot (one month) and look at the content of theeses views to create the appropriate ...
12
How does SQL Server know which rows to delete?
To understand how it's processed, it's probably more helpful to look at the execution plan for the query. Setup scripts are at the end.
First, let's change the query a little bit to something that should throw an error, but doesn't.
DELETE A
FROM table1 AS A
WHERE EXISTS
(
SELECT 1/0
FROM table2 B
...
1
the server goes through every row of table1 and checks, if there is a corresponding id in table2 and deletes it , if that is the case..
1
Switching the offset will take into account the previous offset if it exists. It would be better to cast the original value as a datetime to truncate the previous offset then set the new offset as needed:
TODATETIMEOFFSET(CAST(@OriginalDateTimeOrOffset AS DATETIME), '-06:00')
5
I have a clustered columned stored index on table used for logging - only inserts
if only inserts are applied, should be the operation reorganize and be faster
You should not even bother. Reorganize for a columnstore does:
Physically removes rows from a rowgroup when 10% or more of the rows
have been logically deleted. The deleted bytes are reclaimed on ...
3
Reorganizing your indexes will always be faster than a full rebuild, and typically the difference in performance gains from one vs the other is more bang for your buck with reorganizing.
If you use Partitioning, then you can specify which partitions you want to rebuild and/or reorganize. Partitioning is a good solution to break up large tables / indexes to ...
0
So I re-wrote your query (under a couple of assumptions) to hopefully set it up for "great success". The three main things the below queries do are:
1: Pre-filter the data from Ztest_Participation into a temp table. By doing this filtering upfront you simplify your final INSERT query for the query optimizer by pre-materializing just the data you ...
0
@mustaccio has already answered your question in the comments: place it just before AS at the top of the trigger.
You have a much bigger problem though. Your trigger does not deal with multi-row inserts. It needs a full rewrite. I hope I've got these joins right, as I don't have your full relational model:
create trigger "DBA"."...
1
the problem is the error severity of the backup exception.
I simulated your situation with this script:
BACKUP DATABASE [test] TO DISK = N'X:\DB\SQL\2019\MSSQL15.MSSQLSERVER\MSSQL\Backup\test.bak'
WITH NOFORMAT, NOINIT, NAME = N'test-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
BACKUP DATABASE [test] TO DISK = N'C:\DB\SQL\2019\MSSQL15....
1
You'll want to use the PIVOT operator in SQL Server. (Note UNPIVOT is really useful to know as well).
This StackOverflow Answer gives a really good walkthrough on how to utilize PIVOT.
You basically want a query like this:
WITH CTE_MasterDetails AS -- Join your Master and Details together first in a CTE
(
SELECT M.zplid, M.zplidname, D.CodeType, D....
0
The solution you need is to group by the transaction id, then aggregate the distinct dirs. You need to join t3 to get the dir
SELECT
t2.trans_id,
dir = STRING_AGG(t3.dir, ' - ')
FROM t2
JOIN t3 ON t3.dept_nbr = t2.dept_nbr
GROUP BY t2.trans_id;
I'm assuming that you have STRING_AGG available on your version of MSSQL, if not you must use a solution ...
0
To get the path in the query to the backup file, you need to use the [msdb].[dbo].[backupmediafamily] table.
Such a request should satisfy your conditions:
DECLARE @dbname VARCHAR(255) = NULL --set this to be whatever dbname you want
SELECT bs.[database_name] AS 'Database Name',
CASE bs.type
WHEN 'D' THEN 'Full'
WHEN 'L' THEN '...
3
According to you question, I think this is what you want:
select * from #TableB b
where not exists (
select 1 from #TableA a
where b.StartPos between a.StartPos and a.EndPos
and A.RID = b.RID
)
union
select * from #TableA a
where not exists (
select 1 from #TableB b
where a.StartPos between b.StartPos and b.EndPos
and a.EndPos ...
0
DECLARE @hid_Specific HIERARCHYID
/** Assign your specific node path **/
SET @hid_Specific='/1/1/3/1/';
SELECT
hrchy_id,
*
FROM
tblHierarchyData
WHERE
PATINDEX(hrchy_id.ToString()+'%',@hid_Specific.ToString())=1
3
If you intend to use the cloned database for production use, specify the VERIFY_CLONEDB option which also clears the statistics in the cloned database.
VERIFY_CLONEDB
Verifies the consistency of the new database. This option is required
if the cloned database is intended for production use. Enabling
VERIFY_CLONEDB also disables statistics and query store ...
answered Dec 31 '20 at 0:13
David Browne - Microsoft
29.3k2 gold badges31 silver badges66 bronze badges
0
I got this error when updating a lot of tables in one transaction ( via an entity framework migration)
It seemed to turn out that I needed to update the tables in order of referential integrity, doing the child tables first.
I split the process into several migrations.
2
I believe this is by design because the DBCC CLONEDATABASE documentation specifies system statistics are copied. My interpretation is that includes not only stats blobs but row counts as well.
To rectify the row counts, execute DBCC UPDATEUSAGE with the COUNT_ROWS option:
DBCC UPDATEUSAGE('CloneDBIssue_Clone') WITH COUNT_ROWS;
7
This works as you want on Enterprise/Developer Edition:
use tempdb
go
CREATE TABLE dbo.T (id int PRIMARY KEY, b bit NOT NULL, txt varchar(20));
GO
CREATE OR ALTER VIEW dbo.V
WITH SCHEMABINDING AS
SELECT T.Id, T.txt
FROM dbo.T AS T
WHERE T.b = 1;
GO
CREATE UNIQUE CLUSTERED INDEX idx_V ON dbo.V (Id);
GO
UPDATE V
SET txt = 'Foo';
The update has this ...
answered Dec 28 '20 at 23:26
David Browne - Microsoft
29.3k2 gold badges31 silver badges66 bronze badges
0
I was able to fix my own problem. Below is the script I used to complete my report:
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server,
msdb.dbo.backupset.database_name,
msdb.dbo.backupset.backup_start_date,
msdb.dbo.backupset.backup_finish_date,
CAST((CAST(DATEDIFF(s, msdb.dbo.backupset.backup_start_date, msdb.dbo.backupset.backup_finish_date) ...
1
You have a fairly involved problem with not a lot of details but I think I understand what you're trying to accomplish and the solution is not simple.
You have a two step process. The first step is using PIVOT or UNPIVOT to transpose the customer amounts data you received so that the column headers become values in a column called Customer and the TotalValue ...
0
If you are unable to detach your database then, what is the state of your DB? It may be in SUSPECT mode. If It is in Suspect mode he, try the following solution:
ALTER DATABASE dbName SET EMERGENCY
DBCC CHECKDB ('dbName')
ALTER DATABASE dbName SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CHECKDB (dbName, REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE dbName SET ...
Top 50 recent answers are included
