Is there a way that I can export a list of all table triggers on a server? I have two SQL Server instances that I manage.  One is a SQL Server 2000 instance, and the other is 2005.

Some where, on these servers, I recall setting up a couple table triggers that were executed under certain conditions.  I need to look-up these triggers as a point of reference for a new project, however, for the life of me I can not find them.

Is there some crafty SQL statement that I can execute that can iterate over all my databases and, subsequently, iterate over each table and output any triggers associated with the tables?

This will give you a list of tables that have triggers, and the name of the associated trigger:

SELECT t.name, tr.name
FROM sys.triggers tr
    INNER JOIN sys.tables t ON tr.parent_id = t.object_id

This will give you the same list, with a column that shows the actual text of the trigger statement:

SELECT t.name, tr.name, m.definition
FROM sys.triggers tr
    INNER JOIN sys.tables t ON tr.parent_id = t.object_id
    INNER JOIN sys.sql_modules m ON tr.object_id = m.object_id

As Aaron suggested, this would work for SQL Server 2005+ as well:

SELECT t.name, tr.name, OBJECT_DEFINITION(tr.object_id) AS TriggerText
FROM sys.triggers tr
    INNER JOIN sys.tables t ON tr.parent_id = t.object_id

And per his excellent suggestion, the following works for SQL Server 2000+ (including SQL Server 2012):

SELECT [table] = t.name,
    [trigger] = tr.name,
    c.text
FROM sysobjects AS t
    INNER JOIN sysobjects AS tr  ON t.id = tr.parent_obj
    INNER JOIN syscomments AS c  ON tr.id = c.id
WHERE tr.xtype = 'TR'
    AND t.xtype = 'U';


SQL Server: Why would an ALTER TABLE to add nullable column run forever? SQL Server 2014 Std Edition

We add columns all the time, and the ALTER TABLE commands return instantly.

E.g.

alter table [dbo].[Account] add ChildAccount nvarchar(100) NULL
GO

But at the moment, this alter table seems to run forever.

What would cause this?

Yes, some other session was blocking. A few min later, this command executed instantly.


How to sort words in a column in mysql? I have data as

id action
1  a
1  b
2  b
2  a
3  c

I wrote code as

select id,group_concat(action SEPARATOR ' ')
from table1;

It ends up as

id action
1  a b
2  b a
3  c

Actually, ba and ab is the same one, how could we sort it?

GROUP_CONCAT accepts ORDER BY ,so your query becomes

select id,group_concat(action ORDER BY action SEPARATOR ' ') from table1;


Create ORACLE TRIGGER which (INSERT OR UPDATE) in TABLE1 whenever a new INSERT is done in TABLE2 i'm trying to create a TRIGGER for my Oracle database.
I Have 2 tables in it(table1 , table2)
Table 1 has 3 columns (id, name, value). id can be non unique here
Table 2 has 2 columns ( id, concat) id is unique here
What i want to do, is, every time a new row is inserted in Table 1., the trigger must Update the concat value of Table 2 which is a concate of name and values of all row where table1.id = table2.id
At the moment i think about this trigger, but i dont want to break anything in my db, so any advice would be great before testing :)

CREATE TRIGGER creation
AFTER INSERT  ON table1 ac
FOR EACH ROW
BEGIN
UPDATE table2
SET concat= CONCAT(' ',CONCAT(ac.NAME, CONCAT('=',ac.VALUE)))
WHERE
table1.id = table2.id
END

Thx a lot

Try something like this. It also accounts for situations in which "id" didn't previously exist. Note: I used "concat_col" as the "concat" column name; @nbk is right - you shouldn't have column names that match pre-defined function names.
CREATE OR REPLACE TRIGGER TRIGGER1
AFTER INSERT ON TABLE1
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
BEGIN
   merge into table2
   using (select :new.id l_id from dual) d on (table2.id = d.l_id)
   when matched then
      update set concat_col = concat_col || ' ' || :new.name || '=' || :new.value
   when not matched then
      insert (id, concat_col) values (:new.id, :new.name || '=' || :new.value);
END;
/


How can I get PostgreSQL to not advance sequences when COPY fails? I'm importing CSV files into a relatively simple PostgreSQL database. (The CSVs are sometimes created manually from information in a book, sometimes created from spreadsheets with a little massaging via scripting.) I do it in small batches to make sure things are going ok, and I've noticed that when COPY fails, it doesn't roll back the sequence attached to the column in the table. (Note: I am importing via pgAdmin.)

For example, let's say this is my table:

TABLE
------------------
id | data1 | data2
------------------
1  | abc   | def
2  | ghi   | klm

Then let's say I try to import two more columns and it fails. I fix the error, and then the import succeeds. I'd expect the table to look like this:

TABLE
------------------
id | data1 | data2
------------------
1  | abc   | def
2  | ghi   | klm
3  | nop   | qrs
4  | tuv   | wxy

Instead, it looks like this:

TABLE
------------------
id | data1 | data2
------------------
1  | abc   | def
2  | ghi   | klm
5  | nop   | qrs
6  | tuv   | wxy

The tables all rely on each other (i.e. pretty much every table has a FK pointing to the ID of some other table), so if the IDs stay predictable, my data entry job gets a lot easier. If not, I have to keep double checking the IDs when I finish a section.

Is there any way to prevent this behavior?

The sequences are designed like that, the are not supposed to produce contiguous numbers.

However, you can reset them before (re)trying the COPY with

SELECT setval('sequence_name', (SELECT max(id) FROM your_table));


How to create xml from sql table? I have sql table from which I am trying to get results based on some criteria as:

From above table I want to create xml which has to return result as:

Here is the query I have written for the

DECLARE
    @ResourceName VARCHAR(50)
, @XMLResult XML = NULL
, @xMSG XML = NULL

SET @ResourceName = 'Property'
SET @xmsg = '<Results ReplyCode="0" ReplyText="Operation successful"><Result></Result></Results>'

SET @xMSG =
(
    SELECT 0 AS [@ReplyCode]
    , 'Operation Successfull' AS [@ReplyText]
    , @ResourceName AS ResourceName
    , ( SELECT *
        FROM (
                SELECT SystemName
                        , DBName
                        , ShortName
                        , ClassSortOrder
                        , Pcode
                        , TabNumber
                        , ColumnNumber
                        , RowNumber
                FROM ScreenOrder
            ) SearchFieldsList
        ORDER BY ClassSortOrder
        FOR XML PATH('Field'), TYPE
     )
    FOR
    XML PATH('Results')
)

SET @XMLResult = @xMSG
SELECT   @XMLResult

But this query not returning expected result. This query returning result as:

I trying to put all that Fields as class wise in xml. Where do I need to modify query?

Use FOR XML PATH with subqueries to get the appropriate level of nesting, something like this:

DECLARE @yourTable TABLE
    (
    ResourceName        VARCHAR(20),
    ClassName           VARCHAR(20),

    SystemName          VARCHAR(20),
    ShortName           VARCHAR(20),
    ClassSortOrder      INT,

    TabNumber           INT,
    RowNumber           INT,
    DBName              VARCHAR(20)
    )

INSERT INTO @yourTable VALUES
    ( 'Property', 'Commercial', 'Type', 'Type', 1, 1, 1, 'Fields Type' ),
    ( 'Property', 'Residential', 'Address', 'Address', 2, 2, 2, 'Fields Address' ),
    ( 'Property', 'MultiFamily', 'Price', 'Price', 3, 3, 3, 'Fields Price' ),
    ( 'Property', 'LotsAndLandy', 'BuildYear', 'BuildYear', 4, 4, 4, 'Fields Type' )

SELECT * FROM @yourTable

SELECT
    0 AS "@ReplyCode",
    'Operation Successful' AS "@ReplyText",
    (
    SELECT
        ResourceName AS "ResourceName",
        (
        SELECT
            ClassName AS "ClassName",
            (
            SELECT
                SystemName AS "SystemName",
                DBName AS "DBName",
                ShortName AS "ShortName",
                ClassSortOrder AS "ClassSortOrder"
                FOR XML PATH(''), TYPE
            ) AS "Field"
        FROM @yourTable c
        WHERE r.ResourceName = c.ResourceName
        FOR XML PATH('Class'), TYPE
        ) AS "Classes"
    FROM ( SELECT DISTINCT ResourceName FROM @yourTable ) r
    FOR XML PATH(''), TYPE
    ) AS "*"
FOR XML PATH('Results'), TYPE

It's not 100% clear as your sample data is different from your sample XML but hopefully that gives you a starting point.


Security Question involving Sys tables We have an application user which has been given read write to the appropriate databases.  This user has the ability to query the sys tables by default.  I've also heard that there are times when this user needs to be able to obtain a listing of tables and columns that exist in the database.  Are there any security risks to having the user have read access to the sys tables?

All users have access to sys tables. Giving read access on the database is orthogonal to accessing the sys tables. System catalogs (ie. sys views and tables) have built in security and you cannot grant, deny or revoke read permissions on them. The visibility of entities in system catalogs derives from the permissions granted on the entity itself, not on the system catalog. To give an example, an user can see a row in sys.tables because it has VIEW DEFINITION permission on that table.

This is laso explained in the MSDN:

  The visibility of the metadata in catalog views is limited to
  securables that a user either owns or on which the user has been
  granted some permission. For more information, see Metadata Visibility Configuration.


Select data from the previous 3 months I need help selecting data where the date is in the previous 3 months. (note: I'm not asking for the last 3 months i.e. DATEADD(MONTH, -3, GETDATE()) )

The reason I don't want to use the DATEADD method is that this query could be run in the middle of the month and I don't want it to return data leading up til that point

You can use this methodology to determine the first day of 3 months ago, and the last day of the previous month:

select DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)   --First day of 3 months ago
select DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) --Last Day of previous month

Then, just use it on your where clause.

declare @start date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)
declare @end date = DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1)

select *
from table
where dateField between @start and @end

If your dateField is datetime then you need to add the seconds, or just add a day and use a < operand so you don't miss the last day

declare @start date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-3, 0)
declare @end date = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0)        --First day of current month

select *
from table
where dateField >=@start and dateField < @end


In Microsoft SQL Server's linked server configuration, what does the "RPC" setting do? I'm not talking about "RPC Out". That setting works as I expect it to: when "false", I can't call stored procedures on my linked server. When it's "true", I can.

The specific option I am referring to is in the GUI (SQL Server Management Studio) on the Linked Server Properties window for any linked server (under "Server Options" on the left). Or, you may also set this option by calling sp_serveroption @optname='rpc'.

"RPC" can be set to either true or false. However, I can't find solid documentation on what this actually means in practice. I can't find any situation in my experimentation that behaves differently depending on whether "RPC" is set to true or false.

