Sequence - NO CACHE vs CACHE 1 Is there any difference between a SEQUENCE declared using NO CACHE and one declared using CACHE 1 in SQL Server 2012+?

Sequence #1:

CREATE SEQUENCE dbo.MySeqCache1
AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999
    NO CYCLE
    CACHE 1;
GO

Sequence #2:

CREATE SEQUENCE dbo.MySeqNoCache
AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999
    NO CYCLE
    NO CACHE;
GO

Is there any difference between the two? Will they behave differently when being used in a SQL Server 2012+ environment?

It is hard to give a definitive answer on this question until you actually find a difference. I have found none but that does not mean there is no difference only that I have not seen one in the tests I have made.

The easy test is for performance. Either getting the next value in a loop or using a numbers table as a source to generate multiple values at a time. In my tests there were no difference in performance between using no cache and a cache of 1 value but there was a significant performance improvement of using a cache of 2.

This is the code I used to test performance:

declare @D datetime = getdate();

declare @I int = 0;
while @I < 9999
  select @I = next value for dbo.S;

select datediff(millisecond, @D, getdate());

Result:

Cache        Time(ms)
------------ --------
NO CACHE     1200
1            1200
2             600
1000           70

To dig a bit deeper I used the extended events sqlserver.metadata_persist_last_value_for_sequence and sqlserver.lock_acquired to see if there was something different in how the values persists to the system table.

I used this code to test for no cache and cache size of 1 and 4.

DECLARE @S NVARCHAR(max) = '
CREATE EVENT SESSION SeqCache ON SERVER
ADD EVENT sqlserver.lock_acquired(
    WHERE (sqlserver.session_id=({SESSIONID}))),
ADD EVENT sqlserver.metadata_persist_last_value_for_sequence(
    WHERE (sqlserver.session_id=({SESSIONID})))
ADD TARGET package0.event_file(SET filename=N''d:\SeqCache'');';

SET @S = REPLACE(@S, '{SESSIONID}', CAST(@@SPID AS NVARCHAR(max)));

EXEC (@S);

GO

CREATE SEQUENCE dbo.S
AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 1
    MAXVALUE 9999
    NO CYCLE
    NO CACHE;
--    CACHE 1;
--    CACHE 4;

GO

ALTER EVENT SESSION SeqCache ON SERVER STATE = START;

GO

DECLARE @I INT = 0;
WHILE @I < 10
  SELECT @I = NEXT VALUE FOR dbo.S;

GO

ALTER EVENT SESSION SeqCache ON SERVER STATE = STOP;
DROP EVENT SESSION SeqCache ON SERVER;
DROP SEQUENCE dbo.S;

There is no difference in output for using no cache and cache of 1.

Sample output:

name                                      persisted_value mode
----------------------------------------- --------------- -----
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  1               NULL
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  2               NULL
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  3               NULL

When using a cache of 4.

name                                      persisted_value mode
----------------------------------------- --------------- -----
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  4               NULL
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            SCH_S
lock_acquired                             NULL            IX
lock_acquired                             NULL            U
metadata_persist_last_value_for_sequence  8               NULL

The SCH_S lock is done when a value is needed. And when the cache is exhausted it is followed by a IX and the a U lock and finally the event metadata_persist_last_value_for_sequence is fired.

So there should be no difference between using no cache and and cache 1 when it comes to potentially losing values on an unexpected shutdown of SQL Server.

Finally I noticed something in the Message tab in SSMS when creating a sequence with cache 1.

  The cache size for sequence object 'dbo.S' has been set to NO CACHE.

So, SQL Server thinks there is no difference and tells me so. There is however a difference in sys.sequences in the column cache_size. It is NULL for no cache and 1 for a cache of 1.


Does DBMS_STATS.AUTO_CASCADE ever work? According to the documentation,
"Use the constant DBMS_STATS.AUTO_CASCADE to have Oracle determine whether index statistics are to be collected or not."

But it seems like AUTO_CASCADE will always gather index statistics, even when it obviously does not need to.

This test case demonstrates the problem:

SQL> --Create table
SQL> create table statistics_test(a number, b number, c number, d number, e number);

Table created.

SQL> --Add 10 million rows (about 320 MB)
SQL> begin
  2     for i in 1 .. 100 loop
  3             insert into statistics_test
  4             select level, level, level, level, level from dual
  5             connect by level <= 100000;
  6     end loop;
  7     commit;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> --Create large index (AFTER the data load)
SQL> create index statistics_test_index on statistics_test(a,b,c,d,e);

Index created.

SQL> --Statistics are automatically gathered on index creation or rebuild.
SQL> select to_char(last_analyzed, 'HH24:MI:SS') last_analyzed
  2  from user_indexes
  3  where index_name = 'STATISTICS_TEST_INDEX';

LAST_ANA
--------
16:11:43

SQL> --Gather stats, use AUTO_CASCADE.
SQL> exec dbms_stats.gather_table_stats(user, 'STATISTICS_TEST', cascade => DBMS_STATS.AUTO_CASCADE);

PL/SQL procedure successfully completed.

SQL> --FAIL: Time changed - index was re-analyzed even though no data changed.
SQL> select to_char(last_analyzed, 'HH24:MI:SS') last_analyzed
  2  from user_indexes
  3  where index_name = 'STATISTICS_TEST_INDEX';

LAST_ANA
--------
16:12:12

SQL> --Let's assume that LAST_ANALYZED is lying.
SQL> --What I'm really worried about is the extra time spent analyzing indexes.
SQL> --Compare performance of AUTO_CASCADE, TRUE, and FALSE.
SQL> --FAIL: AUTO_CASCADE is as slow as TRUE.  It should be as fast as FALSE.
SQL> set timing on
SQL> exec dbms_stats.gather_table_stats(user, 'STATISTICS_TEST', cascade => DBMS_STATS.AUTO_CASCADE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:20.80
SQL> exec dbms_stats.gather_table_stats(user, 'STATISTICS_TEST', cascade => true);

PL/SQL procedure successfully completed.

Elapsed: 00:00:19.58
SQL> exec dbms_stats.gather_table_stats(user, 'STATISTICS_TEST', cascade => false);

PL/SQL procedure successfully completed.

Elapsed: 00:00:08.27

I do not think it matters, but my system is using the default for ESTIMATE_PERCENT, METHOD_OPT, DEGREE, GRANULARITY, CASCADE, and NO_INVALIDATE.  I can reproduce this issue on 10.2.0.4, 11.2.0.1, 11.2.0.3, and 12.2.0.1.

This looks like a bug, but I can't find anything on My Oracle Support.  And it's strange that a default setting, with significant performance impact, would be completely broken for so long.

Am I missing something here?

Are you sure the index was analyzed - or was it just that DBMS_STATS updated the LAST_ANALYZED timestamp?  The best way to find out it to trace the process performing the analysis.


aggregating json fields I have a table that holds JSON data. In a query with a GROUP BY clause, I'd like to get an array of all of the JSON field names in the result set.

I tried a query like this:

SELECT array_agg(jsonb_object_keys(data))
FROM table
GROUP BY some_id
WHERE some_id = 3

For an input data like

some_id | data
--------|---------------
3       | {"foo": "bar"}
4       | {"baz": 3}
3       | {"bar": 4}

I'd like to receive:

array_agg
--------------
{'foo', 'bar'}

But it returns an error: ERROR:  set-valued function called in context that cannot accept a set

It seems like I need to somehow convert setof text, which is what jsonb_object_keys returns, into array but I don't know how.

The error message is telling you that you can't use a set returning in the SELECT list. You need to put it into the FROM clause:

SELECT array_agg(t.k)
FROM table tbl, jsonb_object_keys(tbl.data) as t(k)
WHERE tbl.some_id = 3
GROUP BY tbl.some_id;


Automatic aging-out (deletion) of old records in Postgres Does Postgres have any features to support aging-out old records?

I want to use Postgres for logging, as a sort of queue, where records (log events) older than two weeks are automatically deleted.

There is no feature built in to delete rows automatically on a time-based regime (that I would know of).
You could run a daily (you decide) cron-job to schedule simple DELETE commands or use pgAgent for the purpose.
Or you could use partitioning with weekly partitions. That makes deleting very cheap: just keep the latest two weeks and drop older partitions.
Before Postgres 10, partitioning was always based on inheritance. The new declarative partitioning has a lot of advantages and is improved further with every release. Inheritance still provides one key advantage, though. The manual:

Some operations require a stronger lock when using declarative
partitioning than when using table inheritance. For example, adding or
removing a partition to or from a partitioned table requires taking an
ACCESS EXCLUSIVE lock on the parent table, whereas a SHARE UPDATE EXCLUSIVE lock is enough in the case of regular inheritance.

Meaning, adding or removing a partition does not interfere with readers and writers of other partitions with inheritance, while everything is blocked with declarative partitioning (when going through the parent table). It's a very fast operation, though.
With inheritance, create partitions that inherit from a "master" table, lets call it log. Create a RULE or a TRIGGER on the master table that redirects INSERTs to the partition of the current week based on system time.
Either way, you can always log to the master table log. Create partitions ahead of time. Make that several weeks ahead to be sure and run a weekly cron job that adds future child tables ...
There are code examples in the manual for inheritance and declarative partitioning..
Related answer with a plpgsql function creating tables for inheritance automatically:

Drop certain tables in a function managing partitions

The related solution recreates a RULE to redirect INSERTs. A trigger function could write to the current partition dynamically ...


SQL Server Agent job returns a failure status message after the job succeed. We have a sql server agent job which runs every day on sql server 2008R2. The job has been running for years and had no issues before; and nothing has been changed recently.

Two weeks ago, the job started sending a failed job email once every 2 or 3 days. It also logs a failed job status both on sql server agent job history  as well as event viewer. However, the job is successfully executed because we are getting a successful report from the application side all the time.  Can someone have any idea how to fix this issue?

Thanks

For me This looks a clash between job schedules. if the job is executing one day and fail the next day,there may be other job running that might contradict with this schedule. you might have to check all your job schedules and take appropriate action accordingly.

This link might help you to check all the jobs schedules running in your server.
http://www.sqlprofessionals.com/blog/sql-scripts/2014/10/06/insight-into-sql-agent-job-schedules/


Date alignment and pair matching extraction best done with TSQL or C#? For starting with: my title sucks, so help me figure out a new one?

I can't post all the SQL here (over 30k characters for the lot of it), so I stuck it on pastebin.com

The problem:

I get an XML file that I scrape some records from, and I need to extract some data from the records and build another table off it. The records are for an event going off and coming on, and I've included sample data in the pastebin for recreating. Without seeing the data it's kind of hard to explain. I've given all the data I have from my sample import which should be sufficient to build the app off of, but I'm not getting any more information than what is shown in the data.

I'll give you a moment to glance at the data so this makes sense.

So what I need to do is this: For each "off" event, I need to match it to the next "on" event, and I need to have two tables at the end, one table for "historical events" and one table for "current events". However, if I can just get "historical events" built correctly I can figure out how to get "current events" from that.

Business rules:

If two or more "off" events are collected before an "on" event, keep the oldest "off" event. If two or more "on" events are collected before an "off" event, keep the newest "on" event. If there is a complete pair, put them in the historical table. If there is an "off" event and not an "on" event, put it in the current table (so if I wanted to keep inserting/deleting from this table, that's fine too). If there is an "off" event already in the current table I can take it and move it to the historical table for an "on" event being read in (this will need to be implemented later, but if I can get the pairings matched initially I'll be able to go forward for now.

I think that's pretty much it for the logic. My thoughts are to either figure out how to do this in SQL or push it out to an app written in C# and do it with some temporary lists in C# and build what I need using for...next logic. This may be infinitely easier in C#, but I have a feeling SQL can do this job just as easily as C# can, so I needed some help from the dba gurus.

The queries I have already don't work, but that's where I got to with my starting before going home on Friday, and I've been mulling it since then, and building a sample problem that I can post online (and that whole life thing too ya know). The data is live data and exact, except the ID's being anonymized and the text fields changed to something simple to work with.

Here's a spreadsheet showing roughly how I want the data to look at the end and the way it looks now. There is the current data (with a spacer row between each ID for clarity), the data that would be in the historical table (aligned with the ID of the original data for understanding) and the current table (again aligned). I hope this can help clarify the business rules.
https://spreadsheets.google.com/ccc?key=0AuvCdeHuVU5ddHRCNkpuWHBUREpRajlmLU5VX2xsWnc&hl=en&authkey=COq7y50H

So the complete SQL including tabledefs and current (very not-right) queries is on pastebin http://pastebin.com/k2f2CLnQ

Here's something I was fiddling with based on some work I had laying around.  It doesn't handle the events clustered at a single time well.  It could theoretically be helpful anyhow...:)

;WITH ordered_rows AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY Identifier ORDER BY EventTime) AS Row,
        Identifier, Type, EventTime, DiscoveredDate, FileId FROM #EventDataTemp
)
,filtered_rows AS
(
    SELECT Row, Identifier, Type, EventTime, DiscoveredDate, FileId,
        CAST(CASE Type WHEN 'Went Off' THEN 1 ELSE NULL END AS INT)
            AS OffEventRow
    FROM ordered_rows
    WHERE Row = 1
    UNION ALL
    SELECT o.Row, o.Identifier, o.Type, o.EventTime, o.DiscoveredDate, o.FileId,
        CAST(CASE WHEN o.Type = 'Went Off' AND f.Type = 'Went Off'
            OR o.Type = 'Came On' THEN f.OffEventRow ELSE o.Row END AS INT)
    FROM ordered_rows o INNER JOIN filtered_rows f
        ON o.Row = f.Row + 1 AND o.Identifier = f.Identifier
)
,on_events AS
(
   SELECT Identifier, OffEventRow, MAX(Row) AS OnRow
   FROM filtered_rows
   WHERE Type = 'Came On' AND OffEventRow IS NOT NULL
   GROUP BY OffEventRow, Identifier
)
SELECT f.Identifier, f.Type, f.EventTime, f.DiscoveredDate, f.FileId
FROM filtered_rows f LEFT JOIN on_events o
    ON f.Identifier = o.Identifier
    AND f.Row = o.OnRow
