Posts Tagged ‘sql’

T-SQL: Show running queries

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext;
— Alternate approach
SELECT s.session_id
,r.
STATUS
,r.blocking_session_id 
‘blocked by’
,r.wait_type
,wait_resource
,r.wait_time / (1000.0) 
‘Wait Time (in Sec)’
,r.cpu_time
,r.logical_reads
,r.reads
,r.writes
,r.total_elapsed_time / (1000.0) 
‘Elapsed Time (in Sec)’
,Substring(st.TEXT, (r.statement_start_offset / 2) + 1
, (
(
CASE r.statement_end_offset
WHEN – 1
THEN Datalength(st.TEXT
)
ELSE r.statement_end_offset
END – r.statement_start_offset
) / 2
) + 1) AS statement_text
,Coalesce(Quotename(Db_name(st.dbid)) + N’.’ + Quotename(Object_schema_name(st.objectid, st.dbid)) + N’.’ +
Quotename(Object_name(st.objectid, st.dbid)), ”) AS command_text
,r.command
,s.login_name
,s.
host_name
,s.
program_name
,s.host_process_id
,s.last_request_end_time
,s.login_time
,r.open_transaction_count
FROM sys.dm_exec_sessions AS s
INNER JOIN sys.dm_exec_requests AS r ON r.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS st
WHERE r.session_id != 
@@SPID
ORDER BY r.cpu_time 
DESC
,r.
STATUS
,r.blocking_session_id
,s.session_id

Performance Benchmark — Storm v Spark v Flink (Re-post)

https://yahooeng.tumblr.com/post/135321837876/benchmarking-streaming-computation-engines-at

T-SQL: Select / list all tables of a database

USE YourDBName
GO
SELECT *
FROM sys.Tables
Go
 microsoft-sql-00

SQL Server: Delete all tables in a database (T-SQL)

USE MyDB
GO
EXEC sp_MSforeachtable @command1 = “DROP TABLE ?”
GO
microsoft-sql-00

Quick Tip: How to Fix “Invalid Object Name” for New Tables in SQL Server Management Studio (SSMS)

This big headache has a simple solution:

Edit -> IntelliSense -> Refresh Local Cache

SQL Server / T-SQL: ROLLUP(), CUBE(), GROUPING_SETS()

Note: Much of this was just pulled from the language manual; still, I find it a useful extract with and I’ve added a little commentary.

ROLLUP ( )Generates the simple GROUP BY aggregate rows, plus subtotal or super-aggregate rows, and also a grand total row.
The number of groupings that is returned equals the number of expressions in the plus one. For example, consider the following statement.

SELECT a, b, c, SUM ( <expression> )
FROM T
GROUP BY ROLLUP (a,b,c);


One row with a subtotal is generated for each unique combination of values of (a, b, c), (a, b), and (a). A grand total row is also calculated.
Columns are rolled up from right to left. The column order affects the output groupings of ROLLUP and can affect the number of rows in the result set.
CUBE ( )Generates simple GROUP BY aggregate rows, the ROLLUP super-aggregate rows, and cross-tabulation rows.
CUBE outputs a grouping for all permutations of expressions in the <composite element list>.
The number of groupings that is generated equals (2n), where n = the number of expressions in the <composite element list>. For example, consider the following statement.

SELECT a, b, c, SUM (<expression>)
FROM T
GROUP BY CUBE (a,b,c);

One row is produced for each unique combination of values of (a, b, c), (a, b), (a, c), (b, c), (a), (b) and (c) with a subtotal for each row and a grand total row.
Column order does not affect the output of CUBE.
GROUPING SETS ( )Specifies multiple groupings of data in one query. Only the specified groups are aggregated instead of the full set of aggregations that are generated by CUBE or ROLLUP. The results are the equivalent of UNION ALL of the specified groups. GROUPING SETS can contain a single element or a list of elements. GROUPING SETS can specify groupings equivalent to those returned by ROLLUP or CUBE. The can contain ROLLUP or CUBE.
( )The empty group generates a total.

SQL: Compare Columns of 2 Tables

SELECT * FROM (
SELECT * FROM Jason.INFORMATION_SCHEMA .COLUMNS WHERE TABLE_NAME='cool_table'
) a FULL OUTER JOIN (
SELECT * FROM [SOMESERVER].[SOMEDB] .INFORMATION_SCHEMA. COLUMNS WHERE TABLE_NAME='DIM_Product'
) b ON a.COLUMN_NAME =b. COLUMN_NAME
WHERE a .COLUMN_NAME IS NULL OR b.COLUMN_NAME IS NULL
OR a .DATA_TYPE<> b.DATA_TYPE OR a.IS_NULLABLE <>b. IS_NULLABLE
OR a .CHARACTER_MAXIMUM_LENGTH<> b.CHARACTER_MAXIMUM_LENGTH
OR a .NUMERIC_PRECISION<> b.NUMERIC_PRECISION OR a.NUMERIC_SCALE <>b. NUMERIC_SCALE
OR a .COLLATION_NAME<> b.COLLATION_NAME -- and maybe some other columns

Transact-SQL, PL/SQL, PL/pgSQL defined and compared

  • PL/SQL is a proprietary procedural language used by Oracle
  • PL/pgSQL is a proprietary procedural language used by PostgreSQL
  • TSQL is a proprietary procedural language used by Microsoft in SQL Server.

TSQL is a proprietary procedural language used by Microsoft in SQL Server . Procedural languages are designed to extend the SQL ‘s abilities while being able to integrate well with SQL .Several features such as local variables and string/data processing are added.These features make the language Turing complete.

Quick Tip: Create Table as Select (CTaS) Hack in MS SQL Server in 1 Line

select * into target_table from [DB]. [dbo].[table]

Quick Tip: Get the Size of MS SQL Server Table in 1 Line

sp_spaceused ‘[dbo].[table]’