Can anyone give an example of what behaves differently depending on this setting?

Thanks!

One allows A=>B the other allows B=>A.  In other words just because server A can call a proc on B doesn't mean B can call a proc on A.

http://msdn.microsoft.com/en-us/library/ms186839.aspx

RPC
   Enables RPC from the specified server.

RPC Out
   Enables RPC to the specified server.


How can I identify the number of times a View is called in SQL Server? How can I identify the number of times a View was called in SQL Server?

I am looking for an answer that is similar to this below which shows how it can be done for stored procedures.

How can I monitor the call count per stored procedure per timespan?

Here is one not-overly-scientific way to do it, it is quite possible that it will miss references due to, say, indexed view expansion, and get you false positives if the batch text contains your view name in comments or as part of another entity name or string. Also won't work on earlier versions of SQL Server (which is why it is important to tag your question with the minimum version of SQL Server you need to support).

SELECT t.text,s.execution_count,s.last_execution_time
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.sql_handle) AS t
WHERE t.text LIKE '%your-view-name%';

Also note this only contains metrics going back to the last service restart, RECONFIGURE, etc.


How do foreign keys affect partial and transitive dependencies (databases) I'm struggling to work out how foreign keys can affect partial and transitive dependencies when normalising databases.
I have multiple tables, two of which are job offer and accepted job.
in job offer, offerID -> expectedStart, expectedEnd, hourlyPay, in the accepted job table jobID -> startDate, endDate, totalPay, offerID(FK).

My thinking is that the expected start and end dates can change after the job offer, so start and end dates are derived just from jobID, however totalPay is derived from startDate, endDate and offerID as the hourly pay is in the job offer table (of which offerID is the foreign key).

I'm trying to normalise the tables but am really struggling to work out if totalPay is causing partial or transitive dependencies as it is derived from two other entries in accepted jobs and also a foreign key.

If you quote & use definitions for FD (functional dependency), partial FD & transitive FD & for when they hold in a table (value or variable), you will see that they have nothing to do with FKs (foreign keys) or any other table(s).

Also you will see that they have nothing to do with an everyday notion of "derived from". A set of columns is "(functionally) dependent on" another  set or single column when/iff certain conditions are true, as given in the definitions.

Also you will see that the FDs that hold in a table variable are determined by all the values that can arise for it. That is determined by the condition for rows going in the variable in a business situation & all the possible business situations that can arise. But it has nothing to do with whether (a part of) a variable's value "can change after" it holds some different value.

Also if you quote & use definitions for 2NF & 3NF you will see that it is only certain partial and transitive FDs that matter to those NFs.


Can SQL Server be configured to not silently truncate VARCHAR values? In some scenarios like this one, SQL Server will silently truncate (N)VARCHAR values leading to serious data loss when incorrectly declaring variables.

Question: Can SQL Server be configured to not silently truncate VARCHAR values? (and issue an error/raise an exception instead)

No there's no way to change this behavior.  But this truncation only happens with variable and parameter assignments, not table inserts.

EG

declare @str varchar(8) = 'some very long string'

will be silently truncated

but an insert:

create table #t(a varchar(8))
insert into #t(a) values('some very long string')

will fail with

Msg 8152, Level 16, State 14, Line 7
String or binary data would be truncated.

This is a consequence of TSQL's permissive use of implicit conversions, which is convenient in many cases, but sometimes troublesome.


Log all user error messages for retrospective troubleshooting Suppose I want to log all severity 16 (and higher) user error messages to a database table in order to allow some retrospective application troubleshooting (since the answer to "Can you send me the error message?" is quite often "No, I closed it.") Is a server-side trace going to be the most effective option, or would there be an even better approach?

The problem with a server side trace is that logging the error messages without any context probably isn't very helpful and collecting that context requires tracing and filtering the more expensive events such as SQL:StatementCompleted

As you are on SQL Server 2008 you can look at extended events for this. This is much more efficient as instead of capturing all statements and then discarding those without error it only fires on error then retrieves the statement text. For some example code see my answer here. though I recommend you download the Extended Events SSMS Addin and customize it for your needs.


get column from too many tables in mysql .I have too many tables X1,X2,...Xn (they might exceed 75 tables) in Mysql DB to represent departments of a very large company.
every table has structure something like this

desc X1;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| ReqF    | int(11)     | Yes  |     | NULL    |       |
| EmpName | varchar(32) | Yes  | MUL | NULL    |       |

now I am supposed to return all ReqF from departments that John works in.
I know i can do so by left join like this

select X1.ReqF,X2.ReqF,...,Xn.ReqF FROM X1 left join X2 on (EmpName) left join .... left join Xn on X1.EmpName=Xn.EmpName where X1.EmpName='John'

is there any better way to do so and what if we need to retrieve different fields from tables (ex X1.ReqF1, X2.ReqF2 or random number of fields from tables like [SELECT X1.F1, X1.F2, X2.F3,...])??

If all the tables use the MyISAM Storage Engine and have the same table structure, I have some good news for you.

You can create a single table that consumes no additional space except a .frm file and some mapping info. The key is to take advantage of the MERGE (MRG_MyISAM) Storage Engine.

Here is how you can do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4) INSERT_METHOD=LAST;

Using this method, you can query the 4 tables at the same time like this:

SELECT ReqF FROM XMerge WHERE EmpName='John';

Was that simple, or what ???

In your case, you have 75 tables. You would do this:

CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

The beauty of this is that creating a MERGE table takes milliseconds. Just make sure every table has an index on EmpName. Better to do 75 indexed lookups that 75 full table scans. If there is no index on EmpName, you need to do this:

ALTER TABLE X1 ADD UNIQUE KEY (EmpName);
ALTER TABLE X2 ADD UNIQUE KEY (EmpName);
.
.
.
ALTER TABLE X75 ADD UNIQUE KEY (EmpName);
CREATE TABLE XMerge LIKE X1;
ALTER TABLE XMerge ENGINE=MRG_MYISAM
UNION=(X1,X2,X3,X4,X5,X6,X7,X8,X9,
X10,X11,X12,X13,X14,X15,X16,X17,X18,X19,
X20,X21,X22,X23,X24,X25,X26,X27,X28,X29,
X30,X31,X32,X33,X34,X35,X36,X37,X38,X39,
X40,X41,X42,X43,X44,X45,X46,X47,X48,X49,
X50,X51,X52,X53,X54,X55,X56,X57,X58,X59,
X60,X61,X62,X63,X64,X65,X66,X67,X68,X69,
X70,X71,X72,X73,X74,X75) INSERT_METHOD=LAST;
SELECT ReqF FROM XMerge WHERE EmpName='John';

Give it a Try !!!


Best way to create searchable and sortable product database I'm pretty good with coding but I'm a noob at database design.  This is a common problem but I didn't see an exact answer so I just want to confirm before I start coding everything.

I have a shopping site that lists a lot of products.  Currently I have 6,000 products but this could grow in the future to be quite large, maybe 100,000+ some day.  I list the products on the site by page and I let the user search and sort the products.

What is the most efficient way to design and query the database to search and sort products?  Do I simply create one big product table and then use queries like:

SELECT *
FROM productTable
WHERE searchCriteria
ORDER BY sortColumn
LIMIT basedOnPageNumber

Will this still work quickly when I have 100,000+ items? Is there a better strategy to use?

If an index can be defined on the search criteria then this will work OK at any scale. BTree indexes have O(log N) performance IIRC so working through 100,000 rows will be 5/3 the time needed for 1,000 rows.

However .. most systems can't fulfill that criteria. Often a user can fill as many or few search fields as she chooses. Then things get complicated. Sometimes it may be best to define separate queries, and indexes, for each combination of search values. This quickly gets large. 5! = 120; that's a lot of indexes and queries. Some DBMS have optimisations for generic searches - OPTION (RECOMPILE) in SQL Server, for example, will cause parameter embedding. Probably some combination of often-used specific queries and a generic catch-all will work.

As the list gets hugh users will expect ever fuzzier searches. At some stage a full text search on the product name and / or description may be useful. Maybe, eventually, you offload that task entirely.


Pgpool-II: sequences advanced by 1 after PITR recovery I have tested pgpool-II 3.0.1 and pgpool 3.0.4, PostgreSQL 8.4
In both cases I'm getting the same result: after online recovery on primary and target nodes all 'serial' values got "+1". Other nodes enabled in pgpool have old serial values.
Here is my commands:

postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c 'create schema sch'
postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c 'create table sch.mytable (id serial, name text);'
postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c '\d sch.mytable'
                             Table "sch.mytable"
 Column |  Type   |                        Modifiers
--------+---------+----------------------------------------------------------
 id     | integer | not null default nextval('sch.mytable_id_seq'::regclass)
 name   | text    |
postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c "insert into sch.mytable (name) values ('usera')"
postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c 'select * from sch.mytable'
 id | name
----+-------
  1 | usera
postgres@server01:~> psql -h primary_node -p 5432 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 2

# Online recovery via PITR (http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#online-recovery)
# Recover the first standby node: standby_1
postgres@server01:~> /opt/pgpool-II-3.0.4/pcp_recovery_node -d 1600 localhost 9999 user pass 1 &
# DONE recovery
postgres@server01:~> psql -h primary_node -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 4
postgres@server01:~> psql -h standby_1 -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 4

# Recover the second node: standby_2
postgres@server01:~> /opt/pgpool-II-3.0.4/pcp_recovery_node -d 1600 localhost 9999 user pass 2 &
# DONE recovery
postgres@server01:~> psql -h primary_node -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 6
postgres@server01:~> psql -h standby_1 -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 5
postgres@server01:~> psql -h standby_2 -p 5433 -d serial_test -c "select nextval('sch.mytable_id_seq')"
 nextval = 6

What am I doing wrong?

I've tried to set this options without success:

black_function_list = 'lastval,currval,nextval,setval'
insert_lock = true

Resolved

Got it! Script pgpool_recvery_pitr:

psql -t -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND
datallowconn' template1|
while read i
do
if [ "$i" != "" ];then
psql -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE relkind =
'S'" $i
fi
done

After recovery all sequences in database advance by 1.

There is no information in official docs about it:
They forgot about other enabled nodes in pgpool. This statement must be run for every enabled node in pgpool to keep all sequence points on all nodes equal.

Answer per the question:

Script pgpool_recvery_pitr:

psql -t -c 'SELECT datname FROM pg_database WHERE NOT datistemplate AND
datallowconn' template1|
while read i
do
if [ "$i" != "" ];then
psql -c "SELECT setval(oid, nextval(oid)) FROM pg_class WHERE relkind =
'S'" $i
fi
done

After recovery all sequences in
database advance 1.

