Postgres - Regroup rows with at least one common attribute I have the following table :

Letter | Figure
-------|-------
   A   |    1
   B   |    1
   B   |    2
   C   |    2
   C   |    3
   D   |    3
   E   |    4

I would like to group rows that share a common letter OR figure. Result should then be :

Letter | Figure | Groupment
-------|--------|-------
   A   |    1   |   I
   B   |    1   |   I
   B   |    2   |   I
   C   |    2   |   I
   C   |    3   |   I
   D   |    3   |   I
   E   |    4   |   II

I know I can define a postgres function and re-scan the all table for each new row, but I'm trying to find something a little bit less unoptimized.

Thanks for your help !

This was tricky but fun. Using a recursive query:

with recursive
  v(l, f) as (
    -- select Letter, Figure from table
    values ('A', 1), ('B', 1), ('B', 2), ('C', 2), ('C', 3),  ('D', 3), ('E', 4)
    ),
  r as (
    select *
    from (
      select array[l] as l, array[l] as al, array[f] as f, array[f] as af, 1 as g, 1 as n
      from v order by l, f limit 1
      ) a
    union all
    select CASE WHEN g.newgrp THEN g.l ELSE r.l || g.l END, r.al || g.l,
           CASE WHEN g.newgrp THEN g.f ELSE r.f || g.f END, r.af || g.f,
           CASE WHEN g.newgrp THEN g + 1 ELSE g END, n+1
    from r
    join lateral (
      select CASE WHEN newgrp THEN ARRAY(SELECT l FROM v WHERE l<>ALL(r.al || g.l) AND f<>ALL(r.af || g.f) ORDER BY l, f LIMIT 1) ELSE l END AS l,
             CASE WHEN newgrp THEN ARRAY(SELECT f FROM v WHERE l<>ALL(r.al || g.l) AND f<>ALL(r.af || g.f) ORDER BY l, f LIMIT 1) ELSE f END AS f,
             newgrp
      from (
        select l, f, g.l = '{}' AND g.f = '{}' as newgrp
        from (
          select COALESCE(ARRAY(SELECT l FROM v WHERE f =ANY(r.f) AND l <>ALL(r.l)), '{}') as l,
                 COALESCE(ARRAY(SELECT f FROM v WHERE l =ANY(r.l) AND f <>ALL(r.f)), '{}') as f
          ) g
        ) g
      ) g on (true)
    where (not g.newgrp or (g.newgrp and g.l <> '{}'))
    ),
  g as (
    select distinct on (g) l, f, g
    from r
    order by g, n desc
    )
select v.l as Letter, v.f as Figure, g.g AS Groupment
from v
join g on (v.l =any(g.l) or v.f =any(g.f))
order by 3, 1, 2


Tablespace Creation and Reassignment It has been determined that user "SCOTT" should use a new temporary
  tablespace called TEMP2 size equivalent to TEMP tablespace (refer to
  Notes below for determining the size of TEMP).   Create a script that
  performs the following

  Create one Bigfile tablespace for TEMP2.  size equal to the size of
  TEMP Change the default temporary tablespace for "SCOTT" to TEMP2

This is what I have come up with so far, I have used a combination of the Oracle 11g DBA Handbook, also the Oracle website knowlege base at http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#ADMIN011
for example.

I cannot find how to determine the size of "TEMP". I have been able to locate the temp tablespace though. I just havent figured out the query to find out the size.

what I have so far is:

create bigfile tablespace TEMP2
    datafile '/u06/oradata/TEMP2.dbf' size ?g;

alter user SCOTT temporary tablespace TEMP2;

any help or insight would be appreciated, the script is for 11g and SQL Plus. Thanks, I am a newb.

To get the size of TEMP, in bytes (obviously divide by a few 1024s to get Gigabytes):

select sum(bytes)
from dba_temp_files
where tablespace_name = 'TEMP';

Doc link for the view here.


How to set Database Recover Mode to Full for all databases? Using a command similar to:

ALTER DATABASE test0402er2Y8 SET RECOVERY FULL
GO

How can I automatically execute this on all databases in server that are in simple mode now (using SQL Server 2012) ?

Thanks

How can I automatically execute this on all databases in server that are in simple mode now (using SQL Server 2012) ?

Below T-SQL will help you. It will check for ONLINE databases with SIMPLE  recovery model and will print TSQL to change it into FULL Recovery mode. Run below code in TEXT Mode -- SSMS CTRL+T. Once you are happy, run the output generated  from another session :

set nocount on
go

if exists (
        select 1
        from sys.databases
        where recovery_model_desc = 'SIMPLE'
            and state_desc = 'ONLINE'
        )
begin
    print '-- You are setting up database to FULL recovery mode. '
    print '-- Make sure you take first full backup and then schedule LOG BACKUPS for proper transaction log maintenance !'

    select 'ALTER DATABASE ' + QUOTENAME(name) + ' SET RECOVERY FULL with ROLLBACK IMMEDIATE;'
    from sys.databases
    where recovery_model_desc = 'SIMPLE' -- since you only want SIMPLE recovery model databases to get changed to FULL recovery.
            and state_desc = 'ONLINE'
end
else
    select 'ALL the databases are in FULL RECOVERY MODE - Make sure you take proper LOG BACKUPS !!'

NOTE : For any databases that are newly created, its better to change the recovery model of model database to FULL, so that the new databases that are created inherit the FULL recovery setting from model database.

Worth mentioning : Why Does the Transaction Log Keep Growing or Run Out of Space?, since you are going to switch to FULL recovery and if you dont take care of your transaction log, then you will end up with a bloated transaction log. Read the answer linked for more details.


Ways to speed up IN queries under PostgreSQL I have a query of the following form:

SELECT * FROM twitter_personas WHERE twitter_user_id IN ($1, $2, $3, ..., $25000)

The IN query has anywhere from 10 to 25000 values. The query runs for minutes at a time. I have a backlog of nearly 500,000 queries like this to run.

The twitter_user_id column is indexed. Any ideas on how I could speed this up?

# \d twitter_personas
                                    Table "public.twitter_personas"
      Column      |          Type          |                         Modifiers
------------------+------------------------+------------------------------------------------------------
 persona_id       | uuid                   | not null
 twitter_user_id  | bigint                 |
 screen_name      | character varying(40)  | not null
 avatar_url       | text                   |
 hashval          | integer                | not null default nextval('personas_hashval_seq'::regclass)
Indexes:
    "twitter_personas_pkey" PRIMARY KEY, btree (persona_id)
    "index_twitter_personas_on_screen_name" UNIQUE, btree (screen_name)
    "index_twitter_personas_on_screen_name_persona_id" btree (screen_name, persona_id)
    "index_twitter_personas_twitter_user_id" btree (twitter_user_id) WHERE twitter_user_id IS NOT NULL

IN() using many parameters will result in many cases in a sequential table scan. That might be slow, depending on table size and speed of your system.

Create a temporary table with all your variables and join on this table:

CREATE TEMP TABLE t AS
  SELECT * FROM (VALUES(1),(2),(3)) x(twitter_user_id);

SELECT
  twitter_personas.*
FROM twitter_personas
  JOIN t USING(twitter_user_id);

Use EXPLAIN to see the difference between the queryplans.


Extremely long postgres query I've run a migration query within a transaction, it went like this:

alter table big_and_loaded_table
    add column col1 bool;

update big_and_loaded_table set col1 = false;

It seems quite innocent, but it took about two minutes to execute. Moreover, it "locked" big_and_loaded_table: any query in my application that involved it -- both read and write -- took oh so long to execute, the same minute or so. I don't use transactions in my application at all, as well as any kind of explicit locks.

So I have two questions:

Why did that migration query take so long to execute? Was it because of a transaction? Or was it because of the query itself?
Why did it lock application queries that involved table big_and_loaded_table?
How should I run such kinds of migrations in the future? Probably this point won't make any sense when I'll figure out the first two though.

Postgres version is 11.6.

Postgres 11 added a feature where a table rewrite is not necessary if you add a column with a default value.

If you had used

alter table big_and_loaded_table
    add column col1 boolean default false;

the statement would have executed nearly instantaneous with the same effect.

But an ALTER TABLE statement will acquire an exclusive lock to prevent access to the table while it's being changed, there is no way around it.


Features and Patterns for Managing Ordered Lists I have a common scenario in app design, where I need to store an ordered list, and make the ordering easily accessible and adjustable via an application UI.

The ordering operations are simple and operate on only one item at a time - move to top, move to bottom, move up one position, or down one position.  These positioning operations may be interspersed in with typical add / edit / delete list operations.  For discussion, this is a single-user app and db store.

Because the objects can be highly complex, I'd like to avoid caching the entire list client-side, and tracking newly created objects in memory until a big save operation occurs.

I am using MS-SQL almost exclusively on these projects, and I'm wondering if there are db features, SQL features, or design patterns I should be using to better support the management of ordered lists.

My current approach is to use a DECIMAL column to store the list positions, which become fractional during the item move process.  Then I sort the list and re-generate integer positions to normalize list.  Following this operation, it's ready for another item move.

In an example implementation, I create a column named Seq for position tracking.  Once decimal place is enough for the .5 storage.

[Seq] [decimal](10, 1) NOT NULL

When an item is re-positioned in the app UI,

I UPDATE that record with new Seq value
I retrieve the full table, sorted ascending by Seq and regenerate all Seq values in the table from a 1-based integer series

The new Seq value is easily calculated-

Move to top = 0
Move to bottom = MAX(Seq) + 1
Move up = subtract 1.5 from the current Seq value
Move down = add 1.5 to the current Seq value

As lists get longer, updates get slower, since I'm doing N UPDATE statements, where N is the number of rows in the table, so it seems only suitable for small tables.

Is there some kind of in-built capability for managing an ordered list in SQL or MS-SQL?
Is there a means to update multiple rows in one call?

A far more performant approach would be to allow more decimal places, and then position at the median of the two adjacent items, with no re-sequencing on the list.

Move to top = Min(Seq) - 1
Move to bottom = MAX(Seq) + 1
Move up = median of the 2 previous item Seq values
Move down = median of the 2 following item Seq values

This approach only requires one SELECT (containing 2 rows max) and one UPDATE.

However with enough re-positions I imagine the field would run out of decimal places. For example, if I start with items at positions 10 and 11, and keep moving items down the list from position 9, they'd be given sequences of 10.5, then 10.25, then 10.125.  After 10 moves, the positions get lengthy... 10.0009765625.

UPDATE #1 - IMPROVED RE-SEQUENCING OF THE LIST

Regarding re-sequencing, I'm more caught up on row_number(), OVER, and WITH capabilities in MS-SQL.  It's nice to find that there's a much smoother way to re-sequence the list in a single UPDATE operation;

with OrderedItems as
(
    select
        ItemID,
        Seq,
        row_number() over (order by Seq asc) NewSeq
    from
        Items
)
update OrderedItems
set Seq = NewSeq

The last is a technique used quite often, and that works. If you're afraid of running out of decimals you can refine it to detect when the average between the next two previous/following values cannot be distinguished from one of them (i.e.: you run out of decimals).

