INFORMATION_SCHEMA Views allow you to find what tables are in your schema.

Search for varchar(max) and nvarchar(max)

select * from INFORMATION_SCHEMA.COLUMNS
where CHARACTER_MAXIMUM_LENGTH = -1

Of all the basic SQL operations, the pivot seems to cause the most problems. We can tell from the way that old articles on Simple-Talk on the topic continue to be read. It turns out that there are several questions that come to mind while learning about pivoting, but which are seldom asked on forums. Once more, Robert Sheldon attempts to answer these unspoken questions.

NULLs in SQL are a mixed blessing. The Three-Valued Logic of SQL has its uses but can cause difficulties to anyone who assumes that NULL has a 'value'. It can make reports go horribly wrong in a number of alarming ways, as Robert Sheldon explains.

Running Totals

SELECT id,month
 , Amount
 , SUM(Amount) OVER (ORDER BY id) as total_sum
FROM bill

Common Table Expressions

WITH idtempp as (
  SELECT id as id
  FROM id 
  WHERE country = "US"
  AND status = "Y"
)

SELECT *
FROM bill
WHERE id in (SELECT id from idtempp)

Temporary Functions

Temporary functions allow you to modify the data easily without writing huge case statements.

CREATE TEMPORARY FUNCTION get_gender(type varchar) AS (
   CASE WHEN type = "M" THEN "male"
        WHEN type = "F" THEN "female"
        ELSE "n/a"
   END
)
SELECT 
  name,
  get_gender(Type) as gender
FROM bill

Continuing a series on table expressions, Itzik Ben-Gan explains recursive CTEs, features from the standard T-SQL is lacking, and workarounds.

Remember that the syntax of a query against a CTE is as follows:

WITH <CTE name> [ ( <with column list> ) ]
AS
(
<table expression>
)
<outer query>;

Text as Data - q is a command line tool that allows direct execution of SQL-like queries on CSVs/TSVs (and any other tabular text files).

Useful T-SQL queries for SQL Server to explore database schema.

select schema_name(tab.schema_id) as schema_name,
    tab.name as table_name, 
    col.column_id,
    col.name as column_name, 
    t.name as data_type,    
    col.max_length,
    col.precision
from sys.tables as tab
    inner join sys.columns as col
        on tab.object_id = col.object_id
    left join sys.types as t
    on col.user_type_id = t.user_type_id
order by schema_name,
    table_name, 
    column_id;

The SQL0901 error is an incredibly frustrating error that occurs with i5 ODBC, OLE DB, and JDBC connections. Because SQL0901 is a general SQL error, it can be difficult to diagnose and harder to solve. This week I will look at one specific SQL0901 scenario, where the failure occurs inside SQL’s extended dynamic package support,

Lateral Join SQL DB2 for i: not everybody knows about it but it’s very powerful in our queries.
We want to get out one row only for each carmaker with the most sold model.

CROSS JOIN LATERAL:

SELECT a.Brand, Country, Modell, Year, Quantity FROM Faq400.AutoBrands A 
cross JOIN LATERAL 
(SELECT * from faq400.AutoStats stat
WHERE Year=2018 and A.idBrand=stat.IdBrand
order by Quantity desc
FETCH FIRST 1 ROW ONLY
) B ;

In this article, we briefly covered types of views in SQL Server and gave several examples of user defined views.

Creating a View Across Instances

In this article, you'll find the answers to the five most-asked questions about SQL COALESCE function in SQL Server. Explore useful examples.

The definition of COALESCE is an expression that returns the first non-null value from a list of values. The COALESCE syntax is:

COALESCE ( expression [ ,…n ] )

(4) SQL COALESCE is Converted to CASE. ISNULL Stays ISNULL

COALESCE returns the data type of the value with the highest precedence.

Locating the row to confirm it exists, only to have to locate it again in order to update it, is doing twice the work for nothing.

BEGIN TRANSACTION;

UPDATE dbo.t WITH (UPDLOCK, SERIALIZABLE) SET val = @val WHERE [key] = @key;

IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.t([key], val) VALUES(@key, @val);
END

COMMIT TRANSACTION;

• Whenever you know that the column, or combination or columns, makes a unique key for a nonclustered index, I always recommend that you specify UNIQUE when you create the index. That information is great for the optimizer to have when generating query plans.

• Think carefully about the impact of your clustering key on your nonclustered indexes. Any non-unique nonclustered index will be larger (more pages, eventually more levels) because the entire clustering key will be in the tree level.

• If you have a clustering key with a column (or multiple columns) that are frequently modified, then those modifications have to be propagated to the tree levels of the nonclustered indexes as well.

You didn't say what version you were using, but in SQL 2005 and above, you can use a common table expression with the OVER Clause. It goes a little something like this:

WITH cte AS (
SELECT[foo], [bar],
row_number() OVER(PARTITION BY foo, bar ORDER BY baz) AS [rn]
FROM TABLE
)
DELETE cte WHERE [rn] > 1

NULL values are special. For instance, a comparison between two NULL values, or even a comparison between a NULL value and a non-value, will always be false. This is because NULL values aren’t real values as such, but rather “unknowns”.

Say that I have a table of People, and these people have a Country column and a unique Primary Key. It's a demographics table, so these are the only things I care about; what Country and how many unique people are tied to that country.

I am thus only ever likely to SELECT WHERE or ORDER BY the Country column; a clustered index on the Primary Key doesn't do me any good, I'm not accessing this data by PK, I'm accessing it by this other column. Since I can only have one clustered index on a table, declaring my PK as Clustered would prevent me from using a Clustered Index on Country.

CMU Database Group
Published on Sep 9, 2017
SUBSCRIBE 3.2K
Subscribe Subscribed Unsubscribe
Slides PDF: http://15445.courses.cs.cmu.edu/fall2...
Notes PDF: http://15445.courses.cs.cmu.edu/fall2...
Annotated Video: https://scs.hosted.panopto.com/Panopt...

Andy Pavlo (http://www.cs.cmu.edu/~pavlo/)
15-445/645 Intro to Database Systems (Fall 2017)
Carnegie Mellon University

Therefore I thought I would give a quick explanation for anyone still unsure what a Key Lookup is actually doing and why it’s considered bad.

We all know that Non-Clustered indexes all include the “Clustered Key” of the table. If not, then you do now. And if you have no Clustered Key (ie. Your table is a Heap) then the non-clustered will contain a Row Identifier which is unique and refers back to a specific record within your Heap. This is how a non-clustered index is linked to your table… whether it be via the Clustered Key or the RID.

We often hear indexes explained using the analogy of an index in the back of a book. You want to find the information about “rabbits” for instance – and so you look that up in the back and find the list of pages that talk about rabbits.

The COALESCE and ISNULL T-SQL functions are used to return the first nonnull expression among the input arguments. SQL Server practitioners often wonder what the difference is between the two functions.