There is no information in official docs about it: http://pgpool.projects.postgresql.org/pgpool-II/doc/pgpool-en.html#online-recovery.

They forgot about other enabled nodes in pgpool. This statement MUST BE RUN for every enabled node in pgpool to keep all sequence points on all nodes equal.


Configuring log shipping with one primary and two secondary instances I have three SQL Server instances (SQLInstance-1, SQLInstance-2, and SQLInstance-3).I want configure three (or more) servers:

SQLServer-1 – primary
SQLServer-2 – secondary
SQLServer-3 – secondary

I want the database in standby mode on the secondary servers – database available for querying, and users can access it, but in read-only mode.

I am able to configure this for two servers (one primary and one secondary), but when I try three servers, log shipping setup and secondary server (SQLInstance-3) gets stuck in restore mode, and the log shipping SQL Agent jobs on the second secondary (SQLInstance-3) fail repeatedly. I am confused here what to do?

The second secondary (SQLInstance-3) is showing No Last_Restore_file. I checked Transactional Log Shipping status and it has the same status for the restore file.

Does this mean log shipping is not configured properly between SQL Instance 1 & 3?

I set up log shipping originally following the process outlined in Configure Log Shipping (SQL Server). I didn't use the wizard, I restored the log backups on secondary, with SQL Agent jobs created for log restores. The copy and restore jobs failed.

I have checked Restoring Job failed with error 'Could not find a log backup file that could be applied' on Log Shipping in SQL Server (Secondary)? but it won't work for me. As suggested, I started from scratch and re-initialized the log shipping on secondary (2), jobs on secondary (1) failed with an error, and jobs on secondary (2) are running fine. Both secondaries are set up the same way, and I got successful notifications for log shipping, but jobs failed on one secondary.

How should I setup log shipping with one primary and multiple secondary servers?

Below are the steps that you need to follow :

Take full backup on primary (server 1 ).
Restore full backup on server 2 and 3 using no_recovery option.
Take log backup on server 1
Restore log backup on server 2 and 3 using no_recovery option.
Using GUI wizard, add secondary servers (server 2 and 3).

This will help you set up tertiary logshipping. I have done it and it just works fine.

Note: When you failover, you need to be careful as to what server you are failing over and have to manually restore the tail log backup on the other secondary server.


Fast Recovery in SQL Enterprise Edition Starting with SQL Server 2005 and all later versions, Enterprise Edition includes a feature called "Fast Recovery".

a) Does fast recovery use multiple OS threads to perform "REDO" operation?

b) Does SQL Server write to the transaction log in parallel spawning multiple threads or is the log written sequentially so only one thread is used for writing to the transaction log?

Understanding Logging and Recovery in SQL Server

  Replaying log records is called the REDO (or roll forward) phase of
  recovery. Reverting log records is called the UNDO (or roll back)
  phase of recovery. In other words, recovery will make sure that a
  transaction and all its constituent log records are either redone or
  undone.

Fast recovery allows a database to come online as soon as the REDO phase completes, before UNDO is run. This is made possible by lock logging, which records the locks that were applied by a transaction in the associated log record.

Fast recovery reads the log records that will be UNDONE, acquires the locks, then brings the database online. The UNDO processing can then proceed in due course while other database activity occurs as the resources that will be affected are protected by locks. See Paul Randal's Lock logging and fast recovery post for the gory details.

REDO operations are single threaded on Standard Edition and Enterprise Edition with less than 5 cores. On Enterprise with 5+ cores, there is a redo thread for every 4 cores (reference). This is separate and unrelated to the Fast Recovery feature as far as I can tell.

In answer to your other question, a single thread is responsible for transaction log writes.


How to make stored procedure with outputs OUT sys_refcursor, OUT varchar2, and OUT number, then use in c#.net app What is the syntax for a stored procedure that outputs a refcursor, a varchar2, and a number?

I would like to output all three in the result, for the purpose of displaying count, and a message which indicates a successful query in addition to the sys_refcursor output but I cannot seem to find a good example of it.

My current C# code looks like this:

using Oracle.DataAccess.Client;
//...

OracleCommand myCmd = new OracleCommand();
myCmd.Connection = myConnection;
myCmd.CommandText = "mypackage.myprocedure"
myCmd.CommandType = CommandType.StoredProcedure;
OracleParameter param0 = myCmd.Parameters.Add("myinput", OracleDbType.Varchar2, myinput, ParameterDirection.Input);
OracleParameter param1 = myCmd.Parameters.Add("myretcursor", OracleDbType.RefCursor, ParameterDirection.Output);

myConnection.Open();
OracleDataReader resultReader = myCmd.ExecuteReader();
mydatatable.Load(resultReader, LoadOption.OverwriteChanges);

and the Oracle stored procedure looks like this:

    PROCEDURE myprocedure (myinput IN Varchar2, myretcursor OUT sys_refcursor)

    IS

    BEGIN
    OPEN myretcursor FOR
    SELECT * FROM MYTABLE

    END myprocedure

I guess I want to add param2, param3 to the the c# as additional output parameters, then also somehow also output them in the stored procedure?  I was not sure how to do this and kept encountering errors.  Ideally I would, for exmple, pass the select count (*) as output into the c# application, as well as a varchar2 string message like 'Query Successful'.

Thank you.

UPDATE:
Vincent's solution works below... regarding the C# code behind, the answer to the link he provided recommends enclosing the reader = executereader in a USING( ) for loading the table, and closing the reader before loading the other variables.

https://stackoverflow.com/q/6882690/613799

Depending on the size of the string from oracle, the calling c# code may need to specify the size of the varchar2 in the output parameter definition for the string message.  Finally the following link was helpful for type casting the count from OracleDecimal to C# int:

https://stackoverflow.com/q/3026036/613799

Your procedure is missing two OUT parameters:

PROCEDURE myprocedure (myinput      IN Varchar2,
                       myretcursor  OUT sys_refcursor,
                       p_count      OUT NUMBER,
                       p_message    OUT VARCHAR2) IS
BEGIN
   OPEN myretcursor FOR
      SELECT * FROM MYTABLE;
   p_count := 123;
   p_message := 'message';
END myprocedure

You would also need to modify your C# code to register the new OUT parameters accordingly.


Postgres: how to limit the number of query results by aggregate type Consider the following table definition

CREATE TABLE associations (
    id SERIAL PRIMARY KEY,
    resource_id integer,
    target_id integer,
    relation_id integer
);

which is used for specifying the association of a resource to a target by means of a relation:

296 10  49  4 <--
297 10  50  4 <--
298 10  51  4 <--
299 10  52  4
300 10  53  4
301 10  54  4
302 10  55  4
303 10  56  5 <--
304 10  57  5 <--
305 10  58  7
306 10  59  7
307 10  60  7
308 10  61  7
309 10  62  7
310 10  64  8 <--
316 11  80  8 <--
320 11  81  8 <--
321 11  82  8 <--
322 11  83  8

resource 10 is associated to targets [49, 50, 51, 52, 53, 54, 55] by relation 4
resource 10 is also associated to targets [56, 57] by relation 5

How can one query the associations table to get the targets of resources for specific relations (e.g. 4, 5 and 8) while limiting the number of returned targets per relation?

For example if we were to limit the number of targets to 3 on relations 4, 5 and 8 we should obtain the following result:

10 | 49, 50, 51 | 4
10 |     56, 57 | 5
10 |         64 | 8
11 | 80, 81, 82 | 8

Thank you.

WITH
cte AS ( SELECT resource_id,
                target_id,
                relation_id,
                ROW_NUMBER() OVER ( PARTITION BY resource_id, relation_id
                                    ORDER BY id ) rn
         FROM associations
         WHERE relation_id IN (4, 5, 8) )
SELECT resource_id, STRING_AGG(target_id::TEXT, ','::TEXT), relation_id
FROM cte
WHERE rn <= 3
GROUP BY resource_id, relation_id

fiddle