When this happens, you should resequence as many rows up or down as needed.

Let's assume you work with just 2 decimals, and you have two rows with Seq 1.00 and 1.01; the next value being 2.00. If you want to move a row using your technique between these two, you would have Seq = (1.00, 1.005, 1.01, 2.00). This would need more decimals than are available and, depending on the rounding rules, 1.005 would either be rounded to 1.00 or 1.01. In any case, you can detect that the inserted value is not distinguishable from either 1.00 or 1.01.

In that case, you need to "push down (or up) and make room". That is, you should change one more row, and have (1.00, 1.25, 1.50, 2.00). You've spread the change to two rows instead of just one.

This might need to be done recursively (or iteratively) if you ever run into a situation such as (1.01, 1.02, 1.03, 1.04, ...).

With this extensions to your original technique, you don't need to renormalize. This is taken care of only in an "as needed" basis, and scales quite well.

NOTEs:

Implementing the algorithm might be a bit tricky, because you need to take into account when you're at the first or last rows, and how to handle those, and when your Seq numbers reach the maximum or minimum available values.
The algorithm works with any number of decimals, including 0. That is, you can use it as well with integer values instead of decimal, which might prove more computing efficient, or, depending on how decimals are implemented, be also more space efficient.


Filter column by records matching a timezone in Postgres I want to check whether any rows in my column (TIMESTAMP WITH TIME ZONE) are not stored using UTC. Is there a query I can write to match values that are stored with a TZ offset of 1 or 8 or 11 or (not 0)?

No, there is not. The type name timestamp with time zone can be misleading. The time zone is not stored at all, it just serves as input / output modifier and all values are stored as UTC internally.

More:

Ignoring timezones altogether in Rails and PostgreSQL
Time zone storage in PostgreSQL timestamps


How to find out the segment behind a corrupted block when the database cannot be opened? Short Version:

alter database open fails with ORA-00600.
alert.log and dbv both point out that one data block (type: 6) is corrupted.
I cannot select from dba_extents as that requires an opened database
I cannot use RMON with the backup validate check logical command as the database is in NOARCHIVELOG mode.
I cannot set the database to ARCHIVELOG mode as an instance recovery is required
I cannot recover the instance as that would require me to fully startup and normally shutdown the database.

=> Is there any way I can find out what exactly is corrupted without opening the database?

Detailed Version:

One of our Development databases (non-productive) recently doesn't start up any longer and provides the following errors when trying to connect with a regular user:

  ORA-01034: ORACLE not available
  ORA-27101: Shared memory realm does not exist

I can connect as sys as sysdba, and I can successfully perform the following commands:

shutdown immediate;
startup mount;

but when I try doing

alter database open;

ORA-00600 along with various internal error codes is shown.

Using the alert.log and the dbv command line tool, I found out a data block is corrupted:

  DBVERIFY - Verification starting : FILE = D:\ORADATA\ORCL\SYSTEM01.DBF
  Page 62507 is influx - most likely media corrupt
  Corrupt block relative dba: 0x0040f42b (file 1, block 62507)
  Fractured block found during dbv:
  Data in bad block:
   type: 6 format: 2 rdba: 0x0040f42b
   last change scn: 0x0000.0eb6f2bc seq: 0x1 flg: 0x06
   spare1: 0x0 spare2: 0x0 spare3: 0x0
   consistency value in tail: 0xfaae0601
   check value in block header: 0xf67f
   computed block checksum: 0x376f

So type: 6 apparently means it's a regular data block which usually is a table, an index or similar. In order to find out which database object was compromised, I've first tried using queries like the following:

select owner, segment_type, segment_name
from dba_extents
where file_id = 1 and 62507 between block_id and block_id+blocks-1;

This however fails with the same ORA-01034 as any other query. This results in a recursive problem, as I would need to open the database in order to find out why I can't open the database.

After googling for a while, I also found I could query the v$database_block_corruption view even if the database is not opened. I do however need to populate that view with data by running e.g. RMAN:

  RMAN> backup validate check logical database;

which unfortunately results in the new errors RMAN-03009 and ORA-19602 which basically mean that a backup (even a validation check) is not possible in NOARCHIVELOG mode.

So I tried setting archivelog mode by executing

alter database archivelog;

on the mounted database.

This however produced yet another error:

  ORA-00265: instance recovery required, cannot set ARCHIVELOG mode

After googling for some more time, I found out that "instance recovery" can be achieved by completely starting up the database and then shutting it down regularly using shutdown or shutdown immediate.

Which leads me back to my initial problem: I cannot fully start up (i.e. open) the database.

Is there any way I can find out which table (or index, ...) is broken, without having to open the database?

Well, I guess the database is expendable since there is no proper backup. So just drop and recreate the database.

OK, seriously there are not many options left. Before you do anything shutdown the database and copy all files which belong to the database somewhere else! Because everything you do can make it worse.

Once you did that you have 2 options:

Open an Oracle Service Request. With their help try to restore the block with lots of tricks and magic.
Call the Oracle Consulting Team, they have unpublished/internal tools which might be helpful.