WHERE f.Type = 'Went Off' AND f.Row = f.OffEventRow
OR f.Type = 'Came On' AND o.OnRow IS NOT NULL
ORDER BY f.Identifier, f.EventTime


How does the log reader process bulk updates Key Problem

The latency we are experiencing isn't applying the changes from the Distributor to the Subscribers (if we watch Replication Monitor it's latency is usually <1 second), it is the time that a transaction committed to the publisher database takes to get to the distributor.

Part of the issue is that we have several filters applied to one of our most volatile tables, and on every update/insert/delete LogReader.exe needs to check which publication to put that record in based on any filters applied.

From tests we've done it seems that each additional filter that is applied it can exponentially increase the time that the LogReader takes to process each transaction.

(edit 23/06/2011: added more detail about the filters)

Background

In our setup of Replication we have several publication with filters on a highly volatile table (average of 1.5million transactions in a 2hr period).  During heavy periods this can result in the LogReader running at a latency of 20 seconds (usually < 1 second)

We have identified several areas of improvement (reducing number of filters, reducing number of updates, farming out the processing etc..), one potential area of improvement is changing how the updates are applied.

An example table (to aid explanation)

myTable
----------------
myID         int
myGroupID1   int
myGroupID2   int
Suspended    bit
FilterFlag1  int
FilterFlag2  int
FilterFlag3  int

For replication to illustrate this we would have 5 publications of [myTable]:

Publication  Filter
-----------  ------
NoFilter1    [all records]*
NoFilter2    [all records]*
Filter1      FilterFlag1 = 1
Filter2      FilterFlag2 = 1
Filter3      FilterFlag1 = 1 AND FilterFlag2 = 1

Other tables are combined into these publications which is why the same table with identical filters (none) are in more than one publication.

Current update process

The majority of applications that update this table do so by looping through their collection of objects, applying the change to that single object then committing their change to the database before moving onto the next object.

From a DB trace perspective this means we get up to 120 update statements when the change occurs

Example

update [myTable] set Suspended = @Suspended
where myID = @pID

Proposed update process

As the object collections are actually based around the group ID's, one potential improvement is to do a bulk update (rather than individual updates) and then refresh the object collection.  Reducing the number of update statements to 1 or 2 (depending on the business scenarios).

Example

update [myTable] set Suspended = @Suspended
where myGroupID1 = @groupID1
      and myGroupID1 = @groupID2

Impact on LogReader?

From an app processing perspective doing a single update makes sense to me (fewer round trips between the app and the database = quicker), however I'm not sure how the LogReader will treat both scenarios, as it needs to process each record updated by the transaction..

Will the LogReader process those records faster or slower on a bulk update?

In the log records those are separate statements that are written to the log.  The log reader would then batch those up and run them as a single transaction against the database.

If your database uses stored procedures, you can replicate the stored procedures, which would then allow the stored procedure call to be replicated instead of the actual data updates.


How can I alter the primary key of an index-organized table? We started with a naive primary key:

SQL> CREATE TABLE books(
        title VARCHAR2(10),
        CONSTRAINT pk_title PRIMARY KEY(title))
      ORGANIZATION INDEX;

Table created.

Then realized it is not unique. Say, a book can be re-released under the same title:

SQL> ALTER TABLE books ADD(release_date DATE NOT NULL);

Table altered.

Now to insert a re-released book, we have to relax the uniqueness on title somehow.

But I cannot drop and recreate the PK due to the table being index organized:

SQL> ALTER TABLE books DROP PRIMARY KEY;
ALTER TABLE books DROP PRIMARY KEY
*
ERROR at line 1:
ORA-25188: cannot drop/disable/defer the primary key constraint for
index-organized tables or sorted hash cluster

I cannot manage to extend the existing key in-place either:

SQL> CREATE UNIQUE INDEX pk_title_date ON books(title, release_date);

Index created.

SQL> ALTER TABLE books MODIFY PRIMARY KEY USING INDEX pk_title_date;
ALTER TABLE books MODIFY PRIMARY KEY USING INDEX pk_title_date
*
ERROR at line 1:
ORA-14196: Specified index cannot be used to enforce the constraint.

What are my options ? Is it possible without moving data or recreating the table (the actual table is huge) ?

An index-organized table (IOT) is just that; an index with no "real" table.  All data besides the primary key are just "tacked on" to the primary key, so for example if you have an IOT with 6 columns, 2 of which make up the primary key, behind the scenes you just have an index with 6 columns, of which the first 2 columns make it unique.

So, sorry, only way to fix it is to recreate the table; to improve the rebuild time temporarily disable logging.  You cannot use an append hint on inserts to an  IOT table to force a direct path insert:

SQL> CREATE TABLE books_new (
    title VARCHAR2(10),
    release_date DATE NOT NULL,
    CONSTRAINT pk_title PRIMARY KEY(title,release_date))
  ORGANIZATION INDEX NOLOGGING;

SQL> INSERT INTO books_new
     (SELECT title, '01-Jan-1980' FROM books);
SQL> ALTER TABLE books_new LOGGING;
SQL> ALTER TABLE books RENAME TO books_old;
SQL> ALTER TABLE books_new RENAME TO books;


Why doesn’t IMPLICIT_TRANSACTIONS increment @@TRANCOUNT for my IF statement? I was playing around with SET IMPLICIT_TRANSACTIONS ON. I found a situation where I am convinced the database is automatically creating and committing a transaction without leaving it open. If I do IF EXISTS (SELECT * FROM dbo.t1) or any variation, I am certain that a transaction is happening in the background which I’d expect to stay open if I’m using implicit transactions.

However, after reading the docs, it says something like:

  This means that if @@TRANCOUNT = 0, any of the following Transact-SQL statements begins a new transaction.

and the list it shows does not include IF. However, then I’d expect all statements not found in the documentation’s list to not leave @@TRANCOUNT incremented. So, I looked for another statement not included in that list. I found DECLARE. However, I found that if I perform a subquery with DECLARE, I get the behavior I’d expect from SET IMPLICIT_TRANSATIONS ON. That is, if my DECLARE has a subquery, it increments @@TRANCOUNT if it is zero. So it appears that the documentation is inconsistent with itself

SET IMPLICIT_TRANSACTIONS ON
GO
IF OBJECT_ID('dbo.t1') IS NOT NULL
BEGIN
    DROP TABLE dbo.t1
    COMMIT
END
GO
CREATE TABLE dbo.t1 (x INT);
COMMIT;
GO

IF CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END = 1 PRINT 'unreachable';
PRINT 'after1 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after1 0

DECLARE @x INT = 2;
PRINT 'after2 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after2 0