Mongodb restore “local” db I need to restore a replica set. Unfortunately my lack of experience as administrator (I'm not) let me with the only solution of restore the replica using the data of the primary db (the only replica element still alive) as a "seed". At the moment I'm copying the files of the primary to secondary PC using rsync (there are 600G to copy). Then I have three questions regarding the restore:

1 - Once the files are copied should I use this data as the dbpath of the secondary?

2- Is the "local" db of primary copied? This may be a silly question but in https://docs.mongodb.com/manual/tutorial/resync-replica-set-member/#replica-set-resync-by-copying the say to be sure that this db is copied along with the data files, and I'm not sure about it.

3- How long it will take to "synchronize" with primary once I start the mongod instance in the secondary PC?

Yes
Yes, if you copy all dbpath files from primary (what is stopped; or you use file system snapshot), then local database is copied too
It depends.. If nothing is inserted to primary during rsync, about one second... If there is lot's of inserts, updates, deletes at primary, then more than few seconds.


Availability Group database stuck in Initializing / In Recovery mode even after restarting service While setting up a new replica in an Availability Group on our reporting cluster a few of the databases were not seeded correctly. I tried manually seeding them using a recent backup and applying the latest log file backups, which worked for most of the databases but a few of them are stuck in Initializing / In Recovery on startup in SSMS. Even restarting the SQL Server service didn't help. Here are the steps I have tried and the resulting errors:

--Get a list of database not Online and not Restoring
select name,state,state_desc from sys.databases where state not in (0,1)

name                        state   state_desc
StackExchange.Aviation.Meta 2       RECOVERING

drop database [StackExchange.Aviation.Meta]
Msg 3702, Level 16, State 4, Line 21
Cannot drop database "StackExchange.Aviation.Meta" because it is currently in use.

RESTORE LOG [StackExchange.Aviation.Meta] FROM DISK = '\\Backups\SQL\_Trans\StackExchange.Aviation.Meta_LOG_20170901_043251.trn' WITH NORECOVERY;
Msg 1222, Level 16, State 24, Line 11
Lock request time out period exceeded.
Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.

RESTORE DATABASE [StackExchange.Aviation.Meta] FROM DISK = '\\Backups\SQL\StackExchange.Aviation.Meta_FULL_COPY_ONLY_20170901_040130.bak' WITH NORECOVERY;
Msg 1222, Level 16, State 24, Line 12
Lock request time out period exceeded.
Msg 3013, Level 16, State 1, Line 12
RESTORE DATABASE is terminating abnormally.

RESTORE DATABASE [StackExchange.Aviation.Meta] with recovery
Msg 1222, Level 16, State 24, Line 11
Lock request time out period exceeded.
Msg 3013, Level 16, State 1, Line 11
RESTORE DATABASE is terminating abnormally.

ALTER DATABASE [StackExchange.Aviation.Meta] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
Msg 5061, Level 16, State 1, Line 5
ALTER DATABASE failed because a lock could not be placed on database 'StackExchange.Aviation.Meta'. Try again later.
Msg 5069, Level 16, State 1, Line 5
ALTER DATABASE statement failed.

Even stopping and restarting the sql service did not help, as it comes back in the same stuck state. What else can I do to re-seed this database?

Well, when I saw the error messages I figured the database was not added to the Availability group, but after checking the Availability Databases in SSMS on the new replica it was there with a pause symbol next to it. This means it was added but the data movement has stopped. I tried resuming data movement, but it was too far behind. So instead I had to remove it from the availability group and manually apply log files so it could start replicating again.

-- Remove database from Availability Group:
Alter Database [StackExchange.Aviation.Meta] SET HADR OFF;

-- Apply t-logs to catch up. This can be done manually in SSMS or via:
RESTORE LOG [StackExchange.Aviation.Meta] FROM DISK = '\\Backups\SQL\_Trans\StackExchange.Aviation.Meta_LOG_20170901_043251.trn' WITH NORECOVERY;

-- Re-join database to availability group
ALTER DATABASE [StackExchange.Aviation.Meta] SET HADR AVAILABILITY GROUP = [SENetwork_RAG];
ALTER DATABASE [StackExchange.Aviation.Meta] SET HADR RESUME;

I should have noticed it was added, but the AG has over 300 DBs and was still in the process of seeding so it was missed among the noise. The "Lock request time out" error message also made me think that something had gone wrong in the seeding, but it was just a poorly worded message indicating the database was already part of the Availability Group.


Read ancient Oracle database into current Oracle version An old computer with Oracle 8i is dead.  We have the daily backup of the database (C:\oradata).  We are unable to install this old Oracle version anywhere because we don't have the install CDs for it.

Can we read this database with, for example, Oracle Express? If yes, how?

Oracle 8.x.x has been out of support for some time.

Apologies for the bad news, but your only option is to either recover the software from the dead disk, or ask Oracle (obviously with a valid support contract) for a copy of the software. Raise a ticket on My Oracle Support and they will help you.


Do VIEWS need a primary key in Galera? We have a MariaDB Galera Setup, so do we require primary keys in a MySQL view?

It contains a complex select query with joins of tables and the values get updated. so do they replicate in a Galera DB setup properly or do we need to create primary Key for the views as well. Can anyone explain what's the behaviour of the Views in a Galera cluster environment.

VIEW it is just a query (since mysql does not start support materialized views)

as a query - it doesn't store data and doesn't need to have any keys.
and view - does not replicate across a cluster and/or replicas, it just executed over local tables.

but underlying tables (included in a views query), yes tables included in this query must have.


Oracle empty string converts to null When doing an insert, an empty string is converted to null:

insert into test (f) values ('');

Now, there is a row with f containing a null.

But, when I query the table, I cannot use '':

select * from test where f='';
no rows selected

I can use null:

select * from test where f is null;
____F_
NULL

So... it appears that Oracle decided that empty strings cannot be used for insert,  but they remain empty strings when doing queries. Where is the documentation on when an empty string becomes a null and when it remains an empty string?

This says it all:

select NVL('','it is null') as value
from dual;

SQL Fiddle

2 things:

1) '' gets converted to NULL on insert. That's an Oracle VARCHAR2 thing.

2) select * from test where f=''; is trying to do select * from test where f=NULL, which isn't defined, and will return nothing because NULL doesn't like the equality operator. You have to use IS NULL or IS NOT NULL.

I'll add that the CHAR datatype behaves differently because it is padded.


Migrate saved logins I've been using SQL Server Management Studio 2012 for a while (on Windows 10, if that matters) and I've collected a good bunch of saved logins.

I've now installed version 17.2, which apparently installs side by side (rather than replacing previous version), and my logins are not available in this version.

Is there a way to migrate them from 2012?

All cached logins are saved on %APPDATA%\Microsoft\SQL Server Management Studio\%Version%\SqlStudio.bin

Each time you run SSMS and save a login, it is added to SqlStudio.bin file. Now i assume your new SSMS has no saved logins, therefore simply replace SqlStudio.bin with the one from old SSMS

Steps:

Go to: %APPDATA%\Microsoft\SQL Server Management Studio\11.0\SqlStudio.bin , copy that file and then go to %APPDATA%\Microsoft\SQL Server Management Studio\14.0 and replace the existing one.


Slot Time Challenge - Doctor Appointment database schema I am trying to make a doctor appointment system where patient online will have two options:

First time visit (this visit should be 20 mins)
Follow up visit (this visit should be 10 mins)

Constraints:

Price will be different based on wither its a first time/follow up
Doctor might have break times between slots
System interface will support both options when booking the slot
we need the least gap time between slots to make max. usage of doctors availability

The model we finished so far was based on creating a Doctor Slot table that's like the below

Consider the below Example
if DoctorSlot is N and
N=10 mins

Case First Time
if(N*2)== Free
Make reservation
block two slots instead of one

Case Follow Up
if(N)==Free
Make Reservation
Block one slot

Example
Doctor: 9:00 to 9:10
Doctor: 9:10 to 9:20
Doctor: 9:20 to 9:30

Case First Time
Show to patient 9:00 to 9:20

Challenges:

Append time from 9:00 to 9:20 (there could be a buffer time (break for doctor) between slots for doctors)
We will have two slot IDs from database instead of one (which SlotID will be used with Order)
How to show user in run time based on his case which time generic model we will use and update prices later on accordingly
If user booked a first time slot, then another user booked a follow up, there will be gaps and how to handle time in SQL Server in database

Questions:

What is the best database schema to achieve a solution that satisfy all possible scenarios?
What is the best method to deal with time entity from SQL Server/ASP.NET POV?

I would suggest an Appointment table which stores the current appointments for each doctor. We can add some constraints on this table which limit the appointment start times to even ten-minute times (e.g. 9.00, 9.10, 9.20) plus add some other common sense checks like EndTime after StartTime and doctor can't have two appointments starting at the same time. Assume that you'd also like doctors to only work between 9am and 5pm, because everyone needs some work-life balance.

CREATE TABLE Appointment (
    DoctorID char(1) NOT NULL,
    [Date] date NOT NULL,
    StartTime time(0) NOT NULL CONSTRAINT CHK_StartTime_TenMinute CHECK (DATEPART(MINUTE, StartTime)%10 = 0 AND DATEPART(SECOND, StartTime) = 0),
    EndTime time(0) NOT NULL CONSTRAINT CHK_EndTime_TenMinute CHECK (DATEPART(MINUTE, EndTime)%10 = 0 AND DATEPART(SECOND, EndTime) = 0),
    Status char(1) NOT NULL,
    UserID char(1) NOT NULL,
    Price int NOT NULL,
    CONSTRAINT PK_Appointment PRIMARY KEY CLUSTERED (DoctorID, [Date], StartTime),
    CONSTRAINT CHK_StartTime_BusinessHours CHECK (DATEPART(HOUR, StartTime) > = 9 AND DATEPART(HOUR, StartTime) < = 16),
    CONSTRAINT CHK_EndTime_BusinessHours CHECK (DATEPART(HOUR, EndTime) > = 9 AND DATEPART(HOUR, DATEADD(SECOND, -1, EndTime)) < = 16),
    CONSTRAINT CHK_EndTime_After_StartTime CHECK (EndTime > StartTime));
CREATE INDEX iDoctor_End ON Appointment (DoctorID, [Date], EndTime);

We can insert some data to this table to see what it looks like. Note that the third insert will fail because it is prevented by our constraint. The doctor can't have two appointments starting at the same time.

INSERT INTO Appointment VALUES ('A', '20170420', '09:00:00', '09:10:00', 'P', '1', '0');
INSERT INTO Appointment VALUES ('A', '20170420', '09:20:00', '09:40:00', 'C', '2', '10');
INSERT INTO Appointment VALUES ('A', '20170420', '09:00:00', '09:20:00', 'C', '2', '10');

Let's assume that you have a numbers table. If you don't many other people have described how to create one. If all else fails, this could create one for you but it's probably not the best way.

CREATE TABLE Numbers (Number int PRIMARY KEY CLUSTERED);
DECLARE @number int = 0;
WHILE @number < 1000
BEGIN
    INSERT INTO Numbers VALUES (@number);
    SET @number += 1;
END

Now if we want to see free slots for a particular doctor, all we need to do is specify which doctor, and how long the slot is that we're looking for:

DECLARE @doctorID char(1) = 'A';
DECLARE @length tinyint = 20;
WITH Slots AS (
    SELECT StartTime = DATEADD(MINUTE, ((DATEPART(MINUTE, GETDATE())/10)+1+Number)*10, DATEADD(HOUR, DATEPART(HOUR, GETDATE()), CONVERT(smalldatetime, CONVERT(date, GETDATE())))),
           EndTime = DATEADD(MINUTE, @length, DATEADD(MINUTE, ((DATEPART(MINUTE, GETDATE())/10)+1+Number)*10, DATEADD(HOUR, DATEPART(HOUR, GETDATE()), CONVERT(smalldatetime, CONVERT(date, GETDATE())))))
      FROM Numbers)
SELECT TOP 15 DoctorID = @doctorID,
    s.StartTime,
    s.EndTime
  FROM Slots AS s
  WHERE NOT EXISTS (SELECT 1
                      FROM Appointment AS a
                      WHERE (CONVERT(time(0), s.StartTime) < a.EndTime AND CONVERT(time(0), s.EndTime) > a.StartTime)
                        AND a.DoctorID = @doctorID
                        AND a.[Date] = CONVERT(date, s.StartTime))
    AND DATEPART(HOUR, s.StartTime) >= 9
    AND DATEPART(HOUR, DATEADD(MINUTE, -1, s.EndTime)) <= 16
ORDER BY s.StartTime;

That looks a little awkward, so if anyone can improve that date logic happy to take suggestions.

If a doctor wants a break, then enter the break as an appointment and it won't be available for booking.

Note that the table constraints don't enforce non-overlapping appointments. This is possible but it's more complicated. If this were my system I'd think about some system (e.g. trigger) to finally verify that the appointment doesn't overlap with an existing one at the time of insert, but that's up to you.


Seeing the query text, not just the plan, could help us help you.

Your hash joins are resorting to multiple batches, so increasing "work_mem" (by a lot) could help, if you have the RAM to support it.

Increasing "max_parallel_workers_per_gather" could lead to more parallelism which could be beneficial.

An index on (calltime,imsi) if you don't already have one might help, but given that you are fetching nearly a million rows it might not.

What are you going to do with all these rows once you get them?  Maybe you do aggregation on the server, rather than doing it in the client (if that is what you are doing).


