Returns information about the connection properties for the unique connection that a request came in on.
SELECT ConnectionProperty('auth_scheme')
SET QUOTED_IDENTIFIER
Determine if we can/can't create and use objects that have reserved keyword names.
SET QUOTED_IDENTIFIER OFF
GO
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO
--> will be failed.
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE "select" ("identity" INT IDENTITY NOT NULL, "order" INT NOT NULL);
GO
--> will be succeeded.
Notes:
Reserved keyword should be delimited by double quotation
Default value of QUOTED_IDENTIFIER = ON.
Concatenate column results to a string
select STUFF((SELECT N';' + TableColumnName
FROM dbo.SrcTableName
FOR XML PATH(''), TYPE).value('.[1]', 'nvarchar(max)'), 1, 2, N'')
Or:
declare @StrList nvarchar(max) = ''
update dbo.SrcTableName
set @StrList = @StrList+ isnull(TableColumnName, '') + ','
select @StrList=left(@StrList,len(@
select @StrList
Paging in Oracle
select *
from (select rownum,fld1,fld2,fld3... from bigtable where <<CONDITION>> )
where rownum between 1 to 100 -- or between 101 to 200, and so on....
DELETE VS TRUNCATE
- TRUNCATE is similar to the DELETE with no WHERE clause.
- DELETE is a logged operation on a per row basis. This means that the deletion of each row gets logged and physically deleted. TRUNCATE is also a logged operation, but in a different way. TRUNCATE logs the deallocation of the data pages in which the data exists.
- Because of this, TRUNCATE is faster and uses fewer system and transaction log resources.
- You can DELETE any row that will not violate a constraint, while leaving the foreign key or any other contraint in place.
- You cannot TRUNCATE a table that has any foreign key constraints. You will have to remove the contraints, TRUNCATE the table, and reapply the contraints.
- TRUNCATE will reset any identity columns to the default seed value. DELETE will not do this.
- You need to be db_owner, ddl_admin, or owner of the table to be able to fire a TRUNCATE statement.
If TRUNCATE is failed - check one of those differences.
sys.dm_os_wait_stats
Returns information about all the waits encountered by threads that executed. You can use this aggregated view to diagnose performance issues with SQL Server and also with specific queries and batches.
SELECT *
FROM sys.dm_os_wait_stats
ORDER BY wait_time_ms DESC
Subscribe to:
Posts (Atom)