Oracle PLSQL validate a row and insert each cell value of row as a new row with validation results in another table oracle I am very much new to the DB world, so wanted to review whether I am following right approach or not.
I have two tables,

table A  --> is a table with 40 columns

table B --> is a table with the 3 columns (each column of table A is represented as a row in this table.)

Example:

A:
column_1    |      column_2          |    column_3 ......... | column_40
-----------------------------------------------------------
value1_1    |      value1_2          | value1_3......        | value1_40

B:
column_name      |column_value  | column_errorKey
----------------------------------------------------
column_1         | value1_1     | value1_1_errorKey
column_2         | value1_2     | value1_2_errorKey

What am I doing?

Validate each value of a row from table A and insert into the table B with
 its value, error key and corresponding column name.

My PL SQL code is as below, (Note: SQL code has considered only two columns to minimize the code here)

INSERT WHEN  (LENGTH(column_1)  <=7) THEN
        into table_B values(
          'column_1',column_1,'NoError')
WHEN  (LENGTH(column_1)  >7) THEN
        into table_B values(
          'column_1',column_1,'invalidLength')
WHEN  (LENGTH(column_2)  <= 75) THEN
        into table_B values(
          'column_2',column_2,'NoError')
WHEN  (LENGTH(column_2)  > 75) THEN
        into table_B values(
          'column_2',column_2,'invalidLength')
( select column_1,column_2,...,column_40
          from table_A );

The validation that is happening within When the condition has only one validation but we have more validation like this for the value of each cell. I wanted to know whether I am on right approach or not.

We know that it is best when a single SQL statement can be written for solving a problem.  However, your situation requires a SQL statement that would be very unwieldy and difficult to debug and test.  One of your comments* contains the following facts:

  User can enter anything between 0 to 1000 characters (Data is read
  from the excel, and end user do not want any restriction on the
  excel). But on our application, we have to show the errors of the
  excel.