This database is not in archivelog mode so there is no option to restore the block from a backup. If you still have an rman backup of datafile 1 you might be able to restore this file and then open the database with _allow_resetlogs_corruption (Don't use this option on your own!). Because you cannot fully recover the block, you have to tell Oracle that you don't care that the datafile do not have the same SCN. See details here: https://practicalappsdba.wordpress.com/2008/04/01/how-to-recover-and-open-the-database-if-the-archive-log-required-for-recovery-is-missing/

If this is a block which contains standard objects you might be able to extract the block from a healthy database and inject it on this block.

Good luck!


Converting a string to an object reference Sometimes I end up in a position where I have a string version of my object reference like so:

'server.database.schema.table'

And I need to use it as an actual object reference:

'server.database.schema.table'+'.'+[column]

How do I turn that string into:

[server].[database].[schema].[table]

so I can actually use it?

I'm sure this has been asked before, and it's probably newbish, but I just can't find the right keywords.  I'm not using it in an EXEC EXECUTE.

Per the comments, here's an example dataset:

CREATE TABLE #example
(
    Server NVARCHAR(255) NOT NULL
    ,[Database] NVARCHAR(255) NOT NULL
    ,[Schema] NVARCHAR(255) NOT NULL
    ,[Table] NVARCHAR(255) NOT NULL
    ,Field1 INT
    ,Field2 DATE
    ,Field3 VARCHAR(50)
)

INSERT INTO #example
(Server,[Database],[Schema],[Table],Field1,Field2,Field3)
VALUES ('LinkedServer','DB','dbo','TableA',15,CONVERT(DATE,'1-1-18'),'Text')

So, if I wanted to select the reference of the first row, I'd imagine I'd do something like:

DECLARE @SERVER ??, @DB ??, @SCHEMA ??, @TABLE ??

SELECT TOP 1 @SERVER = Server FROM #example
SELECT TOP 1 @DB = [Database] FROM #example
SELECT TOP 1 @SCHEMA = [Schema] FROM #example
SELECT TOP 1 @TABLE = [Table] FROM #example

SELECT * FROM
    @SERVER.@DB.@SCHEMA.@TABLE
WHERE
    Field1 = (SELECT TOP 1 Field1 FROM #example)
    AND Field2 = (SELECT TOP 1 Field2 FROM #example)
    AND Field3 = (SELECT TOP 1 Field3 FROM #example)

What you're looking to do is called dynamic SQL.

Using your example, I think you want to do something like this:

DECLARE @SERVER sysname,
        @DB sysname, @SCHEMA sysname,
        @TABLE sysname,
        @FIELD1 INT,
        @FIELD2 DATE,
        @FIELD3 VARCHAR(50);

SELECT TOP 1 @SERVER = Server,
             @DB = [Database],
             @SCHEMA = [Schema],
             @TABLE = [Table],
             @FIELD1 = Field1,
             @FIELD2 = Field2,
             @FIELD3 = Field3
FROM #example
--ORDER BY Server
--You need to order by a unique column or set of columns to make this determinstic

DECLARE @StringToExecute NVARCHAR(MAX) = N''

SET @StringToExecute += N'
SELECT *
FROM '
    + QUOTENAME(@SERVER)
    + N'.'
    + QUOTENAME(@DB)
    + N'.'
    + QUOTENAME(@SCHEMA)
    + N'.'
    + QUOTENAME(@TABLE)
    + N'
WHERE
    Field1 = @sp_FIELD1
    AND Field2 = @sp_FIELD2
    AND Field3 = @sp_FIELD3
';

PRINT @StringToExecute;

EXEC sys.sp_executesql @StringToExecute,
                       N'@sp_FIELD1 INT, @sp_FIELD2 DATE, @sp_FIELD3 VARCHAR(50)',
                       @sp_FIELD1 = @FIELD1, @sp_FIELD2 = @FIELD2, @sp_FIELD3 = @FIELD3;

Take note of a couple things:

I'm using QUOTENAME to escape objects that I can't parameterize -- the server, database, schema, and table names.
I'm using sp_executesql to parameterize what I can, to prevent SQL injection.

I'll link to it again, because it's a really important thing to read: The Curse and Blessings of Dynamic SQL


Difference between FreeProcCache and FreeSystemCache Currently I run DBCC FREEPROCCACHE when I want to wipe information out of the buffer pool between running SQL queries. However, I was reviewing this Technet article referencing DBCC FREESYSTEMCACHE.  What caches does FREESYSTEMCACHE wipe that FREEPROCCACHE doesn't?

What caches does FREESYSTEMCACHE wipe that FREEPROCCACHE doesn't?

There are many system caches available in SQL Server. I am referring to SQL 2008R2 (as I tested on it).

Below query will return all the caches available :

--- ONLY for Educational purpose. Don't attempt to run on PROD !!

select 'DBCC freesystemcache ('+''''+name+''''+')'  from   sys.dm_os_memory_clerks group by name
-- 155 rows

so for e.g. you want to clear all caches you will run

DBCC FREESYSTEMCACHE ('ALL')

for specific caches to be cleared you can run

DBCC FREESYSTEMCACHE ('userdatabase') -- cleans cache for specific user database
DBCC FREESYSTEMCACHE ('tempdb')  -- clears cache for tempdb
DBCC FREESYSTEMCACHE ('Temporary Tables & Table Variables') -- clears all the temp table and variables
.. and many more (see above 155 rows returned from sql server 2008R2)

Using FREEPROCCACHE, you wont be able to clear all above mentioned at that detailed level.

FREEPROCCACHE ==> from BOL

  Removes all elements from the plan cache, removes a specific plan from the plan cache by specifying a plan handle or SQL handle, or removes all cache entries associated with a specified resource pool.

So essentially, you might think of it as only related to Plan cache with the ability to remove specific plans from the cache with the plan handle and or pool name.

Fun With DBCC FREEPROCCACHE is a blog from Glenn Berry


duplicate rows based on a column value Rather than merging rows I need to 'expand' rows, here's an example to explain:

|Material|count|
|Rock    | 3   |
|Gold    | 2   |
|Silver  | 1   |
|Wood    | 2   |

I need one row per material e.g. translated to this

|Material|count|
|Rock    | 1   |
|Rock    | 1   |
|Rock    | 1   |
|Gold    | 1   |
|Gold    | 1   |
|Silver  | 1   |
|Wood    | 1   |
|Wood    | 1   |

Is there an sql solution for this type of problem?

You need a sufficiently large table to multiply the source table. In your example it is sufficient to do a self join:

select material, 1
from (
    select x.material, x.count
         , row_number() over (partition by x.material) rn
    from t as x
    cross join t as y
) as u
where rn <= count
order by material;

Gold               1
Gold               1
Rock               1
Rock               1
Rock               1
Silver             1
Wood               1
Wood               1

PostgreSQL also have a nifty table function generate_series that can be used:

select material, 1 from (
    select t.material, t.count
         , row_number() over (partition by material) as rn
    from t
    cross join generate_series(1,10)
) as u
where rn <= count
order by material;

instead of 10 you can determine:

select max(count) from t

and use that to avoid creating an unnecessary large table.

While educating myself I realized that generate_series can take a variable as argument:

select material, 1 from (
    select t.material, t.count
         , row_number() over (partition by material) as rn
    from t
    cross join ( SELECT  generate_series(1, cnt)
                 FROM ( SELECT MAX(COUNT) FROM t ) u (cnt)
               ) as v
) as u
where rn <= count
order by material;

Using LATERAL means we can correlate cnt with count in the outmost table, and thereby remove the need for row_number():

select material, 1
from (
    select t.material, t.count
    from t
    cross join LATERAL ( SELECT  generate_series(1, cnt)
                         FROM (
                             SELECT COUNT
                             FROM t as t1
                             WHERE t.material = t1.material
                         ) u (cnt)
                       ) as v
) as u
order by material;

If you made it this far, go read @ypercubeᵀᴹ answer. It does'nt get much simpler or better than that. Please vote for that one.


Postgres: extracting multiple bit-columns from single 'flags' column after join with 2nd table I have a query similar to this below. As you can see, there are multiple parts which are very similar.

SELECT
    id,
    aaaa,
    bbbb,
    tags
    ,(
        SELECT COUNT(*) > 0
        FROM A
        LEFT JOIN B
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags & 1 <> 0
    ) as "has_children_tag_1"
    ,(
        SELECT COUNT(*) > 0
        FROM A
        LEFT JOIN B
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags & 2 <> 0
    ) as "has_children_tag_2"
    ,(
        SELECT COUNT(*) > 0
        FROM A
        LEFT JOIN B
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags & 4 <> 0
    ) as "has_children_tag_3"
    ,(
        SELECT COUNT(*) > 0
        FROM A
        LEFT JOIN B
            ON A.id = B.parent_id
        WHERE A.buzz=C.buzz
            AND B.tags & 8 <> 0
    ) as "has_children_tag_4"
FROM A
LEFT JOIN Z
    ON A.id = Z.id

I know one thing, that I cannot split flag column into multiple bit columns, so I have to figure out how to query my existing tables in efficient way.

I am wondering is it possible (I don't know how to do that) to create some kind of temporary table for part:

    SELECT COUNT(*) > 0
    FROM A
    LEFT JOIN B
        ON A.id = B.parent_id
    WHERE A.buzz=C.buzz

to have a possibility to run on this tmp table WHERE B.tags & 1 <> 0, WHERE B.tags & 2 <> 0, etc.

Any help/suggestion will be appreciated.

You could merge the subqueries using this model:

SELECT bool_or(B.tags&1<>0) as "has_children_tag_1",
       bool_or(B.tags&2<>0) as "has_children_tag_2",
       bool_or(B.tags&4<>0) as "has_children_tag_3",
       bool_or(B.tags&8<>0) as "has_children_tag_4"
FROM A LEFT JOIN B ON A.id = B.parent_id
WHERE [conditions]

bool_or is an aggregate function that evaluates to true if at least one value is true, so that should lead to the same results as the multiple count(*)>0 in the question, except as one single subquery.

To push these columns into an otherwise unrelated query, you may add the above subquery as a CTE in a WITH clause, and just put it in the FROM list. For instance, modifying your original query:

WITH flags AS
(SELECT bool_or(B.tags&1<>0) as "has_children_tag_1",
       bool_or(B.tags&2<>0) as "has_children_tag_2",
       bool_or(B.tags&4<>0) as "has_children_tag_3",
       bool_or(B.tags&8<>0) as "has_children_tag_4"
FROM A LEFT JOIN B ON A.id = B.parent_id
WHERE [conditions] )
SELECT
  id,
  aaaa,
  bbbb,
  tags,
  has_children_tag_1,
  has_children_tag_2,
  has_children_tag_3,
  has_children_tag_4
FROM flags,A
LEFT JOIN Z
    ON A.id = Z.id


telnet to mysql, which timeout is used? When I telnet to a MySQL DB, the connection gets dropped quickly. Should I increase the interactive_timeout or the wait_timeout?

(Background: trying to keep SSH port forwarding alive on 3306)

I suggest not using telnet to connect to database servers.

Recommend using ServerAliveInterval (man 8 ssh_config) to keep your connection alive rather than a mysql mechanism.


SQL Server 2008 R2 : multiple files and filegroup I am getting bit confused regarding advantages of multiple files and filegroups in SQL Server

Using SQL Server 2008 R2, I have following filegroups and files configuration

Drive D: Primary Filegroup   with two files File1.mdf and file2.ndf
Drive E: Secondary Filegroup  with two files File3.ndf and File4.ndf

Both drive uses its own disk underneath a SAN (dedicated IO Path).

Questions:

If I create a table B on secondary file group, does this mean when inserting data proportional fill algorithm is used and data will be equally filled in both the files on secondary file group? answer in Yes or No
If there is a tableA on primary filegroup, if I perform SELECT query with a join between tableA and tableB (secondary filegroup) two threads will be spawn for the query as both tables resides on a separate volume?
Will restore database be any faster as it would spawn two threads for performing restore? I do have instant file initialization ON so to me no gain splitting file groups on separate drives.
What other benefits of having files in a filegroup on separate drives.

1: yes.

2: depends;) The query execution is still driven by the optimizer. It wont do paralellization for small result sets.

3: Ah - what would that be good given that you are still reading one backup file ;)?

4: Not running the files on one underlying SAN and thus improoving your IO budget?

The most brutal thing I have ever seen like that had nearly 30 files all on separate SAN volumes (count of hard discs going close to 200) and it was done as every LUN had a queue limit in the driver of 255 outstanding requests, which the SAN (with a 32 gigabyte cache) was just not caring about ;) THat thing was pulling in nearly 1.5 gigabyte per second over multiple fiber connections.


Does sys.dm_os_waiting_tasks include RUNNABLE threads? From MSDN:

  Returns information about the wait queue of tasks that are waiting on some resource.

Is CPU considered a resource in this definition? I initially thought it's not, but what made me concerned is the definition of wait_duration_ms from the same page:

  wait_duration_ms Total wait time for this wait type, in milliseconds. This time is inclusive of signal_wait_time.

If it does include RUNNABLE tasks, then what would be the value of wait_type for them? Is it some special value indicating that it's not blocked, but rather waiting for the scheduler, or is it the same value it had when it was in SUSPENDED state?

A task that yields the scheduler because it exhausts its quantum will not appear in sys.dm_os_waiting_tasks; however, any signal wait time it experiences will be accumulated in sys.dm_os_wait_stats under SOS_SCHEDULER_YIELD.

A task that yields to acquire a currently-unavailable "waitable resource" does appear in sys.dm_os_waiting_tasks with the appropriate wait_type and is suspended. It accumulates resource wait time until signalled (when the resource becomes available) and runnable. The task then accumulates signal wait time until it acquires the scheduler and transitions to running status. The task remains visible in sys.dm_os_waiting_tasks until the wait is fully completed (including signal wait), and the wait_type never changes.

That is my understanding, and observation. I could not find this level of detail officially or semi-officially documented anywhere.

A note of caution: sys.dm_os_waiting_tasks is a non-transactional view over rapidly-changing internal SOS task data structures. Many DMVs work in this way, and have been written by different developers at different times. Their output can be very useful, but joining several DMVs can easily lead to apparently inconsistent results.

Comparing snapshots of the sys.dm_os_waits DMV is a better way of splitting aggregate resource and signal waits for a particular wait type. Extended events e.g. scheduling.wait_completed can be used to capture specific wait events if that level of detail is required.

  ...do you agree the wording of BOL is not correct/confusing?

Technical writing is hard. There'll always be some who complain there is too much detail, while others say there is too little. The DMV shows waits on some resources, until the wait is over. I guess it could be clearer in some respects, but how far can you go without detailing the internal implementation, and ultimately providing the source code?


EXECUTE AS USER = 'dbo' - The user does not have permission to perform this action I created a trigger that now works fine as result, but there is 1 line where I run a query:

DECLARE @source as nvarchar(30) = (SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID);

The user that will trigger this, has no permission to execute something like

EXECUTE AS USER = 'dbo' ;

I receive the error:

The user does not have permission to perform this action.

How can I achieve this?

The whole trigger code can be seen in this question:
UPDATE Trigger with condition

No, using EXECUTE AS 'dbo' in the CREATE TRIGGER statement and setting the Database to TRUSTWORTHY ON is not an option. I mean, it would technically work, but it's a completely unnecessary security risk (which is the case 99.9% of the time when wondering if you should enable TRUSTWORTHY).

Yes, you could use a Certificate to sign the Trigger, then copy the Certificate to [master] and create a Login from it and then grant that Certificate-based Login the VIEW SERVER STATE Server-level permission. That would also technically work. However, while I am a huge fan of Module Signing, I prefer simplicity when / where available (as long as it is not a security risk or unmaintainable). Fortunately for this situation (i.e. getting connection info for the current Session / @@SPID) there is a built-in function that has what you need: CONNECTIONPROPERTY:

SELECT client_net_address FROM sys.dm_exec_connections WHERE session_id = @@SPID;
/*
Msg 297, Level 16, State 1, Line XXXXX
The user does not have permission to perform this action.
*/

SELECT CONNECTIONPROPERTY('client_net_address');
-- <local machine>

So, in your Trigger, replace your current DECLARE @source line with the following:

DECLARE @NetAddress VARCHAR(48) =
                     CONVERT(VARCHAR(48), CONNECTIONPROPERTY('client_net_address'));

Please note the change in datatype from what you originally had. The documentation states that the base type is VARCHAR(48), so it's best to go with that.


Why does Oracle use a different byte length than java for the supplementary unicode character chipmunk? I have java code trimming a UTF-8 string to the size of my Oracle (11.2.0.4.0) column which ends up throwing an error because java and Oracle see the string as different byte lengths. I've verified my NLS_CHARACTERSET parameter in Oracle is 'UTF8'.

I wrote a test which illustrates my issue below using the unicode chipmunk emoji ()

public void test() throws UnsupportedEncodingException, SQLException {
    String squirrel = "\uD83D\uDC3F\uFE0F";
    int squirrelByteLength = squirrel.getBytes("UTF-8").length; //this is 7
    Connection connection = dataSource.getConnection();

    connection.prepareStatement("drop table temp").execute();

    connection.prepareStatement("create table temp (foo varchar2(" + String.valueOf(squirrelByteLength) + "))").execute();

    PreparedStatement statement = connection.prepareStatement("insert into temp (foo) values (?)");
    statement.setString(1, squirrel);
    statement.executeUpdate();
}

This fails on the last line of the test with the following message:

  ORA-12899: value too large for column
  "MYSCHEMA"."TEMP"."FOO" (actual: 9, maximum: 7)

The setting of NLS_LENGTH_SEMANTICS is BYTE. Unfortunately, I cannot change this as it is a legacy system. I'm not interested in increasing the column size, just reliably being able to predict the Oracle size of a string.

What follows is my speculation.

Java Strings are internally represented using the UTF-16 encoding. When you getBytes("UTF-8") Java converts between the two encodings, and you probably use an up-to-date Java platform.

When you attempt to store a Java String in the database, Oracle also performs conversion between the Java native UTF-16 and the database character set as determined by NLS_CHARACTERSET.

The chipmunk character was approved as part of the Unicode standard in 2014 (according to the page you linked), while the latest release of Oracle 11g rel.2 was published in 2013.

One might assume that Oracle uses a different or outdated character conversion algorithm so the byte representation of ) on the server (9 bytes long) is different than what getBytes() returns on the client (7 bytes).