Errors declaring variables in stored procedure? I'm sort of new to SQL and stored procedures. I'm trying to set some variables to use later on in a transaction while also setting a rollback variable on an exception, but I'm not able to do this.

I don't know where the error is because when I switch the how_many section after the _rollback, The error changes.

What's wrong with the way I'm declaring variables here?

DELIMITER $$
DROP PROCEDURE IF EXISTS `do_thing` $$
CREATE PROCEDURE `do_thing`()
BEGIN
    DECLARE how_many INT;
    SELECT COUNT(*) FROM things INTO how_many;
    -- Prepare roleback.
    DECLARE `_rollback` BOOL DEFAULT 0;
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
    -- Start transaction.
    START TRANSACTION;
    -- Do all the things.
    IF `_rollback` THEN
        ROLLBACK;
    ELSE
        COMMIT;
    END IF;
END $$
DELIMITER ;

DECLARE is permitted only inside a BEGIN ... END compound statement
  and must be at its start, before any other statements. Declarations
  must follow a certain order. Cursor declarations must appear before
  handler declarations. Variable and condition declarations must appear
  before cursor or handler declarations.

Try this :

DELIMITER $$
DROP PROCEDURE IF EXISTS `do_thing` $$
CREATE PROCEDURE `do_thing`()
BEGIN
       -- DECLARE STATEMENTS first
        DECLARE how_many INT;
        DECLARE `_rollback` BOOL DEFAULT 0;
        DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET `_rollback` = 1;
        --
        SELECT COUNT(*) INTO how_many FROM things;

        -- Start transaction.
        START TRANSACTION;
                -- Do all the things.
        IF `_rollback` THEN
                ROLLBACK;
        ELSE
                COMMIT;
        END IF;
END $$
DELIMITER ;


How do I get the GRANT statements for a specified schema? Is there a way to generate the GRANT statements that have been run on a schema in PostgreSQL, for example if myUser has been granted usage on mySchema the script would generate

GRANT USAGE ON SCHEMA mySchema TO myUser;

Use pg_dump

pg_dump -d myDatabase --schema mySchema --section=pre-data |
  grep '^GRANT'

If you want just the GRANT on the schema object.

pg_dump -d myDatabase --schema mySchema --section=pre-data |
  grep '^GRANT .* ON SCHEMA .* TO'


You don't need the long CASE, use MySQL's CEILING function - https://dev.mysql.com/doc/refman/8.0/en/mathematical-functions.html#function_ceiling
What is the data type for the rating column? My guess is that it's a FLOAT, and being an approximate, your actual values fall within the ranges that aren't covered by the CASE , 2.00001 for example. Change it to DECIMAL to avoid similar issues.
When using CASE, it's a good idea to always include an ELSE clause. Try adding ELSE -1  or other value just to troubleshoot this issue, and you will see for yourself.

HTH
UPDATE - I stand partly corrected. While my answer and points are still valid, I believe that @RickJames and @RolaondoMySQLDBA were right for this case as well.
According to IEEE 754 these number do happen to have exact representation as FLOAT, and the problem was your CASE. Using CEILING is better than CASE and IF for this, but to understand the root issue see @RolandoMySQLDBA Answer below, and @RickJames comments.
UPDATE 2019-07-21 18:03 EDT (RolandoMySQLDBA)
In all fairness, CEILING() does make the most sense.
I ran this
SELECT CEILING(rating) ratings,COUNT(1)
FROM user_review GROUP BY CEILING(rating);

on the sample data and got this
mysql> SELECT CEILING(rating) ratings,COUNT(1)
    -> FROM user_review GROUP BY CEILING(rating);
+---------+----------+
| ratings | COUNT(1) |
+---------+----------+
|       5 |        3 |
|       3 |        2 |
|       4 |        2 |
|       1 |        2 |
|       2 |        2 |
+---------+----------+
5 rows in set (0.00 sec)

So, CEILING() is the most concise in this case. +1 to you !!!


Best way to define related resources db model We have different type of resources (docs, events, media,...) each one has each own table to store related information for each resource, having their respective PK-id.

We have the need to manage the relation between different resources, it is, a document could have, for example, two events and one media collection related to it.

Our first idea is have the following schema:

resource_id | resource_type | related_id | related_type

We need to know the type of the resource because different type of resources could have the same id.

A tuple example of the approach:

resource_id | resource_type | related_id | related_type
-------------------------------------------------------
1           | document      | 1          | event
1           | document      | 5          | event
1           | document      | 33         | media

The relation could be bidirectional or directional. Then, for example this record could also be valid

33          | media         | 1          | document

Then,
 - when displaying the collection with id 33, we will find the document related (doc_id:1)
 - when displaying the document with id 1, we will find the related collection and other two events (media_id:33,event_id:1,event_id:5)

To know the details of the related resources (title of the media collection, for example, we will query the table of media collections)

I think that schema could cover our need, but since I am not a DBA guru, I would appreciate to know if this approach is good enough or there are other ideas which cover this need better in terms of performance, flexibility, ...

EDIT: Adding the schema of the mentioned approach