DECLARE @y INT = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;
PRINT 'after3 ' + CAST(@@TRANCOUNT AS VARCHAR(MAX)); -- after3 1
GO

WHILE @@TRANCOUNT > 0 ROLLBACK
GO

Why does DECLARE increment @@TRANCOUNT for an embedded SELECT but not IF? Is the SELECT embedded in the IF somehow not actually using a transaction?

I’m using SQL Server 13.0.4411.

I suspect that what you're seeing is because some statements get transformed to SELECT statements behind the scenes and others do not. Consider your final DECLARE statement:

DECLARE @y INT = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;

That is just shorthand for this:

DECLARE @y INT;
SET @y = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;

Which can also be written like this:

DECLARE @y INT;
SELECT @y = CASE WHEN EXISTS (SELECT * FROM dbo.t1) THEN 1 ELSE 0 END;

In which case you'd expect to see @@TRANCOUNT incremented. However, can you make the same statement about the IF statement? If I run your original code with actual execution plans turned on I see the following:

I don't see a SELECT in the plan for the first query. I only see COND WITH QUERY. So it seems consistent with the documentation.


how to estimate rebuild index on 140 million records? My existing project has data of about 140million records in Contentq table. And I have planned to rotate the table just incase not to face any load on the table.
So I have planned to rotate data older than 2 months to another table contentq_old and rename current latest 2 months data to contentq1 and create a structure only table contentq. As I have only updates/deletes for the  latest inserted records I can take that risk. Let the application to point to empty table contentq for its operation. As there were almost 80million records deleted I should be going for rebuild indexes on contentq1 that has latest 2 months records. Once rebuilt done lock table contentq and do -

"insert into contentq1 select * from contentq';" .

Then do drop contentq and rename contentq1 to contentq.

My problem is no down time for application hits, but I can park for somewhile. I should be knowing how long would the rebuild go on a latest 2months record that holds 50 million records max. Below is the structure of the table contentq. But how can I estimate how long an index rebuild would take?

Mysql 5.0.77 on 16GB RAM

 contentq | CREATE TABLE `contentq` (
 `idcontent` bigint(120) unsigned NOT NULL auto_increment,
 `keyvendor` varchar(32) default '0',
 `msisdn` varchar(16) default NULL,
 `cli` varchar(16) default NULL,
 `message` text,
 `udh` text,
 `priority` int(10) unsigned default '1',
 `recorddate` timestamp NOT NULL default CURRENT_TIMESTAMP,
 `callback_url` text,
 `transactionid` varchar(256) default NULL,
 `status` tinyint(4) default '0',
 PRIMARY KEY  (`idcontent`),
 KEY `PriorityIndex` (`status`,`priority`,`keyvendor`),
 KEY `NewIndex1` (`keyvendor`,`status`),
 KEY `idx_contentq_msisdn` (`msisdn`)
 ) ENGINE=InnoDB AUTO_INCREMENT=136583422 DEFAULT CHARSET=latin1

Please advise. Thanks!!

Partition on day or week.  That will give you the best possible way to delete old data (via DROP PARTITION).  There is not (unless you have 5.6) a way to move a partition from one table to another; that would be even nicer for "archiving".

The size of a table does not necessarily impact its performance.  It depends on the queries.  Queries that look just at "recent" data (at one end of the table) will probably perform nicely.  Queries that need to scan the entire table, or need to randomly fetch rows (either from the data or from an index) will get slower and slower as the table grows.  This is because of the table being so much bigger than can be cached in RAM.  "Count the disk hits."

Partition discussion:  http://mysql.rjweb.org/doc.php/partitionmaint

Deleting lots of rows without bogging down:  http://mysql.rjweb.org/doc.php/deletebig

Make sure your cache is the right size:  http://mysql.rjweb.org/doc.php/memory

ALTERing a huge table:  http://mysql.rjweb.org/doc.php/alterhuge

(Hehe... I guess your question hits more of my canned answers than any other question.)

InnoDB has a hard limit at 64TB (data+index) for table size.


What are Objective Business Reasons to Prefer SQL Server 2012 over 2008 R2? My company is facing the decision whether to purchase SQL Server 2012 Denali or SQL Server 2008 R2 for a new database server. I am looking for objective reasons to choose one over the other.

Our requirements:

Standard edition (for financial reasons and a lack of need for enterprise features)
OLTP workload (this means we don't need the new windowing functions and column store indexes)
Database size of 10-100 GB
No business intelligence features needed. Only the relational engine is required
Synchronous Database Mirroring

Currently, the following reasons are known to me:

SQL Server 2012 Denali

Newest version available

SQL Server 2008 R2

Proven technology

I can't seem to find a lot of technical reasons to prefer one over the other. Basically, it comes down to choosing proven technology that is running successfully vs. the newest and greatest version available.

What are objective reasons to make the decision?

Everybody's excited about AlwaysOn and ColumnStore, but many of the benefits of SQL Server 2012 are not exclusive to high-end editions. I don't want to sound like a spokesperson, but I've given plenty of presentations on SQL Server 2012 and I think it has a lot to offer at whatever edition suits you.

Partially Contained Databases which allow you to move databases between servers or environments with a few less shackles (namely server-level logins and server collation dependencies - future versions will handle thornier items like linked servers and Agent jobs).
Management Studio is now a much better tool, aligned with Visual Studio. IntelliSense is better and a whole bunch of other features make editing easier. Now of course you can have 2008 R2 on your server and use the 2012 version of SSMS, but I'm not sure how that works licensing-wise, and some shops don't want mixed versions (I prefer having the most recent tools on my workstation even to manage downlevel servers). I blogged about the changes early on, when there were still bugs, so please ignore the negatives since most or all are fixed as of RTM. I shudder now when I have to use an earlier version of SSMS.
Metadata enhancements allow you to inspect resultsets of objects and ad hoc queries, and also allow you to better shape the output of queries.
Custom Server Roles allow you to define a much more granular set of permissions for users at a role level instead of granting/revoking one by one, or just giving in to complexity and giving them sysadmin.
FileTable lets you manage a folder like a table of documents, but still have external control over the contents (so imagine being able to do this with T-SQL, and imagine how hard it would be to do in cmd or PowerShell: UPDATE C:\Docs\*.* SET ReadOnly = 1 WHERE Author = 'Bob' AND Created < '20100101';)... think FileStream meets WinFS and gets some usability to boot.
T-SQL Enhancements allow you to do many things that were a pain in previous versions:

THROW (think of it as re-raise)
OFFSET / FETCH (simpler, ANSI-standard paging - also see this post)
SEQUENCE (centralized IDENTITY mechanism, like in Oracle)
Windowing/Framing enhancements (various things here, such as awesome running totals performance)
IIF() / CHOOSE() / CONCAT() / EOMONTH()
Date/Time Constructors (e.g. DATEFROMPARTS) similar to DateSerial in VB
PARSE() / FORMAT() - like their .NET counterparts (but see this post about the latter)
TRY_CONVERT() / TRY_PARSE() - return NULL if CONVERT / PARSE fail

Extended Events has an enhanced UI for configuration / viewing, and finally completely covers trace / audit functionality (including much better causality tracking).
Lots of new DMVs, system procedures and ShowPlan enhancements for diagnostics and performance troubleshooting. Also take a look at what CSS is calling "The Black Box Recorder."
Server Core allows you to run on a bare-minimum server without all the UI components (smaller surface area means it is more secure, and reduced maintenance since less parts of OS are subject to Windows Update).
Full-Text Search gets some important underlying performance enhancements, as well as semantic search (think keywords) and customizable proximity / NEAR.
AWE is no longer supported, meaning your SQL Server instance on x86 with 32GB of RAM is only going to be able to use 4GB - so you will finally have motivation to get off your old 32-bit hardware.


MySQL - Query a date range I have an orders table. Imagine it having only 2 columns, id and created_at.

Imagine I have a data in my orders table that looks something like this:

id | created_at
01 | 2018-09-29
02 | 2018-09-29
03 | 2018-09-30

I want to select all orders within that day.

I can select the records you by using the following query.

SELECT created_at AS date, COUNT(id) AS result
FROM orders
GROUP BY created_at
ORDER BY date

This will return:

date | result
2018-09-29 | 2
2018-09-30 | 1

I want to expand the query above to generate the following result instead: (daterange)

date | result
2018-09-15 | 0
2018-09-16 | 0
...
2018-09-29 | 2
2018-09-30 | 1

Is this possible? To select a data that is non-existent to generate a date range report?

You wanted the result must be like this:

 date       | result
2018-09-01   |    0
2018-09-02   |    0
2018-09-03   |    0
2018-09-04   |    0
....
2018-09-28   |    0
2018-09-29   |    2
2018-09-28   |    1

You need to create a table or temporary table for number of days

id
1
2
3
4
...
28
29
30
31

And try to query this:

SELECT
    DATE((select CONCAT(YEAR(CURDATE()), '-', MONTH(CURDATE()),'-' ,day))) as Date1,
        COUNT(o.order_id)
        as counting from orders as o
        right join numberofdays d
        on d.day = DAY(o.created_at)
        GROUP BY Date1
        ORDER BY Date1

I used RIGHT JOIN:

Hope this might help. Thanks.


Range Partitioning Oracle I need to understand how partitioning can be applied to tables for an oracle 11.2.0.4.0 database.
Please start a guide with me that hopefully with helps others who would like to learn as well.  After the go live , the production database will grow very quickly. Currently a DBA on client side is partitioning the tables on the fly when it is needed and I am not sure what scripts or logic he is using.  I will have to take over this responsibility soon.

My Job/Task/Goal:
Each important table has specifications for the type of partitioning that will need to be implemented to handle the data. Which I will be applying.

For example using the following parameters to partition a table:

Table: Clogs
Schema: Schema name
Range Partition on ISSUEDATE ( MAJORITY of the table will need range partitionin)
Group partition by daily.
Partition Naming Convention : clogmd_YYYYMMDD

Note: I have no idea where to start. I have a 5 years of IT and Systems Admin experience but I am not an expert but I am not a complete newbie either. I have paid my dues so please take it easy on me.

Some of the code I am working with is.

ALTER TABLE tableowner.tablename drop partition
partitionname_YYYYMMDD (respective partitionname)

ALTER TABLE tableowner.tablename split partition
maxpartitionname_YYYYMMmax at (to_date('YYYY-MM-DD
00:00:00','SYYYY-MM-DDHH24:MI:SS'))  into (partition NAME_YYYYMMDD
tablespace name_WXY1, partition NAME_201602MAX tablespace NAME_WXY2;

ALTER TABLE tableowner.tablename add partition NAME_YYYYMMDD
(respective PARTITIONNAME);

Please guide me and let me know of any considerations I should be taking. I greatly appreciate any help.

I can give you a few things to watch out for, but I'm not sure what level of detail that you're looking for.

Merging and splitting partitions can be an expensive operation in Oracle, except for a few special cases which are optimized, such as splitting off an empty partition. For example, if you have a single partition of 12 months and you split off a month then Oracle will need to rewrite the entire 12 months worth of data to complete the operation. If you want to split off another month then Oracle will need to rewrite 11 months worth of data to complete the operation, and so on. In Oracle 12c you have the option to split and merge N partitions in a single operation but I do not believe this is available in Oracle 11g. Because of this it can often be faster in 11g to just create a new table with the partitioning structure that you want and to load all data into that new table. Once the operation is done you can drop the old table and rename the new table.

You said that you have specifications for the partitioning. Do those specifications include the tablespaces for the partitions? Are there any existing indexes on the tables to be partitioned? If so, should those be defined as global or local indexes? Do you need to apply any kind of compression to the partitions? Perhaps you are using advanced compression for all compressions or basic compression for old partitions which will no longer have data modified in them. If all of that is documented, great. If not, you might need to do some analysis and make some decisions.

Depending on your requirements you may be able to use interval partitioning. With interval partitioning you can define your starting partitions upfront and also the size of the partitions that you want going forward as new data is loaded into the table. So if you want your table partitioned by a date column into single day partitions you don't need to write code to get a new partition for each day or to script it out. That can be done for you automatically by Oracle.

Here is an example of interval partitioning from one of the links below:

CREATE TABLE interval_sales
   ( prod_id        NUMBER(6)
   , cust_id        NUMBER
   , time_id        DATE
   , channel_id     CHAR(1)
   , promo_id       NUMBER(6)
   , quantity_sold  NUMBER(3)
   , amount_sold    NUMBER(10,2)
   )
 PARTITION BY RANGE (time_id)
 INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
   ( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2007', 'DD-MM-YYYY')),
     PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2008', 'DD-MM-YYYY')),
     PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2009', 'DD-MM-YYYY')),
     PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2010', 'DD-MM-YYYY')) );

In that example query Oracle will automatically create monthly partitions for any data after TO_DATE('1-1-2010', 'DD-MM-YYYY'). Data before TO_DATE('1-1-2010', 'DD-MM-YYYY') will be loaded into one of the non-monthly partitions p0, p1, p2, or p3.

If you want examples of how to apply range partitioning or interval partitioning you could start here:

https://oracle-base.com/articles/11g/partitioning-enhancements-11gr1
https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#BAJHFFBE
https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#insertedID1

I know that you aren't supposed to just provide links without summarizing information from them, but there's a lot of content in those links and I'm not quite sure what you're looking for.


Database: Storing multiple Types in single table or multiple intermediate delta tables Using Java and Oracle.

We need to update changes in Email, UserID of employee to third party.
Actual table is Employee and intermediate table we keep which we will use for comparison of changes before sending to third party.

Following are database designs coming in mind for intermediate table:

Only Single table:

EmployeeiD|Value|Type|UpdateDate

Value is userid or email, type will be 'email' or 'userid'. Update date is kept so to figure out that which of email or userid was different and update to third party.

Multiple Table:

 Employee_EmailID
      EmpId|EmailID|Updatedate

 Employee_UserID
      EmpId|UserID|Updatedate

Java flow will be:
 - Pick employee from actual table.
 - Pick employee from above intermediate table.
 - Compare differences. Update difference to third party.
 - Update above table with updated value and last update date.

Which one is consider as best way, single table approach or multiple table or is there any standard way to implement the same? There are 10,000 Employees in system.
Also consider approach when 10 intermediate tables are required like phonenumber, salary etc.

For  the employee table you should have two audit tables.

  1 employee_audit (employee_audit_id , ref_employee_id, reason,update_date, update_by)

and

  2 employee_audit_detail (id,ref_employee_audit_id , field_name, old_value)

So you have every value saved only once and you have a complete record,what was changed and by whom grouped the date it was change.

Programming is every time you save data for an employee , you save first a new row in employee_audit  get the last id and then enter a new row for every changed value the name of the value and old  content


How to know the Database user Login History from begining? I need to know one particular user's login and logout database history from beginning in SQL SERVER 2005.
Can anyone help?

No, SQL Server does not track successful logins by default. And even if you turn that option, it'll only keep it back through the current log + 6 previous (again, by default), and those logs will be bloated and full of noise. You may wish to implement a logon trigger going forward, but going back in time, this information is simply not available.


mongoDB: querying for a document that has an object reference I have a widgets collection that has records like this:

{ "_id" : ObjectId("55a6abe193819c033d4d755a"), "name" : "widget1"}
{ "_id" : ObjectId("55a6abe193819c033d4d755a"), "name" : "widget2", "loc" : ObjectId("55a69475da314d9984fc6201") }

I also have a locations collection that looks like this:

{ "_id" : ObjectId("55a69475da314d9984fc6201"), "abbrev" : "CAN", "description" : "Canada" }
{ "_id" : ObjectId("55a6948eda314d9984fc6202"), "abbrev" : "USA", "description" : "United States" }
{ "_id" : ObjectId("55a69496da314d9984fc6203"), "abbrev" : "MEX", "description" : "Mexico" }

If I want to find all widgets that are located in Canada, using the Objectid, I know I can do this:

> db.widgets.find( { loc: ObjectId("55a69475da314d9984fc6201")})
{ "_id" : ObjectId("55a6abe193819c033d4d755a"), "name" : "widget2", "loc" : ObjectId("55a69475da314d9984fc6201") }

But what if I want to search for locations not using the objectid but the abbreviation or the description?  How would I do that?

To query two collections with related data the common approach (as at MongoDB 3.0) is to find a list of matching _id values from the first collection which can used with a $in query on the second.

Here's an example using the mongo shell:

// Find _ids for matching locations based on some criteria (eg. "abbrev")
var matches = db.locations.find({ "abbrev": "CAN"}, { _id: 1 })

// Transform matching _id values into an array
var locs = [];
matches.forEach(function(match) { locs.push(match._id) });

// Look for widgets matching the location criteria
db.widgets.find({"loc": {$in : locs} } )

Depending on the driver and/or framework you are using, there may be helper functions to make it easier to populate related data. These helper functions will ultimately result in multiple queries as there is currently no server-side support for joins.

Some examples of frameworks with helpers (not intended to be a comprehensive list):

MongooseJS: query population: Node.js Object-Document Model (ODM) framework including declarative schema & query population for relationships
Mongoid: relations: Ruby ODM including declarative schema & query population for relationships
mongo-views: experimental add-in for the mongo interactive shell adding support for queryable views / virtual collections

You may note that I've said there is currently no server-side support for populating data. There is actually a new $lookup operator being added to the Aggregation Framework in the MongoDB 3.1.x development cycle (which will culminate in the eventual production release series for MongoDB 3.2). For more information see: SERVER-19095 in the MongoDB Jira issue tracker.


Oracle to Postgres Statement Migration I'm trying to port over some statements from Oracle to Postgres.
One specific one that I have a hard time even understanding on the Oracle side is this:
rating_scheme_oid NUMBER;
rs_cnt number;

From what I can understand it seems like these are just defining variables, so I figured I could translate that to this:
CREATE TYPE rating_scheme_oid NUMERIC;
CREATE TYPE rs_cnt NUMERIC;

However this doesn't seem to work:
ERROR: syntax error at or near "NUMERIC"
If anyone with expertise in this area could describe what these are doing and a possible solution, that'd be great.
Here's the full context (this is the working Oracle statement):
        DECLARE
        CURSOR update_cursor
        IS
        SELECT oid, subscription_id, configuration_oid
        FROM scope
        WHERE subscription_id != 1 and subclass_type = 'W' and configuration_oid is not null;

        TYPE type
        IS
        TABLE OF update_cursor%ROWTYPE;
        update_array type;

        rating_scheme_oid NUMBER;
        rs_cnt number;

        BEGIN
        OPEN update_cursor;
        LOOP
        FETCH update_cursor BULK COLLECT INTO update_array LIMIT 1000;
        FOR i IN 1 .. update_array.COUNT LOOP

        BEGIN
        select count(*) into rs_cnt from workspace_config where workspace_oid = update_array(i).oid and OBJECTIVE_STATE_SCHEME_OID is null;
        IF rs_cnt = 1
        THEN

        SELECT OID_SEQ.nextval into rating_scheme_oid from DUAL;

        INSERT INTO RATING_SCHEME (OID, SUBSCRIPTION_ID, VERSION, NAME, CREATION_DATE, WORKSPACE_OID, UUID)
        VALUES (rating_scheme_oid, update_array(i).subscription_id, 1, null, SYSDATE, update_array(i).oid, sys_guid());

        UPDATE WORKSPACE_CONFIG set OBJECTIVE_STATE_SCHEME_OID = rating_scheme_oid where oid = update_array(i).configuration_oid;

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Defined', 0, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Committed', 1, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'In-Progress', 2, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Measuring', 3, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Achieved', 4, sys_guid());

        INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
        VALUES (OID_SEQ.nextval, update_array(i).subscription_id, update_array(i).oid, 1, SYSDATE, rating_scheme_oid, 'Closed', 5, sys_guid());

        END IF;

        END;
        END LOOP;
        COMMIT;
        EXIT WHEN update_cursor%NOTFOUND;
        END LOOP;
        CLOSE update_cursor;
        END;

A direct replacement of the code to PL/pgSQL would be something like this.
The following is (obviously not) tested and may contain typos, so you need to work out the details from here.  But it should get you started.
DECLARE
  l_oid_array int[];
  l_subscription_id_array int[];
  l_config_oid_array int[];

  l_rating_scheme_oid int;
  l_cnt bigint;
BEGIN
  SELECT array_agg(oid order by oid),
         array_agg(subscription_id order by oid),
         array_agg(configuration_oid order by oid)
     into l_oid_array, l_subscription_id_array, l_config_oid_array
  FROM scope
  WHERE subscription_id <> 1
    and subclass_type = 'W'
    and configuration_oid is not null;

  FOR i IN 1 .. cardinality(l_oid_array) LOOP

    select count(*) into l_cnt
    from workspace_config
    where workspace_oid = workspace_oid = l_oid_array[i]
      and OBJECTIVE_STATE_SCHEME_OID is null;

    IF l_cnt = 1 THEN
    THEN

      l_rating_scheme_oid := nextval('oid_seq');

      INSERT INTO RATING_SCHEME (OID, SUBSCRIPTION_ID, VERSION, NAME, CREATION_DATE, WORKSPACE_OID, UUID)
      VALUES (rating_scheme_oid, l_subscription_id_array[i], 1, null, current_date, l_oid_array[i], uuid_generate_v4());

      UPDATE WORKSPACE_CONFIG set OBJECTIVE_STATE_SCHEME_OID = rating_scheme_oid where oid = l_config_oid_array[i];

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Defined', 0, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Committed', 1, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'In-Progress', 2, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Measuring', 3, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Achieved', 4, uuid_generate_v4());

      INSERT INTO RATING (OID, SUBSCRIPTION_ID, WORKSPACE_OID, VERSION, CREATION_DATE, RATING_SCHEME_OID, NAME, ORDINAL_VALUE, UUID)
      VALUES (nextval('oid_seq'), l_subscription_id_array[i], l_oid_array[i], 1, current_date, l_rating_scheme_oid, 'Closed', 5, uuid_generate_v4());
    END IF;

  END LOOP;

END;

To get the uuid_generate_v4() function you need to install the uuid-ossp extension
Doing things in loops like that is usually the least efficient way (in Oracle and Postgres). More often than not, a simple insert into select ... is a lot more efficient


select * from Table1 t
where not exists(
  select 1 from Table1 p where p.FD = t.FD
      and p.FA != t.FA and p.FA = 1
);

Or as a LEFT JOIN (might be better optimized on 5.5)

select t.* from Table1 t
left join Table1 p on p.FD = t.FD
      and p.FA != t.FA and p.FA = 1
where p.`Key` is null;

http://sqlfiddle.com/#!9/39664/4


You write sea* here and Sea* there.  This bug report (which turned out not to report a bug, but a (for me at least) cryptic feature) can be useful for you. (I found the link by searching for case sensitivity in full text search.)


Reorder missing numbers in a sequencein two different tables using MySQL This is an extended question of reorder/ fill missing value in a sequence

I have a table "number" having a column "n" which is of type int. I inserted some values like,

n
--
1
3
5
9
11

i also have another table "num" having column "n" which is of type int. I inserted some values like,

n
--
7
12
15
23

Now I want the column n of "number" table as 1,2,3,5,6 and column n of "num" table as 4,7,8,9. That is 1 should be as 1, 3 as 2, 5 as 3, 7 from "num" table as 4 and 9 as 5 and so on. The values of column n should be reordered with the missing values on both the tables.

drop procedure IF EXISTS ALTERTABLEVALUES;
delimiter $$
CREATE PROCEDURE ALTERTABLEVALUES()
BEGIN
DECLARE finished INT DEFAULT 1;
DECLARE total_count INT DEFAULT 1;
DECLARE val varchar(5);
DECLARE i INT DEFAULT 1;
DECLARE table_num INT;
DECLARE table_tab varchar(5);
DECLARE number_cursor CURSOR FOR select num,tab from tempnumbers;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 0;

drop temporary table if exists tempnumbers;
CREATE TEMPORARY TABLE tempnumbers as
select num,'temp' as tab from numbers union select num,'audit' as tab from numbers1 order by num;
SET val = (select num from tempnumbers limit 1);
SET total_count = (select count(*) from tempnumbers);

OPEN number_cursor;
get_values: LOOP
FETCH number_cursor INTO table_num,table_tab;
IF finished = 0 THEN
 LEAVE get_values;
 END IF;
IF table_tab = 'temp' THEN
    update numbers set numbers.num = val where numbers.num = table_num;
ELSE
    update numbers1 set numbers1.num = val where numbers1.num = table_num;
END IF;
SET val = val +1;
END LOOP get_values;
CLOSE number_cursor;
DROP TEMPORARY TABLE tempnumbers;
END;
$$
delimiter ;

After running the procedure I got numbers table as
n
--
1
2
3
5
6

and numbers1 table as

n
--
4
7
8
9

This is how i expected for the result.


TempDB Log File Growth - questions Problem:
On one of the production boxes, TempDB transaction LOG file grew unexpectedly, filling the drive
It was 32 GB but now it is 240 GB
Questions:

What is the mechanism of TempDB log file usage ?

TempDB is in SIMPLE recovery model, what can delay TempDB log file truncation
(and cause its growth as a result) ?

How can I know or be alerted when TempDB data files or log file are growing ?

Is there an Extended Event for this, or other means to know, and can alert be sent by email when growth happens ?

Performance Monitor metrics

Log file may be 240 GB, but it is really empty at the moment
Is there a metric that can be used to monitor amount of used space INSIDE the TempDB data/log files at any given point in time ?
I can see there are Active Temp Tables, Temp Tables Creation Rate, Worktables Created/sec, etc. but these do not show exactly what I would like to see

Drive space monitoring

Is there native tool in SQL Server or Windows, that can alert me, when the free space on the TempDB drive reaches below 20%, 10% ?

How to find out historically which session or task filled the TempDB data or log file ?

I can see which user/system sessions CURRENTLY consume at TempDB by querying this: sys.dm_db_session_space_usage. But how can I know, which user/task consumed large portion of TempDB, say, 2 hours ago, without having to sit and query that view ?

sys.dm_db_session_space_usage

user_objects_alloc_page_count - (minus) user_objects_dealloc_page_count = the difference will be what session is CURRENTLY consuming (in pages) in TempDB ? Or it does not work that way ?
If you know at least few of those questions, your help is much appreciated

Why might the tempdb log grow
The usual suspect for delaying tempdb log file truncation is a long-running transaction.  You can run a query like this, while the problem is occurring, to see why the log can't be truncated:
SELECT log_reuse_wait, log_reuse_wait_desc
FROM sys.databases d
WHERE database_id = 2;

The reasons are described on this MS docs page.
Monitoring and alerting
Questions 2 - 5 can all be solved by buying a dedicated monitoring tool (like Sentry One or SolarWinds, etc).
You can certainly accomplish all of these things with "home grown" methods, but they will inevitably have gaps and failures that the monitoring tool vendors have had years to think of and address.
For instance, you can use Extended Events (XE) to monitor for file growth events - Aaron Bertrand has a post about doing that with the sqlserver.database_file_size_change event here: Removing the default trace – Part 2.
To alert on file growths, you could have a SQL Agent job that queries this event data (either directly, or through Aaron's views) periodically, like every 1 / 5 / 15 minutes, and then emails you if there have been any in that time frame.
Getting the space used inside data and log files, as well as disk space used / available, is discussed in a different Q&A on this site, so I won't duplicate it here: Query to report disk space allocation and used space
Again, the low-tech way of alerting on this would likely be through an Agent job.
SQL Server doesn't keep a log of who or what was using tempdb at a given time - you'd have to write DMV query results to a table, check them periodically, etc.
Current tempdb usage
This is included in the results of sp_WhoIsActive.  You might consider using that directly, or at least basing the calculation on the relevant part of that proc.
You'll need to add together user_objects_alloc_page_count and internal_objects_alloc_page_count (which includes LOB variables and spills), then subtract to the dealloc version of both for the "current" state of things.
You'll note that sp_WhoIsActive also includes the data from dm_db_task_space_usage for this, as some allocations will show up there and not in the other view.  You can see a detailed example on my blog, where a LOB parameter being streamed from a .NET application will show up in this view: Invisible tempdb Killer


Why is my query suddenly slower than it was yesterday? [Salutations]

(check one)

[ ] Well trained professional, [ ] Casual reader, [ ] Hapless wanderer,

I have a (check all that apply)

[ ] query [ ] stored procedure [ ] database thing maybe

that was running fine (if applicable)

[ ] yesterday [ ] in recent memory [ ] at some point

but is suddenly slower now.

I've already checked to make sure it's not being blocked, and that it's not the victim of some long running maintenance task, report, or other out of band process.

What is the problem, what should I do, and what information can I provide to get some help?

[*Insert appropriate closing remarks*]

Dear [your name here]!

Oh no, I'm sorry to hear that! Let's start with some basics to get you fixed up in a jiffy.

The thing you're running into is called Parameter Sniffing

It's a way out wiggy weird problem. The name rolls right off the tongue. Like the German word for squirrel.

And it's usually your friend.

When a query hits your server, a plan has to be compiled. To save time and resources later, an execution plan is cached based on the estimated rows that parameter will cause your code to process and return.

The easiest way to picture this going bad is to imagine a stored procedure that needs to count things from two lopsided populations.

For example:

People wearing CrossFit shirts who aren't injured: Zero
People wearing CrossFit shirts who wince when they wince: All

Obviously, one execution of that code would have to do a lot more work than another, and the query plans you'd want to do totally different amounts of work would look totally different.

What am I up against?

This is a genuinely difficult problem to find, test, and fix.

It's hard to find because it doesn't happen consistently
It's hard to test because you need to know which parameters cause different plans
It's hard to fix because sometimes it requires query and index tuning
It's hard to fix because you may not be able to change queries or indexes
It's hard to fix because even if you change queries or indexes, it might still come back

Quick Fixes

Sometimes, all you need is a little clarity. Or rather, your plan cache does.

If it's a stored procedure

Try running EXEC sys.sp_recompile @objname = N'schema.procname'. That'll cause the procedure to recompile a new plan the next time it runs.

What this won't fix:

Processes currently running it.

What this doesn't guarantee:

The next process that runs after recompiling will use a parameter that gives you a good plan.

You can also point sp_recompile at a table or view, but be forewarned that all code that touches that table or view will recompile. This could make the problem a whole lot harder.

If it's a parameterized query

Your job is a little more difficult. You'll need to track down the SQL Handle. You don't want to free the entire plan cache -- just like using sp_recompile against a table or view, you could trigger (ha ha ha) a whole bunch of unintended consequences.

The easiest way to figure that command out is to run sp_BlitzWho*! There's a column called "fix parameter sniffing" that has a command to remove a single plan from the cache. This has the same drawbacks as recompile, though.

What this won't fix:

Processes currently running it.

What this doesn't guarantee:

The next process that runs after recompiling will use a parameter that gives you a good plan.

I still need help!

We're going to need the following things:

The good query plan, if possible
The bad query plan
The parameters used
The query in question
Table and index definitions

Getting the query plans and query

If the query is running, you can use sp_BlitzWho* or sp_WhoIsActive to capture currently executing queries.

EXEC sp_BlitzWho;

EXEC sp_WhoIsActive @get_plans = 1;

If the query isn't currently executing, you can check for it in the plan cache, using sp_BlitzCache*.

If you're on SQL Server 2016+, and have Query Store turned on, you can use sp_BlitzQueryStore*.

EXEC dbo.sp_BlitzCache @StoredProcName = 'Your Mom';

EXEC dbo.sp_BlitzQueryStore @StoredProcName = 'Your Mom';

These will help you track down cached version(s) of your Stored Procedure. If it's just parameterized code, your search is a little more difficult. This may help, though:

EXEC dbo.sp_BlitzCache @QueryFilter = 'statement';

You should see fairly similar output from any of those. Again, the query plan inviting cool blue clicky column is your friend.

The easiest way to share plans is using Paste The Plan*, or dumping the XML into pastebin. To get that, click on either one of those inviting blue clicky columns. Your query plan should appear in a new SSMS tab.

If you're touchy about sharing your company's code and query, you can use Sentry One's free Plan Explorer tool to anonymize your plan. Keep in mind, this makes getting help harder -- anonymized code is a lot harder to read and figure out.

All of these tools we talked about should return the Query Text. You don't need to do anything else here.

Getting the parameter(s) is a little more difficult. If you're using Plan Explorer, there's a tab at the bottom that lists them all out for you.

If you're using sp_BlitzCache*, there's a clickable column which gives you the execution statement for stored procedures.

Getting the table and index definitions

You can easily right click in SSMS to script things out.

If you want to get everything in one shot, sp_BlitzIndex* can help if you point it directly at a table.

EXEC dbo.sp_BlitzIndex @DatabaseName = 'StackOverflow2010',
                       @SchemaName = 'dbo',
                       @TableName = 'Users';

This will give you the table definition (though not as a create statement), and create statements for all your indexes.

Collecting and adding this information to your question should get people enough information to help, or point you in the right direction.

I wanna do it myself!

Well, cool. I'm happy for you. You crazy person.

There are a lot of ways people think they "fix" parameter sniffing:

Recompile hints
Optimize for unknown
Optimize for a value

But these really just disable parameter sniffing in different ways. That's not to say they can't solve the problem, they just don't really get to the root cause.

That's because getting to the root cause is usually kind of difficult. You have to look for those pesky "plan quality issues".

Starting with the fast vs slow plans, look for differences like:

Indexes used
Join order
Serial vs Parallel

Also look for different operators that make your code sensitive to parameter sniffing:

Lookups
Sorts
Join type
Memory grants (and by extension, spills)
Spools

Don't get too wrapped up in seek vs scan, index fragmentation, or any of the cargo cult-y stuff people hem and haw about.

Usually, there's a pretty basic indexing problem. Sometimes the code needs a little rewriting.

If you want to learn more about parameter sniffing:

Slow in the Application, Fast in SSMS? - Erland Sommarskog
Troubleshooting Parameter Sniffing 1, 2, 3 - Tara Kizer
Why You’re Tuning Stored Procedures Wrong (the Problem with Local Variables) - Kendra Little
How to Use Parameters Like a Pro and Boost Performance - Guy Glantser
Parameter Sniffing, Embedding, and the RECOMPILE Options - Paul White

If you're reading this, and you think I missed a link or helpful tool, leave a comment. I'll do my best to keep this up to date.


Database Design where there's three parent tables and only 1 child table Below is a simplified version of my current tables. Each test type is a type of test (eg: one table is for different appearance tests, ie substance is yellow and sticky, or substance is green and dry, where as another table might be for microbial counts in food products).

A result set is just a group of results for each test. So a test can be tested 5 times in one batch, so that would be a result set for a test that has 5 results.

What I really want is just one result set table and one results table as right now it's overly complicated and there's duplication. How do I do that when there's three different types of tests? Ultimate the results can be boiled down to a numeric value OR a pass/fail value.

I could have three columns in the result set table (Type1TestId, Type2TestId, Type3TestId) and populate whichever the result set belongs to but that seems ugly. I could have a new parent table called Tests and from there have a link to the TestType tables and each Test in TblTest would link to one of the three test type tables?

All my solutions seem ugly, so I'm asking for help.

I could do something like this?

Your ResultsForType* and ResultSetFoType* tables are the same, there is no difference between them. Your problem is only about the Type?Tests tables.

Your problem is very common: how to map class inheritance and polimorphism to the ORM level. There are two most common practices to solve it:

What you are currently doing. You could make it redundency-free, on the cost of a much more complicated table structure. In exchange, your database will be pure and perfect, although a little bit overkill.
You can unify the Type?Tests tables, latter I explain, how.
Such a solution, where you get everything without losing anything, does not exist.

The sad truth is that SQL does not know polyporhism, and this is the ultimate cause of your current troubles. But it knows something which can help a lot to achieve some quite similar.

It can nullable values.

In your case, I would simply use instead of the Type?Tests tables this:

+----+------------+-----+-----+------------+------------+
| id | TestTypeId | min | max | daysStored | NoMoreThan |
+----+------------+-----+-----+------------+------------+

The essence is that we use a union of all possible columns in all the used classes, plus yet another column to identify the type.

Use the following constraints:

TestTypeId should be 1, 2 or 3
If TestTypeId=1, daysStored and NoMoreThan should be NULL
If TestTypeId=2, NoMoreThan should be NULL
If TestTypeId=3, daysStored should be NULL

Constraints are very useful - they make things guaranteed.


pgpool connect failed: No such file /tmp.s.PGSQL.0 replication mode = true Setup: I have two instances of Postgres 9.0.3 and pg_pool 3.14 all running on the same Ibuntu (10.04) machine and am attempting to evaluate pgpool.

I can attach to pgpool using psql -p <pgpool port> but only when the pgpool.conf file has replication mode = false. If I set the replication mode = true then I see the log file generate the connection failure error on /tmp.s.PGSQL.0 when I attempt the psql connection to pgpool's port.

After more exploration I would note that the additional error messages in the log indicate a failover handler is called, starting degeneration and shutdown secondary host(0) ????

After this if I do not restart pgool I can successfully connect (with replication mode = true) but I do not see any load sharing of my selects between my servers. (I have the load_share variable also set to true.)

What I am attempting to do is have have my insert/update statements replicated across my pool of servers and my selects to be load shared. Seem to be following the suggested setup but I encounter the above error problem.

Comments?

AS per my comment this ended up being a misunderstanding between pgppool and pgpool-II being separate open source projects.  When correctly using pgpoolII and it's documentation this error was not a problem.


Installing an older CU over a Security Patch? According to this Microsoft link, SQL Server 12.0.6372.1 requires any system with at least SQL 2014 SP3, so my predecessor installed it over SP3.  Now the version is "2014 (SP3-CU-GDR) (KB4535288) - 12.0.6372.1".
Since that security patch only fixes a specific problem with Reporting Services, and is smaller than the CU4 package, ISTM that I should also install the CU4 patch.
But how?  Do I first uninstall KB4535288, then install KB4500181 (CU4) and then reinstall KB4535288, or can I just slap KB4500181 on top of KB4535288 (which seems like it would overwrite the KB4535288 files.

#TL;DR
There is nothing you currently need to do to get the fixes included in CU4. They're already there, even though @@VERSION incorrectly suggests you have a GDR (and, as you've read elsewhere, GDRs do not contain CU fixes). If you want more background, please read on.
#Background
Patches are cumulative, including security updates, which means they include all previous patches in that branch. The size of the (compressed executable) file is not a very reliable way to determine whether or not it includes an earlier patch.
To be clear, each SQL Server release (including each service pack before SQL Server 2017) is what I'll call a "main branch." Once that main branch is released, they later need to enhancements, usually in categories of performance, stability, or security. They release these fixes in the form of GDR and CU updates. These become two separate branches off the main branch:

The CU branch contains all fixes - performance, stability, security. Currently the cadence for these is one a month for the first year of a main branch, and then every other month after that (until that branch is retired, which is longer now than it used to be because their are no service packs making previous service packs obsolete). Once you're on the CU branch, as you apply more updates, you will always be getting all the CU fixes. If you apply CU1 and then CU4, you have all the fixes from CU1, CU2, CU3, and CU4. If you apply CU5 and then CU12, you have all the fixes from CU1, CU2, ..., CU11, CU12.
Sometimes a security release is published out-of-band (meaning not as part of the monthly/bi-monthly CU cadence). This is usually either because the issue is critical or because it needs to be coordinated with a security bulletin that involves other products (like Windows). Like a CU, security updates that apply to a CU branch contain all the fixes from the CU branch up to that point.
The GDR branch only includes critical fixes, usually security-related, for the main branch (e.g. Service Pack 3). That is the branch I was talking about in the link referenced below: I meant that the GDR update for SP3 does not contain any of the CU updates (the post says "critical updates" but I don't have editorial control to fix it).
The point of the GDR branch is to provide as little surface area changes as possible (because they have to test the impact of all those changes), while still keeping customers safe. Each successive GDR update is just a new build on the GDR branch and so, like CUs and security updates on CU branches, a security update for the GDR branch also contains all the other GDR updates that came before it.
Except for occasionally early in a release, there are more CU updates than GDR updates, primarily because they're restricted to critical fixes and there are just a lot more non-critical fixes over time.

In your case, the (understandable) confusion comes from the fact that @@VERSION returns both CU and GDR in the name, which is nonsensical. When the patch was first released, I complained about this confusion, and after a lengthy debate, they fixed the terminology in the KB articles and other documentation. But it was too late to fix what @@VERSION returns on a build of the engine that was already distributed (and that problem remains today because that was the last update on the CU branch).
That's a lot of background, but to summarize, I can assure you that:

The build you have installed is on the CU branch and is not a GDR update, in spite of what @@VERSION might be telling you. You need to ignore what that string says because it is wrong but there is no way to fix it.
You definitely cannot apply CU4 on top of KB4535288. The installer will recognize that your version already includes all the fixes in CU4.
You do not need to uninstall the security update (which will put you back on whatever build you were on before you applied it), apply CU4 (which will put you on 12.0.6329.1), and reapply the security update (which will put you right back on 12.0.6372.1). You already have those fixes.
You can choose to not believe me and go ahead and do all of this, I'm just trying to help you avoid work that serves zero purpose, since you asked the question. Again, read everything here, but try to forget that your @@VERSION incorrectly suggests you are on a GDR build. You're not.

I had previously said the following, and don't believe I have contradicted myself with anything I've said here:
You can see the list of SQL Server 2014 updates here.
In this case, there is nothing you can possibly apply to SQL Server 2014 that will add anything to 12.0.6372.1. Literally nothing. Uninstalling KB4535288 to install CU4 just means you will be removing the security fix in KB4535288. I see absolutely no reason to do this. And no, you can't just "slap CU4 on top of KB4535288" - well, you can try, but you won't get very far, because the installer will recognize you already have everything in CU4.


How do I encrypt data to be decrypted on a linked server? SQL I have been searching for 2 days for the solution to this, but every solution does not apply to my situation.

I have 2 SQL servers: DataServer and InterfaceServer set up as linked servers. DataServer does all of our data processing. InterfaceServer pulls only the data it needs from DataServer. Each server has its own encryption process and our compliance department does not want them to use the same passwords/certficates/etc... Due to these restrictions, I am unable to execute a stored procedure that does the decryption for me.

I have tried creating views with the DecrypByKeyAutoCert() function as well as creating views with SELECT ... FROM OPENQUERY() to execute the stored procedure that does the decryption, but they always return NULL values for the decrypted data. If I run them on the DataServer itself, it returns the data fine.

Is there a way to create an assymmetric key on InterfaceServer, use the public key to encrypt data in a table on DataServer, then decrypt it on the InterfaceServer after copying everything over?

It's possible to do what you are suggesting. You can't create the same asymmetric key directly using parameters, but you can backup the database containing the asymmetric key and backup the database master key on the data server. Restore them on the Interface server and drop the private key on the data server using ALTER ASYMMETRIC KEY remove private key. That way the data server can only encrypt and the Interface server can decrypt using the same asymmetric key.

Or, you can create a symmetric key encrypted by the asymmetric key using the same key_source and identity_value on each server and transfer the data in it's encrypted stated, then decrypt it on the Interface server. It does not matter, in the latter case, if the asymmetric keys are different because each protects keys that are identical on each server. If your goal is to encrypt data in transit, then the second one will be easier to recover if there are any issues.


row-based replication with slave triggers I'm replicating a prod MySQL database to an analytics one.  I need to drop several tables with sensitive data from the analytics and also redact several columns of several remaining tables, replacing NULLable fields with NULLs.  How can I set up triggers on the slave to achieve this?

You can't.
Triggers do not fire on the slave in row-based replication, even if they are defined, because the rows in the other tables affected by the triggers on the master are replicated as row events, and if the triggers were allowed to fire on the slave, that would not work.

“With row-based replication, triggers executed on the master do not execute on the slave. Instead, the row changes on the master resulting from trigger execution are replicated and applied on the slave.”
— http://dev.mysql.com/doc/refman/5.6/en/replication-features-triggers.html

If you have entire tables that need to be eliminated on the slave, you can alter those tables to use the BLACKHOLE storage engine, which happily accepts the insert queries from replication and then discards the data, returning no data when you SELECT from them... though this requires some attention to detail down the road since an ALTER TABLE on the master that specifies a storage engine will alter the table on the slave as well.
If you are in a position to rearrange column order on tables, MySQL supports the slave having fewer columns than the master in row-based mode, as long as the columns they do have are all identical by definition and ordinal position in the table, as I discussed here.  If moving the sensitive columns to the right side of the table is practical, you can then ALTER TABLE ... DROP COLUMN ... on the slave.
But this also requires attention down the road.
Probably the simplest solution is to use permissions on views that you essentially create for the purpose of enforcing the access rights to the underlying tables.  Views, by default, are executed with the permissions of the user who defined them, not the user who invokes them... so a view can allow a user to access columns in a table that aren't otherwise accessible to that user, if the table and columns are accessible to the DEFINER user.

“...views can have a SQL SECURITY characteristic with a value of DEFINER or INVOKER to specify whether the object executes in definer or invoker context. If the SQL SECURITY characteristic is omitted, the default is definer context.”
“A stored program or view that executes in definer security context executes with the privileges of the account named by its DEFINER attribute.”
— http://dev.mysql.com/doc/refman/5.6/en/stored-programs-security.html

If you create a view with ALGORITHM=MERGE then queries written against that view will be handled by the server by rewriting the query internally based on the view definition, so there's no significant overhead.  As long as you are simply selecting all or some of the columns from each table, and not using functions in the views that are incompatible with MERGE then this seems like the optimum solution, since the analytics user would have no access at all to the underlying tables, and not necessarily even be aware of their presence on the server if the permissions are set correctly.  Technically speaking, as long as there are no conflicts in naming, you could even create these views directly on the slave, since MySQL replication has no problem with spurious structures on the slave as long as the replication SQL thread never has any reason to notice them.

If none of these are suitable alternatives, is one more alternative approach that can be used if part of the problem is that you can't even allow the sensitive data to be sent to or stored on the other server, because of its physical location or other security concerns... but it requires a third MySQL server in the middle (between existing master and slave) and is somewhat fragile because every ALTER TABLE operation occurring on the master will cause the intermediate machine's replication SQL thread to pause until you manually reconfigure it to understand the new table definition.  On a stable schema, it works pretty much flawlessly (I've had one in production for over a year) but it's still a delicate setup, so I'll spare you the details on this option for the moment.


Why COUNT(*) on linked server is inaccurate? I have two SQL Servers, separate hardware boxes.
Server A:
 Microsoft SQL Server 2012 (SP3-GDR) (KB3194721) - 11.0.6248.0 (X64)
    Sep 23 2016 15:49:43
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor).

Server B:
Microsoft SQL Server 2012 (SP4) (KB4018073) - 11.0.7001.0 (X64)
    Aug 15 2017 10:23:29
    Copyright (c) Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

I recently deleted all the rows in one table from Server A and copied the rows from Server B using the import wizard.

When I execute these COUNT(*) queries from Server A, I get the identical result for each query, 200,703 rows.

select count(*) from dbo.thisTable

select count(*) from [Server B].[mydb].[dbo].[thisTable]

But if I exec the second COUNT(*)  on Server B, it tells me I have 196,420 rows.

The copy wizard threw no errors.  Any idea what happened?

Some of the things that could describe a different count on the same table (remote or not) are:

query a different table without knowing it due to default schema
query at a different time, where data changes have happened in the meantime
query with different isolation semantics (e.g. using NOLOCK or snapshot during data changes)
query a table with row-level security
query a view with filtering of any kind
query a synonym that actually points to a different object

Other explanations are usually that they are not the same table. Does server B have a loopback linked server to server B? Or did you execute a slightly different query on server B? Is it possible it was in a different database, or that the linked server on server A might be pointing at something else?


How to add a server list into Registered Servers on Microsoft SQL Server Management Studio (SSMS) I have a list of sqlserver on on table on tbl_servers which have 500 server.
example:
Select IP_instance, DBname, username, password from tbl_servers
which will return the all sql server and their sa credential.
--> 10.10.1.30, myDatabase, sa, password
I would like to add all of this into the Registered server, can this done by SQL scripting? or any faster way to add?

Phil Factor wrote a great article at the Simple-talk website that includes this and much other useful information
https://www.red-gate.com/simple-talk/sql/sql-tools/registered-servers-and-central-management-server-stores/


What are some practical use cases for memory tables? Trying to learn mysql and having tons of other material to learn, I'm considering skipping memory tables, since they have many special characteristics to consider especially that they vanish, after server restart.
In production environments, what are they used for?

If you are learning MySQL, skipping MEMORY storage engine is not a bad idea. InnoDB is the default storage engine and it is suitable for most cases. Only in edge cases it makes sense to use another one, and you probably shouldn't consider this option before knowing InnoDB quite well.

However, to answer your question, first let's check pro's and con's of MEMORY.

Cons:

You could lose your data (except for table definition).
No transactions, no consistency of any kind.
Only supports HASH indexes, so there is no optimisation for queries like WHERE col > n, ORDER BY or GROUP BY.
Massive usage consumes memory. No trivial way to monitor how much memory you are using for these tables.

Pros:

Not dramatically faster than InnoDB (in terms of latency), but still generally faster when used properly.
Better throughput because you don't talk to storage devices.

So the question becomes, which use cases don't suffer because of the cons and take advantage of the pro's? Well, a first obvious generic answer is, avoid all use cases where data loss is a problem. More specifically, you could consider MEMORY for:

Caches: all queries are point searches, data can always be reconstructed.
Speed up some massive insertion. You put data into a MEMORY table temporarily, do your transformations, and copy the rows to a "regular" table. But if your transformations require BTREE indexes, this is not a good option.
TEMPORARY tables, when you don't need BTREE indexes.


Best practice for "recycling" rows on PostgreSQL (UPDATE vs INSERT + DELETE) I searched responses on "UPDATE vs INSERT + DELETE" in general terms and the consensus is UPDATE is faster, as expected.
My question is centered on a somewhat niche use case. Let's say you have a table of fairly high-frequency ephemeral data. For example, modelling notifications on a social network, those are created very often and you always see the latest N.
Would it make sense to keep the latest N (say 20) and when one comes in, the Nth row is UPDATED into the new one (ID included). Rather than inserting a new one and then trimming old rows (maybe in bulk on an interval)?
Any thoughts?

Postgres is MVCC. Every write produces a new row-version irrespective of whether the operation is insert or update. So I would not expect the proposed ring buffer style to give performance benefits.
With single-value systems, and fixed length rows I would expect faster performance. I'd like to see some thorough testing before I believed it was worthwhile, though.


ib_logfile0 and ib_logfile1 got deleted After restoring from a percona-xtrabackup without a preparation step, ib_logfile0 and ib_logfile1 were not present in the restore data. Therefor during startup the following message is observed:

140223 04:10:24 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
140223 04:46:51 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140223  4:46:51  InnoDB: Initializing buffer pool, size = 18.0G
140223  4:46:54  InnoDB: Completed initialization of buffer pool
140223  4:46:54  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
140223  4:46:54  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
140223  4:46:54  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
140223  4:46:55  InnoDB: Error: page 7 log sequence number 45 3368182252
InnoDB: is in the future! Current system log sequence number 42 975484940.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html

How can I fix this?

You have three(3) options
OPTION #1 : Redo the XtraBackup with the Proper Preparation Steps
The preparation step was meant to carry the logs along for the ride. You must go back and run the backup again.
OPTION #2 : Play with Forced Recovery
Rather that reinvent the wheel, please see my post
The InnoDB log sequence number is in the future
OPTION #3 : Run Multiple rsyncs
This is rather risky, but you can run rsync multiple times against the live MySQL instance until two consecutive rsyncs take the same amount of time. Then, shutdown mysql on the source, run one more rsync. Then, after due diligence, startup mysql on the new server.
See my earlier post Any better way out of MySQL InnoDB log "in the future"?
Give it a Try !!!


Warning: mysqli_real_connect(): (HY000/2002): Connection refused in /var/www/html/wordpress/wp-includes/wp-db.php on line 1626 I'm just starting to code and I try to host a Wordpress website on a server of my school.
However, as soon as I upload the website on the Wordpress interface with the plugin "all-in-one migration", the database crashed. Wordpress gives me the error :

Warning: mysqli_real_connect(): (HY000/2002): Connection refused in /var/www/html/wordpress/wp-includes/wp-db.php on line 1626

When I check my database, I find it :

● mariadb.service - MariaDB 10.3.22 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: deactivating (final-sigterm) (Result: timeout) since Thu 2020-06-18 21:23:34 UTC; 28min ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
  Process: 10644 ExecStartPre=/usr/bin/install -m 755 -o mysql -g root -d /var/run/mysqld (code=exited, status=0/SUCCESS)
  Process: 10645 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
  Process: 10647 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1
 Main PID: 10695 (mysqld)
   Status: "Waiting for page cleaner"
    Tasks: 3 (limit: 563)
   Memory: 65.1M
   CGroup: /system.slice/mariadb.service
           └─10695 /usr/sbin/mysqld

Jun 18 21:23:40 genius systemd[1]: Starting MariaDB 10.3.22 database server...
Jun 18 21:23:41 genius mysqld[10695]: 2020-06-18 21:23:41 0 [Note] /usr/sbin/mysqld (mysqld 10.3.22-MariaDB-0+deb10u1) starting as process 10695 ...
Jun 18 21:39:50 genius systemd[1]: mariadb.service: State 'stop-sigterm' timed out. Skipping SIGKILL.

And, of course, when I try to enter my database, I get the code :

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (111)

I tried to restart the database, but it doesn't return any response.

By searching on StackOverflow, a few answers suggest changing localhost  to a local IP like 127.0.0.1 in the file "wp-config.php", but it didn't change anything.

Can you help me please ?

first check wp-config.php for this 4 lines

// ** MySQL settings - You can get this info from your web host ** //
/** The name of the database for WordPress */
define( 'DB_NAME', 'database_name_here' );
/** MySQL database username */
define( 'DB_USER', 'username_here' );
/** MySQL database password */
define( 'DB_PASSWORD', 'password_here' );
/** MySQL hostname */
define( 'DB_HOST', 'localhost' );

then Check Your Database Host Information
and if you failed do this action
add define('WP_ALLOW_REPAIR', true); in your wp-config.php
and after you have done ,navigate with http://yourwebsite.com/wp-admin/maint/repair.php.
and better before doing disactivate all pluging via ftp
rename plugins to plugins.deactivate.
repair wordpress
and finnaly Update WordPress Site URL in wp_options tables
Update WordPress Site URL


Excel Query Using MariaDB ODBC Driver I'm using MariaDB ODBC driver version 3.0.2 for Windows(on Windows 10) and can't get excel (Excel 2013) to query the database. I'm able to connect to my MariaDB server using HeidiSQL and query just fine, and my PHP application can query just fine. Now I would like to start pulling data for analytics.

I've setup the DSN using the "ODBC Data Source" tool under Administrative Tools in the control panel. I've just left all of the options blank. Below are some screenshots of how I setup the DSN.

When trying to setup the connection in Excel, I go to the data tab and from the "From Other Sources" drop down, I click Microsoft Query.

Then I select my data source (DSN) from the list and click "OK". Then I get two errors. The first error says "String data, right-truncated" and the second error says "Couldn't read this file". Screenshots below.

I'm not sure what is causing this. I've tried using the servers IP address and the servers resolvable name, and I've tried specifying a database. I've also tried specifying a connection character set and selecting the option for using compression. I've also tried enabling dynamic cursors and the option to return matched rows instead of affected rows. Nothing has made any changes.

UPDATE: This is still an issue for me. 6/21/2018

I've figured out how to execute custom queries against MariaDB in Excel.

Create your DSN using the "ODBC Data Sources (32-bit)" desktop app and leave all options as the default, except the connection page settings and "Return matched rows instead of affected".

In Excel, on the sheet you want the query to show up on, on the data tab, select "From Other Sources" -> "From Data Connection Wizard".

In the dialog that pops up, select "ODBC DSN" and click "Next".
Find the DSN you created earlier and click "Next".
Now you need to select a table, just one. (We will change this later)
Click "Next", then "Finish", then "OK". The data from your table should populate the sheet, starting in what ever cell you specified before hitting that last "OK".

Okay, That's great! But how do I run a custom query with joins and a where clause? Read on my friend.

Select any cell in the table that resulted from the above steps.
In the design tab (should have opened when you clicked into the table) select the "Properties" button.

Click the "Connection Properties" button beside the "Name" field.

Now, on the "Definition" tab, in the "Command text:" field, you can paste in your query.

I've tested this with basic joins and where statements. Everything seemed to work fine, so this should be enough to get you started. I have not tested sub-queries or any other advanced features yet.


Permissions to create certificate Is there a way to grant a user permissions to create certificate in SQL Server?

There is a CREATE CERTIFICATE permission:

GRANT CREATE CERTIFICATE TO [user_or_role_name];

Here is a simple test:

USE [tempdb];

CREATE USER [t] WITHOUT LOGIN;

EXECUTE AS USER = 't';

PRINT 'Before GRANT:';
CREATE CERTIFICATE [tt] WITH SUBJECT = 'test';

REVERT;

GRANT CREATE CERTIFICATE TO [t];

EXECUTE AS USER = 't';

PRINT 'After GRANT:';
CREATE CERTIFICATE [tt] WITH SUBJECT = 'test';

REVERT;

DROP USER [t];

Which returns (in the "Messages" tab):

  Before GRANT:
  Msg 15247, Level 16, State 1, Line 15
  User does not have permission to perform this action.
  After GRANT:
  Msg 15581, Level 16, State 1, Line 26
  Please create a master key in the database or open the master key in the session before performing this operation.


Partitioning - when to create new scheme? I have the following partition setup on a table.

I'm wanting to create a new partition that begins on 2019-01-01.

Is the the correct way of doing it, and can it be run now so it's ready to go for 2019?

ALTER DATABASE mydatabase ADD FILEGROUP [Year7FileGroup]
GO
ALTER DATABASE mydatabase
  ADD FILE
  (NAME = N'data_year7',
  FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL12.SQL2014\MSSQL\DATA\data_year73.ndf',
  SIZE = 5000MB,
  FILEGROWTH = 500MB)
  TO FILEGROUP [Year7FileGroup]
GO
ALTER PARTITION SCHEME psYearlyPartition_Log
    NEXT USED Year7FileGroup;
ALTER PARTITION FUNCTION pfYearlyPartition_Log()
    SPLIT RANGE('20190101');

Firstly, I would encourage you to read Dan Guzman's excellent post on Table Partitioning Best Practices.  It does a great job of explaining why:

  "One needs a good understanding of how the RANGE LEFT/RIGHT
  specification affects partition setup and management."

Now, on to your question.

I'm a little puzzled why you're adding a new partition for '2019-01-01', when your latest partition seems to be for '2016-01-01'.  Since you're trying to add a new partition for '2019-01-01', I would've expected to already see partitions for '2017-01-01' and '2018-01-01'.

In any event, when you're planning on using the SPLIT command, the main thing you want to consider is minimizing data movement between existing partitions.  Pre-defining empty partitions 'before' the minimum value you can have and 'after' the maximum value you can have will allow you to minimize data movement when using the SPLIT.  Referencing Dan's post:

The actions performed by a SPLIT of a RANGE LEFT partition function:

For each table/index using the affected partition scheme(s), move
rows from the existing split partition that are less than or equal to
the new boundary into the newly created partition on the left

The actions performed by a SPLIT of a RANGE RIGHT partition function:

For each table/index using the affected partition scheme(s), move
rows from the existing split partition that are greater than or equal
to the new boundary into the newly create partition on the right

It looks like you might be using RANGE RIGHT partition.  As long as you currently do not have any dates that are greater than or equal to '2019-01-01', there should be no data movement and the SPLIT should execute very quickly.  You definitely want to get the '2019-01-01' partition defined before you get too much data in your table with dates greater than or equal to that date.

The main point I'm trying to stress is to make sure you already have partition boundaries defined well ahead of the data that currently exists on the table.  Many times, you can go ahead and define partitions ranges years in advance of the data, but you should always monitor your partitioned tables to ensure you aren't creeping up on an ending boundary.


SQL Server 2012 RC installation failing I'm trying to test out the new SS 2012 RC, but I'm having problems with the installation.

My installation stops a ways in saying that the resource needed cannot be found. I'm using the ISO from MSDN and mounting it using MagicDisc. I browse to the location of the missing resource (vs_setup.msi) on the disc and it says that the vs_setup.msi file is not valid for VS 2010 Ultimate. I tried both vs_setup.msi's on the installation iso and both said they weren't valid. Any ideas on what I am doing incorrectly?

I do have VS2010 Ultimate already installed.

The dialogue specifically says:

The feature you are trying to use is on a network resource that is unavailable.

It tried to default to this directory, but the directory doesn't exist on my machine:

C:\Users\[UserID]\AppData\Local\Temp\30319.01\1033\VS_VSTS\

Browsing to the vs_setup.msi in E:\1033_ENU_LP\redist\VisualStudioShell\VSIntSetup and in E:\1033_ENU_LP\redist\VisualStudioShell\VSSetup doesn't work either.

Anyone have any suggestions for what I might be doing wrong?

Download the Visual Studio 2010 ISO (Ultimate, in your case).

Extract it to a folder and use that folder path when prompted for vs_setup.msi.