Category: SQL

Posted on: March 12, 2019 Posted by: John Huh Comments: 0

Count What?

Found this article today to optimize count on Postgresql DBMS. I wonder if this will work for my use case.   Source: https://www.cybertec-postgresql.com/en/postgresql-count-made-fast/  

Posted on: November 8, 2011 Posted by: John Huh Comments: 0

Dump database structure

How do you get a full database structure from Microsoft SQL Server? [sql] select * from INFORMATION_SCHEMA.COLUMNS [/sql]

Posted on: August 9, 2011 Posted by: John Huh Comments: 0

Use Postgresql for WordPress?

I would love to run WordPress on Postgresql database, simply because most of DBs I use are Postgresql-based. Here’s what WordPress says about it. http://codex.wordpress.org/Using_Alternative_Databases I’m sure there must be a way to make WordPress more portable to other databases. What’s your take on this?

Posted on: February 17, 2011 Posted by: John Huh Comments: 0

Find first and last sunday

This is a nifty query that I can use on reporting, I’m sure there are other uses for it. [sql] declare @year int set @year =2011 — First and Last Sunday by SqlServerCurry.com select min(dates) as first_sunday,max(dates) as last_sunday from ( select dateadd(day,number-1,DATEADD(year,@year-1900,0)) as dates from master..spt_values where type=’p’ and …

Posted on: January 21, 2011 Posted by: John Huh Comments: 0

Excel MDX extension

I was searching for a way to view MDX query within Excel sheets and found one here… http://olappivottableextend.codeplex.com/ Now I can see what query just ran from Excel to SSAS.

Posted on: November 12, 2010 Posted by: John Huh Comments: 0

Text what?

Problem with datatype “text” is… you can’t search and replace a string using the REPLACE function. One way is to cast the text field to a varchar/nvarchar to use the REPLACE function. Example: [sourcecode language=”sql”] UPDATE tablename SET textfield = REPLACE( CAST(textfield AS VARCHAR(8000)), ‘\r\n’, ” ) [/sourcecode] Also, len(textfield) …

Posted on: September 28, 2010 Posted by: John Huh Comments: 0

Limit what?

How do you start at a certain row and limit it? Used for paging perhaps, but here it is how: [sourcecode language=”sql”] SELECT Description, Date FROM (SELECT ROW_NUMBER() OVER (ORDER BY Date DESC) AS Row, Description, Date FROM LOG) AS LogWithRowNumbers WHERE Row >= 11 AND Row <= 20 [/sourcecode]

Posted on: September 28, 2010 Posted by: John Huh Comments: 0

Postgresql 9.0 Cheatsheet

PostgreSQL 9.0 has been released. Here’s a cheatsheet for it: http://www.postgresonline.com/journal/archives/177-postgresql90_cheatsheet.html

Posted on: September 22, 2010 Posted by: John Huh Comments: 1

Microsoft SQL Xtype?

XType Datatype —————- 34 image 35 text 36 uniqueidentifier 48 tinyint 52 smallint 56 int 58 smalldatetime 59 real 60 money 61 datetime 62 float 98 sql_variant 99 ntext 104 bit 106 decimal 108 numeric 122 smallmoney 127 bigint 165 varbinary 167 varchar 173 binary 175 char 189 timestamp 231 …

Posted on: September 22, 2010 Posted by: John Huh Comments: 0

Tables and Fields dump

Here’s a quick way to see the user tables and fields within a database. [sourcecode language=”sql”] select s.id, s.name as table_name, c.[name] as column_name, t.name as datatype, c.length, c.xprec from sysobjects s inner join syscolumns c on c.id = s.id inner join systypes t on t.xtype = c.xtype where s.type …