(* see: https://stackoverflow.com/questions/48839473/oracle-plsql-validate-a-row-and-insert-each-cell-value-of-row-as-a-new-row-with )

My suggestions are: {1} Create a table ("checks") that contains all necessary conditions (checks) including error messages, comments etc. {2} Using PL/SQL and BULK COLLECT to read all rows from this table, and subsequently generate dynamic SQL statements that perform batches of tests (checks) and write their results to a log table (your "table B").

{1}

create table checks (
  condition varchar2(256)
, colname varchar2(256)
, errmessage varchar2(256) default 'no error'
);

begin
insert into checks ( condition, colname ) values ( 'LENGTH( column_1 ) <= 7', 'column_1') ;
insert into checks ( condition, colname ) values ( 'LENGTH( column_2 ) <= 75', 'column_2') ;
insert into checks ( condition, colname ) values ( 'LENGTH( column_3 ) <= 17', 'column_3') ;
insert into checks ( condition, colname ) values ( 'LENGTH( column_4 ) <= 17', 'column_4') ;
-- etc
-- etc
-- etc
insert into checks ( condition, colname, errmessage )
values ( 'LENGTH( column_38 ) > 44', 'column_38', 'invalid length' ) ;
insert into checks ( condition, colname, errmessage )
values ( 'LENGTH( column_39 ) > 44', 'column_39', 'invalid length' ) ;
insert into checks ( condition, colname, errmessage )
values ( 'LENGTH( column_40 ) > 44', 'column_40', 'invalid length' ) ;
end;
/

{2}

declare
  type checks_t is table of checks%rowtype ;
  tchecks checks_t ;
  sqlstring   varchar2(1000) := '' ;
begin
  select condition, colname, errmessage
  bulk collect into tchecks
  from checks ;

  for i in tchecks.first .. tchecks.last
  loop
    sqlstring := 'insert into b ( column_name, column_value, column_errorKey ) '
              || 'select '
              || ' ( select ''' || tchecks( i ).colname || ''' from dual ), '
              || 'substr( '
              ||  tchecks( i ).colname || ', 1, 256 ) ,'
              || ' case '
              || ' when ' || tchecks( i ).condition
              || ' then ''' || tchecks( i ).errmessage
              || ''' else ''condition evaluates to FALSE'' end '
              || ' from A' ;
    execute immediate sqlstring ;
    sqlstring := '' ;
  end loop;
end;
/

There is quite a lot of example code in the dbfiddle.  However,  the first 5 sections are just about test data for "table A", which is supposed to resemble data stored in the spreadsheet (40 columns x 16 rows).  With this setup, table B will end up having 1280 rows after the PL/SQL anonymous block has been executed (80 checks are run on each row of table A).

Being a bit wary about the performance of the script, I've done some testing on a smallish VM (Oracle 12c).
Results as follows:

-- table A with 50 rows -> 4000 generated INSERTs into B (80 checks per row)
-- PL/SQL procedure successfully completed.
Elapsed: 00:00:00.050

-- table A with 234256 rows -> 18740480 generated INSERTs into B
-- PL/SQL procedure successfully completed.
Elapsed: 00:02:18.305

SQL> select count(*) from B;
COUNT(*)
18740480

Elapsed: 00:00:25.945

Due to limitations of Oracle 11g (no identity columns) and dbfiddle (could not create a sequence), there are no ID columns or PK constraints anywhere in this example.  Also, the error messages (in the checks table) can be a bit misleading, but all that can be "easily" adjusted .


Linked Server to Oracle seems to be slow we have a linked server between a SQL Server 2008 R2 instance and an Oracle 11.1.0.7 instance using OraOLEDB.Oracle Provider.

The enabled options for provider are:

Dynamic parameter
Nested queries
Allow inprocess
Supports "Like" Operator

The options of the linked server are:

@optname=N'collation compatible', @optvalue=N'false'
@optname=N'data access', @optvalue=N'true'
@optname=N'dist', @optvalue=N'false'
@optname=N'pub', @optvalue=N'false'
@optname=N'rpc', @optvalue=N'true'
@optname=N'rpc out', @optvalue=N'true'
@optname=N'sub', @optvalue=N'false'
@optname=N'connect timeout', @optvalue=N'0'
@optname=N'collation name', @optvalue=null
@optname=N'lazy schema validation', @optvalue=N'false'
@optname=N'query timeout', @optvalue=N'0'
@optname=N'use remote collation', @optvalue=N'true'
@optname=N'remote proc transaction promotion', @optvalue=N'true'

The processes using this linked server just take orders from Oracle, do what it has to do and then sends back to Oracle informations about what have been done.

According to the end users, it seems to be slow.

How can I ...

Assess that the configuration is done appropriately?
How can we make sure that performance is good ? Monitoring queries that goes through it, etc.

You must change the Incompatable Database Engine Server Collation into Linked Server Options. Set Value to True


Advantage of exposing data model to customers using our product We have a product through which we provide a standard definition to customer's asset data. The problem currently lies in importing customer's data from different types of data sources to our data model. I am looking for an option of exposing our data model to customers so that they can populate it accordingly. What are the pros and cons of this approach ?
What are the different ways through which I can achieve this ? Any inputs would be greatly appreciated.

One obvious disadvantage is that no matter how good your data model is, it better suit your needs for years to come because you will have just set it in concrete.

I try not to even give app developers access to the the underlying data model. There is a "wall of abstraction" between users and the physical data. That wall is build from a wide variety of views. Just about any format that best fits the user's need can be provided. View triggers do the conversion behind the scenes.

Give your customers whatever logical model best suits their requirement in getting their data to you. So you have to convert it to your current actual model. It sure beats not being able to fix flaws or implement new designs in your database.


when were the sys-dm_os_wait_stats last cleared? sql-2005! I intend to monitor the execution of my stored procedures in a specific database, using the following script, which I got from
How can I monitor the call count per stored procedure per timespan?

SELECT
    OBJECT_NAME(qt.objectid)
  , qs.execution_count AS [Execution Count]
  , qs.execution_count / DATEDIFF(Second, qs.creation_time, GETDATE()) AS [Calls/Second]
  , qs.total_worker_time / qs.execution_count AS [AvgWorkerTime]
  , qs.total_worker_time AS [TotalWorkerTime]
  , qs.total_elapsed_time / qs.execution_count AS [AvgElapsedTime]
  , qs.max_logical_reads
  , qs.max_logical_writes
  , qs.total_physical_reads
  , DATEDIFF(Minute, qs.creation_time, GETDATE()) AS [Age in Cache]
FROM
    sys.dm_exec_query_stats AS qs
CROSS APPLY
    sys.dm_exec_sql_text(qs.[sql_handle]) AS qt
WHERE
    qt.[dbid] = DB_ID()
ORDER BY
    qs.execution_count DESC
OPTION (RECOMPILE);

The DMV sys.dm_exec_query_stats reports cumulative values since the last restart. If you want to measure over a fixed period, use the command below to reset wait stats.

DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

DBCC SQLPREF("sys.dm_os_latch_stats",CLEAR); --this one never worked for me

When was sys.dm_os_wait_stats last cleared? But in Sql Server 2005, how do I find it out?

there is a wait type called SQLTRACE_BUFFER_FLUSH -> Occurs when a task is waiting for a background task to flush trace buffers to disk every four seconds.

this article is very good:
Figuring Out When Wait Statistics Were Last Cleared - by Erin Stellato

I came out with the following script that seems to be working fine on my servers, including the sql2005 ones.

/* last time the wait cache was cleared - sql 2005*/
SELECT
[wait_type],
[wait_time_ms],
DATEADD(ss,-[wait_time_ms]/1000,getdate()) AS [Date/TimeCleared],
CASE
WHEN [wait_time_ms] < 1000 THEN CAST([wait_time_ms] AS VARCHAR(15)) + ' ms'
WHEN [wait_time_ms] between 1000 and 60000 THEN CAST(([wait_time_ms]/1000) AS VARCHAR(15)) + ' seconds'
WHEN [wait_time_ms] between 60001 and 3600000 THEN CAST(([wait_time_ms]/60000) AS VARCHAR(15)) + ' minutes'
WHEN [wait_time_ms] between 3600001 and 86400000 THEN CAST(([wait_time_ms]/3600000) AS VARCHAR(15)) + ' hours'
WHEN [wait_time_ms] > 86400000 THEN CAST(([wait_time_ms]/86400000) AS VARCHAR(15)) + ' days'
END [TimeSinceCleared]
FROM [sys].[dm_os_wait_stats]
WHERE [wait_type] = 'SQLTRACE_BUFFER_FLUSH'
  OR  [WAIT_TYPE] = 'SQLTRACE_INCREMENTAL_FLUSH_SLEEP';

/* check SQL Server start time - 2008 and higher */
SELECT
[sqlserver_start_time]
FROM [sys].[dm_os_sys_info];

-- to clear the wait cache
DBCC SQLPERF("sys.dm_os_wait_stats",CLEAR);

this time I will leave it like this, because I in a bit of a rush to monitor stored procedures executions, but for the next time I will use other Several Methods to collect SQL Server Stored Procedure Execution History

and maybe also a bit of this one:
Converting SQL Trace to Extended Events in SQL Server 2012

hopefully soon we will migrate all of the sql server 2005


Although data_length and index_length aren't exact multiples of read_buffer_size, they are based on it.  So adjusting read_buffer_size in my.cnf is the solution (thanks @Wilson Hauck), as Shane Bester pointed out on the MySQL forums:

  memory tables grow by 'read_buffer_size' bytes when they need extending (https://forums.mysql.com/read.php?92,405702,417606#msg-417606)

Based on performance tests by Peter Zaitsev in 2007 (https://www.percona.com/blog/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/), the default 128K setting is generally best, and using smaller values brings very little penalty.


VACUUM returning disk space to operating system VACUUM usually does not return disk space to operating system, except in some special cases.
From the docs:

  The standard form of VACUUM removes dead row versions in tables and indexes and marks the space available for future reuse. However, it will not return the space to the operating system, except in the special case where one or more pages at the end of a table become entirely free and an exclusive table lock can be easily obtained. In contrast, VACUUM FULL actively compacts tables by writing a complete new version of the table file with no dead space. This minimizes the size of the table, but can take a long time. It also requires extra disk space for the new copy of the table, until the operation completes.

The question is: how can this database state when one or more pages at the end of a table become entirely free be achieved? This can be done via VACUUM FULL, but I haven't got enough space to implement it. So are there any other possibilities?

To return space to the OS, use VACUUM FULL. While being at it, I suppose you run VACUUM FULL ANALYZE. I quote the manual:

FULL
Selects "full" vacuum, which can reclaim more space, but takes much
longer and exclusively locks the table. This method also requires
extra disk space, since it writes a new copy of the table and doesn't
release the old copy until the operation is complete. Usually this
should only be used when a significant amount of space needs to be
reclaimed from within the table.

Bold emphasis mine.
CLUSTER achieves that, too, as a collateral effect.
Plain VACUUM does not normally achieve your goal ("one or more pages at the end of a table entirely free"). It does not reorder rows and only prunes empty pages from the physical end of the file when the opportunity arises - like your quote from the manual reveals.
You can get empty pages at the end of the physical file when you INSERT a batch of rows and DELETE them before other tuples get appended. Or it can happen by coincidence if enough rows are deleted.
There are also special settings that might prevent VACUUM FULL from reclaiming space. See:

How to optimize a table for a very high-frequency updates?

Prepare empty pages at the end of a table for testing
The system column ctid represents the physical position of a row. You need to understand that column:

How do I decompose ctid into page and row numbers?

We can work with that and prepare a table by deleting all rows from the last page:
DELETE FROM tbl t
USING (
   SELECT (split_part(ctid::text, ',', 1) || ',0)')::tid     AS min_tid
        , (split_part(ctid::text, ',', 1) || ',65535)')::tid AS max_tid
   FROM   tbl
   ORDER  BY ctid DESC
   LIMIT  1
   ) d
WHERE  t.ctid BETWEEN d.min_tid AND d.max_tid;

Now, the last page is empty. This ignores concurrent writes. Either you are the only one writing to that table or you need to to take a write lock to prevent race conditions.
The query is optimized to identify qualifying rows quickly. The second number of a tid is the tuple index stored as unsigned int2, and 65535 is the maximum for that type (2^16 - 1), so that's the safe upper bound.
db<>fiddle here  (reusing a simple table from a different case.)
Old sqlfiddle
Tools to measure row / table size:

Measure the size of a PostgreSQL table row

Disk full
You need wiggle room on disk for any of these operations. There is also the community tool pg_repack as replacement for VACUUM FULL / CLUSTER. It avoids exclusive locks but needs free space to work with as well. The manual:

Requires free disk space twice as large as the target table(s) and indexes.

As a last resort, you can run a dump/restore cycle. That removes all bloat from tables and indexes, too. Closely related question:

I need to run VACUUM FULL with no available disk space

The answer over there is pretty radical. If your situation allows for it (no foreign keys or other references preventing row deletions), and no concurrent access to the table), you can just:
Dump the table to disk connecting from a remote computer with plenty of disk space (-a for --data-only):
From a remote shell, dump table data:
pg_dump -h <host_name> -p <port> -t mytbl -a mydb > db_mytbl.sql

In a pg session, TRUNCATE the table:
-- drop all indexes and constraints here for best performance
TRUNCATE mytbl;

From remote shell, restore to same table:
psql -h <host_name> -p <port> mydb -f db_mytbl.sql
-- recreate all indexes and constraints here

It is now free of any dead rows or bloat.
But maybe you can have that simpler?

Can you make enough space on disk by deleting (moving) unrelated files?

Can you VACUUM FULL smaller tables first, one by one, thereby freeing up enough disk space?

Can you run REINDEX TABLE or REINDEX INDEX to free disk space from bloated indexes?

Whatever you do, don't be rash. If in doubt, backup everything to a secure location first.


Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays? I have been playing with arrays in one of my PostgreSQL databases.

I have created a table with a geometry array with at least one element:

CREATE TABLE test_arrays (
    polygons geometry(Polygon,4326)[],
    CONSTRAINT non_empty_polygons_chk
        CHECK ((COALESCE(array_length(polygons, 1), 0) <> 0))
);

I have added a few rows, and queried the table for the first element in each geometry array:

SELECT polygons[0] FROM test_arrays;

To my surprise, I got a list of empty rows!

After some research, it turns out that PostgreSQL arrays are one-based:

  The array subscript numbers are written within square brackets. By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].

So SELECT polygons[0] FROM test_arrays; worked and returned the first polygon of each row.

If PostgreSQL uses a one-based numbering convention, why is querying the 0-th element allowed, and does the result have any meaning?

Postgres arrays are 1-based by default. And in typical applications it's best to stick with the default. But the syntax allows to start with any integer number. The documentation:

  Subscripted assignment allows creation of arrays that do not use
  one-based subscripts. For example one might assign to myarray[-2:7] to
  create an array with subscript values from -2 to 7.

Examples:

SELECT ('[0:2]={1,2,3}'::int[])[0];    --> 1
SELECT ('[-7:-5]={1,2,3}'::int[])[-6]; --> 2

You can query any index of an array, if it does not exist you get NULL.

Related:

Normalize array subscripts for 1-dimensional array so they start with 1


How to move data from big table There is a big table (5-6 million records). I have to move 90% of old records to other database(table). Solution?

Couple of additions to Rolando's suggestion.

If you're clearing out the old table, as well as populating new you could use the OUTPUT clause. Be mindful of the potential for log growth, consider a loop/batch approach if this may be a problem.

DELETE
    OldDatabase.dbo.MyTable
OUTPUT
    DELETED.col1
    , DELETED.col2
    , DELETED.col3
INTO
    NewDatabase.dbo.MyTable

BCP is a handy alternative to be aware of. Note this is using SQLCMD syntax.

:setvar SourceServer OldServer
:setvar SourceDatabase OldDatabase
:setvar DestinationServer NewServer
:setvar DestinationDatabase NewDatabase
:setvar BCPFilePath "C:\"

!!bcp "$(SourceDatabase).dbo.MyTable" FORMAT nul -S "$(SourceServer)" -T -n -q -f "$(BCPFilePath)MyTable.fmt"
!!bcp "SELECT * FROM $(SourceDatabase).dbo.MyTable WHERE col1=x AND col2=y" queryout "$(BCPFilePath)MyTable.dat" -S "$(SourceServer)" -T -q -f "$(BCPFilePath)MyTable.fmt" -> "$(BCPFilePath)MyTable.txt"
!!bcp "$(DestinationDatabase).dbo.MyTable" in $(BCPFilePath)MyTable.dat -S $(DestinationServer) -T -E -q -b 2500 -h "TABLOCK" -f $(BCPFilePath)MyTable.fmt


How to INSERT all entries to a map table I have a map table as

CREATE TABLE keymap
(
col1 int(11) unsigned NOT NULL,
col2 int(11) unsigned NOT NULL,
col3 int(11) unsigned NOT NULL,
FOREIGN KEY(col1) REFERENCES table1(col1) ON DELETE CASCADE,
FOREIGN KEY(col2) REFERENCES table2(col2) ON DELETE CASCADE,
FOREIGN KEY(col3) REFERENCES table3(col3) ON DELETE CASCADE,
PRIMARY KEY (col1,col2,col3)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci

What is the most efficient way to INSERT all possible combination of col1, col2, and col3?

The reason I am asking this simple question is that the table is big (tens of millions of rows) and I need to do INSERT regularly to fill the missing combinations (table1, table2, and table3 are regularly being enlarged).

APPROACH #1

If you never delete from table1, table2, or table3

STEP #1

Using a Cartesian Join, generate a script that makes individual inserts

SQL="SELECT CONCAT('INSERT IGNORE INTO keymap VALUES"
SQL="${SQL} (',col1,',',col2,',',col3,');') FROM"
SQL="${SQL} (SELECT col1 FROM table1) A,"
SQL="${SQL} (SELECT col2 FROM table2) B,"
SQL="${SQL} (SELECT col3 FROM table3) C;"

read -s pw ; echo ${pw} | less
DB=mydb
echo "SET foreign_key_checks = 0;"         > All_Key_Combinations.sql
mysql -uroot -p${pw} -D${DB} -ANe"${SQL}" >> All_Key_Combinations.sql

STEP #2

Review the script and make sure it's OK

head All_Key_Combinations.sql
echo
tail All_Key_Combinations.sql
echo
sleep 15
less All_Key_Combinations.sql

STEP 3

Execute All_Key_Combinations.sql

DB=mydb
mysql -uroot -p${pw} -D${DB} < All_Key_Combinations.sql

APPROACH #2

If you ever delete from table1, table2, or table3

STEP #1

Using a Cartesian Join, generate a script that makes individual inserts

SQL="SELECT CONCAT('INSERT INTO keymap VALUES"
SQL="${SQL} (',col1,',',col2,',',col3,');') FROM"
SQL="${SQL} (SELECT col1 FROM table1) A,"
SQL="${SQL} (SELECT col2 FROM table2) B,"
SQL="${SQL} (SELECT col3 FROM table3) C;"

read -s pw ; echo ${pw} | less
DB=mydb
echo "SET foreign_key_checks = 0;"         > All_Key_Combinations.sql
echo "TRUNCATE TABLE keymap;"             >> All_Key_Combinations.sql
mysql -uroot -p${pw} -D${DB} -ANe"${SQL}" >> All_Key_Combinations.sql

STEP #2

Review the script and make sure it's OK

head All_Key_Combinations.sql
echo
tail All_Key_Combinations.sql
echo
sleep 15
less All_Key_Combinations.sql

STEP 3

Execute All_Key_Combinations.sql

DB=mydb
mysql -uroot -p${pw} -D${DB} < All_Key_Combinations.sql

GIVE IT A TRY !!!


Innodb: Operating system error number 1117 in a file operation i installed apache and mysql 5.5 on windows 7 64 bit. it worked but today mysql can not start. when i see at error log i find this

120616 16:35:47 [Note] Plugin 'FEDERATED' is disabled.
120616 16:35:48 InnoDB: The InnoDB memory heap is disabled
120616 16:35:48 InnoDB: Mutexes and rw_locks use Windows interlocked functions
120616 16:35:48 InnoDB: Compressed tables use zlib 1.2.3
120616 16:35:48 InnoDB: Initializing buffer pool, size = 103.0M
120616 16:35:48 InnoDB: Completed initialization of buffer pool
120616 16:35:48 InnoDB: highest supported file format is Barracuda.
120616 16:35:50  InnoDB: Operating system error number 1117 in a file operation.
InnoDB: Some operating system error numbers are described at
InnoDB: http://dev.mysql.com/doc/refman/5.5/en/operating-system-error-codes.html
InnoDB: File name .\ib_logfile0
InnoDB: File operation call: 'Windows aio'.
InnoDB: Cannot continue operation.

i search "InnoDB: Operating system error number 1117 in a file operation." and find this link but i cant open mysql site.
what is problem?
what can i do?

from System Error Codes (1000-1299)

ERROR_IO_DEVICE
1117 (0x45D)
The request could not be performed because of an I/O device error.

It seems that you had a problem with your disks


Should fields that are set to Not Null also have a constraint refusing empty strings? Any field that I would expect a value (Like an orderNumber or customerNumber) I would set as not nullable.

If I'm expecting the value of the field to be not null, shouldn't I also always have a constraint that does not allow for zero length strings or space only strings?

If I'm expecting the value of the field to be not null, shouldn't I also always have a constraint that does not allow for zero length strings or space only strings

Yes.  Yes you should.

It's ANSI standard behavior to enable storing empty strings, but that doesn't mean it's a good idea.  And you especially should not allow both Null and '' in a column.

And note that because strings are compared without trailing spaces,

 check (SomeCol<>'')

suffices for both.


How to simplify a carry over values process? I have created a "sample case" here for Postgres 10.0 (actually I am using AWS equivalent for 10.1):

https://www.db-fiddle.com/#&togetherjs=EKTJ6eQ62V

where you can find the table:

create table test(l text, v1 integer, v2 real, monthly_date date);

insert into test
    values
    ('a', 2, 1.3, '2001-01-01'),
    ('a', 1, 2.2, '2001-02-01'),
    ('a', 5, 6.2, '2001-04-01'),
    ('b', 3, 9.0, '2001-03-01');

The expected output is:

l   v1  v2  monthly_date
a   2   1.3 2001-01-01T00:00:00.000Z
a   1   2.2 2001-02-01T00:00:00.000Z
a   0   2.2 2001-03-01T00:00:00.000Z
a   5   6.2 2001-04-01T00:00:00.000Z
a   0   6.2 2001-05-01T00:00:00.000Z
a   0   6.2 2001-06-01T00:00:00.000Z
b   3   9   2001-03-01T00:00:00.000Z
b   0   9   2001-04-01T00:00:00.000Z
b   0   9   2001-05-01T00:00:00.000Z
b   0   9   2001-06-01T00:00:00.000Z

In a monthly time range that goes from '2001-01-01' to '2001-06-01', if there are missing following months, they get filled with the previous month values. The only difference is in the column 'v1' where the value for the missing month gets replaced with 0.

The query that I am using at the moment is:

WITH
    md AS (
      SELECT *,
      LEAD(monthly_date) OVER (PARTITION BY l ORDER BY monthly_date) AS next_date
      FROM test
    ),
    calendar AS (
      select interval_date::date
      from generate_series('2001-01-01'::date, '2001-06-01'::date, '1 month'::interval) interval_date
    )
    select T.l, coalesce (m2.v1, 0) as v1, T.v2, T.interval_date as monthly_date
    from (
        SELECT m.l, m.v1, m.v2, c.interval_date
        FROM calendar c
        JOIN md m
            ON c.interval_date BETWEEN m.monthly_date AND
            (CASE WHEN m.next_date IS NULL THEN '2001-06-01' ELSE m.next_date - '1 month'::interval END)
    ) T
    LEFT JOIN md m2 ON m2.l = T.l AND m2.monthly_date = T.interval_date;

which works fine but it is using a "LEFT JOIN" to inject the value '0' for the column 'v1'. Is there a better (possibly more efficient) way to do that?

You should check it, I can't assure this is better in terms or efficiency.

with x as -- all possible combinations
(
    select distinct l, m
    from   test tt
    join   lateral (select m from
                    generate_series((select min(monthly_date) from test where l=tt.l),
                                     '2001-06-01'::date, '1 month'::interval) m) t1 on true
)
select    x.l,
          coalesce(test.v1, 0) as v1,
          coalesce(test.v2, (select v2 -- only executed for missing rows
                             from test
                             where l = x.l and monthly_date < x.m
                             order by l, monthly_date
                             desc limit 1)) as v2,
          m as monthly_date
from      x
left join test
on        test.l = x.l
and       date_trunc('month', monthly_date) = date_trunc('month', m)
order by  x.l, x.m;

l  | v1 | v2  | monthly_date
:- | -: | :-- | :---------------------
a  |  2 | 1.3 | 2001-01-01 00:00:00+00
a  |  1 | 2.2 | 2001-02-01 00:00:00+00
a  |  0 | 2.2 | 2001-03-01 00:00:00+00
a  |  5 | 6.2 | 2001-04-01 00:00:00+01
a  |  0 | 6.2 | 2001-05-01 00:00:00+01
a  |  0 | 6.2 | 2001-06-01 00:00:00+01
b  |  3 | 9   | 2001-03-01 00:00:00+00
b  |  0 | 9   | 2001-04-01 00:00:00+01
b  |  0 | 9   | 2001-05-01 00:00:00+01
b  |  0 | 9   | 2001-06-01 00:00:00+01

dbfiddle here


How are entries in index leaf node ordered? I am reading the book 'SQL performance explained' and when talking about indeces, it says that databases use doubly linked lists to connect index leaf nodes. Each node is stored in a database block and consists of different index entries.
It then says that the index order is maintained at two levels: one within each leaf node, and also within the different leaf nodes themselves (via the linked list), as you can see in the left side of the picture below.

My question is: I understand the advantages of using a doubly linked list for the blocks so that when inserting new blocks it's easier to maintain the order (it's a matter of moving around some pointers). However, within the block itself, if the order is maintained, how is that performant?   Assuming than in a block there are a lot of entries, if one were to insert a new entry in the block, wouldn't that be really unperformant (because there is no data structure such as a doubly linked list).

The actual data in a page is not necessarily in any particular order.
The logical order of data in any index 8k page is determined in the row offset array.
https://www.sqlservercentral.com/blogs/does-a-clustered-index-really-physically-store-the-rows-in-key-order


How does order of index affect performance in SQL Server? I am wondering how does order of index affect performance (ASC, DESC) ? If it affects, why ? And which order should I choose ?

In this article someone looked at the performance of an index and the ordering of the data in the index.

Summary
As we have shown creating an index in ascending or descending order does not make a big difference when there is only one column, but when there is a need to sort data in two different directions one column in ascending order and the other column in descending order the way the index is created does make a big difference.


What's the term for a schema that has field+value column rather than individual columns? I sometimes see a schema pattern where instead of:

|id|mystring|myinteger|mysecondstring|
| 1|hello   |        1|world!        |

the table looks like:

|field         |value |
|mystring      |hello |
|myinteger     |     1|
|mysecondstring|world!|

i.e. the columns are not homogenous, and essentially not typed (everything must be a string, so that string values are possible).

Does this style of schema have a name?

(I would like to convince people to not use this, and it would help if I can find articles about it, but that's very difficult without knowing what it's called).

AFAIK it's referred to as the Entity-Attribute-Value (EAV) model


What is the best way to include a large amount of configuration data in a SQL project? I have a BIDS SQL Server Database Project with a database that I publish dynamically to various servers (SQL2016). It initially starts blank and everything is parameterized. Everything is in Azure DevOps and deploys to places.

I want to include/pack in (mostly static) configuration data for a few tables. Initially I used the script wizard to script the tables, then just put that script text in my post-deployment file (InitConfigs.PostDeployment1.sql).

I've come across one config table, when scripted, is a nearly 600mb script. The size isn't the problem, it just seems like there must be a proper/better way to embed it in the project.

Some things I've tried:

Export to flat file and embed somehow. - Seems messy and I have to figure out a way to import it in my post deployment script
Export single table bacpac - Doesn't seem to work because it tries to include entire database
Use Script wizard to generate a giant *.sql file. - I can add to the project and reference with SQLCMD :r. Seems odd.

Is there some sort of data package dacpac/bacpac-style file that can wrap everything up nicely and can be easily imported? Perhaps compressed too?

Your intuition is right here - it's not common to have a 600 MB SQL script as part of your SSDT project in source control.

Especially in the world of distributed version control systems, like git, you're talking about potentially numerous copies of that file, on various machines, and tracking the history of it in detail.

However, there is no "built-in" way of dealing with static data other than post deploy scripts, as you've noticed:

SQL SSDT Database Projects: Source Control Lookup Data

One solution would be to create a one-time-use SSDT project, and all it has is your configuration data scripts.  Then you can produce a dacpac file from that (which is really just a compressed folder), and "reference" that dacpac in your main project.

The advantage of this approach is that you get a compressed deploy script, but it's in a format that easily fits with your existing deployment pipeline (rather than just throwing the script file into a .zip archive).

Here's what that looks like.

I have an SSDT project with the AdventureWorks sample database schema in it.  The resulting dacpac is 80 KB:

I'll create a second project, and all it contains is a Post Deploy script, which references my static config data script:

Post_Person.ContactType.sql is a 10 MB script file with a bunch of insert statements
it's configured as "Build Action" = "None" and "Copy to Output Directory" = "Do Not Copy"

This produces a dacpac file that is 34 KB.

Based on that compression ratio, which depends a lot on your data, your 600 MB could get as small as 2 MB (there is a lot of repetition in a script like this, which lends itself to good compression).

Then you can add the dacpac as a "Database Reference" in your main SSDT project:

At this point, you could discard the "InitScripts" project.  Or you could store it in a separate standalone repository, that less people will need, in case you ever need to update and regenerate the dacpac.

Note that, in the end, your build / deploy process will need to deploy both dacpac files - your "main" one, and the "InitScripts" one only on initial setup (or you could deploy it every time, and have the init scripts check for existing data before taking any action).


How to "UPDATE table SET col = (CASE WHEN ... THEN (SELECT col FROM table ORDER BY col DESC LIMIT 1) END)"? How would I update a column using a CASE expression where the inner THEN clause needs to calculate a value based on that same column of other rows in the same table?

i.e. Here's a list table with six children ordered by position in ascending order.

CREATE TABLE list (
    id int unsigned NOT NULL AUTO_INCREMENT,
    parent_id int unsigned,
    position int unsigned,
    PRIMARY KEY(id)
);

-- parents
INSERT INTO list (position) VALUES (0),(1);
-- parent 1 children
INSERT INTO list (parent_id, position) VALUES
  (1,0), (1,1), (1,2), (1,3), (1,4), (1,5);
-- parent 2 children
INSERT INTO list (parent_id, position) VALUES
  (2,0), (2,1), (2,2), (2,3), (2,4), (2,5);

I'm trying to move one of the children to another parent. I need to adjust the old sibling positions to close the gap and set the position of the moving item to be last in the new parent's children (last being the one with the highest position).

For the reparenting operation, I have the below query. The subquery here seems to be ignored and the moving item's position isn't changed. I've tried other subqueries and functions like COUNT(position) and even CTEs. Most other options I've tried gave errors though.

-- move row 11 as a child of row 1, readjusting row 2's children
UPDATE list AS l1
  JOIN list AS l2
  ON l1.parent_id = l2.parent_id AND l1.position > l2.position AND l2.id = 11
  SET l1.position = (CASE
    -- reposition old siblings
    WHEN l1.parent_id = l2.parent_id AND l1.position > l2.position THEN l1.position - 1
    -- reposition self to position +1 more than row 1's highest child
    WHEN l1.id = 122 THEN (SELECT position FROM steps WHERE parent_id = 1 ORDER BY position DESC LIMIT 1)
    END),
  l2.parent_id = (CASE WHEN l2.id = 11 THEN 1 END);

I'm trying to do this all in one query (unless such a query would be terribly inefficient). The above query is the only way I've found to update both the position and parent_id for only the items that need those changes. The way it's set up, I think the l2.parent_id = part is being ignored by the sibling items, which is desired and seems like it would be efficient.

I'm not sure of your reasoning for wanting to do it within a single query, but the one thing I would ask though is that you think about the maintenance of such a query - you may not be the only person who has to support this.
It took me a while to understand what your query was doing, and in the end I ignored it completely and continued based upon the question you were asking. It would have been much simpler, easier to understand and support if you used a single statement for each step and then wrap it in a transaction for consistency. Depending on the number of rows in the table, it might even be more performant as you won't need to join the table to itself a few times.
However the below will achieve what you want:
CREATE TABLE list (
    id int unsigned NOT NULL AUTO_INCREMENT,
    parent_id int unsigned,
    position int unsigned,
    PRIMARY KEY(id)
);

-- parents
INSERT INTO list (position) VALUES (0),(1);
-- parent 1 children
INSERT INTO list (parent_id, position) VALUES
  (1,0), (1,1), (1,2), (1,3), (1,4), (1,5);
-- parent 2 children
INSERT INTO list (parent_id, position) VALUES
  (2,0), (2,1), (2,2), (2,3), (2,4), (2,5);

WITH a AS
(
    SELECT parent_id, MAX(position) as max_position
      FROM list
  GROUP BY parent_id
)
   UPDATE list AS b
     JOIN a
       ON a.parent_id = b.parent_id
LEFT JOIN list AS c
       ON c.parent_id = b.parent_id
      AND c.position > b.position
      SET b.parent_id = 1
        , b.position = a.max_position + 1
        , c.position = c.position - 1
    WHERE b.id = 11;

SELECT *
  FROM list
 ORDER BY parent_id, position;

Below are the results when moving a item that is not the last child of a parent.
Before

After

Below are the results when moving a item that is the last child of a parent.
Before

After


Shrink Log File without breaking Log Chain What is the best way to shrink a log file without breaking the log chain?  I realize you can do something like:

alter database yourDb
set recovery simple
go

dbcc shrinkfile(LogFileName, 0)
go

alter database yourDb
set recovery full
go

But I really don't want to break the log chain.  Is there any way to accomplish this?

I vaguely recall you have to do something like this, since the SQL Server log file is written cyclically.

Make a log backup. This marks the active log space for reuse.
Shrink the log file. This will shrink it down to the end of the last virtual log file in use (which will probably leave it larger than what you wanted to shrink it to).
Since the end of the log file will now be in use, given that's where it stopped shrinking due to a VLF being in use, SQL Server will wrap back to the beginning of the log file for further log writes.
CHECKPOINT to make sure dirty pages for active transactions are flushed, then make a backup again. (The CHECKPOINT might not be necessary - you can try without first.)
Shrink the log file again. You should be able to bring it down to where you want.

If that still doesn't work, check DBCC OPENTRAN, and the log_reuse_wait column in master.sys.databases.


Auto vacuuming high-write, high-update, and mostly read table types What are good auto vacuum settings (recommendations) for tables like:

High Write Table Insert load
Range between 30-10,000 inserts in a day. The table can idle for weeks without load, but can get bursts of inserts at least three times a week.

High Update Table
It uses partition table data, is 3-8 times the size of my table from a single insert.

High Write Table
A single row gets updated only once, but bursts of unique key updates in a day
and needs to be updated it could be 30-10,000 key update.

High Read Table
Most tables are high read tables set to fill factor 80 for my data warehouse, housing table that came from the computation of High Update Table

My deletes happen monthly and in batches. Everything relating to the key gets deleted or moved as backup.
Currently my fill factor is set to be 10-20 for high update table.
Using TDS db.t3.large but I switch to db.t3.micro during low traffic.
Also, does setting fill factor really low slow down selects?

That question is too broad, but here are some pointers about configuring autovacuum:

For tables that receive bulk inserts, run an explicit VACUUM afterwards or use PostgreSQL v13.

For tables with many updates, a fillfactor of 70 to 90 (depending on row size) is a good thing if no updated columns are indexed. 10 or 20 is just a crazy waste of space.

There is no need to care about autovacuum for tables that are only read.

With mass deletes, there is nothing much you can do. If you can use partitioning, that pain may vanish completely.

Of course low fillfactor will have a negative impact on query performance; that is the price you are paying for more efficient data modifications:

For sequential scans, the impact is clear: you have to read all that empty space too.

For index scans that read only a single row, there will be no performance impact.

Index scans that read several rows are somewhere in the middle, since they will have to read more blocks if fillfactor is low, because the rows will be spread across more blocks.

Don't forget the impact on caching efficiency: If your blocks consist mostly of air, the RAM used for caching will also mostly contain dead space.


The definition of object '[sp_name]' has changed since it was compiled on fail-over to mirror database We have two SQL 2012 database servers configured in a mirror with a witness for automatic fail-over.

Yesterday the primary server suffered a HDD degradation which triggered the fail-over, however when the secondary server became the principle we started to see many errors on execution of some SPROCs.

  The definition of object '[sp_name]' has changed since it was compiled

I understand that this can be resolved by running sp_recompile on the new principle, which would force a recompile on all SPROCs, functions and triggers.

This is not ideal as it requires manual intervention on fail-over. Is this a problem I can resolve through amending the configuration, or is it a known SQL Server bug?

Which SQL Edition are you using? Sometimes the engine couldn't refer the metadata catalog resulting in such error. This was identified as a bug. But you've workaround to resolve this issue. If You could recompile the stored procedure the next time it runs using sp_recompile.

The details of compile and re-compile is discussed below.

Recompile a Stored Procedure

Update Stored Proc in SSMS and executing a separate application which calls the procedure. You need to end the session in which you are editing the procedure. The changes are only visible in the editing session until they are committed.

Check the link below:

The definition of object 'name' has changed since it was compiled


SQL3550W: ... target column has been defined as GENERATED ALWAYS I am trying to export + import data from a table in a db2 database to another.
But while running the export command I am getting the following error.

SQL3550W  The field value in row "26" and column "17" is not NULL, but the
target column has been defined as GENERATED ALWAYS.

and

SQL3185W  The previous error occurred while processing data from row "26" of
the input file.

Why is this error coming and how can I fix it.

The Table

CREATE TABLE COMPANY_CLIENT (
        CLIENTGUID CHAR(36) NOT NULL,
        TYPECODE VARCHAR(2) NOT NULL,
        COMPANYNAME VARCHAR(150),
        LASTNAME VARCHAR(150),
        FIRSTNAME VARCHAR(150),
        MIDDLEINITIAL VARCHAR(150),
        PREFIX VARCHAR(10),
        SUFFIX VARCHAR(10),
        SEX VARCHAR(10),
        DATEOFBIRTH TIMESTAMP,
        DATEOFDEATH TIMESTAMP,
        TAXID VARCHAR(60),
        EMAIL VARCHAR(300),
        XMLDATA CLOB(1073741824) INLINE LENGTH 276,
        UPDATEDGMT TIMESTAMP,
        LEGALRESIDENCECOUNTRYCODE VARCHAR(3),
        LASTNAME_LOWER VARCHAR(150) GENERATED ALWAYS AS ((LOWER(LASTNAME))),
        FIRSTNAME_LOWER VARCHAR(150) GENERATED ALWAYS AS ((LOWER(FIRSTNAME))),
        COMPANYNAME_LOWER VARCHAR(150) GENERATED ALWAYS AS ((LOWER(COMPANYNAME))),
        TAXID_LOWER VARCHAR(60) GENERATED ALWAYS AS ((LOWER(TAXID))),
        ALTERNATENAME1 VARCHAR(150),
        ALTERNATENAME2 VARCHAR(150),
        ALTERNATENAME3 VARCHAR(150),
        ALTERNATENAME4 VARCHAR(150),
        ALTERNATENAME5 VARCHAR(150),
        ADDITIONALPREFIX VARCHAR(10),
        ADDITIONALSUFFIX VARCHAR(10),
        TAXIDTYPE VARCHAR(10),
        TITLE VARCHAR(10),
        MARITALSTATUS VARCHAR(10),
        BIRTHCOUNTRYCODE VARCHAR(3),
        CITIZENSHIPCOUNTRYCODE VARCHAR(3),
        BIRTHREGIONCODE VARCHAR(10),
        PRIMARYPHONE VARCHAR(60),
        TEXTFIELD1 VARCHAR(765),
        TEXTFIELD2 VARCHAR(765),
        CHECKBOX1 VARCHAR(9),
        CHECKBOX2 VARCHAR(9),
        RADIO1 VARCHAR(10),
        RADIO2 VARCHAR(10),
        COMBO1 VARCHAR(10),
        COMBO2 VARCHAR(10),
        DATE1 TIMESTAMP,
        DATE2 TIMESTAMP
    )

Using the same IXF files created by db2move export, you can manually load the data using db2 import with the MODIFIED BY IDENTITYOVERRIDE parm for those tables that require it.


Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement A few times a day, it is not able to connect and the error mentions:

  Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.

There is is large Microsoft article that suggests: To resolve this issue, upgrade the .NET Framework 4.5.1 to the Microsoft .NET Framework 4.5.2

Our SQL Server 2012 is running on VMWare and we have not been able to get much help from them even though we provided a lot of info that points to their implementation of the vsocklib.dll being a non-IFS Winsock.

Have you guys ever had to deal with that particular scenario ? Has 4.5.2 this fixed the problem ?

We had a situation where our sql server, a vm, went down with the ESX host it was on.

The vm rebooted, but TCP 1433 would not let us connect with the posted error.

We confirmed that all TCP settings were as intended in SQL Server Configuration, but ultimately the resolution was to change the IP of the server. Weird right?

My only thought is that something at the VMWare level was causing traffic to be dropped, but had no view into that platform as it was in the 'cloud.'

I don't know if is useful, but I hope someday it helps someone else.

Other notes are that SQL started fine, nothing in error log, with agent disabled we could access sql via pipes and udp 1434.


Does a SQL Server job run on the server or the local machine? Does a SQL Server job always run on the server, or will it run in the context of the local machine, similar to a DTS package run from Enterprise Manager on the user's machine?

The job in question calls a DTS package. The package succeeds when run locally; the job fails whether run manually or scheduled.

When a SQL Server agent job is run it is run on the SQL Server machine not on your workstation.

It sounds like you don't have the drivers setup correctly on the SQL Server.

Keep in mind that if you are using a 64bit machine you'll need 64bit drivers and god only knows how DTS will handle 64bit stuff.


How extract cldr timezones from xml file to store in a DB table? In our DB, I need each location to be associated with a "timezone".

These timezones are in https://github.com/unicode-org/cldr/blob/release-36-1/common/supplemental/metaZones.xml

I want to make a table of the timezone name strings in that xml file, so that we will have a "timezone table ID" that I can send as part of location data to client software. The client contains the code that uses timezone to determine time offset (e.g. UTC+1) for a given datetime at that location.

That is, instead of storing/sending/parsing timezone strings, for some (internal) purposes we will use this table ID.

How can I extract the list of timezone name strings from that xml file, and store it in a DB table? (Obviously I could write a little program to do this, but someone must have done this already.)

Or perhaps somewhere else in each cldr release, there is a (plaintext) list of those timezones?

Or CSV formatted info, that would be easier to massage manually.

Partial answer: how to convert the relevant info to csv.

Edit the file to remove everything except this section:

<metazoneInfo>
    <timezone type="Africa/Abidjan">
        <usesMetazone mzone="GMT"/>
    </timezone>
...
    <timezone type="PST8PDT">
        <usesMetazone mzone="America_Pacific"/>
    </timezone>
</metazoneInfo>

Use an XML to CSV converter. (I used https://www.convertcsv.com/xml-to-csv.htm)

Result:

usesMetazone/0/_mzone,_type,usesMetazone/0/_to, ...
GMT,Africa/Abidjan,,,,,,,,,,,,,,,,,,,,,,,,,
GMT,Africa/Accra,,,,,,,,,,,,,,,,,,,,,,,,,
...
Wallis,Pacific/Wallis,,,,,,,,,,,,,,,,,,,,,,,,,
America_Pacific,PST8PDT,,,,,,,,,,,,,,,,,,,,,,,,,

The first line describes the XML attributes extracted. The remaining lines start with "metazone,timezone".

The additional columns describe historical changes; e.g. "from date, to date, metazone". That is, some time zones used different metazones over specified date ranges.

Care must be taken when interpreting any timezone that has such historical data. The metazone in first column is probably NOT the CURRENT timezone; this is easier to see in the XML, where "metazone 0" is the first metazone mentioned, hence probably the EARLIEST one (it has a "to" date but no "from" date).

In that situation, I think the CURRENT metazone is always the LAST one mentioned (column farthest to right, that has a .../_mzone header).

For example, timezone "Africa/Algiers":

XML:

<timezone type="Africa/Algiers">
    <usesMetazone to="1977-10-20 23:00" mzone="Europe_Western"/>
    <usesMetazone to="1979-10-25 23:00" from="1977-10-20 23:00" mzone="Europe_Central"/>
    <usesMetazone to="1981-05-01 00:00" from="1979-10-25 23:00" mzone="Europe_Western"/>
    <usesMetazone from="1981-05-01 00:00" mzone="Europe_Central"/>
</timezone>

CSV:

Europe_Western,Africa/Algiers,1977-10-20 23:00,1979-10-25 23:00,1977-10-20 23:00,Europe_Central,1981-05-01 00:00,1979-10-25 23:00,Europe_Western,1981-05-01 00:00,Europe_Central,,,,,,,,,,,,,,,,

In Spreadsheet, we see that rightmost two columns are:

usesMetazone/3/_from   usesMetazone/3/_mzone
1981-05-01 00:00       Europe_Central

So current metazone is Europe_Central, NOT Europe_Western from first column.

Except for this issue getting the correct "current" metazone, the first two columns can be easily imported to DB: stackoverflow thead.


How do I resolve Postgresql error, "could not determine which collation to use for string comparison"? When ever I run this SQL script I get:

  ERROR:  could not determine which collation to use for string comparison

This is what is on line 34:

CREATE TEMPORARY TABLE weight_options AS
  SELECT
    io.lot_id
    , io.vin
    , map.fkey_style AS chrome_styleid
    , SUM( CASE WHEN co.code = io.code THEN 1 ELSE 0 END ) AS exact_option
    , SUM( CASE WHEN length(io.code) = 2 AND co.code LIKE io.code || '%' THEN 1 ELSE 0 END ) AS appx_option
    , (
      SUM( CASE WHEN co.code = io.code THEN 2 ELSE 0 END )
      + SUM( CASE WHEN length(io.code) = 2 AND co.code LIKE io.code || '%' THEN 1 ELSE 0 END )
    ) AS option_weight
  FROM inventory.options AS io
  -- options *must* have same index as inventory.vehicles
  JOIN chrome_vinmatch_best_match AS vd
    USING ( vin )
  JOIN chrome_vinmatch.style AS map
    ON ( vd.chrome_patternid = map.fkey_pattern )
  JOIN chrome_nvd.options AS co
    ON ( io.code = co.code AND map.fkey_style = co.fkey_style AND io.code IS NOT NULL )
  GROUP BY io.lot_id, io.vin, map.fkey_style
;

I just did a migration from an install of 8.4.11 to an install of 9.1.3 using pg_dump.

Found the error in the Release Notes for 9.1.2

  Make contrib/citext's upgrade script fix collations of citext columns
  and indexes (Tom Lane)

  Existing citext columns and indexes aren't correctly marked as being
  of a collatable data type during pg_upgrade from a pre-9.1 server.
  That leads to operations on them failing with errors such as "could
  not determine which collation to use for string comparison". This
  change allows them to be fixed by the same script that upgrades the
  citext module into a proper 9.1 extension during CREATE EXTENSION
  citext FROM unpackaged.

  If you have a previously-upgraded database that is suffering from this
  problem, and you already ran the CREATE EXTENSION command, you can
  manually run (as superuser) the UPDATE commands found at the end of
  SHAREDIR/extension/citext--unpackaged--1.0.sql. (Run pg_config
  --sharedir if you're uncertain where SHAREDIR is.)

So I looked in that file for the UPDATE commands and found these:

UPDATE pg_catalog.pg_type SET typcollation = 100
WHERE oid = 'citext'::pg_catalog.regtype;

UPDATE pg_catalog.pg_attribute SET attcollation = 100
WHERE atttypid = 'citext'::pg_catalog.regtype;

UPDATE pg_catalog.pg_index SET indcollation[0] = 100
WHERE indclass[0] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[1] = 100
WHERE indclass[1] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[2] = 100
WHERE indclass[2] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[3] = 100
WHERE indclass[3] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[4] = 100
WHERE indclass[4] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[5] = 100
WHERE indclass[5] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[6] = 100
WHERE indclass[6] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);

UPDATE pg_catalog.pg_index SET indcollation[7] = 100
WHERE indclass[7] IN (SELECT oid FROM pg_catalog.pg_opclass
                      WHERE opcintype = 'citext'::pg_catalog.regtype);


What is the migration/deployment path from a LocalDB solution to Enterprise instance? Given the extant definition for SQL Server 2012's LocalDB as

  SqlLocalDB is a local, low-overhead database engine of SQL Server 2012
  (and future versions) that allows developers to focus on development
  rather than instance configuration or security.

I was curious what the path from development in LocalDB to a production instance would look like, especially in light of the issues with user instances (ca. SQL 2005) and trying to promote one of them to production and devs would lose track of which instance they were actually trying to promote. A quick search on StackOverflow betrays such issues in various forms. Does LocalDB improve/streamline deployment in such circumstances?

I was curious what the path from development in LocalDB to a production instance would look like

...

  Does LocalDB improve/streamline deployment in such circumstances?

Not really that much difference, to be honest. While User Instances is no longer a factor, it is still very easy for a developer to create a bunch of SqlLocalDb instances and lose track of which one holds the "true" version of the truth.

What LocalDb does is eliminate the need to set up a full service-based instance of SQL Server (Express or otherwise), and reduces the complexity of the security model. It is still up to the developer or the team to implement sound development practices with regard to source control and promotion. As expressed in the comments, there are several ways to promote code from a local instance to production (hopefully through some kind of QA/test systems first):

backup / restore
database projects via VS/SSDT
3rd party comparison tools like SQL Compare
manual placement of schema / scripts / data in source control
manual generation of scripts at deployment time
import/export data wizards (caveat: I have not tried the wizard against SqlLocalDb)

(I've intentionally left off detach / attach because backup / restore is much safer. With detach if something happens to the .mdf file during or after detaching, you now have zero copies of your database. If a backup goes wrong, you still have the source.)

Whether any of these are more or less prone to the same kinds of problems as today, depends more on team discipline and established procedures than whether they use Express or LocalDb for local development. IMHO.

The nice thing LocalDb provides over user instances is that if you connect to a single instance of LocalDb you don't end up with the specific case where every time you use AttachDbFileName you get a new copy of the database. The most problematic part there is you change a table in one instance, then get an error from your application because it's connected to a copy that doesn't have your table change. As you've pointed out, this has led to a never-ending stream of confusion and similar questions on SO. In fact SqlLocalDb still supports AttachDbFileName, but I think it's going to be very uncommonly used, if ever.

With LocalDb it is less likely that you run into these problems, but they still exist. It's a different tool with some advantages but it can still be used inappropriately.


InnoDB error: exceeded log capacity...but only during import I see this error in mysql.log:

InnoDb error: the age of last checkpoint is...which exceeds the log group capacity etc..

I think I understand it, but the fact is that it's happening only during a night operation at minimum load,  consisting in a 100MB db import.

I import the result of mysqldump --opt, with the purpose of duplicating a db for testing.

The error suggests an increase in the log file x 10 times.

Considering that in no way I'm reaching this limit during normal daily time, I wonder if there is another way of dealing with the problem, rather than changing the log file size and restarting mysql...

The speed of the import operation is not important.

Mysql v5.1.69

You are probably trying to import very large rows. The 5 MB default log file size (10MB in total for the log) may be too small in many cases. The default for 5.6 is 50MB (100Mb in total), which is probably a more sane default in most cases.

If you do not have much load, the only problem of augmenting the log is having to handle a larger set of files, nothing else should change (this is not true in heavy-write environments). it will also make the load process much faster, assuming you have enough memory size for the buffer pool. You may want to change the innodb_log_buffer_size if you are loading lots of large rows.

Check a tutorial on how to change the transaction log size in MySQL 5.1.


Can creating indexes colide with future database upgrades? I have a mysql Database with very slow querys. I am using the slow query log to monitor those.

This is a database created by a third party software that we're using. Their developers didn't do any kind of optimization at this level on the database. Some indexes would certainly improve performance.

My main concern is that my indexes may collide with future database upgrades (structural or index creation).

By nature indexes are not invasive in the DB structure/data, but upon table change they may? prevent these from happening successfully.

Can I add indexes safely, with the certainty that they will be handled (maybe even dropped) if changes need to be done, or should I avoid that?

What I would do is maintain a SQL script for the indexes that you've added to the "virgin" database. Store this script in a version control system, as you'll be adding indexes to fix performance problems.

I would employ a naming strategy to make it easy to identify which indexes are "yours" and not part of the database as delivered. I'm not sure what features MySQL has, but in Oracle for example you can store these indexes in a different tablespace making it very easy to identify them as "added".

When your vendor supplies an upgrade, first remove your added indexes, then apply your vendor's upgrade and afterwards execute your script to add your indexes.

Of course, you should also have a QA environment where you can test all of this before applying it to your production system. You'll need to do this to discover if any indexes will conflict with indexes that the vendor may create in a future version.


Continuously backup/restore selected tables I'm backing up production data locally to perform experiments and investigate production bugs.

I need only a handful of tables from product DB. I'm using generic mysqldump and now facing a problem where backing up & restoring takes around 20 minutes.

My question is: is there a way to incrementally amend the data I already have with what appeared on production since the last time I dumped the selected tables?

You have few options to consider but first I hope you're not troubling your production server but may be a slave of it to dump out things? (things may get bad there.)

If you are only needing data that's changed since last backup, and your tables does have modified date as one of the columns (consider to add if you can to ease the process?) you can:

mysqdump --where=' modified_date_column>="Last_bkup_dttime"' XYZ tabll1 table2 table3 > tables.sql

How about setting a slave replicating only tables of choice?
Did you know about mydumper/loader? That can take faster dumps than mysqldump.
Are you backing up your data daily? If yes then why don't you setup an automatic nigtly restore process that restores your dev with latest backup?
You may also choose to play the binary logs from production and discard other tables:

Generate sqls from each of the binlogs:
mysqlbinlog --db=dbofyourchoice binlog.000xxx > binlog.000xxx.sql

Play that all those sqls with --force to ignore errors.
mysql dbofyourchoice --force < binlog.000xxx.sql

and ofcourse partial backups from xtrabackup is there...


Subtract multiple quantities in table2 from table1 How can I subtract the quantities in table2 from table1?
I'm using SQL Server 2014.
Table1
P_ID     name       quantity
1       carrot        100
2       eggplant      100
3       Avocado       100
4       Cabbage       100
5       peppers       100

Table2
   P_ID      name       quantity
    1       carrot        3
    2       eggplant      12
    5       peppers       7
    2       eggplant      8
    1       carrot        25
    5       peppers       13

The final result should be like this:
Table1
P_ID        name     quantity
1            a         73
2            b         80
3            c         100
4            d         100
5            e         80

How can I do this? Is this even possible?

Here is an example of how that could be done

--Demo setup
Declare @Table1 table (P_ID int, [name] varchar(1), quantity int)
INSERT INTO @Table1
    (P_ID, [name], quantity)
VALUES
    (1, 'a', 100),
    (2, 'b', 100),
    (3, 'c', 100),
    (4, 'd', 100),
    (5, 'e', 100)
;

Declare @Table2 TABLE
    (P_ID int, [name] varchar(8), quantity int)
;

INSERT INTO @Table2
    (P_ID, [name], quantity)
VALUES
    (1, 'carrot', 3),
    (2, 'eggplant', 12),
    (5, 'peppers', 7)
;

--The solution
UPDATE t1
SET t1.quantity = t1.quantity - t2.quantity
FROM @Table1 t1
JOIN @Table2 t2
    ON t2.P_id = t1.P_ID

SELECT *
FROM @Table1

| P_ID | name | quantity |
|------|------|----------|
| 1    | a    | 97       |
| 2    | b    | 88       |
| 3    | c    | 100      |
| 4    | d    | 100      |
| 5    | e    | 93       |

Update based on OP's comment about grouping table2

Declare @Table1 table (P_ID int, [name] varchar(1), quantity int)
INSERT INTO @Table1
    (P_ID, [name], quantity)
VALUES
    (1, 'a', 100),
    (2, 'b', 100),
    (3, 'c', 100),
    (4, 'd', 100),
    (5, 'e', 100)
;
Declare @Table2 table
    ([P_id] int, [name] varchar(20), [quantity] int)
;

INSERT INTO @Table2
    ([P_ID], [name], [quantity])
VALUES
    (1, 'carrot', 3),
    (2, 'eggplant', 12),
    (5, 'peppers', 7),
    (2, 'eggplant', 8),
    (1, 'carrot', 25),
    (5, 'peppers', 13)
;

;WITH Table2Grouped
AS (
    SELECT P_ID
        ,SUM(cast(quantity AS INT)) AS quantity
    FROM @Table2
    GROUP BY P_ID
    )
UPDATE t1
SET t1.quantity = t1.quantity - t2.quantity
FROM @Table1 t1
JOIN Table2Grouped t2
    ON t2.P_id = t1.P_ID

SELECT *
FROM @Table1


Is there a way to find out who changed the password for a login? I am trying to find out who changed the password for a login in SQL Server 2008 R2.

I have already checked default trace - and it does not log that event. The default trace will include these security-related events:

/*
    Audit Add DB user event
    Audit Add login to server role event
    Audit Add Member to DB role event
    Audit Add Role event
    Audit Add login event
    Audit Backup/Restore event
    Audit Change Database owner
    Audit DBCC event
    Audit Database Scope GDR event (Grant, Deny, Revoke)
    Audit Login Change Property event
    Audit Login Failed
    Audit Login GDR event
    Audit Schema Object GDR event
    Audit Schema Object Take Ownership
    Audit Server Starts and Stops
*/

Also, looked into transaction log backup to find that out, but no luck.

Is there any other way to find it out ?

Also, I am aware that a server side trace will help, but unfortunately in our server side trace, we did not include the Audit Login Change Password Event.

Best article that I found is from Aaron Bertrand : Tracking Login Password Changes in SQL Server

My article will help if you set it up in advance, but not when the event happened in the past and you didn't have any kind of auditing mechanism set up.

There is still hope, though. Let's say I did this:

CREATE LOGIN flooberella WITH PASSWORD = N'x', CHECK_POLICY = OFF;

This information is in the default trace under EventClass 104 (Audit Addlogin Event). However, if I change the password using either of these methods:

ALTER LOGIN flooberella WITH PASSWORD = N'y';

EXEC sp_password N'y', N'z', N'flooberella';

These events are not captured by the default trace, for obvious security reasons - it should not be possible for anyone with access to the default trace to figure out what someone else's password is, nor do they want to make it easy to even find out that a password has been changed (polling the frequency of these events, for example, can reveal certain properties of your security strategy).

So what else can you do? While this relies on the information still being in the log, and it also relies on using an undocumented DBCC command against a system database (you may wish to back up master and restore it elsewhere), you can get some information from the transaction log, e.g.:

DBCC LOG(master, 1);

This will yield, for the above two commands, rows with the following (partial) information:

Current LSN             Description
======================  ======================================================================
000000f2:000001b8:0002  ALTER LOGIN;0x01050000000000051500000093a3bcd7a9f8fb1417ab13bce8030000
000000f2:000001b8:0004  Alter login change password;0x01050000000000 ... same sid as above ...

Doesn't seem like much, but now take that 0x portion of the description, and then do:

SELECT name FROM sys.server_principals
  WHERE sid = 0x01050000000000051500000093a3bcd7a9f8fb1417ab13bce8030000;

Smoking gun! This is the person responsible for that event.

Of course, if they use ALTER LOGIN syntax for all operations (which they should be using instead of sp_password), you can't distinguish between someone changing the default database and someone changing the password. You also can't tell (at least that I can see) which login this affected, only that this person changed a login. Jon seems to think that this information is in the log as well, but I failed to find it (unlike the time information, which somehow I scrolled right past).

There may be different answers for contained users in SQL Server 2012 - though I suspect password changes are still obfuscated in similar ways. Will leave that for a separate question.


Why doesn't FIND_IN_SET return on NULL values Can anybody explain why FIND_IN_SET isn't returning the row with the NULL value? To me it seems obvious, the string "test", isn't part of NULL, so it should return it?

http://sqlfiddle.com/#!9/389043/2

CREATE TABLE test (
  id INT AUTO_INCREMENT,
  result VARCHAR(255),
  PRIMARY KEY (`id`)
);

INSERT INTO test (id, result) VALUES (1, NULL), (2, 'abc'), (3, 'abc,test');

SELECT * FROM test
WHERE
  NOT FIND_IN_SET('test', result);

Well, NULL is not a string, not even an empty one. When you compare to NULL special operators are of need, like column IS NULL or the NULL-safe comparator <=>.

Usually this is solved with a query like

SELECT * FROM test
WHERE
  NOT FIND_IN_SET('test', result)
  OR result IS NULL;

I for one think, that the FIND_IN_SET() function shouldn't exist at all. It makes it seem like having comma separated values in a column is absolutely normal and quite common. But it isn't. You shouldn't store the values this way. You have a lot of disadvantages (see this post for a probably incomplete list) and you should really look into database normalization. Like Bill says in the linked post, comma separated values violate the very first normal form already.


Order By Case String Not Working I am trying to get my SQL search to work. What I am looking for is a search result that pulls information based on either a company's name or their stock trading ticker. If one would to type in M, I would expect:

MAT  - Mattel
MFST - Microsoft
MA   - MasterCard
MDLZ - Mondelez
MON  - Monsanto

It queries the "ticker" just fine, but how would I get it to allow both? If I search MO instead of "M", I would expect to see this:

MON  - Monsanto
MDLZ - Mondelez

I want it to give bias towards the "ticker", but also query the name if it is like the search term. Here is my code:

SELECT `stock_id`, `stock_ticker`, `stock_simpleName`
FROM `stocks`
WHERE `stock_ticker` = 'M'
GROUP BY `stock_ticker`, `stock_simpleName`
ORDER BY CASE
    WHEN `stock_ticker` LIKE 'M%' THEN 1
    WHEN `stock_simpleName` LIKE 'M%' THEN 2
    ELSE 3
END
LIMIT 5

I wrote a different query that easily pulled the "ticker" alone, but trying to query them both with bias towards one of the columns like stated above seems to be a bit difficult. My AJAX returns an error which obviously means it's returning a PHP error rather than the data that should be requested.

Is there a tweak for this to get it to work?

So I tweaked the code a bit by testing it in phpMyAdmin, and this is what worked:

SELECT `stock_id`, `stock_ticker`, `stock_simpleName`
FROM `stocks`
GROUP BY `stock_ticker`, `stock_simpleName`
ORDER BY CASE
    WHEN `stock_ticker` LIKE 'MO%' THEN 1
    WHEN `stock_simpleName` LIKE 'MO%' THEN 2
    ELSE 3
END
LIMIT 5

I just removed the WHERE Statement entirely. It works exactly like I needed it to. I thought WHERE was always required for any statement that succeeds the FROM table.column part of the query. `


How to convert time without time zone to timestamp without time zone in PostgreSQL? I received this error when trying to alter a column of type time to type timestamp:

PG::CannotCoerce: ERROR:  cannot cast type time without time zone to timestamp without time zone

It's not important to me how the existing times are converted, but I do need this column to be changed. How can I force this or cast this?

This is the SQL statement:

ALTER TABLE "students" ALTER COLUMN "time_since_missing_schedule_notification" TYPE timestamp USING CAST(time_since_missing_schedule_notification AS timestamp)

I'm using Rails/ActiveRecord and this was the Ruby code that generated the above SQL statement:

change_column :students, :time_since_missing_schedule_notification, 'timestamp USING CAST(time_since_missing_schedule_notification AS timestamp without time zone)'

When I connect to Heroku's PostgreSQL server, I get these versions:

psql (9.3.1, server 9.2.7)

Since time does not have a date component you need to provide a date. You can just add date and time values in the ALTER TABLE statement:

ALTER TABLE students ALTER COLUMN time_since_missing_schedule_notification
 TYPE timestamp USING ('2000-1-1'::date + time_since_missing_schedule_notification)


Attempting to Pivot a column of grades and coming up with NULL values I have Data in a table that i'm attempting to pivot from rows to columns, and the script im using is working, but the pivot is coming up with NULL for everything.

Here's the source data:

    Catalog Class Nbr   ID  Status  Grade
    281 82142   5000293644  Enrolled    1
    295 12128   1012642448  Enrolled    1
    296 12124   1012642448  Enrolled    4
    280 82141   5000289133  Enrolled    4
    295 10528   1012923963  Enrolled    1

And the Pivot Query:

      SELECT *
      FROM (
      SELECT [ID], [Catalog], [Grade]
      From CSNCombined.dbo.csnName
      ) as c
      PIVOT
      (
        SUM([Grade])
        FOR [Catalog] IN ([198], [281], [280], [295], [296], [297])
        )
        AS PVT

The query executes fine, but all values are NULL.

Change your query to this:

SELECT
     pvt.ID
    ,[198] = isnull([198],0)
    ,[281] = isnull([281],0)
    ,[280] = isnull([280],0)
    ,[295] = isnull([295],0)
    ,[296] = isnull([296],0)
    ,[297] = isnull([297],0)
FROM ( SELECT [ID], [Catalog], [Grade] From data ) as c
PIVOT (
    SUM([Grade]) FOR [Catalog] IN ([198], [281], [280], [295], [296], [297])
)AS PVT

or perhaps this:

SELECT
     ID = cast(pvt.ID as char(12))
    ,[198] = isnull(str([198]),'        -')
    ,[281] = isnull(str([281]),'        -')
    ,[280] = isnull(str([280]),'        -')
    ,[295] = isnull(str([295]),'        -')
    ,[296] = isnull(str([296]),'        -')
    ,[297] = isnull(str([297]),'        -')
FROM ( SELECT [ID], [Catalog], [Grade] From data ) as c
PIVOT (
    SUM([Grade]) FOR [Catalog] IN ([198], [281], [280], [295], [296], [297])
)AS PVT

to yield:

ID           198        281        280        295        296        297
------------ ---------- ---------- ---------- ---------- ---------- ----------
1012642448           -          -          -           1          4         -
1012923963           -          -          -           1         -          -
5000289133           -          -           4         -          -          -

The SQL value null represents the absence of data, so when you pivot and a cell as no data a null will perforce be generated as the cell value. It is your responsibility as the programmer to coalesce the null to whatever domain value is appropriate for missing data, when that makes sense.


Where can I find the SQL standard document? Where can I find a legal copy of the ISO SQL 2008 standard?

The ISO SQL 2008 Standard document ISO/IEC 9075-1:2008 Information technology -- Database languages -- SQL -- Part 1: Framework (SQL/Framework) is now freely available from the ISO website.


SQLIOSIM fails to run when using UNC path I'm trying to run SQLIOSIM on NAS cluster's NTFS shared folder.
Here's the command line:

  sqliosim.com -dir \\c16\vol1cifs -size 512 -d 600

But it repeatedly fails to create files during the setup due to following error:

  Error: 0x80070057  Error Text:   Description: Unable to get volume
  name for mount point \\c16\vol1cifs\

vol1cifs - NTFS shared folder, that can be opened and edited on the same client where
SQLIOSIM is installed, without any problem.
According to SQLIOSIM description it does supports UNC path.

What I'm doing wrong?

Thanks for help.
Sam

Well, technically I'd solved the problem, although there's still question about weird sqliosim behavior.

It appears that sqliosim do creates it's files in shared NAS folder (sqliosim.mdx, sqliosim.mdx) but for some reason I can't explain, it fails to resize files and aborts.

Workaround:
Create files before sqliosim run, with exactly the same size it expects,
I.e:
fstool file createnew \\SERVER\shared FOLDER\sqliosim.ldx [size in BYTES]

Repeat the same action for sqliosom.mdx
Now run sqliosim and make yousrlf a coffee. :)

Thank you.
Sam


MySQL console: Ctrl+C driving me nuts There is something in the MySQL console that drives me nuts. When I hit ctrl+c to cancel the current command being typed, the terminal exits.

In every terminal I know (*nix terminals, Python, PostgreSQL), ctrl+c cancels the current command and ctrl+d exits the terminal.

This issue has been reported at 2003, and bumped several times since.

Is there a way to:

Change this behaviour, or
Convince the MySQL dev team that this is really annoying?

At least version 5.6.14 of the client has the --sigint-ignore option to totally ignore the SIGINT handler. It appears to have been added on Oct 7th, 2004 -- so it's been around a while at least. I just tested it, and it does keep CTRL-C from cancelling the MySQL client. However, it would be nice if CTRL-C would also cancel the current command line buffer like it does on the BASH shell -- as the PostgreSQL CLI does. I've forked MySQL in order to see how hard it would be to implement some such thing. I'll post a Homebrew formula with a tarball link when/if it's ready.

UPDATE 1
Ever have one of those days? I created the promised patch, and then I decided to create a video to demonstrate it. It worked! However, I couldn't disable it! Well, it turns out that MySQL actually baked in the functionality to the core client last year! Yep, the A to this Q is to download at least MySQL 5.7.3-794-g901d27f's client. It has the functionality we all desired.

EXAMPLE
From the command line just add the --sigint-ignore flag:

mysql --host=127.0.0.1 --port=8900 --user=root -p --sigint-ignore DBNAME

Or, do like I did and create a MySQL config file for your user account at $HOME/.my.cnf (%USERPROFILE%/.my.cnf on Windows) with the following content:

[mysql]
sigint-ignore=TRUE

This will ensure that all mysql executions ignore the annoying Ctrl-C and behave like a good SQL client.


AlwaysOn AG, DTC with failover Problem: How can I run Distributed Transaction Coordinator (DTC) on all servers in an AlwaysOn Availability Group (AG)? I do NOT need to maintain transactions over failover/switchover events.

Setup: I have a Windows Failover Cluster (WSFC) with three Windows 2008 R2 servers where they are all running SQL 2012. Two servers are in one data center and are part of an AlwaysOn Failover Cluster (FCI), while the third server is in a second data center. The WSFC is a multi-subnet cluster. Here's a sketch of the setup:

I've been able to install and configure DTC to work between the two FCI nodes because they are on the same subnet and share storage. I have configured a couple of AGs and they have been working fine. This screenshot shows DTC installed on the FCI:

This screenshot shows that I can configure DTC on one of the FCI nodes (whichever is active):

I'd like to migrate an application that uses DTC onto this cluster and use an AG. I've read that DTC is not supported with AGs (Reference). I have not been able to find a way to configure DTC on the third node in the second data center. When I try to configure DTC on the third node, it seems to be unavailable, as shown in this screenshot:

In Brent Ozar's Free Setup Checklist PDF for Availability Groups he lists:

Cluster Installation...

29. If an FCI is involved, configure DTC per your Planning section decisions.

In the comments on SQL Server 2012 AlwaysOn Availability Groups Rock Brent says that "... nothing changes when AGs are in play. Just keep in mind that databases in an Availability Group don’t support transactional consistency when failed over together to another replica..."

This makes it seem that DTC can be used in Availability Groups as long as you understand that transactions will not be maintained in a AG switchover. I would not need it to maintain transactions from the FCI nodes. I would just need DTC available for the application to use in the case of a catastrophic disaster (where I lost my primary data center).

How do I configure DTC on my third node? Or, is it the case that I'm just out of luck when it comes to using AGs and an application that needs DTC?

UPDATE: The solution I've settled on is to use Log Shipping. However, in the case of a fail over, I still need DTC to be available on Node3. I've discovered that it becomes available by uninstalling the clustered MSDTC-MSSQLSERVERCLU instance of DTC that is shared between Node1 and Node2. Once removed, I can setup and configure a LocalDTC instance on Node3. Afterwards, I can reinstall the clustered MSDTC-MSSQLSERVERCLU instance. Doing the install sequence in that order seems to work. I've been running like that for a little while now and I have not discovered any ill effects. It seems like this would also work for running an AlwaysOn Availability Group. I understand that distributed transactions would not be preserved in an AG failover, I would just need new ones to work after the failover. But I haven't gone there...

Elijah. There's two separate questions here:
1. Is DTC supported with AlwaysOn Availability Groups?
You're using SQL Server 2012, and according to Microsoft's Documentation, that answer is no. I totally understand that you want to try it anyway, but keep in mind that you're now putting something into production that Microsoft simply will not support, AND you're using two separate niche features together (AGs and DTC). If anything whatsoever goes wrong, you're going to be in a world of hurt. This just isn't something I'd ever even think about trying in production.
Keep in mind that if your managers find out that you deployed something Microsoft specifically says in big letters, "YOU CAN'T DO THIS," and you have any kind of outage where you have to call Microsoft for support, you're going to have some ugly explaining to do.
Technically, DTC is supported starting with SQL Server 2016 SP2 and later, but it just means that you can pick which database loses data on failover, and the application has no idea data was permanently lost. That's not what a normal database administrator would call DTC support.
2. How should DTC be configured in a multi-node, multi-subnet cluster?
Read Allan Hirt's post on configuring DTC with multiple instances of SQL Server in a cluster, and make sure to read all of the links in the post as well.


how to connect SQL server from oracle in SQL developer I have two connections in SQL developer that one of them is SQL server (A) and the other one is oracle (B).
how can I make a database link between this two connections from (B) to (A) to select connection A data from connection B?

The last time I looked (and that's quite a number of years ago), you needed Oracle Gateway to establish such a link.

Just checked, the product is still around: Oracle Database Gateway for SQL Server.


Upgrade scenario: Oracle 8.0.5.0.0 to Oracle 12c? we have a very old Oracle 8.0.5.0.0 database here that - unfortunately - is still used heavily as every day production system.

I want to start making plans on how to get this DB upgrade to a recent Oracle 12c.

Unfortunately, both DB versions don't seem to be able to talk to each other anymore (*). So to my current knowledge, the only solution is to import daily backups from 8.0.5.0.0 into 12c, which - from the technical point of view - works, but is not feasible because the 8.0.5.0.0 is used in daily business. Beside: The data on the 8.0.5.0.0 server is that large that an import of the backup in the 12c server (on a recent Dell server) takes about 6-8 hours.

So, the question is:

What might be a good approach for a upgrade strategy from 8.0.5.0.0 to 12c?

Thanks for any input.

(*) Of course old Oracle 8.0.5.0.0 clients can talk to the Oracle 8.0.5.0.0 server and recent 12c clients can talk to the 12c server. But there does not seem to be a client solution that can talk to both 8.0.5.0.0 and 12c.

There are multiple solutions to this problem but the driving issues are cost, allowable downtime and complexity.

Golden Gate could be purchased with a one year license and will do a zero downtime upgrade from Oracle 8i to 12. You still have to upgrade from 8.05 to 8i.
Your life could be simplified by using VmWare P2V to convert the machine to a virtual machine without any downtime.  This method allows you to:

get your production database on better hardware which should improve the speed of most operations
allows you to make a copy of the machine, put it on a different subnet and start testing various strategies

I have imported schemas from our Oracle 8i database directly into Oracle 11.2 without issues.  Your mileage may vary depending on character sets, licensed features and any unorthodox strategies your programmers have in the PL/SQL code.

One method that could be easily tested:

buy a new server with some serious hardware like 32GB RAM, install VMWare Esxi
convert your old machine to a virtual
create a development copy of the Oracle 8.05
install another virtual machine with Oracle 12
install another virtual machine with Oracle 10XE or 11XE
take snapshots of all the machines with no changes
take an export of the 8.05 development, import into Oracle 10XE or 11XE, test and verify
data pump to the 12 database
verify the data on 12 and offline the 8.05 and oracle 10XE or 11XE

This method needs downtime. A weekend should be more than adequate and only costs you a new server, Oracle 12 licence and some VMWare software licenses.  Keep in mind that testing and verification can take more time than importing the data.


When I set Datestyle to 'SQL, YMD' all queries still return as 'SQL, MDY' I am working on a database migration from Oracle 12c to Postgresql 12.
We must keep the same date format as the program interacting with this database is written in COBOL and should not be changed.
According to the documentation, set datestyle = 'SQL, YMD' should give me the result I need as the NLS_DATE_FORMAT on the Oracle side is set to 'YYYY/MM/DD HH24:MI:SS'.

The output should be 2020/10/15 15:02:39.710888 UTC as I have set datestyle to YMD.
Other formats seem to work as intended:

Is there a way for me to get the intended Datestyle?

I'm not a PostgreSQL guru, but according to PostgreSQL docs:

DateStyle (string)
Sets the display format for date and time values, as well as the rules
for interpreting ambiguous date input values. For historical reasons,
this variable contains two independent components: the output format
specification (ISO, Postgres, SQL, or German) and the input/output
specification for year/month/day ordering (DMY, MDY, or YMD). These
can be set separately or together. The keywords Euro and European are
synonyms for DMY; the keywords US, NonEuro, and NonEuropean are
synonyms for MDY. See Section 8.5 for more information. The built-in
default is ISO, MDY, but initdb will initialize the configuration file
with a setting that corresponds to the behavior of the chosen lc_time
locale.

Now, looking at Section 8.5.2 Date/time Output

The output format of the date/time types can be set to one of the four
styles ISO 8601, SQL (Ingres), traditional POSTGRES (Unix date
format), or German. The default is the ISO format. (The SQL standard
requires the use of the ISO 8601 format. The name of the “SQL” output
format is a historical accident.) Table 8.14 shows examples of each
output style. The output of the date and time types is generally only
the date or time part in accordance with the given examples. However,
the POSTGRES style outputs date-only values in ISO format.

+---------------------+------------------------+------------------------------+
| Style Specification | Description            | Example                      |
+---------------------+------------------------+------------------------------+
| ISO                 | ISO 8601, SQL standard | 1997-12-17 07:37:16-08       |
+---------------------+------------------------+------------------------------+
| SQL                 | traditional style      | 12/17/1997 07:37:16.00 PST   |
+---------------------+------------------------+------------------------------+
| Postgres            | original style         | Wed Dec 17 07:37:16 1997 PST |
+---------------------+------------------------+------------------------------+
| German              | regional style         | 17.12.1997 07:37:16.00 PST   |
+---------------------+------------------------+------------------------------+

If you want an output format as YMD, you should use ISO instead of SQL.

set datestyle = 'ISO, YMD';
SELECT '20200201'::timestamp;

| timestamp           |
| :------------------ |
| 2020-02-01 00:00:00 |

set datestyle = 'SQL, YMD';
SELECT '20200201'::timestamp;

| timestamp           |
| :------------------ |
| 02/01/2020 00:00:00 |

db<>fiddle here


Is there any string compaction option for PostgreSQL? I have a table like this:

CREATE TABLE BLOG(
   CATEGORY CHAR(32)
);

Category is a column, which stored enum values.

For example, I have 1 millions of rows and 10 unique values of Category.

Let's populate the table with sample data:

CREATE TABLE TMP_STRINGS(
   id SERIAL PRIMARY KEY,
   CATEGORY CHAR(50)
);

insert into TMP_STRINGS values (1, 'category1'), (2, 'category2'), (3, 'category3'), (4, 'category4'), (5, 'category5'), (6, 'category6'), (7, 'category7'), (8, 'category8'), (9, 'category9');

insert into blog(category)
SELECT category FROM (SELECT floor(random() * 9 + 1)::int as n from generate_series(1,1000000)) AS T INNER JOIN tmp_strings ON T.n = tmp_strings.id;

This database is 42 MB:

SELECT pg_size_pretty(pg_total_relation_size('"blog"'));

A row of database is 44 byte:

SELECT octet_length(t.*::text) FROM blog AS t WHERE category='category1' LIMIT 1;

Categories can be encoded with smallint, like:

category1 -- 1
category2 -- 2
category3 -- 3
category4 -- 4
category5 -- 5
category6 -- 6
category7 -- 7
category8 -- 8
category9 -- 9

Is there any way to do this automatically? I mean some option or module for String interning (String pooling).

if you want an effect like strings stored as integers use an enum type, the SQL syntax for interacting with the table will be the same, except you'll need to cast if you want to do string operations on the new column;

CREATE TYPE example_category AS ENUM (
 'category1', 'category2', 'category3', 'category4', 'category5',
 'category6', 'category7', 'category8', 'category9');

ALTER TABLE tmp_strings ALTER COLUMN category
  TYPE example_category USING category::example_category;

I think enums use 4 bytes (possibly 5?)


How to manage RAM consumption in MongoDB? I am using MongoDB. I have a hardware device which continuously submits data to the database every 10 seconds for 4 hours a day. The data includes latitude, longitude, and other basic information of the device. I have indexed latitude and longitude for faster querying. I have used an event based document creation approach. That is, every time the device submits data, I am creating a document in my collection. The average number of documents created for per day will be 1,440. My document size is 300 bytes. Since I have 3 indices (including _id) the RAM consumption per document is going to be 8176*3 = ~24KB. I will have a million devices submitting data in a similar fashion. If this continues I will end up wanting 33,000 GB of RAM.

Am I assuming something wrong? What is the right approach to manage the documents in this case?

If your base document with all the data is 300 bytes, and worst case scenario for storage usage scenario, your indexes are all 300 bytes each but just sorted differently, would get you 900 bytes of memory used per document for indexes and 300 bytes for the base.

1440 events * 1200 bytes (Base document + 3 indexes each at 300 bytes) = 1728000 bytes

1296000 bytes / 1024 = 1687.5 kilobytes

1265.625 kb / 1024 = 1.64 megabytes

So if you are firing off 1440 events per day per device, each single device will take up 1.64 mb of memory plus a bit more for metadata overhead.  If you are firing off that many events for 1,000,000 devices it will be:

1647949.21875 mb / 1024 = 1609.32 gb

so roughly a total of 1.8 TB of ram will be needed to fully fit this into memory without utilizing any compression.

You can deal with the RAM issue by scaling out in shards or finding a few very expensive systems that can handle that much RAM (note I haven't heard of one and I'm not sure how many CPU's you'd need, this isn't realistic).  You can shard but then you'd typically want 3 hosts per shard and it comes with it's own operational overhead.  It also eats up a lot of power an data center space.

You can compromise and just deal with the fact that you will read from disk, if your app is OK with that.  Here are some other solutions.

There are all flash SANs out there that measure returns in microseconds, with RAM delivering data in nanoseconds.  It will be an order of magnitude slower than RAM but it is still insanely fast and might be just fine for your reqs.  The problem with these is you need top of the line Fiber Channel HBAs to have the throughput.

There are FusionIO cards that could work very well.  These will give you a PCI-E bus to connect to so throughput is no problem and you don't need expensive HBAs.  They aren't cheap though and if your app is very time sensitive it still isn't good enough.  FusionIO will typically give you a loaner card to test and they always make me drool.  They have a marketing page on it here.


Setting READ_COMMITTED_SNAPSHOT to ON while ALLOW_SNAPSHOT_ISOLATION is OFF I experimented with various configurations of SQL Server databases and ended up setting READ_COMMITTED_SNAPSHOT to ON while ALLOW_SNAPSHOT_ISOLATION is OFF.

I noticed that when enabling this, many queries got a lot faster. I'm still using the default READ COMMITTED isolation level to connect to the database.

What is actually happening here? I would think that when ALLOW_SNAPSHOT_ISOLATION is OFF, setting READ_COMMITTED_SNAPSHOT to ON wouldn't have any effect... I'm still not actually using the snapshot isolation, or am I? Can anybody please explain? I'm confused.

I tried researching this topic online, but whenever I see READ_COMMITTED_SNAPSHOT being used, it's always together with ALLOW_SNAPSHOT_ISOLATION, which I didn't enable.

I noticed that when enabling this, many queries got a lot faster. I'm still using the default READ COMMITTED isolation level to connect to the database.

Dan noted this:

  The options are independent and both do not need to be turned on, although many mistakenly do so. Turning on either incurs the row-versioning overhead. READ_COMMITTED_SNAPSHOT ON causes all READ_COMMITTED sessions to use row-versioning instead of locking so that will avoid blocking and may explain your perceived performance improvement.

In addition, READ_COMMITTED_SNAPSHOT (henceforth called RCSI) will only give you some properties of optimistic concurrency. RCSI and Snapshot (SI) differ in three main ways:

RCSI will be used immediately by all RC isolation queries.
RCSI provides a point-in-time (snapshot) view of the database for a single statement.
SI provides a point-in-time view for an entire transaction.
SI can have write conflicts.

  What is actually happening here?

RCSI is turned on, thus all RC isolation queries are being promoted to RCSI where readers don't block writers and writers don't block readers. The "speed up" you're seeing is most likely due to the reduced waits on blocking for those queries as it is now using the optimistic concurrency's version store instead of blocking and waiting. This may or may not be giving you the desired results in your queries.

  I would think that when ALLOW_SNAPSHOT_ISOLATION is OFF, setting READ_COMMITTED_SNAPSHOT to ON wouldn't have any effect... I'm still not actually using the snapshot isolation, or am I?

See above. You are using it when RCSI is turned on (which is the one you did in fact turn on) but SI is not automatically used when turned on: You need to explicitly SET TRANSACTION ISOLATION LEVEL SNAPSHOT (or equivalent) to use SI.

For more information, see Row Versioning-based Isolation Levels in the SQL Server Database Engine in the product documentation.

Other useful details relating some gotchas and caveats when using RCSI/SI can be found in these articles:

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide
Read Committed Snapshot Isolation: Writers Block Writers (RCSI)


Comparing the data in two Postgres databases I am looking for a way to compare the data in two Postgres databases.

Basically a 'Before' and 'After' snapshot of a transaction being posted.  What I am looking for is all the tables/records that have been altered by a particular type of transaction.

The solution could use ODBC, or PostgreSQL directly. I have connections to the databases both ways.

This question (in some shape or form - no two questions are identical), for example see the threads here and here - I did answer there.

The two most popular (Open Source) tools in this area are Liquibase and Flyaway. There are numerous commercial tools which you can find by Googling.

I would like to echo Paul White's welcome, but just a wee word of warning. Tool recommendations aren't really on-topic here. Check out the tour and also the "help us to help you" blog - both at the bottom left of the page. Also, take a look at the help centre for everything you could ever want to know about dba.se. These forums are a great resource and you'll get the most from them if you take the time to check out and follow the guidelines.

Googling the term "diff tool for postgresql" gives some other ideas. Perhaps a good approach would be to try out a couple (few) of them and if you're having problems or require more specific advice (too broad is also off-topic :-( ) - I got this and this - PostgresQL specific. Best of luck.