I guess to resolve this issue you could upgrade your Oracle server or use UTF-16 as the database character set.


How to trigger an action without using triggers I just want to ask. We have a calendar table, let's call it CAL1 in one database, lets call it DB1. CAL1 is manually populated. Now in another database, DB2, there is another calendar table CAL2. What I need to do is to populate CAL2 based on the values from CAL1. I don't know when will CAL1 be populated. There is also some other calendar tables/tables in other databases that will refer to CAL1 so I need to make an API that will populate or at least execute the procedures from the other databases e.g. DB2 whenever new data is loaded to CAL1. I initially think of using triggers but read that it's better to not use it. So is there any other way that I can do this without using trigger? Thanks a lot.

it depends on how up to date you want CAL2 to be.

If it immediately should reflect the values in CAL1 then a trigger is the way to go.

If it can be a few mnutes out of sync then you could have a job that runs every 5 mins and calls a stored procedure that syncs the tables.


mysql says "too many open files" when importing large *.sql file I have a large database dump I want to import with MariaDB / MySQL.

mysql -u DBUSER DBNAME < DUMP.sql

The process goes on for a while, and then I get this:

ERROR 23 (HY000) at line 11304: Out of resources when opening file './DBNAME/' (Errcode: 24 "Too many open files")

When this happened to me before, I simply split the file in half and imported both halves separately. But this seems like a poor workaround.

System: Linux Mint 17.3 Rosa

You will need to increase the openfiles in ulimit.

More detailed info here: https://duntuk.com/how-raise-ulimit-open-files-and-mysql-openfileslimit


Why cp1256 collation is not 1 byte in MySQL? I've read an article that cp1256 collation takes 1 byte for Arabic characters, but when I created an index on the column that was cp1256 with type of varchar(900), only 767 characters picked up for index purposes:

  CREATE TABLE col (
   id int(11) NOT NULL AUTO_INCREMENT,
   arabic varchar(900) CHARACTER SET cp1256 NOT NULL,
   PRIMARY KEY (id),
   KEY (arabic(767)
  ) ENGINE=InnoDB;

Arabic varchar(900) truncated to (767) for my index.
Why is that the case?

That's a limit of the InnoDB engine. It cannot index more than 767 bytes (for a single column)

See the manual:

http://dev.mysql.com/doc/refman/5.5/en/innodb-restrictions.html

  By default, an index key for a single-column index can be up to 767 bytes
  ...
  When you attempt to specify an index prefix length longer than allowed, the length is silently reduced to the maximum length


Is there any risk in SELECT ... FOR UPDATE outside transaction scope in MySQL? In a project where I work the sql for selecting objects does always select for update, whether the context is a transaction or not.

My question is if it has any risk, or performance issue, or chance causing deadlock or something else naughty ?

I know that select for update can lock a row only inside a transaction and that is the common use case.

I provide below two concrete use cases, and after the select for update used regardless of inside of transaction or not.

Use case 1 - Just select a book and return it

book = booksRepository->getBook(7);
return book;

Use case 2 - Transaction, Select the book, check that is valid and if so update

bookRepository->startTransaction();
book = booksRepository->getBook(7);
if (//some condition with the book properties) {
   bookRepository->updateBookAuthor(7, 'bbb');
}
bookRepository->commitTransaction();

In both of the above examples, the function getBook inside the repository does select for update

SELECT * FROM BOOKS
WHERE id = :id
FOR UPDATE;

I asked the other devs and the answer to why they do this was "Because otherwise we would have to create two functions in the repository, one getBook and one getBookForUpdate, so we just select always for update"...

That answer does not provide any insight or explanation for where I can learn, so I am asking here.

A query is always in a transaction even if you don't start it explicitly.
SELECT FOR UPDATE acquires an exclusive lock on rows it matches. There is a possibility that other transactions want to modify same records and will have to wait until the SELECT FOR UPDATE finishes and release the lock.

So. Nothing bad will happen if you run SELECT FOR UPDATE, but overall performance may be lower due to possible lock waits.

Deadlocks are possible, too. If other transaction holds a shared lock and than wants to update the records while SELECT FOR UPDATE is waiting.


When is it appropriate to use SQL Server Developer Edition? Do you use SQL Server Developer Edition on server-class machines in DEV and STAGING environments?

I am working on a large project where (if it passes the proof of concept stage) we will have several large geographically distributed enterprise class database servers running SQL Server 2012 Enterprise Edition.

We will have a production environment will initially have 3 servers, our Staging environment will have a minimum of 3 servers, and our Development environment will have 1 server (hosting three instances).

I was under the impression that we would only need to acquire Enterprise licences for the actual production servers, and we could get by with developer edition in our developer and staging environments because they are not "production".

Other sources have told me that we need to have an enterprise licence on all of the above mentioned machines, and that the developer edition is only meant for a single developer on their workstation.

Since developer edition has all the sexy features of Enterprise, I can't really see the value of it on a workstation class machine, especially for developing and testing the type of high availability system we are building.

If we have to fork out Enterprise licences for a dev server, that will just about kill our proof of concept stage, thus killing the project.  Forcing an enterprise licence on a staging environment will make management just want to skip staging altogether.

Development edition licenses can be used to install anything that isn't production, provided that everyone that connects to the server has their own development license.  MSDN licenses can be used as well if everyone has an MSDN license.


Oracle TNS names configuration I've edited added new TNS name to tnsnames.ora.
Toad for Oracle started to work at once. SQL Plus doesn't work even after logoff/logon, the same problem with my build scripts.

Getting the error: ORA-12154: TNS:could not resolve the connect identifier specified

How could I fix it?

There are various methods  of achieving this ,one of them as suggested by Yasir.
Please follow the below steps:

Close toad/sqlplus
Create a folder TNS_ADMIN
Add tnsnames.ora in TNS_ADMIN
Add TNS_ADMIN in enviornment variable.
Open Toad/Sqlplus and login

I think this will be very clear ,cheer


SQL Server data folder access rights I did a fresh SQL Server 2014 install. Next, I wanted to attach some databases from a previous SQL Server 2012 server. I copied all mdf and ldf files to the data directory, but when I tried to attach them, I got this error message:

  CREATE FILE encountered operating system error 5 (Access is denied)
  while attempting to open or create the physical file
  'D:\SQLSERVERDATA\MSSQL12.BIDEV\MSSQL\DATA\controlling_demo.mdf'.
  (Microsoft SQL Server, Error: 5123)

When I checked the file security settings (right click on file, properties, security), it said that I don't have read permission. The account I used to copy the files is in the administrators group. I have full control over the DATA folder and yet, just the files that I copied there are restricted.

When I try to re-apply the security rights on the whole folder, it says:

  Failed to enumerate the objects in the container. Access is denied.

When I copy the files to somewhere else, I have access rights.

Question is, how can I get access rights for these files in the DATA folder?

Have you looked under Advanced Security Settings?

The arrows point to the modifications I've often had to specifically apply when installing SQL Server on Windows 2012 R2.

For the SQL Server Root Directory, I'll give full control to the SQL Server Process account.

Related official documentation:

Configure File System Permissions for Database Engine Access
Securing Data and Log Files


Splitting a large table My scenario is I look after an old system with a VB6 based front end and a SQL back end (good news, the replacement is being developed but in the meantime I need to keep this up). Now the application is used at a dozen or so sites most of which are on SQL2000, one is on SQL2008 and one on SQL2008R2 (all Standard). Changing versions/editions would be a big ask.

The one hosted on SQL2008R2, as it happens, is also the largest site. We now have a table with over two billion rows and 700GB of data. It's a small number of columns, one being a 256 char varchar used to store serialised blob data. This is now of a size that when people try to insert, delete and update to it (via the app) users occasionally lock each other out. This can only get worse as time goes on. Also however, the next largest is on SQL2000..

I need to get the size of this table down. What are some methods or techniques I could use to achieve this? Ideally the technique would be compliant with SQL2000 Standard edition so I could use it at all sites. However just fixing the biggest offender on SQL2008R2 Standard would be something.

The table does have an identity column and as the table definition is controlled by the app this can not be changed (I mention this as I believe this rules out a partitioned view- the rows are INSERTed, UPDATEd and DELETEd by the app).

My current thought is to delete all data past date X months in the past (50% of total) and hive it off to a table not exposed to the user or the app. Then in the app I can put a method for a user to request items of data to be copied back across and this would be done via a scheduled job (overnight possibly). This feels clunky to me however.

We now have a table with over two billion rows and 700GB of data. It's
  a small number of columns, one being a 256 char varchar used to store
  serialised blob data. This is now of a size that when people try to
  insert, delete and update to it (via the app) users occasionally lock
  each other out. This can only get worse as time goes on.

Why do users lock each other out?  Is it that they are all accessing the same rows?  Perhaps the real solution to improve performance is query and index tuning so that only the needed data are touched regardless of table size.

Archiving read-only historical data will improve performance of full scans, improve manageability of current data, and perhaps reduce I/O due to a the smaller working set of active data.  You could simply move old data to an archive table in the same database during a scheduled daily process.  Rather than have users invoke a separate process to copy the data back, consider using a UNION ALL view of current and archived data when users need to report on both current and read-only historical data.


A trigger making a timestamp of when another column was changed I've beeen trying to create a trigger, that'll make a timestamp, of when our account was "deleted" which we do by setting deleted = 1. Below code creates a trigger for that purporse, however it has compilation errors and doesn't work.

So i wanna update the column DELETEDTIME with the the current sysdate timestamp of when deleted was set equal to 1, however i can't see where i've gone wrong in the code below

CREATE OR REPLACE TRIGGER trg_deletiontime
AFTER UPDATE
OF DELETED ON VTIGER_CRMENTITY
FOR EACH ROW
WHEN (old.DELETED = 0 AND old.DELETEDTIME = NULL)
BEGIN
new.DELETEDTIME := sysdate()
END;

Error from sys.user_errors says "PLS-00103: Encountered the symbol "END", however no amount of ; or / seems to fix this

Semicolon is missing after sysdate().
new should be :new.
You can not assign values to :new in an AFTER UPDATE trigger, becasue it fires after the update, the value is already updated. So I changed it to BEFORE UPDATE.

create table vtiger_crmentity (deleted number, deletedtime date);

CREATE OR REPLACE TRIGGER trg_deletiontime
BEFORE UPDATE
OF DELETED ON VTIGER_CRMENTITY
FOR EACH ROW
WHEN (old.DELETED = 0 AND old.DELETEDTIME is NULL)
BEGIN
:new.DELETEDTIME := sysdate();
END;
/


Determine theoretical max connections for MariaDB I want to upgrade the ram our MariaDB server (10.1.25-MariaDB-1~xenial) to handle more connections.
I have been determining the max connections mostly based on this article which suggests to use this formula
(Available_RAM - Global_buffers) / Thread_buffers
Global_buffers it the sum of these variables.
key_buffer_size
innodb_buffer_pool_size
innodb_log_buffer_size
innodb_additional_mem_pool
net_buffer_length

Thread_buffers is the sum of these variables.
sort_buffer_size
myisam_sort_buffer_size
read_buffer_size
join_buffer_size
read_rnd_buffer_size

This appears to work and for me it gives approx. 15 connections as my theoretical max.
When I query SHOW STATUS LIKE 'max_used_connections', which to my understanding gives me the historical maximum connections requested, I get 46 connections.
For background when I run SHOW VARIABLES LIKE 'max_connections' I get 151 which I believe is the default setting value.
Is 15 connections actually my theoretical max and did the additional 31 connections failed but MySQL registered them?
Is my math wrong or more likely am I missing some variables?

Every formula I have found is wrong in some respect.  Almost every computer I have encountered has "overcommitted" RAM; that is, the formula would say that too much RAM could be used.
Their list is missing two important settings:  max_heap_table_size and tmp_table_size.  The min of those two is what is used for internal temp tables in complex SELECTs.  And there could be multiple uses in a single query!  I recommend limiting each to 1% of RAM.
Overall...

Leave most VARIABLES at the defaults.
The 1% mentioned above
use InnoDB, not MyISAM.
Set key_buffer_size to just 20M.
Set innodb_buffer_pool_size to about 70% of available RAM if you have more than 4GB of RAM.  (A lower percentage for tiny machines.)  This is the first thing to reach for if swapping occurs.
When you find problems (swapping, high cpu, high I/O, etc), ask for help.  It will usually be solved by better INDEXing and/or reformulating queries, not by tuning.

Ignore the formulas like what your question is posing.
As for "too many connections", there are two main causes and tuning MySQL/MariaDB is not the cure.

The queries are running so slowly that they need help -- tuning queries is a solution here.
The clients are flooding the database.  Some web servers think that they can efficiently run hundreds or thousands of threads.  But, in reality, they stumble over themselves and cause the database to also stumble over itself.  Throttle them.

On this last bullet item, think of a grocery store where so many shoppers are in there that they can't even push their carts to the next aisle, much less ever get finished.
Put another way, if you let too many connections in, throughput stalls (hits some limit) and latency suffers badly.
Things to watch for:

Swapping means things are tuned too high -- discussed above
100% CPU or I/O -- Fine and fix the queries.  For help in finding them:  http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog
Poor latency (queries taking "too long") -- See the other two items.


Restore stuck in ASYNC_IO_COMPLETION Very frequently when I restore databases the restore operation gets stuck and the wait type is ASYNC_IO_COMPLETION.
It can be in such state for 10-20-30 minutes untill it proceeds to PREEMPTIVE_OS_WRITEFILEGATHER and this is when progress (percent_complete) starts to grow.

However, if I see ASYNC_IO_COMPLETION and interrupt the process of restore and then run it again, I immediately see PREEMPTIVE_OS_WRITEFILEGATHER and percent_complete goes up.

It would be good to understand this behaviour and learn if there is a more proper way to speed up the restore operation.
Thank you

Try to enable "Instant File Initialization" feature if it is not enabled already. It speeds up the restore process and makes it significantly faster.

What is Instant File Initialization(IFI)?

SQL Server needs to initialize the pages before using them. Thus, it tries to fill the pages with zeros(zeroing the files). If IFI is enabled, it skips this part and data files can be initialized instantaneously to avoid zeroing operations.

How to enable IFI?

Unfortunately, there is no in-built checkbox or trace flag to enable this feature. Instead, SQL Server service account needs to have "Perform Volume Maintenance Tasks" permission. Check out below page for detailed steps on how to do that.

MS Documentation: Instant File Initialization

When it will be enabled?

Be aware of that you need to restart SQL Server service so that SQL service will pick this policy up and enable this cool feature.

Bonus: You can check SQL Error Log to see whether IFI is enabled or not. Use "
"Filter" to search for keywords such as "Instant", "Initialization" or You may use below query taken from this blog post.

SELECT  @@SERVERNAME AS [Server Name] ,
    RIGHT(@@version, LEN(@@version) - 3 - CHARINDEX(' ON ', @@VERSION)) AS [OS Info] ,
    LEFT(@@VERSION, CHARINDEX('-', @@VERSION) - 2) + ' '
    + CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(300)) AS [SQL Server Version] ,
    service_account ,
    instant_file_initialization_enabled FROM sys.dm_server_services WHERE servicename LIKE 'SQL Server (%'


