PostgreSQL partitioning query performance inexplicably bad

What version do you use? Version 9.1 has a major improvement for partitioning, check this blog from Greg Smith.

When using version 9.1, I get this result for this query

EXPLAIN ANALYZE
SELECT MAX(logdate) FROM logs;

:

Result  (cost=0.88..0.89 rows=1 width=0) (actual time=0.251..0.252 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.78..0.88 rows=1 width=8) (actual time=0.243..0.243 rows=1 loops=1)
          ->  Merge Append  (cost=0.78..14533886.71 rows=147545737 width=8) (actual time=0.238..0.238 rows=1 loops=1)
                Sort Key: public.logs.logdate
                ->  Sort  (cost=0.01..0.02 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1)
                      Sort Key: public.logs.logdate
                      Sort Method: quicksort  Memory: 25kB
                      ->  Seq Scan on logs  (cost=0.00..0.00 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                            Filter: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_10_logdate on logs_2011_10 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.014..0.014 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_11_logdate on logs_2011_11 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.006..0.006 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_12_logdate on logs_2011_12 logs  (cost=0.00..6785740.95 rows=146709024 width=8) (actual time=0.055..0.055 rows=1 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_4_logdate on logs_2011_4 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_5_logdate on logs_2011_5 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_6_logdate on logs_2011_6 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_7_logdate on logs_2011_7 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_8_logdate on logs_2011_8 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_9_logdate on logs_2011_9 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_1_logdate on logs_2012_1 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_2_logdate on logs_2012_2 logs  (cost=0.00..68278.63 rows=815824 width=8) (actual time=0.025..0.025 rows=1 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_3_logdate on logs_2012_3 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_4_logdate on logs_2012_4 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_1_logdate on logs_2010_1 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.006..0.006 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_2_logdate on logs_2010_2 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.006..0.006 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_3_logdate on logs_2010_3 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_4_logdate on logs_2010_4 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_5_logdate on logs_2010_5 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_6_logdate on logs_2010_6 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_7_logdate on logs_2010_7 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_8_logdate on logs_2010_8 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_9_logdate on logs_2010_9 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_10_logdate on logs_2010_10 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_11_logdate on logs_2010_11 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_12_logdate on logs_2010_12 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_1_logdate on logs_2011_1 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_2_logdate on logs_2011_2 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_3_logdate on logs_2011_3 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_5_logdate on logs_2012_5 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_6_logdate on logs_2012_6 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
Total runtime: 0.718 ms


I would play it safe and delete files older than three days, a week, two weeks, what have you. You never know when management will run to you saying someone accidentally deleted data yesterday or before the weekend and they need to restore to a point in time. Make sure you retain enough data to cover all of those possible scenarios, and if you don't have enough space, buy more - it'll be much cheaper than being up the creek without a paddle, trust me.

Also, make sure you are testing the ability to restore your backups before deleting older ones, and validating they're consistent. You suggested that you would feel comfortable deleting yesterday's logs based on the fact that last night's backup executed successfully. That the backup executed successfully isn't enough. You could be backing up corruption for a week, and how would you know unless you actually tried to restore?


sp_addserver - Where does it add in which system table

sp_addserver is for adding servers - for anything other than the local server, it is adding a linked server. It puts server information in the catalog view (not system table) sys.servers. It does not affect the name of your current server unless you are actually using it to run sp_dropserver / sp_addserver after you have already renamed the underlying server (which you do to ensure functions like @@SERVERNAME return the right value) - this should be a rare incident.


Generate fake data for several tables with relationships

This is my version of the cross-reference table where you'll store users and the categories they are members of.  Notice there is a primary key clustered index on (UserID, CategoryID); this ensures each row is unique:

CREATE TABLE dbo.XREFUserCategories
(
    UserID INT NOT NULL
        CONSTRAINT FK_XREFUserCategories_UserID
        FOREIGN KEY
        REFERENCES dbo.Users(UserID)
    , CategoryID INT NOT NULL
        CONSTRAINT FK_XREFUserCategories_CategoryID
        FOREIGN KEY
        REFERENCES dbo.Categories(CategoryID)
    , CONSTRAINT PK_XREFUserCategories
        PRIMARY KEY CLUSTERED (UserID, CategoryID)
);
GO

Since you already have data in the Users and Categories tables, you can use a CTE with the ROW_NUMBER windowing function partitioned by UserID and ordered by an essentially random value, NEWID().  This allows us to pick precisely two "categories" for each "user":

;WITH rs AS
(
    SELECT u.UserID
        , c.CategoryID
        , rn = ROW_NUMBER() OVER (
            PARTITION BY u.UserID
            ORDER BY NEWID()
    )
    FROM dbo.Users u
        , dbo.Categories c
)
INSERT INTO dbo.XREFUserCategories(UserID, CategoryID)
SELECT rs.UserID
    , rs.CategoryID
FROM rs
WHERE rs.rn <= 2;

The following is a sample of rows I generated on my system:


Get the id of multiple inserted/updated rows

I could try to get the ids out like this:

  SELECT id, tail FROM tails WHERE tail IN ('Hello', 'cafe', 'Café')

Do

SELECT tails.id, tails.tail, criteria.pos
FROM tails
JOIN ( SELECT 1 pos, 'Hello' tail UNION ALL
       SELECT 2,     'cafe'       UNION ALL
       SELECT 3,     'Café' ) criteria ON tails.tail = criteria.tail

and no problems with identifying (you know what pos to what tail was assigned).


How to know the locale that has been used to initialize a Postgres server?

One option is to use psql -l, but it outputs many lines if there a lot of databases.


You should be able to turn your top set of data into the tabular set of data below it by doing something like this:

SELECT
    Customer,
    Count(CASE Month(Date_Column) WHEN 1 THEN [TXN#] ELSE Null END) AS Jan,
    Count(CASE Month(Date_Column) WHEN 2 THEN [TXN#] ELSE Null END) AS Feb,
    Count(CASE Month(Date_Column) WHEN 3 THEN [TXN#] ELSE Null END) AS Mar,
    Count(CASE Month(Date_Column) WHEN 4 THEN [TXN#] ELSE Null END) AS Apr,
    Count(CASE Month(Date_Column) WHEN 5 THEN [TXN#] ELSE Null END) AS May,
    Count(CASE Month(Date_Column) WHEN 6 THEN [TXN#] ELSE Null END) AS Jun,
    Count(CASE Month(Date_Column) WHEN 7 THEN [TXN#] ELSE Null END) AS Jul,
    Count(CASE Month(Date_Column) WHEN 8 THEN [TXN#] ELSE Null END) AS Aug,
    Count(CASE Month(Date_Column) WHEN 9 THEN [TXN#] ELSE Null END) AS Sep,
    Count(CASE Month(Date_Column) WHEN 10 THEN [TXN#] ELSE Null END) AS Oct,
    Count(CASE Month(Date_Column) WHEN 11 THEN [TXN#] ELSE Null END) AS Nov,
    Count(CASE Month(Date_Column) WHEN 12 THEN [TXN#] ELSE Null END) AS Dec
FROM
    Table_name
WHERE
    Date_Column BETWEEN @SearchStartDate AND @SearchEndDate
GROUP BY
    Customer;

This works because aggregate functions like SUM or COUNT will not include Null values.  The case statement for each month outputs Null if the data is not for the particular month, so the data left being aggregated is only for the one specific month.


Database design how to track a change on a patient row and set in a log table

When I have done similar in the past, I created a changed table. For yours I would keep the patient ID as a foreign key and then list the original value for the field and the new value for the field. You do not need to keep the whole record each time it changes, just a list of the changes. I am assuming you are not required to do so by any regulatory agency, in which case check and see what they require.

I also tracked who made the change and when. You can include a field asking them to justify the change. Good info to have in case there is a question later.


What is the effect of having an opened transaction in MSSQL for too long?

Having an open transaction by itself will have almost no consequence. A simple

BEGIN TRANSACTION
-- wait for a while, doing nothing
-- wait a bit longer
COMMIT

will, at worst, hold a few bytes of status values. No big deal.

Most programs will do actual work within the transaction and this is another matter. The point of a transaction is so you can be sure that several facts within the database are true simultaneously, despite there being other users writing to the same database concurrently.

Take the cannonical example of transferring money between bank accounts. The system must ensure that the source account exists, has sufficient funds, the destination account exists, and that both debit and credit happen or neither happens. It must guarantee this while other transactions happen, perhaps even between these two accounts. The system ensures this by taking locks on the tables concerned. What locks are taken, and how much of other peoples' work you see, is controlled by the transaction isolation level.

So if you do a lot of work there is a good chance other transactions will be queued waiting for the objects on which you hold locks. This will reduce the system's overall throughput. Eventually they will hit timeout limits and fail, which is a problem for overall system behaviour. If you use an optimistic isolation level your transaction may fail when you try a commit because of other's work.

Holding locks takes system resources. This is memory which the system cannot use to process other requests, reducing throughput.

If a lot of work has been performed the system may choose to perform lock escalation. Instead of locking individual rows the entire table will be locked. Then more concurrent users will be affected, system throughput will drop further and the application impact will be greater.

Data changes are written to the log file, as are the locks which protect them. These cannot be cleared from the log until the transaction commits. Hence very long transaction may cause log file bloat with its associated problems.

If the current work uses tempdb, which is likely for large workloads, the resources there may be tied up until the end of the transaction. In extreme cases this can cause other tasks to fail because there is no longer enough room for them. I have had cases where a poorly coded UPDATE filled tempdb so there was insufficient disk left for a report's SORT and the report failed.

If you choose to ROLLBACK the transaction, or the system fails and recovers, the time taken for the system to become available again will depend on how much work was performed. Simply having a transaction open will not affect the recovery time, it is how much work was performed. If the transaction was open but idle for an hour recovery will be almost instantaneous. If it was writing constantly for that hour the rule of thumb is that recovery time also will be about an hour.

As you can see long transaction can be problematic. For OLTP systems best practice is to have one database transaction per business transaction. For batch work process input in blocks, with frequent commits, and restart logic coded. Typically several thousand records can be processed inside a single DB transaction, but this should be tested for concurrency and resoruce consumption.

Do not be tempted to go to the other extreme and avoid transactions and locks entirely. If you need to maintain consistency within your data (and why else would you be using a database?) isolation levels and transactions serve a very important purpose. Learn about your options and decide what balance of concurrency and correctness you are prepared to live with for each part of your application.


How do I do data entry with Foreign Key integer IDs?

I think the easiest solution for you would be to create a stored procedure that took @GameName and @ConsoleName.

CREATE PROCEDURE InsertGame @GameName VARCHAR(50), @ConsoleName VARCHAR(50)
AS
BEGIN
DECLARE @ConsoleID INT

SELECT @ConsoleID = ConsoleID FROM Console WHERE ConsoleName = @ConsoleName

IF @ConsoleID IS NULL
BEGIN
INSERT Console (Name) SELECT @ConsoleName
SELECT @ConsoleID = scope_identity()
END

INSERT Game (Name, Console) SELECT @GameName, @ConsoleID

END

If you entered in a @ConsoleName that didn't already exist in the Console table, it would INSERT it there. Next the stored procedure would look up the ID for the console name you provided it, and INSERT a record into the Game table with @GameName and the @ConsoleID.

Then you'd call that stored procedure for each Game you were planning to enter:

EXEC InsertGame 'Grand Theft Auto 5', 'PS4'
EXEC InsertGame 'Grand Theft Auto 5', 'PC'
EXEC InsertGame 'Final Fantasy XV', 'PS4'
EXEC InsertGame 'Overwatch', 'PC'
EXEC InsertGame 'Civilization 5', 'PC'

And so on until you've inserted all of your games.

Please note that the exact syntax I've used is for the database platform I am most familiar with, but the underlying logic should be usable in any database platform.

Also, as I write this I am struck by the idea that what you really want is to support a many-to-many relationship between game titles and platforms, since a platform can have many games and a game can be available on multiple platforms. That would take a little more work, but would be helpful for efficient storage and would give you the ability to easily determine which platform(s) a given game was available for without doing a text-match.

So, at a high level you'd need another table. The existing Game table would not have a ConsoleID column. Instead there would be a new table, likely called Console_Game, that would include only ConsoleID and GameID as keys. Then the stored procedure would need to check for both the existence of ConsoleName, and the existence of GameName. Inserting into both tables as necessary, and then finally write a record to Console_Game to show that a given game is available on a given console.

This will be more work upfront, but it is a more scalable solution than what you currently have. If this is for a job and not a hobby you should probably go that route.


What is the best practice when creating boolean columns in Oracle?

I usually use a number(1) type combined with a check constraint:

some_flag number(1) not null check (some_flag in (1,0))

To make things crystal clear I also add a comment to that table:

comment on column some_table.some_flag is '0 is false, 1 is true';

so that the explanation on what "true" means can be seen when looking at the definition of the table.

I would avoid a character representation due to localization problems. But if you do, make sure you create the approriate check constraint. Because it's not clear if a char(1) would use T, Y, y, t (or even W or J which I have seen in German).


How can I populate a table with a foreign key?

Basically is a syntax "misplacement":

INSERT INTO Entries VALUES (entry_id, subject_id, student_id)

Column names should be placed before VALUES clause or, if you supply values for all columns in the table, you can avoid to use it.

INSERT INTO Entries (entry_id, subject_id, student_id) VALUES

Try it on this way:

INSERT INTO Entries VALUES (1,4,1,'date'), (2,8,2,'date'), ...


Where Clause for Results Appearing after Occurence

it seems you are looking for something like this:
with cte as
    (
    select *
        ,row_number() over(order by col_b) as rn
    from dummy
)
select *
from cte
where rn > (select rn from cte where col_a = 'A' and col_b = 'B' and col_c = 'C' and test = 1 limit 1)


How Can I restore a database to a point of time without Old backup?

If you do not have any backups that contains the deleted table, you can't restore the database to it's original form.


Big Data storage, for relative data storage

This is not a database question, this is a mathematical question.  Mathematicians love problems like that -- find the best match between these 30M things and those 500K things.  It's sort of like matching your picture against all images in Facebook.

The mathematicians will (you can hope) come up with a way of chopping the problem up into pieces and coming up with an approximate best fit in a few million operations instead of 15 trillion operations.  Once you find that algorithm, then database gurus can discuss how to best store and fetch the data involved.

You will need to give the math guys the details of the function for metric of "closeness".  Part of the answer will probably take advantage of those details.


what does that mean? COMMAND [PeriodicTaskRunner] task: UnusedLockCleaner took: 273ms

The nice part about MongoDB is that it is open source, so your answer is in  the code, starting with comments about UnusedLockCleaner:

  Periodically purges unused lock buckets. The first time the lock is
  used again after cleanup it needs to be allocated, and similarly,
  every first use by a client for an intent mode may need to create a
  partitioned lock head. Cleanup is done roughtly once a minute.

Then taking a look at the lock_manager code we get more info about the cleanup function:

  Iterates through all buckets and deletes all locks, which have no requests on them. This
  call is kind of expensive and should only be used for reducing the memory footprint of
  the lock manager.

Basically it looks like some background maintenance being done by the database to clear out unused locks and keep the memory usage of the lock manager under control. Nothing to be concerned about, since if the database is busy and the locks are in use they won't be cleaned up. If you are seeing (reproducible) issues with it, then it would be best to open a ticket to debug with the developers, since this would not be intended.


Use an outer join:

SELECT c.first_name, c.last_name, p.phone_number, p.extension, f.fax_number, e.email
FROM Contact c
LEFT OUTER JOIN Contact_Phone p ON c.ContactID = p.ContactID
LEFT OUTER JOIN Contact_Fax   f ON c.ContactID = f.ContactID
LEFT OUTER JOIN Contact_Email e ON c.ContactID = e.ContactID

Any missing information will show up as NULL.

This assumes that all contacts will at the very least have a master record in Contact.


From my view so far, Year field needs being as text one in some cases e.g. to support Chinese Year Name - this year 2019 is called 'Ji Hai'

But anyway, we should always have Year field as INT.


Calculating disk usage with INFORMATION_SCHEMA.TABLE versus file system?

Well, it seems like there are two ways the deviate in performance,

Statistics when you query with innodb_stats_on_metadata (the default) you force the statistics to be updated (if it's not cached). You can turn this off, but doing so requires you make a GLOBAL change. From the docs:

  When innodb_stats_on_metadata is enabled, InnoDB updates non-persistent statistics when metadata statements such as SHOW TABLE STATUS or when accessing the INFORMATION_SCHEMA.TABLES or INFORMATION_SCHEMA.STATISTICS tables. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Leaving the setting disabled can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.

  To change the setting, issue the statement SET GLOBAL innodb_stats_on_metadata=mode, where mode is either ON or OFF (or 1 or 0). Changing the setting requires privileges sufficient to set global system variables (see Section 5.1.9.1, “System Variable Privileges”) and immediately affects the operation of all connections.

Cache else it seems that the INFORMATION_SCHEMA is reading from a cache in Table_statistics::get_stat. That seems to be documented under information_schema_stats_expiry. But it also seems you can refresh the cache with ANALYZE table (which the docs mention), and the testing suite. So the cache can drift by that many seconds.

I don't see anything else documented or in the code that would leave me to believe there was a difference.


How to ignore sql errors in stored procedure ? (Not handle)

Found it.

You have to declare a continue handler instead of an exit handler. That way even if there is an exception, it goes into the handler and continues with the next record.

  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION, SQLWARNING

    BEGIN
     ...
     Code that handles the exception/warning
     ...
    END


How to hide the partition child tables in PostgreSQL?

If all you want is to remove the partitions from the schema view, you could simply move the partitions to another schema:

ALTER TABLE child_01_2013 SET SCHEMA otherschema;

Everything will continue to work as before, but the visual clutter will be gone.


Do I need full recovery mode when I have multiple daily backup?

Setting a database to full recovery is not enough by itself to ensure you have point in time recoverability. You also need to take regular log backups. You should also be taking time regularly to test that you personally know how to execute the required commands to actually perform a restore to an arbitrary point-in-time.

In lieu of running loads of home brewed full backups, the fire-and-forget solution you want is probably to just install Ola Hallengren's backup solution. Running this script installs the required objects and deploys the required jobs to manage all of the full recovery databases on an instance. You will find these jobs 1 under SQL Server Agent > Jobs named by default DatabaseBackup - USER_DATABASES ... and similar. Add a schedule to these jobs that suits you 2 and disable other backup jobs to prevent conflicts.

You should still need to regularly take time in the normal course of business to ensure you and others on your team are able to actually restore the backups you have, regardless of how you take them.

1: assuming you're using SSMS here

2: 5 times daily if you like, but probably a bit less is fine


MySQL 5.6 performance_schema not showing queries from application

P_S in MySQL versions below 5.7.4 doesn't collect information regarding executed prepared statements: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html.

Maybe your application is using prepared statements instead of simple ad-hoc statements (parameters concatenated). Some client libraries, like PHP's PDO can avoid this (MySQL's) issue by emulating prepared statements in software, instead of sending them to MySQL for preparation (PDO option PDO::ATTR_EMULATE_PREPARES).


Aggregating rows from a condition in a linking table in SQL

A possible solution can be :
The first select gets all combination of date and error combination that exist in the tables.
If  you actually want all possible Dates, you need to add another created table, with all possiible dates

CREATE TABLE Code_SKU_Combos
    ([ErrorCode] varchar(5), [Device] varchar(2))
;

INSERT INTO Code_SKU_Combos
    ([ErrorCode], [Device])
VALUES
    ('12.24', 'A2'),
    ('12.25', 'A2'),
    ('12.26', 'A2'),
    ('12.24', 'B3'),
    ('12.26', 'B3'),
    ('12.25', 'C4')
;
GO

CREATE TABLE DailyUses
    ([Uses] int, [Date] Date, [Device] varchar(2))
;

INSERT INTO DailyUses
    ([Uses], [Date], [Device])
VALUES
    (3, '2020-3-1', 'A2'),
    (5, '2020-3-1', 'B3'),
    (1, '2020-3-2', 'A2'),
    (12, '2020-3-3', 'C4')
;
GO

SELECT DISTINCT [Date],[ErrorCode] FROM DailyUses CROSS JOIN  Code_SKU_Combos
GO

Date       | ErrorCode
:--------- | :--------
2020-03-01 | 12.24
2020-03-01 | 12.25
2020-03-01 | 12.26
2020-03-02 | 12.24
2020-03-02 | 12.25
2020-03-02 | 12.26
2020-03-03 | 12.24
2020-03-03 | 12.25
2020-03-03 | 12.26

SELECT ISNULL (SUM([Uses]),0) AS Uses, DateComp as [Date],[errorcomp] AS ErrorCode
FROM (SELECT DISTINCT [Date] as DateComp,[ErrorCode] AS errorcomp FROM DailyUses CROSS JOIN  Code_SKU_Combos) t1
LEFT JOIN (
DailyUses d INNER JOIN Code_SKU_Combos c ON d.[Device] = c.[Device]) ON t1.DateComp = d.[Date]
AND  t1.errorcomp = c.[ErrorCode]

GROUP BY [DateComp],[errorcomp]
ORDER BY [DateComp],[errorcomp]
GO

Uses | Date       | ErrorCode
---: | :--------- | :--------
   8 | 2020-03-01 | 12.24
   3 | 2020-03-01 | 12.25
   8 | 2020-03-01 | 12.26
   1 | 2020-03-02 | 12.24
   1 | 2020-03-02 | 12.25
   1 | 2020-03-02 | 12.26
   0 | 2020-03-03 | 12.24
  12 | 2020-03-03 | 12.25
   0 | 2020-03-03 | 12.26

db<>fiddle here


How to replace and overwrite all existing objects in Oracle with impdp for full database import?

I don't think there is a "Replace" option for non-table objects like procedures, packages etc.

The best option would be to drop the schema entirely before the datapump import.  This way, datapump will re-create the schema and all of the contained objects.


Postgresql: What the default_statistics_target value really means?

It will sample 300 * default_statistics_target rows from each table.  It will use that sample to determine upto default_statistics_target most common values to store in that array, and upto default_statistics_target histogram bounds to store in that array. Plus a few other scalar statistics, like the number of distinct values.

The multiplier 300 was chosen because some statistical theory says that is how many you need to sample per each histogram bound you wish to compute, in order for your sampled histogram bounds to have an acceptable level of uncertainty.

The most common value list is used to help the planner predict the selectivity of equality expressions, like where state='CA'.  The histogram bounds are used to help the planner predict the selectivity of inequality or range expressions, like where income between 55000 and 64000


can you use msdb.dbo.sp_send_dbmail to set up calendar appointments?

(I dont have outlook to test it .. so the code is not tested by me)

Borrowed from : Send Outlook Calendar Requests in Email

EXEC msdb.dbo.sp_send_dbmail
  @recipients ='<your email address>'
  ,@subject = 'iCalendar Example'
  , @query = 'SET NOCOUNT ON;SELECT ''BEGIN:VCALENDAR''+ CHAR(13)+ ''VERSION:2.0''+ CHAR(13)+ ''METHOD:PUBLISH''+ CHAR(13)+ ''BEGIN:VEVENT''+ CHAR(13)+ ''CLASS:PUBLIC''+ CHAR(13)+ ''DESCRIPTION:This is the iCalendar example Most client application support it''+ CHAR(13)+ ''SUMMARY:iCalendar''+ CHAR(13)+ ''DTEND:20150325T110000''+ CHAR(13)+ ''DTSTART:20150325T120000''+ CHAR(13)+ ''LOCATION:My Office''+ CHAR(13)+ ''END:VEVENT''+ CHAR(13)+ ''END:VCALENDAR'''
  , @attach_query_result_as_file = 1
  , @query_result_header = 0
  , @query_result_separator = 'CHAR(10)+CHAR(13)'
  , @exclude_query_output = 1
  , @query_attachment_filename = '123.ics'


Storing information about office locations for international companies

To normalize this properly, you need what is sometimes called a cross-reference or link table, to avoid making that country table so wide.

For example (this is for demonstration purposes only):

company
-------
companyid
companyname

country
-------
countryid
countryname
countrycode

country_company
---------------
countryid
companyid

With this structure, when you add a new company, you add it to the company table, and then add a reference to it in the link table.

So if you had 100 countries, you would have several hundred rows in this table.

I can assure you that querying this will be far more efficient than a very wide table.

When you want to query the data, you'll need to write an additional JOIN, but this makes managing the data so much easier, and less error-prone.

Notice that I've added a countrycode column, which I would use for a two- or three-digit ISO standard (e.g. "USA", "CAN").

This means you could even use the code as a foreign key instead of the countryid.


Count rows in a table based on a DATE field

You have to utilise the COUNT function along with the GROUP BY function along the lines of

SELECT
 date_added AS Date,
 COUNT(Client_ID ) AS no_of_rows
FROM CLIENTS
GROUP BY date_added
ORDER BY date_added;

Using the above produces output in the form of

Date        no_of_rows
2019-03-01  1
2019-03-02  2
2019-03-03  3
2019-03-06  2

As an aside it is generally a good idea not to name a column with the name of a datatype i.e I would probably call the Data column in the result ClientDate.


You need to record T0 for each student and ΔT for each lesson.
DATE math assumes a difference of 1 === 1 day.  That means a value of 0.5 === half day. etc. etc.  Some RDBMS support an INTERVAL data type that could be used instead.  It is up to you which one you want to use.
So, to calculate "when to start a lesson", you just need to take the date_started value (T0) and add the delta_T value (ΔT) of the lesson to get the DATE-TIME to send the email.
create table lesson_plan (
  lesson_id int -- identifier
  ,class_id      int not null
  ,delta_T       number -- ( or whatever is best for your RDBMS)
  ,constraint lesson_plan_pk primary key (lesson_id)
  ,constraint lesson_plan_fk1 foreign key (class_id)
      references classes(class_id) on delete cascade
);

comment on table lesson_plan is 'Stores chapters and when to send the lesson';

create table student_classes (
   student_id     int
  ,class_id       int
  ,date_started   date -- needs to record DATE and TIME
  ,constraint student_classes_pk primary key (student_id,class_id)
  ,constraint sudent_classes_fk1 foreign key (student_id)
      references students(student_id) on delete cascade
  ,constraint student_classes_fk2 foreign key (class_id)
      references classes(class_id) on delete cascade
);

comment on table student_classes is 'Records when each student started a class.';

create or replace
view student_lessons
as
select a.student_id
      ,b.class_id
      ,c.lesson_id
      ,a.date_started + c.delta_T as lesson_start_datetime
from student_lessons a
  join classes b on a.class_id=b.class_id
  join lesson_plan c on c.class_id=b.class_id;

comment on view is 'When a lesson starts for a specific student.';


Odd SQL Server hang when querying exec DMVs

I ran into the problem and found the bug article on MSDN. It has been fixed with cumulative updates for SQL 2008 and 2012. http://support.microsoft.com/kb/2803799


Improving Mysql updating query performance

Turn it into 4 UPDATES, one per status (EXPIRED, INCORRECT, ...).  Run the updates in the correct order so that if two cases apply, you end up with the desired status.

Each UPDATE would match one of your cases.  For example, for INCORRECT:

UPDATE tdetectable det
JOIN  tdetectable_equipment e  ON det.id = e.id_detectable
JOIN  torganization org  ON det.id_organization = org.id
SET det.detectable_status = 'INCORRECT'
WHERE ( EXISTS(
        SELECT *
        FROM trevision_cycle
        WHERE id_detectable = e.id_detectable
          AND status = 'NO_OK'
          AND active <> 0
          AND id IS NOT NULL -- If `id` is the PK, this test unnecessary
      ) )
      AND det.active
      AND org.id_client = ?

Indexes:

torganization: INDEX(id_client, id)
det:  INDEX(id, active) -- unless you have PRIMARY KEY(id)
trevision_cycle: INDEX(id_detectable, status, id, active)

innodb_buffer_pool_size should be about 10G for that sized machine.

More specifics:

ALTER TABLE trevision_cycle ADD INDEX(id_detectable, status, id, active);

KEY and INDEX are synonyms.  Most datatypes (INT, ENUM, etc, but not TEXT or BLOB) can be indexed.  Since it has more than 1 column, this index is called "composite" or "compound".  The order of columns in an index definition is important; I provided one useful order.


EXECUTE within function not using index?

Queries executed with EXECUTE are re-planned with the actual parameter values passed to it every time. Since you are using Postgres 9.2, you may not even need EXECUTE:

PostgreSQL Stored Procedure Performance

Either way, upgrading to Postgres 9.3 (or the upcoming 9.4) might help some more.

So, unless we have a type mismatch (like you already suspected) or a collation mismatch, your index should be used, if the parameter values are selective enough (retrieving less than ~ 5% of the table), which most probably is the case.

You write:

  r_row.account_type_id is a BIGINT, r_row.member_id is a BIGINT, and
  r_row.external_id is a VARCHAR(64).

But how do you know that? If r_row is declared as record, data types of columns are defined in the assignment of an actual row ...
We need to see the complete plpgsql function with header and footer. And the exact table definition.

COLLATE "C" for your char-type index

An index on an integer type column is far more efficient than one on varchar(64) in any case. If you have to use the character type but you don't need to sort the column according to your collation setting (as I presume) it would be more efficient to use COLLATE "C" for index and query.

CREATE INDEX account_full_qualifiers_idx
ON normal_object.account (account_type_id, member_id, external_id COLLATE "C")
TABLESPACE index_tbsp;

Your query would then be:

EXECUTE '
   SELECT *
   FROM   normal_object.account
   WHERE  account_type_id = $1
   AND    member_id = $2
   AND    external_id COLLATE "C" = $3
   LIMIT  1'
INTO  e_row
USING r_row.account_type_id, r_row.member_id, r_row.external_id;

LIMIT 1 may or may not be needed. Your index is not UNIQUE, though ...

Or you define the table column with COLLATE "C" to begin with. Index and query will default to the collation of the column.

This may or may not solve the question at hand - it's a good idea for your situation in any case.

Details in this related answer (chapters "Strings and collation" and "Index":

How do I efficiently get "the most recent corresponding row"?
The manual on "Collation Support".


That's not weird at all, that's how CHARINDEX works. The third parameter you pass in is the start position to start searching for your expression.

You'll have to deal with this a lot if you're using CHARINDEX along with SUBSTRING to find text in between two expressions.

If you start searching AT the CHARINDEX of C in your example, you get the same results.

SELECT CHARINDEX('C', 'ABCDEFGC', CHARINDEX('C', 'ABCDEFGC'));

Returns 3

You need to start searching for the CHARINDEX one position after to get the next one. Otherwise, it finds the searched expression in the first position it checks.

SELECT CHARINDEX('C', 'ABCDEFGC', CHARINDEX('C', 'ABCDEFGC') + LEN('C'));

Returns 8

If you start searching after the last occurrence, CHARINDEX will wrap back around.

SELECT CHARINDEX('C', 'ABCDEFGC', CHARINDEX('C', 'ABCDEFGC', 9));

Also returns 3.

Hope this helps!


SQL Server 2012 error when exporting to Excel

Seems like more often than not I run into issues trying to export to excel using the SSMS import/export wizard and I've never taken the time to learn and understand the quirks.

This doesn't necessarily answer your question, but might be a viable alternative.
Instead of exporting to excel, consider importing into excel. This YouTube video appears to demonstrate it:
http://youtu.be/VQrKg0GgZR0

Of course, if you need to export a lot of tables or need to do this often this is a rather tedious approach, but it has worked very well for me with a lot fewer headaches when only exporting a few tables.


SSRS 2014 Report - looking up single row from dataset

You can do this in either the SQL or using a matrix table within SSRS. Typically, I prefer to do it in the SQL, but that's not often the most convenient for others. You are correct in that you need to use an aggregate function with any kind of pivot functionality. In your case, you should use a sum() aggregate.

While this sounds slightly counter intuitive, it will work fine as there is only a single entry here per fee_name. What you end up with is a list of values, where the only thing in the list is the desired value. A list such as, [10] will sum to 10.


How to get certificate expiration date for certificate in mirroring endpoint in SQL Server 2016 Always On Availability Group?

Instance-level certificates are stored in the master database
You will need to set your context to master database, and then check certificates:

use [master]
select * from sys.certificates

Or simply use three-part name in a single command:

select * from [master].sys.certificates


Are the switches for the SQLCMD command case sensitive?

Yes, SQLCMD switches are case sensitive.

 -l login_timeout
 -L[c] (list servers, optional clean output)
 -s col_separator
 -S [protocol:]server[instance_name][,port]

When you connect using

sqlcmd -s .\<servername>

is connects to the default instance and uses ".\<servername>" as column separator

While on the second machine there may not be any default instances so you need to enter -S .\ explicitly to connect to a named instance


PostgreSQL 8.4 hangs on create table, reindex

if it is hanging forever, without significant CPU or IO activity, it is waiting on a lock.  The two things I would try here would be checking pg_stat_activity to see if something else is running that might conflict.  Maybe someone else has an open transaction in another window that has conflicting locks?  Then I would check pg_locks.

Then I would look at pg_terminate_backend() on the offending process and try again.

However create table is a weird one.  I can't think, off-hand, of any case where this should be blocked.  Wondering what kinds of locks are required for this and under what circumstances they can conflict.

Edit:  It occurs to me that the locking could be lower level than Pg-level locks and involve semaphores against shmem segments.  But if that's the case your best bet is to take this up with Amazon since it would be more of an OS issue.


postgres: WAL files not being deleted during PITR

You probably have checkpoint_timeout set very high on the production system, and archive switches are being driven by checkpoint_segments, probably in conjunction with a low setting of archive_timeout.

But, checkpoint_segments has no effect during recovery.  Restartpoints during recovery are driven exclusively by checkpoint_timeout.  Since recovery can replay files far faster than it took production to create them, that means you have a huge accumulation of old xlog files waiting for a restartpoint to allow them to be cleared out.

One solution is to lower checkpoint_timeout by quite a bit for the system undergoing recovery, and then set it back once recovery has finished.  This will cause restartpoints to happen more often, allowing the log files to be removed sooner.

Another option is to simply have a script delete the files from pg_xlog during recovery once a newer file has been restored and renamed.  If the system crashes during recovery, it may then have to go refetch some of those deleted files from the archive.

The system was designed to tolerate the case where files are removed from the archive once they are fetched and so cannot be re-fetched.  That is why it stores all the fetched files in pg_xlog until a restartpoint; so it has all the data it needs to restart the recovery if the recovery crashes.  As long as you don't remove files from the archive, you can remove them from pg_xlog.

This situation will thankfully be improved in 9.5.


Delegation: Bulk Insert failed when executed from SSRS dataset but success from SSMS

I'm glad I asked what your end goal was. I was going to reply in a comment but it turned into too long of a response.
Your best bet is honestly to close this question and ask a new one on "best ways to implement data encryption and obfuscation across multiple databases?" with details on each type of data system you're using. There's a multitude of ways to accomplish what your customer wants with out-of-the-box features of SQL Server without rigging together a roundabout solution.
One of them is Encryption another is Dynamic Data Masking, and finally understanding proper Permissioning in SQL Server are all standard and potential ways to hide your customer's data, even from developers.
All three of those previously mentioned plus many other features, are applicable to the databases that are utilized by the systems you mentioned (SQL Server version dependent).


Oracle Support instructed me to apply patch: 16527374


query generate the information one column in two columns in one query

To answer the question here is the query

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON A.id = B.id -1 WHERE MOD(A.id,2)=1;

I made this query up based on staring at the output

It would have been nice to have a table called name_country perhaps like this:

CREATE TABLE name_country
SELECT A.id name_id,B.id country_id
FROM mytable A INNER JOIN mytable B
ON A.id = B.id -1 WHERE MOD(A.id,2)=1;
ALTER TABLE name_country ADD PRIMARY KEY (name_id,country_id);
ALTER TABLE name_country ADD UNIQUE INDEX (country_id,name_id);

That way going forward, you can maintain the name_country table in such a way that name and country do not have to be 1 id apart in the original table. You could freely enter names in bulk. Then later on, attach the country values.

Once you can create a name_country table, you could then do joins like this:

SELECT B.id,B.name,C.id,C.name
FROM name_country A
INNER JOIN mytable B ON A.name_id = B.id
INNER JOIN mytable C ON A.country_id = C.id;

UPDATE 2012-08-15 15:04 EDT

The query I first suggested

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON A.id = B.id -1 WHERE MOD(A.id,2)=1;

is the answer

First here is your sample data

DROP DATABASE IF EXISTS cabita;
CREATE DATABASE cabita;
USE cabita
CREATE TABLE mytable
(id int not null auto_increment,
name varchar(20),primary key (id));
insert into mytable (name) values ('name1'),('country1');
insert into mytable (name) values ('name2'),('country2');
select * from mytable;

Here is your sample data loaded

mysql> DROP DATABASE IF EXISTS cabita;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE DATABASE cabita;
Query OK, 1 row affected (0.00 sec)

mysql> USE cabita
Database changed
mysql> CREATE TABLE mytable
    -> (id int not null auto_increment,
    -> name varchar(20),primary key (id));
Query OK, 0 rows affected (0.14 sec)

mysql> insert into mytable (name) values ('name1'),('country1');
Query OK, 2 rows affected (0.08 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> insert into mytable (name) values ('name2'),('country2');
Query OK, 2 rows affected (0.07 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+----+----------+
| id | name     |
+----+----------+
|  1 | name1    |
|  2 | country1 |
|  3 | name2    |
|  4 | country2 |
+----+----------+
4 rows in set (0.01 sec)

Here is my original query executed

mysql>     SELECT A.*,B.*
    ->     FROM mytable A INNER JOIN mytable B
    ->     ON A.id = B.id -1 WHERE MOD(A.id,2)=1;
+----+-------+----+----------+
| id | name  | id | name     |
+----+-------+----+----------+
|  1 | name1 |  2 | country1 |
|  3 | name2 |  4 | country2 |
+----+-------+----+----------+
2 rows in set (0.03 sec)

mysql>

While the query works for your sample data, you have to pay close attention to how name relates to country. You would have to make sure of three things in order for my query work

the id of name is an odd number
the id of country is an even number
the id of name is one less id of country

That's one relationship I was able to take advantage of.

There is another relationship visible in your sample data: The number at the end of the each field. If you can enter names in bulk and then later load country in bulk, you would then have to rely on the number at the end of each name field. Such a query would look like this:

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
WHERE A.name > B.name;

and would execute as follows

mysql>     SELECT A.*,B.*
    ->     FROM mytable A INNER JOIN mytable B
    ->     ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
    ->     WHERE A.name > B.name;
+----+-------+----+----------+
| id | name  | id | name     |
+----+-------+----+----------+
|  1 | name1 |  2 | country1 |
|  3 | name2 |  4 | country2 |
+----+-------+----+----------+
2 rows in set (0.00 sec)

mysql>

or this query

SELECT A.*,B.*
FROM mytable A INNER JOIN mytable B
ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
WHERE LEFT(A.name,4)='name'
AND LEFT(B.name,7)='country';

which would execute as follows

mysql>     SELECT A.*,B.*
    ->     FROM mytable A INNER JOIN mytable B
    ->     ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
    ->     WHERE LEFT(A.name,4)='name'
    ->     AND LEFT(B.name,7)='country';
+----+-------+----+----------+
| id | name  | id | name     |
+----+-------+----+----------+
|  1 | name1 |  2 | country1 |
|  3 | name2 |  4 | country2 |
+----+-------+----+----------+
2 rows in set (0.00 sec)

mysql>

All these queries make assumptions on the content of the data. Such queries would produce very poor performance results. This is why I suggested creating an external table to perform the JOIN that would couple the id of name and its country together.

UPDATE 2012-08-16 17:19 EDT

In answer to the last question from the comment

SELECT A.*,B.*,
FROM mytable A
INNER JOIN mytable B ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
INNER JOIN mytable C ON SUBSTR(A.name,-1)=SUBSTR(C.name,-1)
WHERE LEFT(A.name,4)='name'
AND LEFT(B.name,7)='country';
AND LEFT(C.name,11)='description';

If there is a different table involved for the descriptions, then perhaps this:

SELECT A.*,B.*,
FROM mytable A
INNER JOIN mytable B ON SUBSTR(A.name,-1)=SUBSTR(B.name,-1)
INNER JOIN difftable C ON SUBSTR(A.name,-1)=SUBSTR(C.name,-1)
WHERE LEFT(A.name,4)='name'
AND LEFT(B.name,7)='country';
AND LEFT(C.name,11)='description';


I suggest instead of using the ALL (*). just type the column names with an AS attached such as: TableA.id AS TableAId,TableB.id AS TableBId and so on.


In MDX what is the ParallelPeriod of UnknownMember?

The way `PARALLELPERIOD' works is so that it moves up the hierarchy up to the specified level and then moves back or forward the number of periods, then moves down again using the same path.

In this 'moving down the path' parallelperiod will descend the same member ordinal again. So if it moved up from the first member (in your example january) it will move down the first member again. If it moved up the second member (in your example february) it will move down the second member again.

This of course breaks with an unknown hierarchy, since each child is the first member of it's parent.

So lets say for example you have this hierarchy

     2015             unknown
jan   feb    mar      unknown

And then you do a parallelperiod on the leaf member of the unknown hierarchy, the parallelperiod will move up to the year level of the unknown hierarchy, move back a year and move down to the leaf level again, remembering it moved up from the first member of the unknown hierarchy and moving down the first member of the 2015 hierarchy, which is jan 2015.


Display last date grouped by warehouse and product

Does this work for you? I am testing in MySQL.

SELECT LatestStock.*
FROM daily_stock as LatestStock
JOIN (
    SELECT warehouse, product, MAX([date]) as MAXdate
    FROM daily_stock
    GROUP BY warehouse, product
    ) as LatestDate on LatestStock.warehouse = LatestDate.warehouse AND LatestStock.product = LatestDate.product AND LatestStock.date = LatestDate.MAXdate
ORDER BY LatestStock.product, LatestStock.warehouse


SKIP_INDEX_MAINTENANCE

  The SKIP_INDEX_MAINTENANCE SQL*Loader command-line parameter specifies
  whether to stop index maintenance for direct path loads.

Then you can build the indexes after.


SQL Table Indexing - Database Overloaded With Queries

There are several approaches that can help.

If you just throw in more indexes, then you are likely to speed up most simple queries, as well as introduce unused indexes. Unused indexes don't really hurt if the database is read-mostly, but they take up space. Unless you care about disk space, adding single-column indexes is the fastest way to speed up table reads but also slow down table writes. Knowing the architecture, however, does help avoiding useless indexes.

You can enable logging slow queries or queries that don't use indexes:

SET GLOBAL log_queries_not_using_indexes 'on';
SET GLOBAL slow_query_log 'on';
SET GLOBAL log_output 'table';

Wait a little for the log to fill up, then query the log with

SELECT * FROM mysql.slow_log;

This should point to potential new indexes. Note that every language feature that is available for normal queries is also available here, so use what you need. Once you create a new index, you can reset and repopulate the log: TRUNCATE mysql.slow_log;

Also read up [here] for the details of slow query logging.


Get object script in SQL Server via T-SQL?

This is not feasible in T-SQL. Not all object have a definition stored and you would have to reverse engineer CREATE scripts for basic things like tables and indexes.

Rather than reinvent the wheel (in T-SQL), do the proper thing and reuse the component dedicated for this purpose the SMO Scripter class. Is already available, works, is up to date with all the latest SQL Server objects, is free to redistribute along with SMO. Simply move your scripting logic out of T-SQL.


Hide table structure to users in firebird

You should not need to hide things from Users.

You should only grant them access to the things they actually need to work with.


Recommended Postgres settings to speed up indexing on a 100GB database?

If I were setting up a new server, I wouldn't use anything below 9.3.6.  There have been many improvements since 9.1.

And if you use 9.4.1, there is some code to let it use larger amounts of RAM for index creation than it previously could.

Neither of the parameters you listed are particularly important for creating indexes.  The most critical for that purpose would be maintenance_work_mem.    On a 32GB server, if I'm only building one index at a time and keeping a close eye on things, I'd set it to 16GB for the local session that is building the large index.

Also, the C collation is much faster than the other collations, if you have a choice.

Is the bottleneck CPU or IO?


How to update the datetime difference between all of the rows of a table?

WITH cte AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY StartTime) rn FROM schedules)
UPDATE cte SET StartTime = DATEADD(minute, 15*(1-rn), StartTime);

fiddle


Can't Restore Database Because of Rogue Log File

I'd bet the database didn't actually get dropped, and is still in use by SQL.

Not sure why it isn't showing up in SSMS, but run the following query to see what database is still using it:

SELECT database_id, DB_NAME(database_id) AS DBName, name AS LogicalName, type_desc,
physical_name, convert(numeric(10,1),(size/128.0/1024.0)) SizeGB
FROM master.sys.master_files
ORDER BY database_id, type_desc desc, name

Is your Rogue database in the list?

If you just do SELECT * FROM master.sys.databases, does it show Rogue? What does it show as the user_access_desc and state_desc?

What does the SQL errorlog say during the time in question? It should mention a database drop or change in status.


Monthly backup of SQL server DB to PostgreSQL?

Can you not just hook Crystal Reports to an instance of MS SQL Server? That way the process should be as simple as "backup the live database, restore it into test/snapshot location when needed".

If the database is less than 10Gb in size you can use SQL Express so there would be no licensing implications, if it is 10Gb or more than you could restore it on the same SQL Server under a different name assuming you have access to be able to do that. Otherwise you might end up paying for an instance of SQL Server (avoiding this may be why you are using postgres in the first place).

If you have a specific process that you are currently following manually which you would like to automate, then you need to specify that process in detail in your question otherwise we can only guess and that may not be helpful.


PostgreSQL Trigger to keep track of table changes

In the end I solved this through 'Divide and conquer' - I split the trigger into three parts and now it works (though I am not sure yet why the original trigger does not work):

    CREATE OR REPLACE FUNCTION taxon_history_delete() RETURNS trigger AS
$BODY$
BEGIN
    IF TG_OP = 'DELETE' THEN
        INSERT INTO history.taxon(operacao, data, tecnico, original_oid, taxon)
        VALUES ('DELETE', current_timestamp, current_user, old.oid, old.taxon);
        RETURN old;
    END IF;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION taxon_history_update() RETURNS trigger AS
$BODY$
BEGIN
    IF TG_OP = 'UPDATE' THEN
        INSERT INTO history.taxon(operacao, data, tecnico, original_oid, taxon)
        VALUES ('UPDATE', current_timestamp, current_user, old.oid, old.taxon);
        RETURN old;
    END IF;
END;
$BODY$
LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION taxon_history_insert() RETURNS trigger AS
$BODY$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO history.taxon(operacao, data, tecnico, original_oid, taxon)
        VALUES ('INSERT', current_timestamp, current_user, new.oid, new.taxon);
        RETURN new;
    END IF;
END;
$BODY$
LANGUAGE plpgsql;

create TRIGGER taxon_history_delete
Before DELETE ON taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history_delete();

create TRIGGER taxon_history_update
Before UPDATE ON taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history_update();

create TRIGGER taxon_history_insert
AFTER INSERT ON taxon
FOR EACH ROW EXECUTE PROCEDURE taxon_history_insert();


This is a bug that's been fixed in MySQL 5.6.5.

See http://bugs.mysql.com/bug.php?id=62505


AlwaysON Availability Groups - IP address change of secondary replica node

Due to hardware maintenance the IP address of the secondary node will change.

This shouldn't be a problem if the node ip itself is changing. It's only a slight issue if cluster resource ips are changing.

  Is there any actions I should take or anything I should be aware of from a SQL Server, AlwaysON Availability Groups and WSFC perspective?

From the WSFC point of view, assuming that the subnet does not change, there shouldn't be anything needed here.

From SQL Server/AlwaysOn AGs... assuming the subnet does not change:

Double check the AG endpoint to see if it's bound to that specific ip. If it is, that will need changed. If it isn't (wizard generated, etc.) it'll say 'ALL' and that won't need changed.
If SQL Server was setup to listen on specific ip's and ports in configuration manager, this will need changed. If it's set for IPALL, nothing needs done.
If you're using read only routing, double check the read only routing urls and endpoints.
Service broker endpoints (similar to AG endpoint)

From an overall point of view:

Make sure the firewall rules have been edited for the new ip if needed.

This may not be an exhaustive list, but should give you an idea and the most critical areas to look at.


DB2: Database alias already exists when cataloging

If this is a development environment I suggest you turn off the directory cache:

db2 update dbm cfg using DIR_CACHE NO
db2 stop force; db2start

I don't really understand why you need to create new databases though, can't you cope with a new schema in an existing database?


Only a single WITH MOVE clause should be specified for any logical file name

You shouldn't be moving the backup file, the MOVE clause takes the logical database file name as the first parameter, and then the TO ... is the destination physical filename for the corresponding database file.
BOL reference on RESTORE

MOVE 'logical_file_name_in_backup' TO 'operating_system_file_name' [ ...n ]
Specifies that the data or log file whose logical name is specified by logical_file_name_in_backup should be moved by restoring it to the location specified by operating_system_file_name. The logical file name of a data or log file in a backup set matches its logical name in the database when the backup set was created.

You can retrieve the logical file name of the database files from RESTORE FILELISTONLY (note, the LogicalName column in the result set).


Adding a distributor for availability groups

The statement you are reading in the Microsoft article says that you should not (and cannot) put a Distribution database inside an Availability Group.

Why?

Because the distribution database is a System database and Availability Groups will only fail over User databases. Therefore you will require another (4th) server in your situation to act as the (remote/scaled out) Distributor.


Creating a temporary copy of a table with partitions

CREATE TEMPORARY TABLE myTable
    SELECT * from source.myTable
        LIMIT 0;           -- Add this

Still creates the temp table, but puts no rows in it.


Error on Inner Join from Nested Select Statements

Some of the meta_key= values changed between updates, but you should still get the point of this.

Using conditional aggregation inside a common table expression to flatten out your EAV style table instead of subqueries:

;with patients as (
  select
      p.ID
    , FirstName     = min(case when meta_key = 'first_name' then meta_value end)
    , LastName      = min(case when meta_key = 'last_name' then meta_value end)
    , CustomerID    = min(case when meta_key = 'customerid' then meta_value end)
    , PartnerClinic = min(case when meta_key = 'partner-clinic' then meta_value end)
    , DoctorID      = min(case when meta_key = 'doctorsid' then meta_value end)
  from users as p
    inner join user_group as ug
      on ug.user_id = p.id
     and ug.group_id != 22
     and ug.group_id = 5
    inner join revision as r
      on p.id = r.wp_users_id
     and r.revision_types = 2
    left join usermeta m
      on p.id = m.user_id
  group by p.id
)
select
    p.*
  , Name          = isnull(f.name, 'No Clinic')
  , Doctor        = isnull(d.name, 'No Doctor')
from patients as p
  left join doctors as d
    on p.DoctorID = d.bt_id
  left join fronts as f
    on p.PartnerClinic = f.id
order by p.ID


MySQL 5.5 - Innodb - innodb_log_file_size higher than 4GB combined?

Years ago, I would set innodb_log_file_size to 2047M. This is the biggest number allowed to create transaction log files without surpassing InnoDB's 4GB limit. I believe it still applies to the current version (MySQL 5.5). Keep in mind that InnoDB was designed to work in a 32-bit environment, thus the 4GB limit.
GOOD NEWS
Percona Server binaries actually surpass this limitation. I believe I have seen 4GB as innodb_log_file_size in one installation. That's right, I said a 4GB ib_logfile0 and a 4GB ib_logfile1, ** totaling 8GB**. Here is a current listing from the DB Server of one of my employer's clients:
[root@*** ~]$ ls -l /var/lib/mysql/ib*
-rw-rw---- 1 mysql mysql 2283798528 Jan 16 22:53 /var/lib/mysql/ibdata1
-rw-rw---- 1 mysql mysql 4294967296 Jan 16 22:52 /var/lib/mysql/ib_logfile0
-rw-rw---- 1 mysql mysql 4294967296 Jan 16 22:53 /var/lib/mysql/ib_logfile1

When I login to mysql, I see this Welcome message:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 177401833
Server version: 5.1.45-51-log Percona SQL Server (GPL), XtraDB 10

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

So, Percona Server 5.1.45 lets you make huge InnoDB Transaction Logs. SO, I am very sure Percona Server 5.5 (which uses XtraDB) will let you create InnoDB Log File > 2GB.
RECOMMENDATION
If you really need log files that big (> 2G), please upgrade to Percona Server with the XtraDB storage engine. It is an excellent drop-in replacement for InnoDB.
WARNING
The larger the log files, the longer the shutdown process may become. I would recommend the following:
[mysqld]
innodb_fast_shutdown=0

That way, any uncommitted transactions left in ibdata1 get committed upon shutdown rather than upon MySQL's crash recovery during startup.
UPDATE 2013-01-17 11:40 EDT
From your last two comments, your question to me is

What is the downside of having innodb_log_file_size too low for the load - less than 1 hours worth?

I would say frequent checkpoints having to be written to disk. That could make it very annoying for InnoDB to handle MVCC for Transaction Isolation.
You could properly size the InnoDB Log files for whatever transaction rate you peak. I wrote about this before : Proper tuning for 30GB InnoDB table on server with 48GB RAM (under the Section Log File Size). Here is an excerpt from my earlier post:
Log File Size
5MB is the default size for innodb_log_file_size. Percona's mysqlperformanceblog.com gave two good articles on computing the right size for your particular MySQL instance:

http://www.mysqlperformanceblog.com/2006/07/03/choosing-proper-innodb_log_file_size/
http://www.mysqlperformanceblog.com/2008/11/21/how-to-calculate-a-good-innodb-log-file-size/

Basically, the blog recommends measuring how many bytes are written to the InnoDB Log Files in one hour. This is what I run to figure that out
SET @TimeInterval = 3600;
SELECT variable_value INTO @num1 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SELECT SLEEP(@TimeInterval);
SELECT variable_value INTO @num2 FROM information_schema.global_status
WHERE variable_name = 'Innodb_os_log_written';
SET @ByteWrittenToLog = @num2 - @num1;
SET @KB_WL_HR = @ByteWrittenToLog / POWER(1024,1) * 3600 / @TimeInterval;
SET @MB_WL_HR = @ByteWrittenToLog / POWER(1024,2) * 3600 / @TimeInterval;
SET @GB_WL_HR = @ByteWrittenToLog / POWER(1024,3) * 3600 / @TimeInterval;
SELECT @KB_WL_HR,@MB_WL_HR,@GB_WL_HR;

Whatever number comes back for @MB_WL_HR, take half of it and resize innodb_log_file_size to it : See my post How to safely change MySQL innodb variable 'innodb_log_file_size'?


Different lock types on source table when I change method of inserting data?

When you use SSIS, the target and source are separate operations. As far as SQL Server is concerned, it sees one connection performing a simple SELECT and another connection performing a bulk load or regular INSERT.

The simple SELECT can benefit from a locking optimization where row-level shared locks are skipped when safe to do so. This leaves only the intent-shared locks at the page level.

When the insert and select are combined in the same T-SQL statement, a different execution plan is produced, and the specific locking optimization is not applied.

If you wish to produce the same non-blocking behaviour on the source table when using INSERT...SELECT, you would need to use a row-versioning isolation level.

Read uncommitted isolation would also not take shared locks, but offers very few consistency guarantees, and might even throw an error when the data structures underneath the scan are modified (error 601).


Include parent node ID in hierarchical recordset of child rows

It's hard to understand your requirements without seeing the definition of category - Presumably, there is a root_id column in that table?

If there is, perhaps you want this?

;WITH level_1 (category_id, category_name, root_id) AS (
    SELECT category_id,
        category_name,
        root_id
    FROM dbo.category
    WHERE parent_category_id = 1
    UNION ALL
    SELECT c.category_id,
        category_name,
        root_id
    FROM dbo.category AS c
    CROSS JOIN level_1
    WHERE c.parent_category_id = level_1.category_id
)
SELECT category_id
    , category_name
    , root_id
FROM level_1;

Just FYI, it's good practice to begin a CTE with a semicolon, to specify the schema, and avoid using SELECT *


How to sort values starting with a letter followed up by a number?

You need to parse the string to separate the alpha + numeric part.

Assuming that it is always alpha first follow by numeric and not the other way round

the 2 cross apply split the alpha and numeric part from the string

select  *
from    yourtable t
        cross apply
        (
            select  alpha   = LEFT(your_col, PATINDEX('%[^0-9]%', your_col))
        ) AS a
        cross apply
        (
            select  num = CONVERT(INT, REPLACE(your_col, alpha, ''))
        ) AS n
order by case when alpha <> '' then 1 else 2 end,
         alpha,
         num


Assuming your source column is called col, I would add a Derived Column Transformation. Use the substring and right function to split the column apart and insert the ampersand in the middle.

SUBSTRING(col,1,1) + "&" + RIGHT(col,1)


Implementing subtype of a subtype in type/subtype design pattern with mutually exclusive subclasses

According to Martin Fowler, there are 3 approaches to the problem of table inheritance:

Single Table Inheritance: One table represents all types. Unused attributes are NULLed.
Concrete Table Inheritance: One table per concrete type, each table columns for each attributes of the type. No relation between tables.
Class Table Inheritance: One table per type, each table has attributes only for new, not-inherited attributes. Tables are related reflecting the actual type inheritance hierarchy.

You can start with these as a starting point to search for pros and cons of each approach. The gist of it is that all approaches have major disadvantages, and none has any overwhelming advantage. Better known as the object relational impedance mismatch, this problem is yet to find a solution.

Personally I find that the type of problems a bad relational design can lead to are orders of magnitude more serious than the kind of problems arising from a bad type design. Bad database design leads to slow queries, update anomalies, data size explosion, deadlocks and unresponsive apps, and tens to hundreds of Gigabytes of data sunk in the wrong format. Bad type design leads difficult to maintain and update code, not runtime. Therefore, in my book, correct relational design trumps any OO type purity over and over and over.


Pre-filtering on right table in a LEFT JOIN

For example you can do it this way:

select p.id, p.name,
COALESCE((select count(*) from child where p.id = pid), 0) AS cnt
from parent AS p
where name like '%xyz%'

or this:

select p.id, p.name,
count(c.pid) AS cnt
from parent AS p
left join child AS c ON p.id = c.pid
where name like '%xyz%'
group by p.id, p.name

if you need more columns from parent:

select p1.*, CTE.cnt
from parent p1
join (select p.id
count(c.pid) AS cnt
from parent AS p
left join child AS c ON p.id = c.pid
where name like '%xyz%'
group by p.id) as CTE on p1.id = CTE.id


Save postgres database from broken ubuntu 10.04 server

I think backing up the "data directory" is what you want.

Moving the whole directory to a new server should be what you might look into. This will move everything within the cluster. From there, you can drop, move,... your single databases as you need to.


How fast can I pull data through a SQL Server client connection?

Some things to try:

Wider data sets - if you're using a narrow table (just a few fields), SQL Server may be waiting for the client app to ack the data and request the next set of rows. Since you're doing experimentation, try building a table with just two fields: an identity, and a CHAR(7000) filled with a string.
Pull data directly from cache - size your VM so that it can hold, say, 32GB of data in the buffer pool (try 64GB memory, for example), read the table up into cache first, and then try to pull it across the network. This isn't a practical long-term fix, obviously - but if you're bottlenecked reading your data files from disk, then it's time to start tuning that.
Avoid intermediate sorts - try pulling table contents directly with a SELECT rather than joining multiple tables together and/or ordering the result sets. Again, not a practical fix, just showing how you can get higher throughput for bandwidth testing.
Try your tests locally first - which just helps to rule out that it's not a bottleneck on the app you're using to pull data across the wire, or network settings between VMs. (Plus, there are limits on network throughput on client VM instances, too - make sure you're using a large enoguh client VM.) If the local test runs fast but the network test is slow, try copying a file locally from the client VM to a shared drive on the SQL Server. If that's slow, then there's your gotcha.
Check your top wait type - use sp_BlitzFirst @ExpertMode = 1 (Github repo) (disclaimer: I'm the author), and look in the Wait Stats section. If you see ASYNC_NETWORK_IO as your top wait type, the client is the bottleneck (or a slow network). If it's PAGEIOLATCH*, then it's waiting to read the data pages from the data file.


Whatever you do, if you go down the RDBMS route, I recommend that you choose PostgreSQL over MySQL - it is a far more sophisticated product and has lots more functionality.

Check constraints
Window (analytic) functions
CTEs - Common Table Expressions
Proper set operators - INTERSECT, EXCEPT as well as UNION (which MySQL does have)
Far superior support for JSON

As for response time, that depends on far more than your RDBMS server.

Your disk config (RAID &c) will be very critical in that domain, as will your server's RAM and CPU, although most database operations are HDD-bound.

It will depend on your queries also (obviously). If you are doing point SELECTs on PRIMARY KEYs, then 1/2s is not unrealistic - but if you are doing analytic work (GROUP BY, HAVING, range scans, subselects... the list goes on), then there's no way anybody can tell you in advance what your response time will be.

If you are always going to be performing the same query or small range of queries against your data, then a NoSQL solution might be the way to go - but SQL's ability to "slice and dice" data is beyond compare - plus, you never know when new requirements will arise.

Your question might be a bit broad for this forum - visit the help centre, take the tour and check out the "help us to help you" blog. p.s. welcome to the forum.


Create Calculated Fields from Distinct Values in Second Table

This looks lika a PIVOT operation, where you want an aggregate (the count) of your error codes, grouped by date (on rows) and error code (on columns).

First off, pivot reports are probably easier to design in your reporting tool, Report Builder, so I would try this query first and try to format the results into a pivot table in the GUI. I don't know Report Builder very well, but the query should look something like this:

SELECT cal.cal_date,
       ec.ERRCODE,
       COUNT(*) AS [count]
FROM dbo.CreateCalendar(@p_year) AS cal
LEFT JOIN Errors.ERRCODE AS ec ON
    cal.cal_date=ec.ERRDATE AND
    ec.ERRTYPE='BATCH'
GROUP BY cal.cal_date, ec.ERRCODE;

If that doesn't work, you can make SQL Server do the pivoting for you, but the downside is that you have to explicitly specify each column (error code) manually in the query, which is repetitive and any future error code won't show up without editing the query later on.

SELECT cal.cal_date,
       SUM((CASE WHEN ec.ERRCODE='0001' THEN 1 ELSE 0 END)) AS [0001],
       SUM((CASE WHEN ec.ERRCODE='0002' THEN 1 ELSE 0 END)) AS [0002],
       SUM((CASE WHEN ec.ERRCODE='0003' THEN 1 ELSE 0 END)) AS [0003],
       SUM((CASE WHEN ec.ERRCODE='0004' THEN 1 ELSE 0 END)) AS [0004]
FROM dbo.CreateCalendar(@p_year) AS cal
LEFT JOIN Errors.ERRCODE AS ec ON
    cal.cal_date=ec.ERRDATE AND
    ec.ERRTYPE='BATCH'
GROUP BY cal.cal_date
ORDER BY cal.cal_date;

For more reading on this type of operation, take a look at GROUP BY, aggregate functions (like COUNT() and SUM()) and PIVOT in T-SQL (although I used a "manual" construct to build a pivot table instead of the built-in PIVOT syntax).


Is it possible to insert un-formatted data for money data type in PostgreSQL

How can I insert locale specific values?

Set LC_MONETARY accordingly. (Only affects the money type and some monetary details in formatting output!) You can do this as permanent default for your whole DB cluster in postgresql.conf, just for the current session with SET or for the transaction only with SET LOCAL. (Or in several other ways.)

Demo on a Windows machine:

BEGIN;
CREATE TEMP TABLE mdemo(id serial, m money);

SET LOCAL lc_monetary = 'German_Austria.1252';
INSERT INTO mdemo(m) VALUES ('123.45');

SET LOCAL lc_monetary = 'English_US.1252';
INSERT INTO mdemo(m) VALUES ('123.45');
TABLE mdemo;

 id |     m
----+------------
  1 | $12,345.00
  2 |    $123.45

ROLLBACK;  -- to clean up

As you can see the string literal '123.45' was interpreted according to the current setting of LC_MONETARY. In Austria, the dot (.) serves as group separator and , is the decimal point. While in the USA it's the other way round.

It's important to find correct locale names. The manual:

  What locales are available on your system under what names depends on
  what was provided by the operating system vendor and what was
  installed. On most Unix systems, the command locale -a will provide a
  list of available locales. Windows uses more verbose locale names,
  such as German_Germany or Swedish_Sweden.1252, but the principles are the same.

  How can I insert values with my own digit grouping and decimal grouping formats?

Use to_number() to produce a numeric value from the string literal, which can in turn be cast to money.

Say,  you have this literal: '12,454.8-':
2 digits, , as group separator, 3 digits, . as decimal point, 1 digit, pending sign. This does not work:

SELECT money '12,454.8-';

Works:

SELECT to_number('12,454.8-', '99,999.9S')::money

Since we are reading in a general number the more general setting for LC_NUMERIC applies for the import while the setting for LC_MONETARY still dictates how the result is displayed! The cast from numeric to money is locale-agnostic (immutable). Don't get confused.

And the pattern in the 2nd parameter is tricky, too. Symbols like , and . are taken literally and interpreted according to C locale (no locale), while patterns (you could use '99G999D9S', too) are interpreted according to the current locale.

I suggested clarifications to the manual on pgsql-docs.

  How can I read unformatted data?

You can read it, but you can hardly cast this to any numeric type without a minimum of information about its format.

I still would rather not use the data type money. Narrow, quirky specification. Use numeric to cover all bases. Or just integer signifying cents, performs better. See:

PostgreSQL: Which Datatype should be used for Currency?


Exactly same query and plan but different duration and total reads. I Know parameter sniffing but

The two query plans are identical, they are trivial plans and they
  just use the cluster index, but the total reads and duration are
  different. Following the two different BlitzCache results:

Total reads in that result set are most likely totalled across multiple executions. See the total rows column is 76,218 but the min, max and avg are identical to the other execution? This suggests the original plan had been executed 3 times so don't look at the total, look at the avg, which is identical.

You need to look at whether these reads are logical or physical because the variance in execution time suggests they're possibly logical and the physical read differences may shed more light on your situation.

  Solarwind shows a drop in Page Life Expectancy, and Sql server
  instance uses only one core (MAXDOP = 1). I think MAXDOP and PLE can
  be the cause of the issue, what do you think ? How can I be sure of
  that ? how can I know if the data was taken from disk or ram ?

If you only have a single core then both query executions will use MAXDOP = 1, so that is unlikely to cause any variation. PLE could be a symptom of a deeper issue causing your performance issue, but it's not the root cause. If you're seeing PLE consistently low or has an extreme sawtooth pattern, you may find that determining the root cause of that helps identify this query's problem.

What you're describing doesn't sound like parameter sniffing because your query has no WHERE clause. It sounds like the query is having to read pages from disk on the first execution (physical read) into the buffer cache and subsequent reads are occurring in memory (logical read), which is typically much faster than physical reads.

Take a look at this article for a description of physical vs logical reads. Basically, when you first executed it took 20 seconds and this is caused by reading pages from disk. The next execution wasn't faster due to the trivial change or the new plan (as you stated, the actual plans were identical) but because those data pages were read from memory instead of disk. If you were to flush the buffer cache and re-run the queries again you would likely see the same behaviour - 1st execution = 20 secs, 2nd execution = 1 sec.

You can force this behaviour to occur to see it in real-time. On a non-production server, run the following:

DBCC DROPCLEANBUFFERS

SET STATISTICS IO ON

SELECT * FROM A400RDATA

SET STATISTICS IO OFF

Review the output in the messages pane and note down the Physical Reads and Logical Reads values. Run the below lines again:

SET STATISTICS IO ON

SELECT * FROM A400RDATA

SET STATISTICS IO OFF

Check the output and compare the physical reads and logical reads values again. Physical reads should have dropped significantly, most likely down to 0, and logical reads will be the same or higher, indicating pages are now read from memory without needing to fall back to disk reads. Execution time should be quicker as well.

How do we confirm this in production for queries that have already executed? Well it will be hard to be definitive without having monitoring and metrics collection in place prior, however, if you query sys.dm_exec_query_stats using the plan handle you can get stats on recent executions. Check the last, min and max physical reads. If min is 0 and max is some higher number this indicates some executions are reading from disk. If query execution has returned to 1 sec, last should be 0 or some really low number.

Also check the last, min and max elapsed time columns from that DMF as well to see if there is a variance in executions times for the 20-second execution plan, I suspect there will be. Solarwinds may help you correlate a drop in PLE with the increase in execution times for this query, since a drop in PLE could indicate these data pages aged out/were forced out of the cache and caused the physical reads to increase. Check out this article for useful hints on optimising/managing PLE to maintain your servers performance.


Disallowing table creation in the public schema

This will probably do what you want:

revoke create on schema public¹ from public²;

Note that the public² does not mean the schema public¹, but a group representation of all users of the database. So when you try to revoke your user from creating tables nothing happens because it probably does not have this permissions at start(except if you give it), but the group public² that your user belongs, is with this access by default - That's the default behavior for schema public.

Now you can give the create privilege for specific users:

grant create on schema public to specificuser;


Can I use mirroring to make a copy of my Database?

The main problem with mirroring is that if you have a network outage or get disconnected mirroring will stop. So across networks it is better to use log shipping.

Additionally it sounds like you have a vldb or poor disk i.o based on your time listed in your question which is why you might prefer log shipping for the migration as you would only need to restore the last log to bring up the new database. This is ideal for minimizing downtime.

This blog, while old, highlights some points i explained


Configured identity seed starting value on database level

No it is not possible at a database level.

IDENTITY is not a data type, It is a table's column definition.


Is it benefit to create a index key if unique key already exists on the same columns?

More discussion on building indexes:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql

Furthermore your table smells like a many:many mapping table??  Here are several tips on efficiency for such:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table


As already shown in the comments, I've seen/used the same feature in SSMS 2005 as shown here. But I didn't manage to see what ever editor window setting would be different than in SSMS 2012/2014 where the behavior has changed.


PostgreSQL 8.4 no levenshtein fuzzymatching?

You have to install the additional module fuzzystrmatch.

In PostgreSQL 9.1 or later, simply run CREATE EXTENSION once per database:

CREATE EXTENSION fuzzystrmatch;

In PostgreSQL 8.4 on Debian Squeeze, you would run as OS postgres matching the DB superuser postgres (so with peer authentication without PW) the SQL script provided by the package postgresql-contrib-8.4 (install that if you haven't already):

sudo -u postgres psql mydb -f /usr/share/postgresql/8.4/contrib/fuzzystrmatch.sql

About CREATE EXTENSION:

Do I need to execute "create extension pgcrypto" everytime?


Do we need wal_keep_segments when we have archive_command?

No, wal_keep_segments and archive_command are independent from each other.

As soon as a WAL segment is finished, it will be archived, no matter how wal_keep_segments is set. wal_keep_segments keeps the file from being deleted at the next checkpoint after it has been archived.


Is IDENTITY continuous in new versions of PostgreSQL?

The term is IDENTITY, not IDENTIFY. (I fixed it in your question.)
IDENTITY columns (GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY) are based on a SEQUENCE just like the older serial columns. You have to expect gaps in the serial numbers with either, that's in the their nature, and nothing to worry about.
Differences are addressed in my answer on stackexchange (where you obviously found the code to convert your serial to an IDENTITY column):

How to change a table ID from serial to identity?

But nowhere did I suggest that the conversion would remove gaps in the ID space. Or that this would be desirable. If you positively think you need that, consider:

Compacting a sequence in PostgreSQL

It starts with a similar disclaimer like you already received in the comments (and some alternatives):

First off, gaps in a sequence are to be expected. Ask yourself if you really need to remove them. [...]


List MATERIALIZED VIEWs in PostgreSQL that do not have a UNIQUE index

Tip: use psql's -E flag to get it to show you how what queries it is issuing for e.g. its \dm meta-command, and adjust from there.

Here's a rough and not at all cleaned-up query, but it should do the trick...

WITH matviews_with_unqiue_keys AS (
  SELECT c.oid, c.relname, c2.relname AS idx_name
  FROM pg_catalog.pg_class c, pg_catalog.pg_class c2, pg_catalog.pg_index i
  LEFT JOIN pg_catalog.pg_constraint con ON (
    conrelid = i.indrelid AND conindid = i.indexrelid AND contype IN ('p','u'))
  WHERE
    c.relkind = 'm' AND
    c.oid = i.indrelid AND i.indexrelid = c2.oid AND indisunique
)

SELECT c.relname
FROM pg_class c
WHERE c.relkind = 'm'
EXCEPT
SELECT mwk.relname
FROM matviews_with_unique_keys as mwk;


Changes to estimates on predicates that contain SUBSTRING() in SQL Server 2016?

I'm not aware of any documentation. I did look into this and make some observations however that are too long for a comment.

The 10% estimate is not always a degradation. Take the following example.

TRUNCATE TABLE dbo.StringTest

INSERT INTO dbo.StringTest
SELECT TOP (1000000) 'ZZ_' + LEFT(NEWID(), 12)
FROM   master..spt_values v1,
       master..spt_values v2;

and the WHERE clause in your question.

WHERE SUBSTRING(TheString, 1, CHARINDEX('_',TheString) - 1) = 'ZZ'

The table contains a million rows. All of them match the predicate. Under compat level 130 the 10% guess yields an estimate of 100,000. Under 120 the estimated rows is 1.03913.

The 120 behaviour uses the histogram but only to get the number of distinct rows. The density vector in my case shows 1.039131E-06 and this is multiplied by the table cardinality to get the estimated row count. All of the values are in fact different but all match the predicate.

Tracing the query_optimizer_estimate_cardinality extended event shows that under 130 there are two different <StatsCollection Name="CStCollFilter" events. The first one estimates 100,000. The second one loads the histogram and uses the CSelCalcPointPredsFreqBased/DistinctCountCalculator to get the 1.04 estimate. This second result appears unused.

The behavior that you observed is not consistently applied in 130. I added ORDER BY TheString expecting this to be a clear win for the 130 estimator as the 120 struggles on with a memory grant for one row but this minor change was sufficient to bring the estimated rows down to 1.03913 in the 130 case too.

Adding OPTION  (QUERYRULEOFF SelectToFilter) reverts the estimate going into the sort to 100,000 but the memory grant doesn't increase and the estimates coming out the sort are still based on the table distinct values.

Similarly tweaking the cost threshold for parallelism so that the query gets a parallel plan was sufficient in the 130 case to revert to the lower estimate. Adding QUERYTRACEON 8757 also causes the lower estimate. It looks like the 10% estimate is only retained for trivial plans.

Your proposed rewrite with

WHERE TheString LIKE 'ZZ[_]%'

Shows much superior estimates to both. The output for this is

  CSelCalcTrieBased

      Column: QCOL: [MyStringTestDB].[dbo].[StringTest].TheString

Showing that it used tries. More info about this is in the string summary statistics section just above here.

It is not the same as your original query however. As the first instance of _ is now assumed to always be the third character rather than being found dynamically.

If this assumption is hardcoded into your original query

 WHERE SUBSTRING(TheString, 1, 3) = 'ZZ_'

The estimation method changes to CSelCalcHistogramComparison(INTERVAL) and the estimated rows become accurate.

It is able to convert that into a range

WHERE TheString >=  'ZZ_' AND TheString < ???

and use the histogram to estimate the number of rows with values in that range.

This applies only to the cardinality estimation however. LIKE is preferable as it can use a range seek at runtime. SUBSTRING(TheString, 1, 3) or LEFT(TheString, 3) can't.


Restoring a tablespace to a different database

Yes,

if you have all files placed in the same locations, you can create a pfile, make it point to the controlfile[s] and start the instance. If the backup was a clean cold backup, it should open without any problems. Don't forget to also enter the name parameters and the dump destination parameters for the database.

You should restore at least the system, undo and your_desired_tablespace_files in order to succeed. Create a new temporary tablespace.


HAVING clauses can place extra restrictions on the output rows of an aggregate query – just like WHERE clauses do in record-by-record queries. A HAVING clause can impose conditions on any output column or combination of columns, aggregate or not.

As far as NULL is concerned, the following two facts are worth knowing (and hardly surprising, I would guess):

Rows for which the HAVING condition evaluates to NULL won't be included in the result set. (“Only true is good enough.”)

“HAVING  IS [NOT] NULL” is a legal and often useful condition, whether  is aggregate or not. (But if  is non-aggregate, you may save the engine some work by changing HAVING to WHERE and placing the condition before the “GROUP BY” clause. This goes for any condition on non-aggregate columns.)

Remarks

MAX ignores any null values.
For character columns, MAX finds the highest value in the collating sequence.


moving all databases from SQL 2005 EXPRESS SP2 to SQL2005 Enterprise SP4

Since you have downtime, best is to use this powershell script- with backup restore option

This script will take care of moving databases, logins, jobs,etc on the new server. make sure to use backup restore switch as opposed to detach/attach.


DIRTY_PAGE_POLL without activated Indirect Checkpoint

According to SQLSERVER-DBA.COM

The background-thread is waiting to poll dirty pages even when indirect checkpoints are disabled (which is the default setting of each database)

  The interesting aspect of DIRTY_PAGE_POLL wait type , is it still appears as a wait type, even though Indirect Checkpoint is not enabled, as the background thread  is waiting to poll for dirty pages.


Parameter evaluation against harcoded string always return false

I tried it and it is working as expected:
http://sqlfiddle.com/#!9/b92cd1/1

However, some clients deals with every one statement as a single transaction. Try to add all of your code to one transaction and execute it.

I tested that in MySQL client (command line in Linux) and on SQL Fiddle (Link above)


The Connection timeout is always set by client. The timeout is normally configured when the connection is created. In order to prevent recompilation of client code the timeout interval is often stored in a config file, registry entry etc

You cannot configure the timeout on the database itself.


Creating a View with results from join of scalar tables

Did you try chaining your WITH statements together?

N.B. This may not be the most-optimal method of solving this problem, but should work based on your existing approach

CREATE VIEW dbo.MyViewName
AS
WITH R(N) AS
(
   SELECT 0
   UNION ALL
   SELECT N+1
   FROM R
   WHERE N < 12
), MonthYears AS
(
  SELECT LEFT(DATENAME(MONTH,DATEADD(MONTH,-N,GETDATE())),3) + '-' +
       CAST(DATEPART(YEAR,DATEADD(MONTH,-N,GETDATE())) as varchar(10)) AS Name
  FROM R
), MonthYearsData AS
(
   SELECT
     YEAR(RequestedOn) Yr,
     MONTH(RequestedOn) Mon,
     SUBSTRING(DateName( month , DateAdd( month , MONTH(RequestedOn) , 0 ) - 1 ),1,3) + '-' + CAST(YEAR(RequestedOn) as varchar(10)) as MonYr,
     count(*) CrCount
   FROM TempTableTest
   WHERE RequestedOn >= DATEADD(MONTH, -13, GETDATE())
   GROUP BY YEAR(RequestedOn), MONTH(RequestedOn)
)
SELECT M.Name, ISNULL(N.CrCount,0) as CRCount
FROM MonthYears M
left outer join MonthYearsData N ON M.Name = N.MonYr
GROUP BY M.Name, N.CrCount, M.LNo
ORDER BY M.LNo DESC;


One idea, without knowing the error you get, is that the operation times out.  This sometimes happens when the locks to be taken conflict with other operations or there are too many indexes and constraints to update and check. My answer below gives an idea for this scenario.  Other possibilities are that you ran out of disk space - add more disks to fix it, and consider the solution below to change the table structure.

Now that you cannot insert into your table anymore, you can just as well create a similar table with bigint instead of the integer PK column.  Copy over the existing data.  When done (and the necessary indexes built), you can simply rename both tables in the same transaction, so that the new one will in the end have the name of the present one.

With normal operation this would be a bit more complicated (has to duplicate the inserts to write to the new table and so on), but in your case it should work as described above.

Copying a table's data without the constraints and indexes will be possibly much faster than altering it in place (for most cases, at least).

Note: 9.1 will run out of support soon, please consider upgrading to a more recent version.


Shrink configuration & content database in Sharepoint DBs

Just leave it at 10GB, it will grow on you. If you absolutely need to shrink the datafile for the configuration database then don't go to the smallest possible option but leave some headroom.


Restoring database backup makes identity column start at 1 again

Some more digging with the server administrators revealed that the problem was Schemas. On the source server, all tables were in a special schema, let's call it Custom. The Login I used to manipulate data and create the backup with was assigned to this schema and used it as default.

Transferring the database to the target server brought the user along, but not the login (that is stored on server level). So the administrator went along and created a new login that matched the user. That didn't work because the user already existed and SSMS tries to create the user in the database when creating a new login.

So he deleted the user, created it along with the new login, but didn't set the default schema to Custom.

That meant that any query and connection that used that login for some reason saw the table definitions, but not the data inside it - and apparently that included the identity columns as well.

So we switched the new user to use the Custom schema and now everything works as expected.


How to use barman to backup PostgreSQL database correctly?

When pg_receivexlog was renamed, barman didn't add sprinkle conditional error messages all over its code.  It is looking for pg_receivewal, but the text of the error message still says "pg_receivexlog" as an umbrella term to cover whichever applies.

The solution is to add the directory holding pg_receivewal to your $PATH.  It should already be there, but apparently it isn't.  How did you install both PostgreSQL and barman?  Which OS user are you running this command as?


Total hours worked per employee per day including overnight sessions

Please check if the performance is sufficient:

SELECT empid
      ,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW())
                                                      FROM emplog b
                                                     WHERE b.empid = a.empid
                                                       AND b.time  > a.time
                                                       AND b.state = 1
                                                    )))) date_worked
  FROM emplog a
 WHERE state=0
 GROUP BY empid;

Edit

To aggregate per day:

SELECT empid
      ,DATE(time) day
      ,SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND, time, (SELECT IFNULL(MIN(time),NOW())
                                                      FROM emplog b
                                                     WHERE b.empid = a.empid
                                                       AND b.time  > a.time
                                                       AND b.state = 1
                                                    )))) date_worked
  FROM emplog a
 WHERE state=0
 GROUP BY empid, DATE(time);