Posts Tagged ‘sql’

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: 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

Quick Tip: MS SQL TOP = MySQL Limit

 The MS SQL equivalent of MySQL’s LIMIT clause is TOP. Example:
SELECT TOP 10 * FROM stuff;
Will return the top ten rows, effectively doing the same thing as
SELECT * FROM stuff LIMIT 10;

Quick Tip: How to Extract a Month as an int in PaDB

select extract(month from ds)::int
from cool_db_name.cool_table limit 10;

Postgres / PaDB: Statistics

corr(YX)double precisiondouble precisioncorrelation coefficient
covar_pop(YX)double precisiondouble precisionpopulation covariance
covar_samp(YX)double precisiondouble precisionsample covariance
regr_avgx(YX)double precisiondouble precisionaverage of the independent variable (sum(X)/N)
regr_avgy(YX)double precisiondouble precisionaverage of the dependent variable (sum(Y)/N)
regr_count(YX)double precisionbigintnumber of input rows in which both expressions are nonnull
regr_intercept(YX)double precisiondouble precisiony-intercept of the least-squares-fit linear equation determined by the (XY) pairs
regr_r2(YX)double precisiondouble precisionsquare of the correlation coefficient
regr_slope(YX)double precisiondouble precisionslope of the least-squares-fit linear equation determined by the (XY) pairs
regr_sxx(YX)double precisiondouble precisionsum(X^2) – sum(X)^2/N (“sum of squares” of the independent variable)
regr_sxy(YX)double precisiondouble precisionsum(X*Y) – sum(X) * sum(Y)/N (“sum of products” of independent times dependent variable)
regr_syy(YX)double precisiondouble precisionsum(Y^2) – sum(Y)^2/N (“sum of squares” of the dependent variable)
stddev(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numerichistorical alias for stddev_samp
stddev_pop(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numericpopulation standard deviation of the input values
stddev_samp(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numericsample standard deviation of the input values
variance(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numerichistorical alias for var_samp
var_pop(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numericpopulation variance of the input values (square of the population standard deviation)
var_samp(expression)smallintintbigintrealdouble precision, or numericdouble precision for floating-point arguments, otherwise numericsample variance of the input values (square of the sample standard deviation)

Quick Tip: Create a Binary Column Conditionally in Postgres/PaDB/SQL


create table c as
    select a.id, tall, funny, weight,
           (case when b.id is null then 0 else 1 end) as rich
    from tablea a left outer join
         tableb b 
         on a.id = b.id;
or
select
    a.id, tall, funny, weight,
    (b.id is not null)::integer as rich
from
    tablea a
    left outer join
    tableb b on a.id = b.id