Linked Servers pointing to same server (localhost) causing "Transaction context in use by another session" error We are using SQL Server 2008. Normally our app that is using different databases distributed over the network and some of the stored procedures we call therefore make use of linked servers.

Now I wanted to test our app locally and thus created all databases (that are normally distributed over the network) on my local machine. So all the databases are now living on "localhost". I changed the linked servers so that they are all pointing to localhost (and a quick select-test shows they are working).

However, when I am calling stored procedures that make use of the linked servers (that now point to localhost) and these procedures are using transactions, then I am getting the following error: "Transaction context in use by another session".

I am guessing this is because SQL Server thinks it's a distributed transaction, but it's really a local transaction?!

What are my chances of resolving this other than eliminating the connection servers on my local install and directly address the different database as they are now living on the same server?

If it's important: I didn't configure MS DTC, I just started the corresponding windows service on my machine. (Mostly because I wasn't sure where to solve this problem.)

Loopback linked servers cannot be used in a distributed transaction . Install two SQL Servers in local to solve your problem. Link one instance to another.

http://msdn.microsoft.com/en-us/library/ms188716(SQL.105).aspx


SQL SERVER – Find Most Expensive Queries I am running the following script to find most expensive queries.

However I want to narrow it down to only queries ran on Saturday 18th October 2014
Is there any way to do this?

SELECT TOP 50 SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
qs.execution_count,
qs.total_logical_reads, qs.last_logical_reads,
qs.total_logical_writes, qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time/1000000 total_elapsed_time_in_S,
qs.last_elapsed_time/1000000 last_elapsed_time_in_S,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC -- logical reads
-- ORDER BY qs.total_logical_writes DESC -- logical writes
-- ORDER BY qs.total_worker_time DESC -- CPU time

ps I got this script from http://blog.sqlauthority.com/2010/05/14/sql-server-find-most-expensive-queries-using-dmv/

The server is SQL Express 2012

No, the DMVs track aggregates, not individual executions, so you can't pinpoint things like that.

Want to drill in, you'll need to capture queries using server-side trace*, extended events, management data warehouse or one of many 3rd party monitoring tools. The latter cost money but, IMHO, you get what you pay for. We have one I could recommend...

* Note: trace and profiler are deprecated, so it is highly recommended to use extended events going forward.


How to rename columns names in dynamically pivot table from other table? I have two tables pointscategory and points:

    CREATE TABLE  pointcategory (
     categoryID int(20) NOT NULL AUTO_INCREMENT,
     categoryname varchar(50) NOT NULL,
      PRIMARY KEY (categoryID)
    )
    CREATE TABLE points (
    pointID int(11) NOT NULL  AUTO_INCREMENT ,
    value int(50) NOT NULL,
    categoryID int(10) NOT NULL,
    studentId int(10) NOT NULL,
    PRIMARY KEY (pointID),
    FOREIGN  KEY (categoryID) REFERENCES pointcategory (categoryID)    )

I have created pivot table from points table by this procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_pivot`()
    SQL SECURITY INVOKER
BEGIN
   SET @sql = NULL;
SELECT
  GROUP_CONCAT(DISTINCT
    CONCAT(
      'count(case when categoryID = ''',
      categoryID,
      ''' then value end) AS `',
      categoryID, '`'
    )
  ) INTO @sql