CREATE TABLE `related_resources` (
  `fk_resource_id` int(11) NOT NULL,
  `resource_type` varchar(64) NOT NULL,
  `fk_related_id` int(11) NOT NULL,
  `related_type` varchar(64) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `related_resources`
--
ALTER TABLE `related_resources`
  ADD PRIMARY KEY (`fk_resource_id`,`resource_type`,`fk_related_id`,`related_type`);

This is definitely a workable solution. The one thing that this prevents is the use of built-in referential integrity options that your DBMS may provide.

As an example, resource_id and related_id cannot be foreign keys to another table - because both could reference multiple other tables.

So, maintenance of the relationship between a "document" resource_id and the document table must be handled in your code. You need to be sure that (for example) when you delete a document row, you remove rows from your resource_link table with that "document" resource_id or related_id.

In general, it's easiest to allow the database to maintain such information appropriately by itself. It's much less likely that you'll inadvertently allow a document row to be deleted without cleaning up connections elsewhere.

To avoid this, you'd need a solution where either:

you have 2 bridge tables for each possible pair of resource types (document_media, document_event, media_document, media_event, event_document, event_media, etc.; possibly even document_document, media_media and event_event); or
each resource type would need a bridge table with a column for each possible related resource (e.g., document_bridge would have the columns document_id, media_id, event_id, related_document_id, etc.), with a constraint to ensure one and only one of the related IDs is set.

Each of your options has its pros and cons. If you're confident that you'll be able to enforce data referential integrity "manually", your solution may be the cleanest, and the easiest for another person to follow. If the available resource type are expected to grow dramatically, then the other solutions (both of which would require adding or modifying multiple tables) will become more and more awkward to work with over time.

On the other hand, if the resource types are expected to be static, and there aren't more than a couple more, using one of the other solutions would ensure referential integrity (if properly set up with foreign keys). Also note that, with either of the other two solutions, it would be possible to create a view matching your proposed single table; if simply having a single place to get a simple list of what's related to something is considered a plus, you can have that with any option.

The queries to retrieve all related resources will be a little messy regardless of which option you take.

In terms of performance, the more indirection you have, the more work the DB engine will have to do - technically, at least. In practice, I suspect the performance difference between what you propose and (say) the "resource X resource" version (specific tables for each possible relationship, for both possible directions) would be small enough to be ignorable.

So, I don't think there is a single "best" way to do this. Ultimately, the comfort level of the development and support teams with the solution is likely to be the most critical determining factor. If your development team has experience with manually maintaining data referential integrity, your solution is perfectly reasonable, and might well be the easiest for someone else to follow.


How to find the actual space being consumed by indexes on a table in Oracle? I want to find the actual space being consumed by indexes on a table in oracle 10g . I do not intend to include the space reserved by oracle for future usage. (The overhead by oracle should not be considered.) I want the used bytes not the allocated bytes.

Can you help me in going ahead?

Also, is there a way to find the actual size of a long field in a table.

PS: vsize() and dbms_lob.getlength() do not work.

SELECT idx.index_name, SUM(bytes)
  FROM dba_segments seg,
       dba_indexes  idx
 WHERE idx.table_owner = <<owner of table>>
   AND idx.table_name  = <<name of table>>
   AND idx.owner       = seg.owner
   AND idx.index_name  = seg.segment_name
 GROUP BY idx.index_name

will show you the amount of space actually consumed by each index.  I'm not clear if that's exactly what sorts of overhead you're trying to account for and how you are distinguishing "used" and "allocated" in the context of an index.  If you want to account for free space in the index, you can use the DBMS_SPACE.SPACE_USAGE procedure to determine how many partially empty blocks are in the index.


Wrong Memory is displaying in task manager for Default and Named instance in Sqlserver I have installed 2 instances on a Sqlserver 2016.When the Performance of the server is degrading ,  i could see huge transactions are happening on the Default instance and there were no connections and a single small database in a named instance.But when i see in the task manager , i see the memory of the named instance is increasing drastically with no CPU and default instance remains idle with High CPU.

Perhaps you have Lock Pages In Memory for the "active but low memory" instance? Task manager only show a minuscule memory usage in this case. You can for instance look in the error log file (startup info) if lock pages are used.
So, there's nothing to "fix". You should look at the memory usage through something else then Task Manager. For instance sys.dm_os_process_memory (physical_memory_in_use_kb) or sys.dm_os_sys_info (committed_kb)


How to determinate in a trigger direct inserts and inserts via stored procedure TableX can be modified in two ways:

client does "direct" inserts
client uses stored procedure to inserts records

How to determinate the way of client's call (direct or stored proc) in a trigger of tableX

Thank you.

You can't determine it. All you have available to you is the INSERTED and DELETED tables. You could probably cheat but it's a gawdawful, bloody hack. Define a view with an "extra" column, what it is doesn't matter. All your proc insert/update assign one value to that column, direct updates supply a different (or non-existent value). Create an instead of trigger on that view and then do your update logic based on the source flag

CREATE VIEW dbo.vwTableX
AS
SELECT
    X.*
,   'T' AS fake_source_column
FROM
    dbo.tableX X

GO
CREATE TRIGGER insertHack ON dbo.vwTableX
INSTEAD OF INSERT
AS
BEGIN

    SET NOCOUNT ON

    IF EXISTS (SELECT * FROM INSERTED I WHERE I.fake_source_column = 'P')
    BEGIN
        -- perform logic for PROC sourced data
        INSERT INTO
            dbo.tableX
        SELECT
            I.col1
        ,   I.col2
        -- everything but our fake column
        FROM
            INSERTED I
        WHERE
            I.fake_source_column = 'P'
    END

    IF EXISTS (SELECT * FROM INSERTED I WHERE I.fake_source_column = 'D' OR I.fake_source_columns IS NULL)
    BEGIN
        -- perform logic for direct access data
        INSERT INTO
            dbo.tableX
        SELECT
            I.col1 * 2 -- or whatever special logic you wish to apply
        ,   I.col2
        -- everything but our fake column
        FROM
            INSERTED I
        WHERE
            I.fake_source_column = 'D' OR I.fake_source_columns IS NULL
    END
END
GO

-- lather, rinse, repeat for update and/or delete trigger

So that's a solution, but really, what is the problem you are trying to solve?  Why do you care whether the DML comes from proc or non-proc?


MySQL Reusing calculation in SELECT clause in a VIEW I have an SQL query that has a simple date difference between NOW() and the creation date (is in the table). I need to reuse this again within the SELECT clause in a VIEW. Is this possible at all?

The query (note substitutions have been made from the original):

CREATE VIEW upcoming_events_view AS
SELECT *, TIMESTAMPDIFF(WEEK, `create_date`, NOW()) AS week_diff,
       (week_diff % week = 0) AS do_current_week
FROM event_repeats

Needless to say, this didn't work due to the week_diff being reused in the SELECT clause. As a normal SELECT query, I found variables worked; however, I can't use that in a VIEW:

SELECT *, @week_diff := TIMESTAMPDIFF(WEEK, `create_date`, NOW()) AS week_diff,
       (@week_diff % week = 0) AS do_current_week
FROM event_repeats

The table:

CREATE TABLE IF NOT EXISTS `event_repeats` (
  `event_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `event_name` varchar(150) NOT NULL,
  `start_date` date NOT NULL,
  `week` int(11) NOT NULL,
  PRIMARY KEY (`event_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=51 ;

What's the best way to do this (if any)?

Thanks in advance.

You can only do this in a view by repeating the expression.

CREATE VIEW upcoming_events_view AS
SELECT *,
       TIMESTAMPDIFF(WEEK, `create_date`, NOW()) AS week_diff,
       (TIMESTAMPDIFF(WEEK, `create_date`, NOW()) % week = 0) AS do_current_week
  FROM event_repeats

The NOW() function uses the time at the beginning of statement execution no matter how long the query takes to run, so you'd never get two different answers from NOW() in the same query.  The TIMESTAMPDIFF() function's second invocation will probably not be optimized away by the optimizer, but this is a trivial calculation.  It benchmarks at 0.5 microseconds on an AMD Opteron 1GHz processor, so a million calls to that function takes half a second.

Since your create_date is a DATE, not a DATETIME, the CURDATE() function might be a more technically accurate choice, since the TIMESTAMPDIFF() function first converts your dates to datetimes with an implicit 00:00:00 time element.

mysql> select TIMESTAMPDIFF(WEEK,NOW(),'2013-02-23'), TIMESTAMPDIFF(WEEK,CURDATE(),'2013-02-23');
+----------------------------------------+--------------------------------------------+
| TIMESTAMPDIFF(WEEK,NOW(),'2013-02-23') | TIMESTAMPDIFF(WEEK,CURDATE(),'2013-02-23') |
+----------------------------------------+--------------------------------------------+
|                                      0 |                                          1 |
+----------------------------------------+--------------------------------------------+


In slow_log tables, sql_text column showing "throttle: index not used' warning(s) suppressed" instead of exact query I'm trying to find the slow running queries from slow_log table (as I don't have direct access to server to check slow log file). In sql_text column instead of query it is showing "throttle: index not used' warning(s) suppressed". Could someone help me how to find the slow running query.

Unfortunately, you are not going to get those queries from mysql.slow_log. Why ?

There are two options evidently being used

log_queries_not_using_indexes
log_throttle_queries_not_using_indexes

According to MySQL Documentation (The Slow Query Log, Paragraph 9)

  To include queries that do not use indexes for row lookups in the statements written to the slow query log, enable the log_queries_not_using_indexes system variable. When such queries are logged, the slow query log may grow quickly. It is possible to put a rate limit on these queries by setting the log_throttle_queries_not_using_indexes system variable. By default, this variable is 0, which means there is no limit. Positive values impose a per-minute limit on logging of queries that do not use indexes. The first such query opens a 60-second window within which the server logs queries up to the given limit, then suppresses additional queries. If there are suppressed queries when the window ends, the server logs a summary that indicates how many there were and the aggregate time spent in them. The next 60-second window begins when the server logs the next query that does not use indexes.

What this shows is that your log_throttle_queries_not_using_indexes is set so that SQL text is suppressed. You can verify this with

mysql> SELECT @@global.log_throttle_queries_not_using_indexes;

Since it is a global dynamic variable, you must login as root@localhost and run

mysql> SET GLOBAL log_throttle_queries_not_using_indexes = 0;

This should expose new queries entering mysql.slow_log, but you will never see what those previous slow queries were.

You must run this

mysql> SELECT @@global.log_output;

If you see FILE,TABLE that means there is a text version of the slow query log. You will have to get your SysAdmin to get you the desired slow log info (Starbucks GiftCard might be required for this one. If the slow log file contains the same messages, take your Starbucks GitftCard back.)


Is it Ok to use GUID as the Foreign Key? Here's my current situation:

We have a table storing data submitted from the website form, say "MyTable". The table uses bigint (auto-increment) as the primary key. Everything is good so far.

Now, that table is required to be able to store the records uploaded from the mobile devices. But the records uploaded from the mobiles are to be verified first before being stored into the "MyTable". Hence, a staging table is created, say "Temp_MyTable".

In other words, this is the flow:

Mobile devices ------> Staging Table ------>  Actual Table

                    [GUID as PK]         [bigint as PK], [GUID as FK]

The thing is, the Staging Table is using GUID as the primary key. Reason, we have to reference back the records uploaded from multiple devices. Users may re-upload the record after modification. Note: The GUID here is generated by the mobile (simple javascript).

So, now, i want to reference back the records of the Actual Table to the Staging Table. In this case, a Foreign Key 'probably' required. Unfortunately, it is the GUID data type.

And after reading a number of articles, it seems that using GUID as the Foreign Key may be disadvantageous - more storage or slower.

I would like to know, is it Ok to use GUID as the Foreign Key? Can someone justify that it is actually 'perfectly fine'? Or is there a better design?

Using a UUID column as a foreign key is fine, and if that is the only candidate key on the parent record recommended/required. The FK must refer to a value that is unique in the referred table, this usually means the primary key though any column (or combination of columns) defined as unique by way of a constraint or index will do. Some suggest that it should be something that is always populated too (i.e. defined as not nullable), otherwise not all the rows in the referred table can be referred to by the FK, though there are many examples where this is not correct.

If the primary key on the referred table is a uniqueidentifier column then unless you have another unique value in that table you should use that as your foreign key target.

The only real reason that people might recommend against UUIDs as foreign keys is space: such values take 16 bytes to store instead of 4 (assuming the alternative is a 32-bit integer) or 8 (for a bigint).

There are arguments against using UUIDs as primary keys (though there are equally valid arguments for too!) especially if your PK is also your clustering key (i.e. the only member in or a significant member of the table's clustered index if it has one), but that is a different discussion.


Transform/map json object array to primitive for GIN indexing Say I have a table with a jsonb column that contains data structured like this:

{
  "records": [
    {
      "id": 1,
      "tag": "address"
    },{
      "id": 1,
      "tag": "receipt"
    }
  ]
}

I'd like to be able to index this column such that I can request "give me all rows where there is a record of type 'receipt'".

I'm trying to find an expression that would output a jsonb array that looks like

["address","receipt"]

so then I can plug that expression into

CREATE INDEX ON t1 USING GIN (expr)

I've been playing around with all different combinations of the JSONB functions, but I think I'm in over my head.

I wouldn't store the data like that in the first place, if I could help it.  Assuming that that ship has sailed, I would at least transform it to a native PostgreSQL array, not a JSON(B) array, as that would give you better statistics estimates and so probably better execution plans, as well as just plain faster execution.

Due to some arbitrary-seeming restrictions on the composition of set-returning functions with aggregations and expression indexes, I couldn't find a way to do this without the help of some helper function (which is probably a good idea anyway).

CREATE OR REPLACE FUNCTION public.helper(jsonb)
 RETURNS text[]
 LANGUAGE sql
 IMMUTABLE
AS $function$
select array_agg(x) from
   (select jsonb_array_elements($1->'records')->>'tag') as f(x);
$function$

Followed by:

CREATE INDEX ON t1 USING GIN (public.helper(jsonb_colname));

And then:

select * from t1 where public.helper(x) && ARRAY['receipt'];

But taking a step back, you could do this directly using the built-in jsonb containment operators with no transformations or expressions at all (although the index will probably larger and slower than a tailored one):

create index on t1 using gin (jsonb_colname);
select * from t1 where jsonb_colname @> '{"records":[{"tag":"receipt"}]}';


Lost data after restore I had an issue after backing up and restoring the DB's from server to server, We have since had some issues with missing information.  Is there a way at this point in time to know if the databases were restored fully?
Are there log files anywhere?

If the restore completed, then the database was restored fully. What kind of backup did you take (show the code, or explain the steps if using a 3rd party tool or SSMS GUI)? How did you restore that database (show the code, or explain the steps)? What information was missing?

This answer and the link to Paul Randal's article will help explain how a full backup happens and should answer your Are the log files anywhere question, assuming you did a Full backup. You can, of course, do transaction log backups too. - scsimon

I've seen cases where backup was done appending to the file and then restore was done from the first file (which is default) from that file. I.e., restoring not-the-latest-backup. Check using RESTORE HEADERONLY. - tibor-karaszi


Consolidating multiple related queries, each using different window and/or criteria for counting records I'm accumulating results for multiple contingency tables, one for each Test. As an example:

Contingency Table for TestA (one for each test, and for each word)
                EventsWithWord  EventsWithoutWord
TestA passed          n11                 n10
TestA failed          n01                 n00

For analysis, I need the number of events matching each of the following categories (this is how R expects the data results):

TestA passed for each word: n11
TestA results available for each word: n_1 (sum of n11 and n01)
TestA passed:   n1_ (sum of n11 and n10)
TestA results available: n__

Table "TestResults": columns: CompanyID nvarchar(20) NOT NULL, TestName nvarchar(255) NOT NULL, CompanyResults bit NULL

Table "Data" (one row for each event): Columns: EventID int NOT NULL, CompanyID nvarchar(20) NOT NULL

Table "WordEventMap": Columns: EventID int NOT NULL, Word nvarchar(255) NOT NULL

Is there a way of combining some of the queries (e.g., with windowing functions)? Some of these tables are very large--40 million rows currently in WordEventMap and 1.3 million rows in Data, and I expect the results table will have about 4 million rows (40 different tests, 100,000 different words). The current execution plan does not consolidate any of the Common Table Expressions.

Current query:

WITH CTE1
AS (SELECT
  COUNT(DISTINCT Data.EventID) AS n__,
  TestResults.TestName
FROM TestResults
INNER JOIN Data
  ON TestResults.CompanyID = Data.CompanyID
WHERE TestResults.CompanyResults IS NOT NULL
GROUP BY TestResults.TestName),
CTE2
AS (SELECT
  COUNT(DISTINCT Data.[EventID]) AS n11,
  TestResults.TestName,
  WordEventMap.Word
FROM TestResults
INNER JOIN Data
  ON TestResults.CompanyID = Data.CompanyID
INNER JOIN WordEventMap
  ON Data.[EventID] = WordEventMap.EventID
WHERE TestResults.CompanyResults = 1
GROUP BY TestResults.TestName,
         WordEventMap.Word),
CTE3
AS (SELECT
  COUNT(DISTINCT Data.[EventID]) AS n1_,
  TestResults.TestName
FROM TestResults
INNER JOIN Data
  ON TestResults.CompanyID = Data.CompanyID
WHERE TestResults.CompanyResults = 1
GROUP BY TestResults.TestName),
CTE4
AS (SELECT
  COUNT(DISTINCT Data.[EventID]) AS n_1,
  TestResults.TestName,
  WordEventMap.Word
FROM TestResults
INNER JOIN Data
  ON TestResults.CompanyID = Data.CompanyID
INNER JOIN WordEventMap
  ON Data.[EventID] = WordEventMap.EventID
WHERE TestResults.CompanyResults IS NOT NULL
GROUP BY TestResults.TestName,
         WordEventMap.Word)
SELECT
  CTE2.TestName,
  CTE2.Word,
  n11,
  n1_,
  n_1,
  n__
FROM CTE2
INNER JOIN CTE4
  ON CTE2.Word = CTE4.Word
  AND CTE2.TestName = CTE4.TestName
INNER JOIN CTE1
  ON CTE2.TestName = CTE1.TestName
INNER JOIN CTE3
  ON CTE2.TestName = CTE3.TestName

Here's a SQL fiddle using the above structure and the below data. The above query is saved as the View [Results]. http://sqlfiddle.com/#!6/a8155e/1

Sample data:

Table TestResults
CompanyA TestA 1
CompanyA TestB 0
CompanyB TestA NULL
CompanyB TestB 1
CompanyC TestA 0
CompanyC TestB 1

Table Data
1    CompanyA
2    CompanyA
3    CompanyB
4    CompanyC
5    CompanyB

Table WordEventMap
1 airplane
1 tightrope
1 eggplant
2 eggplant
2 aardvark
2 eggbeater
3 airplane
3 aardvark
3 spaghetti
4 airplane
4 eggplant
4 wikipedia
5 eggplant
5 eggbeater
5 tightrope
5 licorice

Results Set
TestA   aardvark    1   2   1   3
TestB   aardvark    1   3   2   5
TestA   airplane    1   2   2   3
TestB   airplane    2   3   3   5
TestA   eggbeater   1   2   1   3
TestA   eggplant    1   2   2   3
TestB   eggplant    2   3   3   5
TestB   licorice    1   3   1   5
TestB   spaghetti   1   3   1   5
TestA   tightrope   1   2   1   3
TestB   tightrope   1   3   2   5
TestB   wikipedia   1   3   1   5

When you think that optimizer should be able to combine two CTEs into one why don't you write a single CTE, human beings are still smarter than optimizers :-)

As there's only a different WHERE-condition the CTEs 2&4 and 1&3 can be combined using conditional aggregation:

WITH CTE1
AS (SELECT
  COUNT(DISTINCT Data.EventID) AS n__,
  COUNT(DISTINCT CASE WHEN TestResults.CompanyResults = 1 THEN Data.[EventID] END) AS n1_,
  TestResults.TestName
FROM TestResults
INNER JOIN Data
  ON TestResults.CompanyID = Data.CompanyID
WHERE TestResults.CompanyResults IS NOT NULL
GROUP BY TestResults.TestName),
CTE2
AS (SELECT
  COUNT(DISTINCT Data.[EventID]) AS n_1,
  COUNT(DISTINCT CASE WHEN TestResults.CompanyResults = 1 THEN Data.[EventID] END) AS n11,
  TestResults.TestName,
  WordEventMap.Word
FROM TestResults
INNER JOIN Data
  ON TestResults.CompanyID = Data.CompanyID
INNER JOIN WordEventMap
  ON Data.[EventID] = WordEventMap.EventID
WHERE TestResults.CompanyResults IS NOT NULL
GROUP BY TestResults.TestName,
         WordEventMap.Word)
SELECT
  CTE2.TestName,
  CTE2.Word,
  n11,
  n1_,
  n_1,
  n__
FROM CTE2
INNER JOIN CTE1
  ON CTE2.TestName = CTE1.TestName
WHERE n11 > 0
;

But the main performance problem are those DISTINCTs, based on uniqueness in your actual data you might be able to remove some (for your example data no DISTINCT is needed).


Postgres fetch exact ROW using JSON column I have records in table like below format.

Table struture

CREATE TABLE test.orders (
id serial NOT NULL,
info json NOT NULL,
value varchar NULL,
CONSTRAINT orders_pkey PRIMARY KEY (id)

);

If I pass value for info column {"101":1} then only value s1 should be fetched not the others.

So is there any feasible way in postges to fetch exact this type of result?

Thanks

If you want those where the value matches exactly the key/value pair, use = with a jsonb type.

select *
from the_table
where info::jsonb = '{"101": 1}'

As your column is not defined as jsonb (which it should be) you have to cast it before you can compare it.


What is preventing ORA-00376 errors appearing in the alert log? An overnight hard drive failure had various side-effects, including leaving a single datafile needing recovery:

SQL> select count(*) from v$datafile where status='RECOVER';

  COUNT(*)
----------
         1

Everything is now back to normal but one mystery remains: before recovery, the client application logged an ORA-00376 to a logging table when a batch job tried to access a segment in the datafile. However this error did not appear in the alert log, how can this happen?

At the time of the ORA-00376 error, the following did appear in the alert log:

Errors in file /app/oracle/admin/live33/bdump/live33_j002_938224.trc:
ORA-12012: error on auto execute of job 24760001
ORA-20001: Error during batch run
ORA-06512: at "*******.***_*****", line 266
ORA-06512: at "*******.***_***_***_*****", line 355
ORA-06512: at line 1

And at the time of the original issue:

KCF: write/open error block=0x2fb23 online=1
     file=177 /data/ora9i/live/db3b/nsbme.dbf
     error=27072 txt: 'IBM AIX RISC System/6000 Error: 5: I/O error
Additional information: 195363'
Automatic datafile offline due to write error on
file 177: /data/ora9i/live/db3b/nsbme.dbf

Oracle provides the following information for this error message:

ORA-00376 file string cannot be read at this time
Cause: An attempt was made to read from a file that is not readable. Most likely the file is offline.
Action: Check the state of the file. Bring it online.

Their logic for not including this error message into the alert log is related to the fact that the alert log is to show messages received by the background processes that make up the instance. User session error messages could indicate larger problems with the instance, but all user session error messages are not indicators of instance problems. (ORA-00001, for example)


FIPS 140-2 Compliance Just wondering if anyone has configured FIPS?  Based on the information I found in the Microsoft support pages, it would seem FIPS is configured at the Windows level only and that all instances on my server would then be enabled for FIPS?

At this point, I am only interested in one of the instances using FIPS.

Everything I have read so far, indicates I may need to split this out  to a new server.

Yes, FIPS 140-2 is configured at OS level. You cannot be compliant if the OS itself is not compliant. See KB2733626: Instructions for using SQL Server 2012 in the FIPS 140-2-compliant mode (not sure where is the 2014 equivalent).


Clustered vs Nonclustered indexes, similar to arrays vs linked lists? I'm just starting out as a DBA, and have recently been interested in indexes and how they speed up queries.  I was reading about indexes here:
https://msdn.microsoft.com/en-us/library/ms190457.aspx and trying to understand the difference between a clustered index and a non-clustered index in SQL Server.

From what I read, it sounds like a clustered index sorts the data so the index always 'knows' where the next value lies, directly next to it. This reminded me of an array with contiguous memory.  Similarly, a nonclustered index sounded like a linked list, in that there is a part of the index which holds the value, and also a part  of the index which points to the next index.  Is this an apt compiarison to make?

Hmmm... The leaf level of a clustered index is the table in SQL Server.  This is comparable to a phone book (to borrow from @BrentO) in that all entries in the book are stored in the order of the index - i.e. The index is ordered logically alphabetically (and physically if there is no fragmentation).  This is somewhat similar to a physically contiguous in-memory array, although SQL Server can make no guarantees about data being stored contiguously on disk or about physical order. Each page at leaf level of either index type is part of a doubly linked list and has pointers to the previous and next pages in key order, an ordered index scan uses these to traverse the index in key order.

A non-clustered index is similar to an index at the back of a book in that the index itself is sorted, making it easy to find an item in the index.  You might have two non-clustered indexes, one listing all the phone book entries by phone number, and one listing all the phone book entries by address.  The clustered index (the actual phone book) is laid out physically by the name of the owner of the phone number.  This makes sense since you typically "look up" a phone number by "name of owner".  This is somewhat analogous to an in-memory contiguous array of pointers that point to the locations of the actual data.  The array only holds the index details.

If you only knew the phone number for a person, and needed to know their name, it would take a very long time indeed to find the name by looking through the entire book, name-by-name, for the number, when instead you could simply look at the phone number index to find the name of the owner.  This is how a non-clustered index works.


How to optimize multiple ORDER BYs? I'm getting sluggish performance on a table with ~300,000 rows and B-Trees on each column.

This is for a dynamic pagination page where the query is constructed on demand, and the application caches the primary key in the query's specified order.

For this query

explain analyze
 SELECT supplier_management.buyer_purchase_order_id
     FROM supplier_management
     ORDER BY item_description DESC,
      item_number DESC,
      order_type ASC,
      possession_date DESC,
      shipment_type DESC,
      store_type DESC

I get these results:

  Sort  (cost=51026.98..51750.35 rows=289348 width=72) (actual time=8229.280..12349.596 rows=289348 loops=1)

  Sort Key: item_description, item_number, order_type, possession_date, shipment_type, store_type

  Sort Method: external merge  Disk: 24744kB

  ->  Seq Scan on supplier_management  (cost=0.00..10876.48 rows=289348 width=72) (actual time=0.015..187.426 rows=289348 loops=1)

  Total runtime: 12407.064 ms

How can the performance of a multi-column sort be improved?  Or should I just do it in C++?

Table structure

buyer_purchase_order_id bigint
supplier_number bigint
supplier_name   character varying
purchase_order_number   bigint
store_number    integer
item_number bigint
item_description    character varying
project_type    character varying
order_date  integer
requested_arrival_date  integer
department  character varying
store_type  character varying
shipment_type   character varying
order_type  character varying
quantity_ordered    integer
quantity_allocation integer
quantity_staged integer
quantity_shipped    integer
quantity_received   integer
in_stock_date   integer
in_stock_date_visible_on    integer
show_red    boolean
requested_arrival_date_plus_four_business_days  integer
supplier_status character varying
notes_comments  character varying
requested_arrival_date_color    character varying
grand_opening_date  integer
possession_date integer
consolidator_name   character varying
real_requested_arrival_date_plus_four_business_days integer

No character varying exceeds the length limit for indexing; however, there is some duplication.  Would it be more efficient to put the actual values in another table, normalize, and join on the related integer columns?

A few things you can do:

Use enums or lookups keyed by integer values, or a simple "char" field, instead of varchar sort keys where possible. I'd use an enum  because you can control the sort order easily.

The only serious downside with an enum is that you can't currently drop values from an enum type. You can add them (including inserting them in the middle of the sort order) but not remove them. If that's a problem, you'll want to use a lookup table, or just a field declared "char" that has single character codes.

Also, if you don't need proper language collation, specify COLLATE "C" for character fields, e.g.

CREATE INDEX itemdesc_c ON supplier_management (item_description ASC COLLATE "C");

and then:

ORDER BY ...
    itemdesc COLLATE "C",
    ...

Important things to note:

Pg can combine indexes for predicates (WHERE clauses etc) but not sorting. You can't use a bitmap index scan for a sort. So it can use at most one of the candidate indexes, then it has to sort the rows within each group.
Low-selectivity indexes are a waste of time. If the values aren't widely distributed, don't index the column.
Pg's doing an on-disk sort. Throw more memory at the problem - try SET work_mem = '20MB' to start with. But see my comments below re thrashing with high max_connections. Use a connection pool.
Use a connection pool.
Indexes have a cost - they slow down insert/update/delete and increase vacuum work. So if the index isn't being used lots, get rid of it.
pg_catalog.pg_stat_user_indexes will help you tell which indexes are used.
pg_stat_statements (in contrib) and pg_stat_plans (the latter is an external module) are very useful for capturing data about query patterns, slow queries, etc.
Learn to love the auto_explain module.

Also, if you always do this sort, creating a composite index to match it will help.

CREATE INDEX bigindex ON supplier_management (
      item_description DESC,
      item_number DESC,
      order_type ASC,
      possession_date DESC,
      shipment_type DESC,
      store_type DESC
);

... but be aware that it's only useful for this particular sort, and it'll be a big index so it's only worth having if you do this a lot. In fact, you might as well add supplier_management.buyer_purchase_order_id too, so it can do an index-only scan:

CREATE INDEX bigindex ON supplier_management (
      item_description DESC,
      item_number DESC,
      order_type ASC,
      possession_date DESC,
      shipment_type DESC,
      store_type DESC,
      buyer_purchase_order_id
);


Restore plan cache and buffer pool after database restore? When restoring a database, is there a way to restore the plan cache so it doesn't have to be re-created during production use?

Similar question: is there a way to pro-actively restore the buffer cache during or after a database restore?,…. other than to create and run select statements on the tables previously in the buffer cache, a solution which would be time-consuming to create, and inevitably inaccurate.

This is a followup to this question:  "Does restoring database clear buffer cache and stored proc cache".  edit:  This question is a follow-up because I want to know if there is a workaround to get the plan cache and buffer pool back to their source state.

When restoring a database, is there a way to restore the plan cache so it doesn't have to be re-created during production use?

No.  The caches will have to warm up based on use.  You can force plans using the Query Store for particular queries if you want, as the Query Store is part of the database, and will be present after Restore.


Beginning Data Warehouse Design I am beginning to develop a Data Warehouse. The data does not already exist in a relational database. Instead it will be sourced from various 3rd party vendors.

The data requirements have been drawn up into an Excel spreadsheet containing 15 sheets with column totals ranging from 5 to 60 on each sheet.
Do I create a Fact table of each sheet heading and within each sheet create related dimensions.
In the sheet with 60 columns, none look like they can be grouped into separate dimensions. So do I create one very large dimension?

Also, all dimension columns will have default values, which can be overridden for a specific related fund. How best to do this? Type 2 SCD and add the fund identifier to each dimension?

Thanks!

Your question is too vague and general to get a good response here. What you need is a text (El Masri or Garcia-Molina or Connolly) to explain the basics of RDBMSs and of data warehousing.

Forums like this one are more about smaller particular problems than broad stroke questions like this. Read a text, google. The only tip I would give you is that you should have a good grasp of the basics before going on to DW systems.

There's a time and a place to depart from the rules of normalisation - and don't forget to test with realistic data.


Why MySQL does not provide stddev(distinct expression)? I ask the question because some other numerical aggregates, like sum, avg or count support the distinct modifier. And other databases like Oracle or MSSQL support distinct modifier for stddev.

Possible answer:  Standard Deviation is one of thousands of things that are so little used that it is not worth providing.

You can provide it, even in summary tables, by keeping

COUNT(*)
SUM(x)
SUM(x*x)

There are two stddev forumlas; they differ by only tiny amounts for large sets of data.  One of them can be reformulated using only those 3 aggregates.


SQL Server add zero if there is length less than specific number I have a table with a column of numbers like 789. I want to pad them with 0 to equal 9 digits. For example, 789 should be 789000000

I have tried this code, but there are some numbers more than 3 digits and less than 3 digits.

UPDATE
   Table
SET
   MyCol = MyCol+'0000000'
WHERE
   LEN(MyCol) >9;

The following code does what you need, the bit you need is the REPLICATE section:

declare @var nvarchar(10);

set @var = '321'

select @var + REPLICATE('0',9-LEN(@var))

RESULT:

321000000


SQL Get All Data With The Given IDs And With The Same Data On Single Column So, basically, I have this table
+-------------------
|id  | col1 | name |
|----|------|------|
| 1  |  a   |  la  |
| 2  |  b   |  ria |
| 3  |  w   |  la  |
| 4  |  q   |  la  |
| 5  |  y   |  ria |
|____|______|______|

And, what I want to do is, I want to get all the data based on id 1, 3 and 5 but with the same name. So, since id 5 is different, and id 1 and 3 is the same, I would like to exclude the id 5. I tried with ...HAVING COUNT(name) >=1 but it only shown 1 data. Is it possible? If so, how to achieve this? Thank you
EDIT:
So, what I mean is, I would like to get the result with the same name. Like:
+-------------------
|id  | col1 | name |
|----|------|------|
| 1  |  a   |  la  |
| 3  |  w   |  la  |
|____|______|______|

But, if, for instance in the future, there are case like

"select all data from table where id [1,2,3,5] with the same data in name column"

this will cause a conflict (i guess). the point is, I would like to get the data with the same name where id is given (because I have php app, and the input from the user is only id, and user don't want the different name appear in the result).

It is not absolutely clear what you expect as la should yield te result f 1,3,4 see query below

CREATE TABLE table1
    (`id` int, `col1` varchar(6), `name` varchar(6))
;

INSERT INTO table1
    (`id`, `col1`, `name`)
VALUES

    ('1', 'a', 'la'),
    ('2', 'b', 'ria'),
    ('3', 'w', 'la'),
    ('4', 'q', 'la'),
    ('5', 'y', 'ria');

SELECT id
FROM table1
WHERE `name` IN (SELECT `name` FROM table1  GROUP BY `name` HAVING COUNT(`name`) > 2)
AND id IN (1,2,3,5)

| id |
| -: |
|  1 |
|  3 |

SELECT `name` FROM table1  GROUP BY `name` HAVING COUNT(`name`) > 2

| name |
| :--- |
| la   |

db<>fiddle here


MySQL backup of incremental changesets With a MySQL database, is there a way of logging and/or backing up incremental changesets?

We have a number of large databases (100-200 GB), for which we take daily backups using mysqldump. These backups are a snapshot of the entire database, which can be restored as whole. However, taking/restoring from these backups is not a quick process.

As such, I'd like to be able to take backups of incremental changes - i.e. a backup of what's changed in a given day. Then, assuming a database was in a state consistent with a previous snapshot, we could restore the set of incremental changes to get it to the same/current state.

To some extent, this could be done by operating on mysqldump files - i.e. comparing today's snapshot dump with the previous day's snapshot dump to create a .sql backup of commands to run to convert from the previous snapshot state to today's snapshot state. However, I'd rather not write this myself if there's already a better way of doing this.

Also, I'm conscious that, to some degree this is analogous to MySQL master-slave replication, with the exceptions that the changesets are grouped by time period (i.e. a 24hr window) and not automatically read into another database / server. Furthermore, the binary logs are for the entire database server, not for individual databases.

Additional Comments:

With some further research on this, I've also come across the incremental backup functions in MySQL Enterprise, but we're not using the enterprise version.

It is probably your only option to use the Percona Xtrabackup tool. Fortunately, this is a free tool that has very good documentation.

This tool takes backups in a physical way, rather than logical backups that mysqldump produces. This means that restore time is simply the amount of time required to copy the backup files into the MySQL datadir and not the time to process all the SQL statements in a mysqldump backup. Backup time is generally faster as well, and allows for the possibility for incremental backup.


Alter table modify column on a huge table containing more than 25 million records I want to modify a column on a huge table containing more than 25 million records. I am using sybase 12.5.4. I have read through these posts

What is the most efficient way to alter a column definition in a table with millions of rows

and

ALTER TABLE on a large table with an indexed column

My current strategy is

Drop all non clustered index
Drop clustered index
Alter table modifcy column varchar (32) -- column is char(20) presently
Recreate clustered index
recreate non clustered index

Does it make sense. Is there a faster method do it ?

Edit :

Table Data Size : 16.7 GB
Total rows : 25835155
avg row size : .64 KB
8  Non Clustered Index
Yes,column I am changing is the second column in a 2 column NC index

You should only need to drop and recreate the affected NC index.

Saying that, on a test server, you can see the differences in doing this compared to your strategy of dropping all indexes above.

I reckon dropping/creating the single index would be quicker overall because the data will be shifted around twice otherwise: once char to varchar, another to build the clustered index. Then you have the NC creation overhead.