Archive for the ‘SQL and NoSQL’ Category

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]’

MS SQL Server: Get Size of All Tables in a DB

SELECT 
    s.Name AS SchemaName,
    t.NAME AS TableName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN 
    sys.schemas s ON s.schema_id = t.schema_id
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
WHERE 
    t.NAME NOT LIKE 'dt%' 
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name, p.RowsORDER BY 
    s.Name, t.Name

Quick Tip: Check Last Update Time of a SQL Table (Note: requires permission)

SELECT OBJECT_NAME (OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys .dm_db_index_usage_stats
WHERE database_id = DB_ID( ‘TomasTest’)
AND OBJECT_ID =OBJECT_ID( ‘dbo.forecast’)

Quick Tip: Show Postgres/pSQL Tables in Postgres OR R in 1 Line

Outside of R:

\dt

In R:

dt <- dbGetQuery(con1, “SELECT * FROM pg_catalog.pg_tables”)

 

Quick Tip: Search for SQL User-defined Functions in 1 Line

SELECT * FROM sys.objects WHERE RIGHT(type_desc, 8) = 'FUNCTION'
 

SQL: Use of @ to Avoid SQLInjection

The @ (i.e. @Something) means it’s a parameter that you will supply a value for later in your code. This is the best way of protecting against SQL injection.
Create your query using parameters, rather than concatenating strings and variables.
The database engine puts the parameter value into where the placeholder is, and there is zero chance for SQL injection.

HiveQL: SQL Connector

 
To bridge the difference between SQL and HiveQL, we have the SQL Connector
feature to translate standard SQL-92 queries into equivalent HiveQL queries. The SQL
Connector performs syntactical translations and structural transformations. For
example:
1. Quoted Identifiers
HiveQL uses back-quote while SQL uses double quote when quoting identifiers.
Even when a driver reports the back-quote as the quote character, some
applications still generate double quoted identifiers.
2. Table Aliases
HiveQL does not support the AS keyword between a table reference and its
alias.
3. JOIN, INNER JOIN and CROSS JOIN
SQL INNER JOIN and CROSS JOIN syntax is translated to HiveQL JOIN syntax.
4. TOP N/LIMIT
SQL TOP N queries are transformed to HiveQL LIMIT queries.

MySQL v. MS SQL Server Information Schema Queries

In MySQL, you would run the following to get information on tables in a database:
SELECT * FROM information_schema.tables
WHERE table_schema = ‘YourDatabaseName’;
In MS SQL Server, the same would be:

USE YourDatabaseName
SELECT * FROM INFORMATION_SCHEMA.tables;
– See more at: http://dbadiaries.com/querying-information_schema#sthash.aQp6qbaP.dpuf