FROM  points;
SET @sql = CONCAT('SELECT date(date), ', @sql, '
                  FROM points
                  GROUP BY date');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END

The output is:

Now I want to rename the columns names (categoryId) in the above table by categoryname from pointscategory table.

  from date,1..6 to date,categoryname1 ... etc

You should replace CategoryID by CategoryName as column alias:

select group_concat(
           distinct
               concat('count(case when categoryid = ''',
                      p.categoryid,
                      ''' then value end) AS ',
                      pt.categoryname
                     )
                   ) as cases
from   points p
join   pointcategory pt
on     pt.categoryid = p.categoryid;

count(case when categoryid = '1' then value end) AS cat1,
count(case when categoryid = '2' then value end) AS cat2,
count(case when categoryid = '3' then value end) AS cat3

db<>fiddle here


SQLite return sum of duplicate rows based on array If I have a simple table fruits that looks like this:

+---+---------+------+
|   | name    | lbs  |
+---+---------+------+
| 1 | apple   | 0.25 |
| 2 | melon   | 3.00 |
+---+---------+------+

In a query, I need to return the sum of the lbs based on an array. For example, I could have the array ('melon', 'melon', 'apple') to return the value 6.25.

A query like this won't work:

SELECT SUM(lbs) FROM fruits WHERE name IN ('melon', 'melon', 'apple');

It returns 3.25 because even though melon is listed twice, it only counts it a single time.

How can I get the select statement to sum duplicate rows as dictated by the given array? I've done a lot of searching of StackExchange, but maybe I'm not using the right keywords to find the answer. Thanks in advance for you help/patience.

Create a (virtual) table with the desired names, then join it with the actual table:

WITH my_array(name) AS (
  VALUES ('melon'), ('melon'), ('apple')
)
SELECT SUM(lbs)
FROM fruits
JOIN my_array USING (name);


How to get data from MySQL table based on a junction table I've been trying to tackle this problem but couldn't do it properly, any help is really appreciated.
This is the Distance calculation query based on latitude, longitude and distance in KMs
SELECT *
FROM (
         SELECT *,
                (
                        ((acos(sin((27.6677608 * pi() / 180)) * sin((activities.latitude * pi() / 180)) +
                               cos((27.6677608 * pi() / 180)) * cos((activities.latitude * pi() / 180)) *
                               cos(((68.8543843 - activities.longitude) * pi() / 180)))) * 180 / pi()) * 60 *
                        1.1515 * 1.609344
                    )
                    as distance
         FROM `activities`
         WHERE is_public = true
           AND (
                     DATEDIFF(end_time, start_time) > 0
                 OR
                     TIME_TO_SEC(TIMEDIFF(TIME(end_time), TIME(start_time))) > 10
             )
     ) markers
WHERE distance <= 22

This is the activity related table ER diagram

Now what i have been trying to do is that the user provides latitude, longitude and distance from which i calculate activities within the distance, lat/longs provided by the user.
PROBLEM => problem is that i want to get activities which are not only close to user's lat/long and distance but also matching certain interest_id from activity_interests table.
SCENARIO => for instance, i provide these details as payload
{
    "latitude": "27.6677608",
    "longitude": "68.8543843",
    "distance": 7,
    "interests": [1,2,3]
}

Now i want Activities which have all above payload Interest ids present in the Activity_interests table and still do the distance calculation for which i have provided the above SQL query.
MySQL version 8.0 is used
Thank you, and i hope i explained it well.

You can do something like this:
SELECT a.*
   ,st_distance_sphere(POINT(68.8543843, 27.6677608 ), POINT(a.`longitude`, a.`latitude` ))/1000   as distance
FROM `activities` a INNER JOIN `activity_interests` ai ON a.id = ai.activity_id
WHERE is_public = true
   AND (
             DATEDIFF(end_time, start_time) > 0
         OR
             TIME_TO_SEC(TIMEDIFF(TIME(end_time), TIME(start_time))) > 10
     )
     AND ai.interest_id IN (1,2,3)
HAVING distance <= 22

As long as you don't provide data, I can only hint at how it would look like.
SELECT a.*,st_distance_sphere(POINT(68.8543843, 27.6677608 ), POINT(`longitude`, `latitude` ))/1000   as distance
FROM `activities` a INNER JOIN `activity_interests` ai ON a.id = ai.activity_id
WHERE is_public = true
   AND (
             DATEDIFF(end_time, start_time) > 0
         OR
             TIME_TO_SEC(TIMEDIFF(TIME(end_time), TIME(start_time))) > 10
     )
     AND SUM(ai.interest_id =1) + SUM(ai.interest_id =2) + SUM(ai.interest_id =3) = 3
GROUP BY a.id,distance
HAVING distance <= 22


MySQL query using left join on two inline views not returning rows without values in second view For some reason when I execute the code below I'm only getting results back that have sku in both views. I thought this should give me everything in the first view and add the content from the second view if it was there. I'd like to be able to use 0 as the value for po.tot2 below if oli.sku is not in the po view.

SELECT
   oli.sku,
   oli.tot as oli_tot,
   po.tot2 as po_tot,
   oli.tot - po.tot2 as diff
FROM (
   SELECT
      sku,
      COALESCE(SUM(quantity),0) as tot
   FROM orderlineitems
   GROUP BY sku
) oli
LEFT JOIN (
    SELECT
       sku,
       COALESCE(SUM(amount),0) as tot2
    FROM poentries
    GROUP BY sku
) po
ON oli.sku = po.sku
WHERE oli.tot - po.tot2 > 0;

You're using po in the WHERE clause, and in effect, saying that it can't be null.

Maybe use: IFNULL(po.tot2,0) instead of po.tot2


Can't start asm filter driver I'm trying to install oracle grid with asm filter driver.

I've already installed grid software only.

As root, I can't start asm. Actually, I don't have any answer from asmcmd filter driver commands:

root@srvdb18c:/u01/app/18.0.0/grid/bin# env|grep ORACLE
ORACLE_BASE=/u01/app/grid
ORACLE_HOME=/u01/app/18.0.0/grid
root@srvdb18c:/u01/app/18.0.0/grid/bin# ./asmcmd afd_configure
root@srvdb18c:/u01/app/18.0.0/grid/bin#
root@srvdb18c:~# $ORACLE_HOME/bin/asmcmd afd_state
root@srvdb18c:~#

What can be the problem?

Important: it's a Solaris 11 system.

Configuring Oracle ASM Filter Driver During Installation

  This procedure must be performed after the Oracle Grid Infrastructure
  installation zip files have been unzipped in the Oracle Grid
  Infrastructure home, but before you enable the Configure ASM Filter
  Driver check box during the installation process when the setup script
  is run.

su root
# set ORACLE_HOME=/u01/app/oracle/12.2.0/grid
# set ORACLE_BASE=/tmp

# asmcmd afd_label DATA1 /dev/disk1a --init

# asmcmd afd_lslbl /dev/disk1a

  Run the setup installation script (gridSetup.sh) to install Oracle
  Grid Infrastructure and enable Oracle ASM Filter Driver configuration.

  On the Create ASM Disk Group wizard page during the installation
  process, check the Configure ASM Filter Driver box to enable
  configuration of Oracle ASM Filter Driver and then add your
  provisioned disks to the initial disk group (typically named DATA).

I used the above before an it worked.

Example: https://balazspapp.wordpress.com/2017/03/20/installing-oracle-12c-release-2-12-2-rac-on-virtualbox-silent-installation-part-3/

Based on the documentation, afd_configure and afd_state are used when you migrate an existing installation to AFD.


Replication Setup what's the value of MySQL auto_increment and offset in my.ini I have setup master master MySQL replication to sync changes in both servers.

Now I want to setup 6 servers, is that possible? I'm making 6 because when an area lost it connection over WAN it will continue it's service and then sync it when the connection is back.

Now I'm having problems with the auto increment increment and auto increment offset to avoid data collision.

I'm confused with this and I've read an article and made it worse.

So now I wonder what should be the proper value of my auto increment and offset in each server.

I'd answer your confusion about auto-increment* variables.

Remember:

auto_increment_increment controls the interval between successive column values.
auto_increment_offset determines the starting point for the AUTO_INCREMENT column value.

So if you're setting 6 servers, you can set them as:
auto_increment_offset=1,2,3,4,5,6
auto_increment_increment=6

so on each servers insertion series will be like:
1,7,13...
2,8,14...
3,9,15...
4,10,16...
5,11,17...
6,12,18...

Idea here is just to generate unique auto-increment numbers.

BUT WAIT!!!!!

Before you begin, when you say 6 servers it will be pair of 3 master-master or are they slaves of original master? If they're slaves, are they going to accept writes (not good!)? Are you going to write on all nodes (in case of master-master?  The architecture isn't clear enough and hence your solution of auto-increments might not be right / required as well.

Anyways... I hope we fixed the confusion of auto-increment. Trust the documentation and don't get confused by too many articles.


Select failed from V$LOGMNR_CONTENTS when called from a procedure Assuming the following user with these privileges granted:
-- create admin user on CDB
CREATE USER c##myadmin IDENTIFIED BY myadmin DEFAULT TABLESPACE system QUOTA UNLIMITED ON system ACCOUNT UNLOCK
/
-- allow access to all PDBs to the admin user
ALTER USER c##myadmin SET CONTAINER_DATA=ALL CONTAINER=CURRENT
/
-- grant needed permissions
GRANT DBA to c##myadmin                            ;
GRANT CREATE SESSION TO c##myadmin                 ;
GRANT CREATE TABLE TO c##myadmin                   ;
GRANT EXECUTE_CATALOG_ROLE TO c##myadmin           ;
GRANT EXECUTE ON DBMS_LOGMNR TO c##myadmin         ;
GRANT SELECT ON V_$DATABASE TO c##myadmin          ;
GRANT SELECT ON V_$LOGMNR_CONTENTS TO c##myadmin   ;
GRANT SELECT ON V_$ARCHIVED_LOG TO c##myadmin      ;
GRANT SELECT ON V_$LOG TO c##myadmin               ;
GRANT SELECT ON V_$LOGFILE TO c##myadmin           ;
GRANT RESOURCE, CONNECT TO c##myadmin              ;

Now, when I connected as my myadmin, I can run the following:
BEGIN
  DECLARE v NUMBER := 0;
BEGIN
  DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/path/to/archive/log/arc0000013.0001', Options=>DBMS_LOGMNR.new);
  DBMS_LOGMNR.START_LOGMNR(StartScn=>23456789, EndScn=>23567890,  Options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.NO_ROWID_IN_STMT);
  select count(*) into v from v$logmnr_contents;
END;
END;
/

PL/SQL procedure successfully completed.

but when it created as a procedure it failed with insufficient privileges:
Create Or Replace Procedure Test AS
 v NUMBER:=0;
BEGIN
 DBMS_LOGMNR.ADD_LOGFILE(LogFileName=>'/path/to/archive/log/arc0000013.0001', Options=>DBMS_LOGMNR.new);
 DBMS_LOGMNR.START_LOGMNR(StartScn=>23456789, EndScn=>23567890,  Options=>DBMS_LOGMNR.DICT_FROM_ONLINE_CATALOG+DBMS_LOGMNR.NO_ROWID_IN_STMT);
 Select Count(*) into v from v$logmnr_contents;
END;
/
Exec Test
/

Procedure Test compiled

Error starting at line 9 in command -
BEGIN Test; END;
Error report -
ORA-01031: insufficient privileges
ORA-06512: at "C##MYADMIN.TEST", line 6
ORA-06512: at line 1
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

If I comment out the select the procedure succeeds.
Is there an additional privilege to enable it to run the select from a procedure?

V$LOGMNR_CONTENTS

V$LOGMNR_CONTENTS contains log history information. To query this
view, you must have the LOGMINING privilege.

The LOGMINING privilege was granted to DBA role. When running an anonymous block, all your privileges granted through a role are effective. When running a stored procedure defined with the default definers rights option, privileges granted through a role are ignored. The LOGMINING privilege should be granted directly to your user:
grant logmining to c##myadmin;


Selecting ALL records when condition is met for ALL records only Sorry if this has been asked before. I couldn't find any examples.
I am trying to pull a student's course work for a semester, only if they have received a grade of 'NA' in ALL their courses. Right now, my code is only pulling any student who has a grade of 'NA' in any course. I need for them to have 'NA' in ALL courses, not just 1 or 2 courses.
My data:

Name
Course
Grade

student1
en101
NA

student1
ma101
B

student1
py102
A

student2
en101
NA

student2
ma205
NA

student2
en206
NA

student3
ma101
NA

I am trying to pull ALL rows for a student, ONLY if they have a grade = 'NA' in all their courses.
Results should be:

Name
Course
Grade

student2
en101
NA

student2
ma205
NA

student2
en206
NA

student3
ma101
NA

my code is pulling every row that has a grade of 'NA', even if the other rows don't meet the condition. I need to pull ALL rows for that record, only if it meets the condition for every row.
Seems easy in my mind...can't seem to make it work.
thanx

Something like this would probably be the most conventional approach:
SELECT
  Name,
  Course,
  Grade
FROM
  myTable a
WHERE
  NOT EXISTS
    (
      SELECT
        1
      FROM
        myTable b
      WHERE
        b.name = a.name
          AND b.grade <> 'NA'
    )

Somewhat less conventional but potentially more efficient:
SELECT
 Name,
 Course,
 Grade
FROM
  (
    SELECT
      Name,
      Course,
      Grade,
      SUM
        (
          CASE
            WHEN Grade <> 'NA' THEN 1
            ELSE 0
          END
        )
        OVER
          (
            PARTITION BY
              Name
          ) AS num_not_na
    FROM
      myTable
  ) a
WHERE
  a.num_not_na = 0


ORA-01503 CREATE CONTROLFILE FAILED... while trying to create a database i am trying to create a new database along with another database. The server already has a database running. I used DBCA command to create the new db instance. First i set the new SID and home and then ran the dbca command.. But half way through installation im getting the error

ORA-01503 CREATE CONTROLFILE FAILED

ORA- 01158: database already mounted

First of all, you could check the newly created init/spfile (under; linux: $OB/dbs - or - win: $OB\database) if everything is set correctly (SID and Service Name, Datafile location...)

I also would advise you to check all your env. vars, the error says “Database already mounted” - it seems it’s trying to create a .ctl for your already-running database.

You can try this to start fresh;

Linux

copy your actual .profile in .profile_new
modify .profile_new as per your need ($OH, $ORACLE_SID,...)
./profile new
check you’re all set: env
start dbca again

Windows

open cmd as Admin
set ORACLE_SID=[your new SID]
set ORACLE_HOME=[your new $OH]
run dbca

Last thing, i would take a look at the oraInventory/oraInst.lok file, to check if there’s the old $OH set - if yes, move the file away (just to backup it) and restart the dbca.

Hope one of this solutions helps.


How to Pivot in PostgreSQL Having the following data in a table:

ID     Category   Value
1234   Cat01      V001
1234   Cat02      V002
1234   Cat03      V003
1234   Cat03      V004
1234   Cat03      V005

I want to have the following output:

ID     Cat01  Cat02   Cat03
1234   V001   V002    V003
1234   V001   V002    V004
1234   V001   V002    V005

The output I want to achieve is a kind of pivot table where I have all the values vertically in a table and I want to have those values, horizontally, having the category as a column. But there are some categories that have multiples values, in that case, I need to repeat the values of all other categories and create a row per each repeated value

How can it be done in PostgreSQL?

This is a tricky one. crosstab() expects one (or no) value per category for each row_name.

We can work around this restriction like this:

SELECT id
     , COALESCE(cat01, max(cat01) OVER w)
     , COALESCE(cat02, max(cat02) OVER w)
     , COALESCE(cat03, max(cat03) OVER w)
FROM   crosstab(
   'SELECT id::text || row_number() OVER (PARTITION BY id, category ORDER BY value) * -1 AS ext_id
         , id, category, value
    FROM   tbl
    ORDER  BY ext_id, category, value'

   ,$$VALUES ('Cat01'::text), ('Cat02'), ('Cat03')$$
   ) AS ct (xid text, id int, cat01 text, cat02 text, cat03 text)
WINDOW w AS (PARTITION BY id);

Returns your desired result.

How?

Add an extended id: ext_id from the existing id and a row number for each value of the category for the same id. This way we ensure as many rows per id in as there are values for the most common category. We get a derived table like this to build our crosstab() on:

ext_id   | id   | category | value
---------+------+----------+-------
'1234-1' | 1234 | 'Cat01'  | 'V001'
'1234-1' | 1234 | 'Cat02'  | 'V002'
'1234-1' | 1234 | 'Cat03'  | 'V003'
'1234-2' | 1234 | 'Cat03'  | 'V004'
'1234-3' | 1234 | 'Cat03'  | 'V005'

Now we can feed it to crosstab() using the safe 2-parameter form for missing attributes. Read the basics first if you are not familiar with this:

PostgreSQL Crosstab Query

The original id is carried over as "extra column". See:

Pivot on Multiple Columns using Tablefunc

Your question leaves room for interpretation. My solution pairs the lowest values per category first and keeps filling the following rows until there are no values left. (We could combine multiple values per category any other way, it has not been defined.) If a category is short of values for a given id, the rest is filled in with NULL values.
In the final step I replace those NULL values with the maximum value of each category per id:

COALESCE(cat01, max(cat01) OVER (PARTITION BY id, category))

which is effectively the same as:

max(cat01) OVER (PARTITION BY id, category)

I am hoping to make it slightly faster if we only default to the window function if the value is NULL.


How do I iterate over 1000 records in a particular order I am writing a procedure to migrate two tables into different tables. This procedure will have two cursors. One to fetch the parent records and then second cursor to fetch the details for each parent record. The parent record is a table with the following columns --

  ID || YYYYMMDD || submitdate.

The combination of ID and YYYYMMDD gives the unique id. So now I want to migrate this to another table in the order it was created. I can order this table by submitdate, but I need to fetch 1000 records at a time. How can I achieve this?

First, I'll cover the SQL row-limiting side of things.

In Oracle 11.2.x and lower, you have to use rownum and a subquery, as it doesn't support the LIMIT or FETCH clauses:

select * from
(
  select *
  from yourtable
  order by submitdate ASC
)
where rownum <=1000;

In Oracle 12 you can use:

SELECT *
FROM YOURTABLE
ORDER BY SUBMITDATE ASC
FETCH FIRST 1000 ROWS ONLY;

... then, for the next 1k:

SELECT *
FROM YOURTABLE
ORDER BY SUBMITDATE ASC
OFFSET 1000 ROWS FETCH NEXT 1000 ROWS ONLY;

Sample table:

create table yourtable
(
  ID number,
  YYYYMMDD varchar(8),
  submitdate date
);

The PL/SQL:

CREATE OR REPLACE PROCEDURE process_records(offset IN NUMBER)
IS
  CNT number;
  ID yourtable.id%type;
  YYYYMMDD yourtable.yyyymmdd%type;
  SUBMITDATE yourtable.submitdate%type;
  ID_YYYYMMDD varchar(100); -- variable to hold the concatenated value for the 2nd cursor
  CURSOR yourcursor
  IS
    SELECT *
    FROM (
      SELECT *
      FROM yourtable
      ORDER BY submitdate ASC
    ) WHERE rownum > offset;
BEGIN
  CNT:=0;
  OPEN yourcursor;
  LOOP
    FETCH yourcursor into ID,YYYYMMDD,SUBMITDATE;
    CNT:=CNT+1;
    EXIT WHEN CNT=1000;
    ID_YYYYMMDD := ID||YYYYMMDD;
    --
    -- put your child cursor here
    --
  END LOOP;
END process_records;
/

The stored procedure takes an input, which is the start offset.

Create another cursor, and use your concatenated ID ID_YYYYMMDD in the WHERE clause.

Why do you need to process 1000 records at a time? It'll be slower iterating this way. Often you can achieve the same thing using one or two lone SQL statements, which end up way more efficient.


Is there a SQL Server implementation of the Longest Common Substring problem? Is there a SQL Server implementation of the Longest Common Substring problem? A solution that checks with all rows of a column in SQL Server? I have seen solutions that take two strings as input, but no SQL Server solution that looks at all rows of a column in a table.

I did try a few things, but to be honest I think a solution goes over my head at the moment, so any suggestions are welcome.

There is no "real world" problem here, I'm just looking at programming problems and how they could be solved with SQL Server.

This can be done rather easily as a SQLCLR User-Defined Aggregate (UDA). An aggregate operates over a set of rows, so you would be able to operate over all rows or just a subset, based on a WHERE condition and optional GROUP BY (if wanting to operate over separate sets of rows).

BUT, whether or not you should do this depends on what you are planning on doing with the result. If this is a one-off project to do some research that won't be repeated, then it is probably best to just make a small Console App to read in the rows and process accordingly.

However, if you do have some need to use the returned value within a database-centered process, then SQLCLR should be fine (assuming you follow the two recommendations mentioned under "The following tricks can be used to reduce the memory usage of an implementation" in the Pseudocode section. You will just need to find a "creative" way of dealing with the situation of having multiple results for what is considered the longest common substring (i.e. if 2 or more common substrings tie for "first place"). Using the example from the Wikipedia page (which shows 2 matches for the 2 strings):

ABAB
BABA

Returns both:

BAB
ABA

Perhaps returning an XML document of matches since that is parsable and can contain any string (when properly escaped).

UPDATE (updated, and updated again)

The .NET C# source code for this can be found on Pastebin.com at:

SQLCLR UDA for Longest Common Substring - Source Code

And for anyone that wants to play with this UDA without compiling it, an installation T-SQL script (no external DLL) can be found on Pastebin.com at:

SQLCLR UDA for Longest Common Substring - Installer

The UDA should be fairly memory-efficient as it only stores substrings that match the current string and all previously encountered strings. As a new row calls the UDA, any substrings that are not found in the new string are removed from the collection.

It is also CPU-efficient in that, if at any point the number of "common" substrings goes to zero, it sets a flag indicating that no substrings are even possible and short-cicruits all future executions to simply exit upon being called. This happens immediately if an empty string is encountered. In any of these cases, an empty XML document (i.e. root element only) is returned. The meaning of the empty document is equivalent to an empty string, since the only thing the input strings have in common is that they were non-NULL strings.

NULL, in my interpretation, is ignored and does not indicate no possible matches like an empty string does.

A NULL is returned in the following two cases:

All inputs are NULL
Only a single non-NULL row was in the set and hence there was no other string to compare to, hence nothing to be considered "common".

I added a second input parameter which controls whether or not the return is just the longest common substrings or all common substrings. In the case of returning all, an attribute is added to each "item" to indicate whether or not it is one of the "longest" substrings:

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test1a]
FROM   (VALUES (N'ABAB'), (N'BABA')) tab(col);

Returns:

<Items Merged="False">
  <Item>ABA</Item>
  <Item>BAB</Item>
</Items>

And

SELECT dbo.LongestCommonSubstring(tab.col, 1) AS [Test1b]
FROM   (VALUES (N'ABAB'), (N'BABA')) tab(col);

Returns:

<Items Merged="False">
  <Item IsLongest="True">ABA</Item>
  <Item IsLongest="True">BAB</Item>
  <Item IsLongest="False">AB</Item>
  <Item IsLongest="False">BA</Item>
  <Item IsLongest="False">A</Item>
  <Item IsLongest="False">B</Item>
</Items>

Also, the comparisons are now case-InSensitive to match the typical Collation.

Below are 16 more test cases that check functionality only, not performance. I will post additional tests later that operate over many rows of much longer strings. I intentionally left out Combining Characters and Supplementary Characters, for now, as they are bit a more complicated.

SELECT dbo.LongestCommonSubstring(tab.col, 1) AS [Test2]
FROM   (VALUES (N'ABAB'), (N'BABA'), (N'2BAB5')) tab(col);
-- <Items><Item>BAB</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test3]
FROM   (VALUES (N'ABAB'), (N'BABA'), (NULL), (N'2BAB5')) tab(col);
-- <Items><Item>BAB</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test4]
FROM   (VALUES (NULL), (NULL), (NULL)) tab(col);
-- NULL

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test5]
FROM   (VALUES (N'ABAB'), (N'BABA'), (N''), (N'2BAB5')) tab(col);
-- <Items />

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test6]
FROM   (VALUES (N'ABAB'), (N'BABA'), (N'L'), (N'2BAB5')) tab(col);
-- <Items />

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test7]
FROM   (VALUES (N'ABAB')) tab(col);
-- NULL

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test8a-DuplicatesAcross2Rows]
FROM   (VALUES (N'ABAB'), (N'ABAB')) tab(col);
-- <Items><Item>ABAB</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test8b-DuplicatesAcross3Rows]
FROM   (VALUES (N'ABAB'), (N'ABAB'), (N'ABAB')) tab(col);
-- <Items><Item>ABAB</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test8c-DuplicatesAcross4Rows]
FROM   (VALUES (N'ABAB'), (N'ABAB'), (N'ABAB'), (N'ABAB')) tab(col);
-- <Items Merged="False"><Item>ABAB</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test9-DuplicatesWithinOneString]
FROM   (VALUES (N'ABAB'), (N'zABABh2348923ABABf')) tab(col);
-- <Items Merged="False"><Item>ABAB</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test10-XmlEncodableCharacters]
FROM   (VALUES (N'ABA&B'), (N'zABA&Bh2348923ABA&Bf')) tab(col);
-- <Items Merged="False"><Item>ABA&amp;B</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test11a-FinalMatchesShorterThanInitialSet]
FROM   (VALUES (N'ABCDq1234g'), (N'1234qABCDg'), (N'uiyuiuy1234qBCDg'), (N'512tttrtrtBCDdfdfgdg')) tab(col);
-- <Items Merged="False"><Item>BCD</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test11b-FinalMatchesShorterThanInitialSet]
FROM   (VALUES (N'BCDq1234g'), (N'1234qABCDg'), (N'uiyuiuy1234qBCDg'), (N'512tttrtrtBCDdfdfgdg')) tab(col);
-- <Items Merged="False"><Item>BCD</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test11c-FinalMatchesShorterThanInitialSet]
FROM   (VALUES (N'ABCDq1234g'), (N'1234qABCDg'), (N'uiyuiuy1234qBCDg'), (N'5123tttrtrtBCDdfdfgdg')) tab(col);
-- <Items Merged="False"><Item>BCD</Item><Item>123</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test11d-FinalMatchesShorterThanInitialSet]
FROM   (VALUES (N'BCDq1234g'), (N'1234qABCDg'), (N'uiyuiuy1234qBCDg'), (N'5123tttrtrtBCDdfdfgdg')) tab(col);
-- <Items Merged="False"><Item>BCD</Item><Item>123</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 0) AS [Test11e-FinalMatchesShorterThanInitialSet]
FROM   (VALUES (N'BCDq1234g'), (N'1234qABCDg'), (N'uiyuiuy1234qBCDg'), (N'123tttrtrtBCDdfdfgdg')) tab(col);
-- <Items Merged="False"><Item>BCD</Item><Item>123</Item></Items>

SELECT dbo.LongestCommonSubstring(tab.col, 1) AS [Test12-CaseInSensivity]
FROM   (VALUES (N'AbAB'), (N'BAbA')) tab(col);
/*
<Items Merged="False">
  <Item IsLongest="True">AbA</Item>
  <Item IsLongest="True">bAB</Item>
  <Item IsLongest="False">Ab</Item>
  <Item IsLongest="False">bA</Item>
  <Item IsLongest="False">A</Item>
  <Item IsLongest="False">b</Item>
</Items>
*/

Final Update (hopefully)

I made a couple of minor changes to the test code provided in @MisterMagoo's answer so that it would: a) return ties for "longest" common substring, and b) include the last character in each string as part of the search. I also changed, slightly, how the initial test rows are gathered such that there would be just over 1 million rows, and re-ran the tests. The results were that the T-SQL version took 1 minute and 13 seconds while the SQLCLR UDA took only 12 seconds (and even has the option of returning all common substrings, not just the longest).

I then modified the test data to include another common substring of the same length as the current winner (7 characters), a shorter but still common substring of 4 characters, and 3 random characters. This increased the max test string size from 32 characters to 46 characters. Running the test again (same code), the T-SQL version had to be killed at 23 minutes and had only tested the first 3 lengths: 27, 26, and 25. The SQLCLR UDA returned in about 1 minute and 10 seconds.

Is it time to celebrate? Has SQLCLR saved the day? Hold on..

On a hunch, I decided to see if the optimization I added to the SQLCLR version would help the T-SQL, namely:

Grab the shortest two strings (shortest would have the fewest number of possible substrings and would be the longest possible match anyway).
Extract all possible common substrings from the two short strings (any substrings that are "common" across all rows necessarily must be in the set derived from just these two strings, and no new substrings from other rows can be introduced as they wouldn't be "common").
Starting with the longest common substring, test to see if it is found in all rows. I used IF (NOT EXISTS (WHERE CHARINDEX(substring, test_row) > 0)) because the EXISTS clause will exit on the first row that returns a 0 (meaning substring not present) and hence not need to test all rows. This part is accomplished with a CURSOR (shh, don't tell anyone) because it allows for starting at the top of the list and just picking each new row off without needing to re-scan the list each time to find the next entry.
Once the first substring is found, save its length in a variable, and save the substring itself into a table variable.
Keep testing, but only for strings that are the same length as the first common substring found. as soon as the length of the next substring to search for is less than the length of the first substring to match, exit the loop and clean up the cursor.

All of this cursor stuff must make it painfully slow, right? Well, keeping in mind that the prior T-SQL version would have taken several hours to finish, and the SQLCLR UDA took 1 minute and 10 seconds, you might guess this updated version would take, what? A few minutes? 10 minutes? More? After all, just mentioning "cursor" is an automatic 5 minute hit, right? The actual time for the modified version:

22 seconds!!!

Of course, that is largely due to the substrings being on the longer side (compared to the size of the strings) and so the loop was able to exit earlier than if the longest common substring was only 3 or 4 characters long (i.e. it had less to test, but that is still a valid case and is not cheating). Also, one benefit of working in T-SQL is that you can test the entire set against a single value, whereas SQLCLR only has the current row and cannot see the entire set, hence the SQLCLR cannot short-circuit upon finding the longest common substring (because it won't know what is "common" in the first place until it has been executed across all rows).

A final change was to allow the new T-SQL version to return all "common" substrings, not just the longest ones, while indicating which ones were the longest in a second column of datatype BIT. When returning all substrings, mirroring the functionality of the SQLCLR UDA (even when the UDA only returns the longest common substrings, it still has the full list of all common substrings stored since it, again, has no ability to short-circuit), the T-SQL version returns in 2 minutes and 41 seconds.

So, there are conditions were the T-SQL can be faster, even at 1.2 million rows. But the performance is far more stable for the SQLCLR version, and definitely faster when you want all common substrings.

The test script, containing all 3 tests along with their results, can be found on Pastebin at:

SQLCLR UDA for Longest Common Substring - Testing

P.S. Even though the test was done over 1.2 million rows, the longest string tested was 46 characters. I am not sure how the performance of either approach would be affected by operating over strings that are much longer. That testing will have to wait until there is time to do it ;-).


How do I export a multi table database with exceptions? I am working out of phpmyadmin to export data using an sql.

I have an 88 table database. I would like to export all data to a .csv from 85 of those tables where I am only keeping the rows in which my column Length(Status) > 0.

So far I've come up with being able to select all data from a single table. I would like to apply to my entire database except the 3 tables and export it a .csv.

SELECT * FROM table WHERE LENGTH(Status) > 0

Use SELECT INTO OUTFILE ... WHERE LENGTH(Status) > 0 for each table.

PHPMyAdmin isn't a tool for complex tasks.


SQL Job in idle state A created a new SQL Job in SQl Server Agent. I followed the steps mentioned in the article
http://msdn.microsoft.com/en-us/library/ms187910.aspx

Still it is in idle state. PLease note that the schedular is prepared to execute every 10 seconds.

Below is the Drop and create script

IF  EXISTS (SELECT job_id FROM msdb.dbo.sysjobs_view WHERE name = N'test')
EXEC msdb.dbo.sp_delete_job @job_id=N'7ac1e514-fec2-4516-b716-f00107fb127e', @delete_unused_schedule=1
GO

BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 01/26/2012 14:22:27 ******/
IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'test',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=0,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'No description available.',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object:  Step [rr]    Script Date: 01/26/2012 14:22:31 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'rr',
        @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0, @subsystem=N'TSQL',
        @command=N'insert into [datet](dat) values(getdate())',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N's',
        @enabled=1,
        @freq_type=4,
        @freq_interval=1,
        @freq_subday_type=2,
        @freq_subday_interval=10,
        @freq_relative_interval=0,
        @freq_recurrence_factor=0,
        @active_start_date=20120125,
        @active_end_date=99991231,
        @active_start_time=203200,
        @active_end_time=215959,
        @schedule_uid=N'0ad45ad2-6d8f-4193-a687-f60bf7d5f0e5'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:

GO

As far as I can see your schedule is set to execute between midnight (12:00AM) to noon (12:00PM), i.e. your job will not execute in the afternoon and evening. Change your ending time to 11:59:59PM and it should run all day.


how to GROUP BY on specific column and COUNT() the other columns? I've queried a few tables and result is as below:

I want to count correct col upon ct_id(course) col. I queried a lot but gained no success. How to query such a thing?

for example in SELECT I should have two columns: -correctNo -incorrectNo for each course(ct_id).
Note: The last column(correct) is a derived column: IF(selected_answer=answer,1,0) as correct

I am not so sure what your exact requirement is, but i think you are looking for this..

CREATE TABLE test1(User_ID SMALLINT, Jqci_ID SMALLINT, Ct_ID SMALLINT, Correct BIT)

INSERT INTO test1
SELECT 29, 5, 8, 0 UNION ALL
SELECT 29, 25, 8, 1 UNION ALL
SELECT 29, 26, 8, 0 UNION ALL
SELECT 29, 27, 8, 0 UNION ALL
SELECT 29, 28, 8, 0 UNION ALL
SELECT 29, 34, 8, 1 UNION ALL
SELECT 29, 35, 8, 0 UNION ALL
SELECT 29, 36, 8, 0 UNION ALL
SELECT 29, 37, 8, 0 UNION ALL
SELECT 29, 38, 8, 0 UNION ALL
SELECT 29, 39, 8, 0 UNION ALL
SELECT 29, 41, 8, 1 UNION ALL
SELECT 29, 42, 8, 0 UNION ALL
SELECT 29, 47, 34, 0

SELECT
    Ct_ID,
    SUM(CASE WHEN Correct = 1 THEN 1 ELSE 0 END) AS Correct,
    SUM(CASE WHEN Correct = 0 THEN 1 ELSE 0 END) AS InCorrect
FROM test1
GROUP BY Ct_ID