Postgresql: Cast row to type I read: Use composite type to create new table

I have a table called locations, e.g. representing objects with lat. and long. coordinates.

In another table, I declared a column of type locations (just for fun, not trying to be smart), i.e.

CREATE TABLE XXX (..., some_column locations, ...);

And now I'm asking myself what this means and if I could store a locations object in there.

And here's what I tried to do:

SELECT pg_typeof(ROW(x)) FROM locations x LIMIT 1;

which returns record. I tried casting this to locations, i.e.

SELECT ROW(x)::locations FROM locations X LIMIT 1;

which yields

  ERROR:  cannot cast type record to locations

Next I tried defining a composite type type_location based on the columns of the locations table, and created a typed table (CREATE TABLE ... OF ...) based on it. Still I am unable to do ROW(x)::locations.

Ultimately I'm trying to get a value to store into table XXX of type locations (or type_location) but I don't understand which part my reasoning is fallacious.

PS: I'm not trying to create a sound database design using this construction but really only just toying around with Postgresql and its type system.

And now I'm asking myself what this means and if I could store a locations object in there.

Yes, you can. (But there are not many great use cases for that.)

This does not do what you seem to think it does:

SELECT ROW(x)::locations FROM locations X LIMIT 1;

x is already a row type. By wrapping it into ROW(x) you create a record containing a column of type locations, which cannot be cast to the row type locations as it's something else. Use instead:

SELECT x::locations FROM locations x LIMIT 1;

... where the cast is redundant. So just:

SELECT x FROM locations x LIMIT 1;

However, if there is a column of the same name "x", this resolves to the column name. Pick a table alias that can never appear as column name or use this to be sure:

SELECT (x.*)::locations FROM locations x LIMIT 1;

Now, the cast is not redundant as Postgres would otherwise expand x.* or even (x.*) to the list of columns. Read the manual here and here.

Also just:

SELECT pg_typeof(x) FROM locations x LIMIT 1;

instead of:

SELECT pg_typeof(ROW(x)) FROM locations x LIMIT 1;

Aside: the ROW constructor does not preserve column names and always produces an anonymous record (as you found out the hard way).

SELECT ROW(x)::locations FROM locations X LIMIT 1;

Related:

Array of strings when updating a field


SQL Server Replication Stored Procedures sp_MSdel_db, sp_MSupd_db, sp_MSins_db I have 2 production servers and 2 development servers. All SQL Server 2008 r2.

In production, I had one of our DBAs create a replication publisher and distributor on ProdServer1 and Subscriber on ProdServer2.  This has been running for a few months with out issue.

Now, I'm working on a new project and asked for replication to be set up in our dev environment, identical to production.  So publisher and distributor on DevServer1 and Subscriber on DevServer2.

Now that this is complete, I'm looking at my databases and in DEV I have 3 stored procedures for each replicated table.  They appear to be simple crud procedures (Insert, Update and Delete).

In production, I do not have these stored procedures.  DBA number 2 is saying these are required for replication.  However, Production is still working and these stored procs aren't there.

The DBA who set up the original replication in production isn't available so I can't ask what was done differently there.

Can anyone tell me what would cause these stored procs to be created in one instance yet not in another? This is very confusing to me, and has me concerned because my development environment is so much different from production.

Jeff - Have a look at the parameters for sp_addarticle.  Notice the parameters @ins_cmd, @upd_cmd, and @del_cmd can be configured to replicate inserts, updates, and deletes as either stored procedure calls or straight SQL.

DBA number 1 likely setup the production publication to replicate inserts, updates, and deletes as SQL.  DBA number 2 likely setup the development publication to replicate inserts, updates, and deletes through called stored procs.


How can I drop and create postgresql views in dependency order? I found the following https://stackoverflow.com/a/9712051/61249 which is good but still too much manual labor for me. I need to recreate the views in the right order how would I go about doing that?

@dbenhur talks about the following:

  To be more complete, one could figure out how to query which views
  depend on the table(es) you're modifying and use that query instead f
  enumerating view names. Gonna need to understand pg_rewrite | pg_rule
  to do that, I think

I am unsure what that means exactly but I'll tell you want I need and you tell me if it is possible.

I work on a rails application where I've tried to maintain the views and functions as part of the rails migrations unfortunately this became a real mess and I dumped our views and functions into separate files for each and one of those. There are two ways forward as I see it. I could either use a single file for these which makes it cumbersome but I guess the order of the DDL would be managed by pg_dump.

OR I could when I dump the views and functions also query and create a dependency tree that I later use for recreating them. The reason is that it has become too complex to do manually for just changing the name of a column in a view.

How do others maintain their DDL? Anyone ever done anything like what I want to do?

You may take a look on "drop table base cascade". after that you have to recreace the structure on your own. I think its the easyer way. Or you can extend the answer, you found with some search in the pg_depend table.
For the "clean" method, please check the docs, as a dirty way here is a query:

WITH RECURSIVE t AS (
  SELECT c.oid,c.relname, 0 as level FROM pg_class c where relname='base' UNION ALL
  SELECT c.oid,c.relname, a.level+1 FROM t a
  JOIN pg_depend d ON d.refobjid=a.oid
  JOIN pg_rewrite w ON w.oid= d.objid AND w.ev_class!=a.oid
  JOIN pg_class c ON c.oid=w.ev_class
) SELECT distinct * FROM t order by level;


SQL Server Memory Determination We will be modifying a SQL Deployment in our Hosted Environment. We have a solution which is currently configured to use one Application (and not SQL) System Database (which holds Security and System Level Configurations for our Application) and numerous independent Company Databases, each used by different users (originating from independent Organizations hosted in our Environment). We will be changing this deployment, after which each Organization will have its own System Database (instead of using a unique one for all Organizations). Thus, each Company Database will now have an associated Application specific System Database.

To summarize, before the deployment let us suppose that a Server had 100 Company Databases and one System Database, it will now have 100 Company Databases, plus 100 System Databases. Thus, the number of Databases will have doubled. Meanwhile, the number of transactions on the SQL Server will be identical. As an example, upon logging into a Company Database, a user will log into its associated System Database first in order to verify its Application specific Security Role for instance, instead of accessing the shared System Database as before. Hence, the Number of transactions launched against the System Databases will remain the same (as when there were only one of them), but the number of Databases will double.

Considering that the Number of Transactions will remain the same, but that the Number of Databases on the SQL Server Instance will double, how should we determine how much more Memory should we be adding (if any) to the Server?

Thank you.

"Should I add more memory to the server" is a tough question to answer. Your box might be coping "just fine" with what it has, or you may find that tuning the system in other ways (expensive queries, for example) would be more effective. There are a number of resources that you should consider, including Jon Kehayias' How much memory does my SQL Server actually need, as suggested by Aaron in the comments.

Your system may have other bottlenecks apart from memory, particularly if there are a lot more databases, such as disk reads/writes. Writing to log files, reading from data files - it all adds up. But as far as memory is concerned, if you think your current system could cope with less RAM, then it should be able to cope with more databases and the same RAM.


Checkpoints and Logging I'm working on getting full grasp of logging architecture.
Please correct me if I'm wrong here:
I understand that a checkpoint occurs (also lazywriter) to write the dirty pages in the memory to the disk. Disk here means transaction log file no matter what recovery model the database is in. Then the dirty pages become clean, i.e. data on the disk is up-to-date with memory pages.
I also understand logging must happen after commit and before sending the acknowledgement to the client.
What cloudy is, what happens after the data is written to disk on the transaction log file? What moves the data from ldf to mdf (or ndf)? In case a lazywriter removes the pages off the memory, the data will be in the transaction log but not in the data file (yet) or no longer in the memory. What happens when that page is queried?
Also a brief explanation into relationship between checkpoints and simple-full recovery models is appreciated. How does the behaviour change?
Thanks for helping out.

Dirty pages are written directly to the data files. There is no need to write them to the log because their modifications have been logged already. A dirty page never hits disk until all log records that record its modification have been written already. After the log has been hardened SQL Server is at leisure to write dirty data pages whenever it wants (or never - if the machine loses power for example). After a page has been written is is simply marked not dirty.

A transaction commit leads to a corresponding log record. The very fact of hardening that log record is what makes the transaction considered committed (except for delayed durability in 2014+).

The recovery model has little to do with this. It controls whether the log is eligible for truncation after a checkpoint has completed.


Item Index for all items in nested XML query I have a requirement to build an XML package with a parent record, and 1..N related child records.

The child records are to be a nodes under the parent node, and there may be one child record or several for each parent.  It's also possible to have multiple parents in the same query.

The above query I have with no issue.

I also have a requirement to give each record (parent and child) a unique 0-based index.  I can do with with ROW_NUMBER for each subquery, but can't figure out a way to get a unique index for every record in the entire query.

Fiddle here with sample data and working query.

(Note, SQLFiddle does not display XML in a very easy to read format so you may want to run this in your local SSMS.)

Working SSMS code below:

BEGIN TRY
    DROP TABLE #Child
    DROP TABLE #Parent
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE #Parent
    (RecId int PRIMARY KEY NOT NULL,
     PersonName varchar(100), Age int)
CREATE TABLE #Child
    (ChildID int identity PRIMARY KEY NOT NULL,
     ParentRecId int FOREIGN KEY REFERENCES #Parent(RecId),
     SalesAmt money)

INSERT INTO #Parent
  (RecID, PersonName, Age)
VALUES
  (1, 'Aaron Bertrand', 99),
  (2, 'Paul White', 20),
  (3, 'JNK', 33)

INSERT INTO #Child
  (ParentRecID, SalesAmt)
VALUES
  (1, 10.00),
  (1, 20.00),
  (2, 15.15),
  (2, 100.00),
  (3, 0.00)

SELECT
  RecId as 'RID',
  PersonName as 'PNAM',
  Age,
  (
    SELECT
      C.SalesAmt as 'SAMT',
      (ROW_NUMBER() OVER (ORDER BY ParentRecId) - 1) as 'Index'
    FROM
      #Child C
    WHERE
      C.ParentRecId = P.RecId
    FOR XML PATH ('ChildRec'), ROOT ('ChildRecs'), TYPE
  )
FROM
  #Parent P
FOR XML PATH ('Parent'), ROOT ('Parents'), TYPE

How can I get a unique index across all parent and child records for this XML package?

I found the answer - I needed to create a CTE that uses a union of all my child and parent records and creates a ROW_NUMBER(), then JOIN to that CTE to get the ROW_NUMBER() value which will be unique across all records.

Solution fiddle here.

Full solution to paste into SSMS:

BEGIN TRY
    DROP TABLE #Child
    DROP TABLE #Parent
END TRY
BEGIN CATCH
END CATCH

CREATE TABLE #Parent
    (RecId int PRIMARY KEY NOT NULL,
     PersonName varchar(100), Age int)
CREATE TABLE #Child
    (ChildID int identity PRIMARY KEY NOT NULL,
     ParentRecId int FOREIGN KEY REFERENCES #Parent(RecId),
     SalesAmt money)

INSERT INTO #Parent
  (RecID, PersonName, Age)
VALUES
  (1, 'Aaron Bertrand', 99),
  (2, 'Paul White', 20),
  (3, 'JNK', 33)

INSERT INTO #Child
  (ParentRecID, SalesAmt)
VALUES
  (1, 10.00),
  (1, 20.00),
  (2, 15.15),
  (2, 100.00),
  (3, 0.00)

;WITH IDs AS
(
    SELECT
       RN = (ROW_NUMBER() OVER (ORDER BY  RecId,CASE WHEN ChildId IS NULL THEN 0 ELSE 1 END) -1),
       RecId,
       ChildId
    FROM
       (
       SELECT
          RecId, ChildId = NULL
       FROM
          #Parent
       UNION ALL
       SELECT
          RecId, ChildId
       FROM
          #Parent P
       INNER JOIN
          #Child C
              ON C.ParentRecId = P.RecId) x
)

SELECT
  P.RecId as 'RID',
  P.PersonName as 'PNAM',
  P.Age,
  I.RN as 'Index',
  (
    SELECT
      C.SalesAmt as 'SAMT',
      I.RN as 'Index'
    FROM
      #Child C
    INNER JOIN
       IDs I
          ON I.ChildId = C.ChildID
    WHERE
      C.ParentRecId = P.RecId
    FOR XML PATH ('ChildRec'), ROOT ('ChildRecs'), TYPE
  )
FROM
  #Parent P
INNER JOIN
    IDs I
       ON I.RecId = P.RecId
       AND I.ChildId IS NULL
FOR XML PATH ('Parent'), ROOT ('Parents'), TYPE


Chance of skipping extended events when using an offset for the file target I am exploring extended events and came across a question I can't solve using the Microsoft documentation:

When reading events from the file target we can specify and initial offset so we can read the logs in in a differential manner. For example:

sys.fn_xe_file_target_read_file('log*.xel', 'log*.xem', 'specific.xel', 984576)

Now all events up to and including 984576 will be ignored. Events after the offset will be returned.

From the documentation:

  initial_offset
  Used to specify last offset read previously and skips all events up to the offset (inclusive). Event enumeration starts after the offset specified. initial_offset is bigint. If null is specified as the argument the entire file will be read.

My question is how can we be sure that we don't skip events given the fact that file_offset is not unique and shared:

If we use an offset there is a change that new events with a share offset are not returned right?

Does the function fn_xe_file_target_read_file bump the offset so that this possibility cannot occur?

I couldn't find a definitive answer about how the offset is generated, only one unreliable reference that they're a block number. A little disappointing, I guess the engineers don't use stack exchange.

However I was listening to the SQL Down Under podcast this week and they were discussing the issues with doing ETL based on timestamps which are a little unreliable and result in skipped records.

The advice was that it's a good practice to read 30s back from where you left off and then eliminate duplicates.

So unless someone comes up with something better you can at least address your missed record concerns by starting your reads one block prior to the last block you received, and then eliminating duplicates.


A performance Issue when querying a big table using a PK/clustered index To simplify the situation, I will only consider one large table...
On a nightly basis a store will send all the new and changed data for one day for a large table to the head office. (this portion is good)
Also, the store sends a summary of that table for the last 30 days to the head office for that large table.

At the head office,
The new and changed data is updated in the large table  (no problem here).
The summary of the last 30 that is received and is uploaded into a table.  It is then compared to a query that summarizes the data at the head office of this very large table (that contains all stores) for that same store.<-- this is where the problem is.  This is done to be able to make sure that the store's data matches the head office data for that store (we get a warning if it doesn't match for which they need to take action)

The problem is the the summary query takes too much time... I'm looking to change the way we compare the store table with the host table in a more efficient way.

I tried the indexed view and the results were great but the fact that they have too many limitations, it makes it practically impossible to implement it on a large scale (to all software owners, cash registers, Stores and Head offices) due to different structures and different versions of our software.

I've been trying to think of different ways I can insure that the data of a table  (for at least the last 30 days) for a store matches the head office but I feel like I'm turning in circles... So I'm looking for ideas to help me look at this differently.

Limitations: We use SQL Express at the stores, and usually standard at head offices.  There's no direct connection between both databases (the data is transferred through files).

Any help is appreciated.
thank you

Added more info:
Structure of the table (I know is not ideal, it's what I inherited):
Date, Store, terminal, transNum, lineNum, Qty, Amount + 194 MORE COLUMNS.
The PK and clustered index is: Date, Store, terminal, transNum, lineNum

The query to summarize is simple:

Select Date, Store, sum(Qty) as Qty, sum(Amount) as Amt
from MyHugeTable
where date between '2017-07-22' and '2017-08-22'
and store = '1234'
group by Date, Store;

If speeding up this query is critically important, I'd consider creating a covering index:

CREATE INDEX IX_MyHugeTable_Date_Store_Qty_Amt
    ON MyHugeTable (Date, Store)
       INCLUDE (Qty, Amt)
;

Adding a new index will impact the time to add, update, and delete rows from the table. You should test to determine the impact of adding this index to the nightly update process. It won't help if this speeds up the query generating the summary data for each day, but the slowdown in inserting and updating daily changes is slowed down more than the summary query is sped up. This would hold true with some of the other suggestions from the comments; test to make sure that the changes you make do not harm normal operations.

FYI: the idea behind a covering index is simple - if an index has all the columns a query refers to, then the engine can retrieve that information from the index without actually touching the table itself. This index should take up much less space per row than the table (with around 200 columns), so the query should perform much better.

As noted by David Browne, your index would include not just the four columns listed, but the other 3 keys that make up the primary key. That's because all non-clustered indexes on a table with a clustered index use the cluster key to identify the row location in the main table. See this link for full details. Still, the index will be much narrower than your ~200 column table.


Is there a way to compare very short periods of activity in Oracle? I am having a problem with an application that requires very low latency (less than 100 ms per txn.)  Every once in a while we will see spikes where transactions take 20-30 SECONDS.  We looked at the ASH report and we are seeing "log file sync" waits, but we are unclear on the exact source of the problem.  I want to compare this problem period with a normally operating period.  However, the only way I know of to do this is using AWR compare period reports.  The problem with this is that the periods are too long (30 mins).  I need some way to compare very short periods of time after the problem has occurred since I have now way of knowing when it will happen.  Any advice would be appreciated.

Directly query the DBA_HIST_ACTIVE_SESS_HISTORY view if you want a narrower view than an ASH or AWR can provide. Use something like this query:

select distinct user_id, session_id,session_serial#, sql_id, program, machine
from dba_hist_active_sess_history
where sample_time between
    to_date('20160819 1830','yyyymmdd hh24mi') and to_date('20160819 1835','yyyymmdd hh24mi')
and event = 'log file sync';


List of IDs that have an assignement to ALL other Ids in another table I need help with the following task (giving up after some hours)

CREATE TABLE #cities (cityId INT, city VARCHAR(100));
INSERT INTO #cities (cityId, city)
VALUES (1, 'Metropolis A'),
       (2, 'Town B'),
       (3, 'Village C');

CREATE TABLE #assignements (assignmentID INT, cityId INT, desciption VARCHAR(100));
INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (1, 1, 'Test Alpha Metropol'),
       (1, 2, 'Test Alpha Town'),
       (1, 3, 'Test Alpha Village'),
       (2, 1, 'Test Beta Metropol');

INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (2, 3, 'Test Beta Village');

I need a query, that returns me all assignmentIDs joined to the #cities (easy :-)), but list only those cities, that are used in ALL assignmentID.

In the example above it should me return after the first INSERT only

assignmentID  city
1             'Metropolis A'
2             'Metropolis A'

since the Town and the village are only used once (but there exists two different assignmentIDs)

After the second INSERT it should return

assignmentID  city
1             'Metropolis A'
2             'Metropolis A'
1             'Village C'
2             'Village C'

because Village C is now assigned to both assignmentIDs too.

When I would

INSERT INTO #assignements (assignmentID, cityId, desciption)
VALUES (3, 2, 'Test Gamma Town');

the query should return no row, because there is no city, which is assigned to all three assignmentIDs (Alpha, Beta and Gamma).

Of course this example is simplified, in reality there could be any number of cities which are assigned to (currently) up to 11 assignmentIDs

You can try something like:

select a.assignmentID, c.city
from #cities c
join #assignements a on a.cityId = c.cityId
where c.cityId in ( select c.cityId
                    from #cities c
                    left join #assignements a on a.cityId = c.cityId
                    group by c.cityId
                    having count(*) = (select count(distinct assignmentID)
                                       from #assignements)
                  )


Slow query while using DISTINCT in MYSQL with various other conditions (10 million records) I am working on a social Network where, as usual there is a feed for users where they are able to see the activities of users a user is following and not the users who are blocked by the users.

This is the query that is being used currently from the Activities table

SELECT DISTINCT `activities`.`post_id`
FROM `activities`
WHERE (activities.user_id IN ([followed_user_ids]) AND
activities.language_id IN ([language_ids]) AND
activities.id <= ?) AND
(`activities`.`post_user_id` NOT IN
([blocked_and_deactivated_user_ids]))
ORDER BY `activities`.`id` DESC
LIMIT 10 OFFSET 10

This is the query which is causing issues. Sometimes the Mysql CPU usage goes to 100% which causes issues to the users. I have also used index on the columns being used in the activity table.

There are 10 million records in the Activities table.

What changes can i possibly do to optimise this?

Note - followed_user_ids/blocked_and_deactivated_user_ids - can have an array of thousands of ids.

Update 1

This is the new query that i formed ignoring the blocked users from post_user_id :-

"SELECT  DISTINCT `activities`.`id` FROM `activities`
INNER JOIN `users` ON `users`.`id` = `activities`.`user_id` AND
`users`.`deleted_at` IS NULL
LEFT OUTER JOIN `follows` ON `follows`.`followable_id` = `users`.`id`
AND `follows`.`deleted_at` IS NULL AND `follows`.`followable_type` =
'User' WHERE `activities`.`language_id` IN (19, 21) AND (activities.id
< 99999999) AND `follows`.`blocked` = 0 AND `follows`.`follower_id` = 1
AND `follows`.`follower_type` = 'User' AND `follows`.`followable_type`
= 'User' AND `users`.`deactivated` = 0
ORDER BY activities.id desc LIMIT 10 OFFSET 10"

This doesn't seem to help either. I need to get all the unique post activities from the users i am following, in a pagination format.

IN ( long list ) often performs badly. Try rewriting using [NOT] EXISTS or [LEFT] JOIN. Example:

SELECT DISTINCT activities.post_id
FROM activities
JOIN <table with followed_user_ids>
   ON activities.user_id = ...
JOIN user_languages
   ON activities.user_id = ...
LEFT JOIN <table with blocked users> bu
   ON activities.user_id = ...
WHERE bu.<attribute> IS NULL -- no match
ORDER BY activities.id DESC
LIMIT 1 OFFSET 10;

Make sure you have appropriate indexes on JOIN predicates.


Subtracting With GetDate() I am trying to convert a postgresql query to ms sql query.  This is what I have in postgresql
round(cast((Current_Date - emp.hiredate) As Decimal)/365,1)

now I thought it would be a simple conversion and I tried to change it for MS SQL to

round(cast((GetDate() - emp.hiredate) as decimal) / 365, 1)

but in MS SQL Server I get this error

  The data types date time and date are incompatible in the subtract operator

So, you are looking for the difference in Years between the two dates, correct?  And you want to show one decimal place in the calculation, correct?  Then a simple DATEDIFF() will not work for you.  Try this instead:

SELECT ROUND(CAST(Datediff(Day, emp.hiredate, GETDATE()) as decimal(9,1))/ 365, 1)

This is also imprecise (because of leap years) but matches what you have now.

EDIT:
You might want to check this logic, as the current ROUND() logic would show a person at 1.0 years when they hit 0.95 years. If you want to wait until they actually have 1.0 years or more to show them at 1.0 years, use this variant: SELECT ROUND(CAST(Datediff(Day, emp.hiredate, GETDATE()) as decimal(9,1))/ 365, 1, 1). This adds a trailing 1 to the current command. That added parameter TRUNCATES at the point specified instead of rounding up, so 0.99' would still show '0.9'


SQL Server prevent SA from setting encryption off Is there a way to prevent any user (including SA) from setting database encryption off (unless password or physical file - cert is provided)?

That is: not just some column or specific data - but securing the whole database from being detached and used on another server.

Is there a way to prevent any user (including SA) from setting database encryption off …?

No.  If the database is ONLINE then a sysadmin (including SA) can remove TDE.  Sysadmins are the owners of the SQL Instance, and all the databases.


meaning code at comment part from mysqldump result on the results from mysqldump i got this part from create procedure before line BEGIN
DELIMITER ;
/*!50003 SET sql_mode              = @saved_sql_mode */ ;
/*!50003 SET character_set_client  = @saved_cs_client */ ;
/*!50003 SET character_set_results = @saved_cs_results */ ;
/*!50003 SET collation_connection  = @saved_col_connection */ ;
/*!50003 DROP PROCEDURE IF EXISTS `spPayrollProcess` */;
/*!50003 SET @saved_cs_client      = @@character_set_client */ ;
/*!50003 SET @saved_cs_results     = @@character_set_results */ ;
/*!50003 SET @saved_col_connection = @@collation_connection */ ;
/*!50003 SET character_set_client  = latin1 */ ;
/*!50003 SET character_set_results = latin1 */ ;
/*!50003 SET collation_connection  = latin1_swedish_ci */ ;
/*!50003 SET @saved_sql_mode       = @@sql_mode */ ;
/*!50003 SET sql_mode              = 'PIPES_AS_CONCAT' */ ;
DELIMITER ;;

what mean of part !50003 ?
where i can see meaning of this code ?

It is a executable comment syntax that allows the same sql to be imported into a mysql-5.0.2 or earlier server that doesn't understand the SET modes.


SQL Server 2016 - “Always on” issue on secondary server , redo thread blocked I need help for an always-on issue after upgrading to 2016 from 2014. Redo thread is causing blocking for all the select query on the secondary replica. all long running select queries are getting blocked by redo thread for a specific database. All DBs are already in sync. redo thread is getting blocked once I am running SQL job to fetch the data from secondary database.. redo queue size is also 0 and DB also synchronized before running the job. read routing URL also configured. Has anyone faced such a scenario? Please help with this.

I have tried multiple things to rid of deadlock issue and enabled the Is_Read_Commited_Snapshot option as well but still, that REDO thread ( DB sync) is blocked.

As per my understanding, this issue could be due to the multi-threading model or parallelism.

Until SQL Server 2016, the transaction log redo was handled by single redo thread for each database, this model is called as serial redo.

Starting from SQL Server 2016, the default redo model is parallel redo. It provides multiple threads for transactions and additional worker threads for dirty page flush IO operations.

While working with the concurrent workload, DIRTY_PAGE_TABLE_LOCK waits are generated frequently by the worker thread, and at the same time, query threads will try to access the tables, this will cause the performance issues on both SELECT query and redo operations.

This issue was fixed on the latest Cummulative Update - https://support.microsoft.com/en-in/help/4135048/cumulative-update-1-for-sql-server-2016-sp2

As a workaround, you can disable the parallel redo model and enable the serial redo by enabling the trace flag 3459.

I hope this helps! Thank you.


How to count the reads on index in MySQL I have a table with index.
CREATE TABLE PART  ( P_PARTKEY     INTEGER NOT NULL,
                          P_NAME        VARCHAR(55) NOT NULL,
                          P_MFGR        CHAR(25) NOT NULL,
                          P_BRAND       CHAR(10) NOT NULL,
                          P_TYPE        VARCHAR(25) NOT NULL,
                          P_SIZE        INTEGER NOT NULL,
                          P_CONTAINER   CHAR(10) NOT NULL,
                          P_RETAILPRICE DECIMAL(15,2) NOT NULL,
                          P_COMMENT     VARCHAR(23) NOT NULL );

CREATE TABLE LINEITEM ( L_ORDERKEY    INTEGER NOT NULL,
                             L_PARTKEY     INTEGER NOT NULL,
                             L_SUPPKEY     INTEGER NOT NULL,
                             L_LINENUMBER  INTEGER NOT NULL,
                             L_QUANTITY    DECIMAL(15,2) NOT NULL,
                             L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
                             L_DISCOUNT    DECIMAL(15,2) NOT NULL,
                             L_TAX         DECIMAL(15,2) NOT NULL,
                             L_RETURNFLAG  CHAR(1) NOT NULL,
                             L_LINESTATUS  CHAR(1) NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT CHAR(25) NOT NULL,
                             L_SHIPMODE     CHAR(10) NOT NULL,
                             L_COMMENT      VARCHAR(44) NOT NULL);

ALTER TABLE LINEITEM
ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);

ALTER TABLE PART
ADD PRIMARY KEY (P_PARTKEY);

And query
SELECT Sum(l_extendedprice * ( 1 - l_discount )) AS revenue
FROM   lineitem,
       part
WHERE  ( p_partkey = l_partkey
         AND p_brand = 'Brand#52'
         AND p_container IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG' )
         AND l_quantity >= 4
         AND l_quantity <= 4 + 10
         AND p_size BETWEEN 1 AND 5
         AND l_shipmode IN ( 'AIR', 'AIR REG' )
         AND l_shipinstruct = 'DELIVER IN PERSON' )
        OR ( p_partkey = l_partkey
             AND p_brand = 'Brand#11'
             AND p_container IN ( 'MED BAG', 'MED BOX', 'MED PKG', 'MED PACK' )
             AND l_quantity >= 18
             AND l_quantity <= 18 + 10
             AND p_size BETWEEN 1 AND 10
             AND l_shipmode IN ( 'AIR', 'AIR REG' )
             AND l_shipinstruct = 'DELIVER IN PERSON' )
        OR ( p_partkey = l_partkey
             AND p_brand = 'Brand#51'
             AND p_container IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG' )
             AND l_quantity >= 29
             AND l_quantity <= 29 + 10
             AND p_size BETWEEN 1 AND 15
             AND l_shipmode IN ( 'AIR', 'AIR REG' )
             AND l_shipinstruct = 'DELIVER IN PERSON' );

I want to know

how many reads are performed on index.
how many reads are performed on table data.
how many index are in the buffer (memory) and in the disk.
how many table data are in the buffer (memory) and in the disk.

Thanks!

Metrics
It is possible to find the number of rows looked at in the tables for a single query.  These count rows of index or data, not counted separately, for your connection.  If no other queries are running, total counts are possible.
SHOW SESSION STATUS LIKE 'Handler%';

Here is a discussion of using that to help with optimization: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts
From
SHOW GLOBAL STATUS LIKE 'Innodb_%';

you can get certain global counts for disk accesses, etc. for all InnoDB statements from all connections.  (This is not precisely what you are asking for, but may be interesting.)
Another source is the system database performance_schema.  (In older MySQL versions, information_schema had some tables, but there are mostly moved to PS now.)
If you elaborate on the goals of your question, we might be able to help in more detail.
Optimizing that query
Start by using the modern JOIN syntax:
FROM lineitem AS l
JOIN part AS p  ON p.partkey = l.partkey

That was a common part of the ORs; this makes it so the Optimizer can do the JOIN more efficiently.
In case the Optimizer decides to start with part, have this
lineitem:  INDEX(partkey)  -- (but see below)

Now I see that l.shipinstruct = 'DELIVER IN PERSON' is common across the ORs, so pull it out.  Then this would probably be useful
lineitem:  INDEX(shipinstruct)  -- (but see below)

But, all of that probably still won't do as much Optimization as turning the OR into UNION ALL:
( SELECT ... Brand#52 ... )
UNION ALL
( SELECT ... Brand#11 ... )
UNION ALL
( SELECT ... Brand#51 ... )

Then we can build some even better indexes:
part:  INDEX(brand, container)
part:  INDEX(brand, size)
lineitem:  INDEX(shipinstruct, quantity)
lineitem:  INDEX(shipinstruct, shipmode)
lineitem:  INDEX(partkey, shipinstruct, quantity)
lineitem:  INDEX(partkey, shipinstruct, shipmode)

There are many ways the Optimizer might pick to run the query; I think these cover all the bases.


Query tuning update with hash flow distinct operator Need some help in understanding the slowness in one of the UPDATE statements like below:-
UPDATE TOP (100) xyz
SET xyz.flag = 1
OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc.ENID,abc.Cdate
FROM dbo.table1 xyz WITH (UPDLOCK, READPAST)
INNER JOIN dbo.table2 abc WITH (NOLOCK)
on xyz.id=abc.id
WHERE xyz.flag = 0

Table1 has approx. 0.5 million rows and Table 2 has approx. 5 million rows
Slow Plan
Hash Match distinct flow operator shows a yellow alarm and message is:

Operator used Tempdb to spill data for executed with spill level 4 with 1 spilled thread"
Build residual:
database.dbo.table2.id as abc.id = database.dbo.table2.id as abc.id

I took a screenshot. Unfortunately due to security reasons I can't provide more than that, not even an anonymized plan. From my working station I cannot access the internet so there is no way I can get plan explorer to run there.

Generally for a smaller subset of rows it's under sec like when we have just matching 10K rows or something. But with higher amount of data this seems to be tipping point and app cannot afford 1 min of run time. From SSMS I get 30 secs but from app we have avg. 50 secs approx.  RCSI is in testing phase.
My good plan does not have that Hash Match Flow Distinct operator visible as shown in my screenshot, while rest of plan remains same. Good one completes under 3 secs or so. As seen nearly 16 seconds are spent on that operator. Can we eliminate it via proper indexing or query re-write?
Table schema
CREATE TABLE dbo.table1
(
    Recid  VARCHAR(128) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    Cdate DATETIME NULL,
    flag BIT   NULL DEFAULT (0),
    Rcode INT NULL,
    EDR VARCHAR(255) COLLATE SQL_Latin1_general_CP1_CI_AS NULL,
    id BIGINT NULL
);

CREATE TABLE dbo.table2
(
    ENID BIGINT IDENTITY(1,1) NOT NULL,
    EID VARCHAR(50) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    CID VARCHAR(350) COLLATE SQL_Latin1_general_CP1_CI_AS NOT NULL,
    CDate DATETIME NOT NULL DEFAULT(getdate()),
    id BIGINT NOT NULL,

    CONSTRAINT PK_ENID PRIMARY KEY (ENID ASC, EID ASC),
);

-- table1
CREATE INDEX ix_Cdate on dbo.table1 (Cdate) WITH (FILLFACTOR=100);
CREATE CLUSTERED INDEX ix_Recid on dbo.table1 (Recid) WITH (FILLFACTOR=80);

-- table2
CREATE INDEX ix_ENID_id on dbo.table2 (ENID,id) WITH (FILLFACTOR=100);

Changes
Changes I made and some numbers:

Added hint OPTION (QUERYTRACEON 4138) - avg. execution 7 secs down
from original 50secs, but app team seems not have access to perform
this in code. Need to check further on this.

OPTION (ORDER GROUP)gave same results of avg. 50secs so no improvement
there.

Added index as suggested:

CREATE INDEX i ON dbo.table2 (id) INCLUDE (CID, CDate);

Not much improvements there.  Avg. 45 secs and plan was similar to one attached in this question (top plan).
Before and after each test I made sure plan was not generated from previous cached plan.
Fast plan
Attaching the plan which is faster and without any change in data or query is still fast for same amount of rows in both tables. App team continuously submit above query throughout the day to finish the batch by completing those TOP 100. There is a plan change based on some tipping number and below is how the good plan looks:

Edit:- With everything unchanged, no code change or any index being added, as suggested when i am trying to add hint (FORCESEEK) its giving me below error

Query processor could not produce a query plan because of the hints
defined in this query. Resubmit the query without specifying any hints
and without using SET FORCEPLAN.

You have three main problems:

There is no useful index to support the join on id.
The TOP (100) introduces a row goal, so estimations may be too low.
The UPDATE is non-deterministic.

Multiple rows from table2 could match on id, so it is not clear which matching row from table2 should be used to provide values for the OUTPUT clause. The aggregate is there to group on table2 id and choose ANY matching values for the other columns. The aggregate is a Flow Distinct because of the row goal.
One needs to be very careful with ANY aggregates in non-deterministic UPDATE statements because you may get incorrect results.
There is not enough detail in the question to make high quality recommendations, but:

Add an index like CREATE INDEX i ON dbo.table2 (id) INCLUDE (CID, CDate);
Use OPTION (QUERYTRACEON 4138) to disable the row goal, or OPTION (ORDER GROUP) to use a Stream Aggregate instead of Hash.
How you fix the non-deterministic UPDATE depends on the data relationships. The key point is to identify at most one row from the source that matches each target row. Typically, this will involve a unique index or constraint, or using ROW_NUMBER or TOP (1).

Step 2 may or may not be necessary. I add it for completeness.
You may find it easier to visualize the issues and tune the query by writing it in this form:
UPDATE TOP (100)
    xyz WITH (UPDLOCK, READPAST)
SET xyz.flag = 1
OUTPUT
    inserted.Rcode, inserted.EDR, inserted.id,
    abc.EID, abc.CID, abc.ENID, abc.Cdate
FROM dbo.table1 AS xyz
CROSS APPLY
(
    -- At most one source row per target row
    SELECT TOP (1)
        abc.*
    FROM dbo.table2 AS abc
    WHERE
        abc.id = xyz.id
    -- ORDER BY something to choose the one row
) AS abc
WHERE
    xyz.flag = 0;

Execution plan:

I probably wouldn't bother with a filtered index on table1, but if you did want to try it, this appears to be suitable:
CREATE INDEX i
ON dbo.table1 (Recid)
INCLUDE (id, flag)
WHERE flag = 0;

If you want to continue with the update syntax given in the question without addressing all the underlying issues properly, you may find this is faster:
UPDATE TOP (100) xyz
SET xyz.flag = 1
OUTPUT inserted.Rcode, inserted.EDR, inserted.id, abc.EID,abc.CID,abc.ENID,abc.Cdate
FROM dbo.table1 xyz WITH (UPDLOCK, READPAST)
INNER JOIN dbo.table2 abc WITH (NOLOCK, FORCESEEK)
on xyz.id=abc.id
WHERE xyz.flag = 0;


How to create Unicode parameter and variable names All of this works:

CREATE DATABASE [¯\_(ツ)_/¯];
GO
USE [¯\_(ツ)_/¯];
GO
CREATE SCHEMA [¯\_(ツ)_/¯];
GO
CREATE TABLE [¯\_(ツ)_/¯].[¯\_(ツ)_/¯]([¯\_(ツ)_/¯] NVARCHAR(20));
GO
CREATE UNIQUE CLUSTERED INDEX [¯\_(ツ)_/¯] ON [¯\_(ツ)_/¯].[¯\_(ツ)_/¯]([¯\_(ツ)_/¯]);
GO
INSERT INTO [¯\_(ツ)_/¯].[¯\_(ツ)_/¯]([¯\_(ツ)_/¯]) VALUES (N'[¯\_(ツ)_/¯]');
GO
CREATE VIEW [¯\_(ツ)_/¯].[vw_¯\_(ツ)_/¯] AS SELECT [¯\_(ツ)_/¯] FROM [¯\_(ツ)_/¯].[¯\_(ツ)_/¯];
GO
CREATE PROC [¯\_(ツ)_/¯].[sp_¯\_(ツ)_/¯] @Shrug NVARCHAR(20) AS SELECT [¯\_(ツ)_/¯] FROM [¯\_(ツ)_/¯].[vw_¯\_(ツ)_/¯] WHERE [¯\_(ツ)_/¯] = @Shrug;
GO
EXEC [¯\_(ツ)_/¯].[¯\_(ツ)_/¯].[sp_¯\_(ツ)_/¯] @Shrug = N'[¯\_(ツ)_/¯]';
GO

But you can probably see where I'm going with this: I don't want @Shrug, I want @¯\_(ツ)_/¯.

Neither of these work on any version from 2008-2017:

CREATE PROC [¯\_(ツ)_/¯].[sp_¯\_(ツ)_/¯] @[¯\_(ツ)_/¯] NVARCHAR(20) AS SELECT [¯\_(ツ)_/¯] FROM [¯\_(ツ)_/¯].[vw_¯\_(ツ)_/¯] WHERE [¯\_(ツ)_/¯] = @[¯\_(ツ)_/¯];
GO
CREATE PROC [¯\_(ツ)_/¯].[sp_¯\_(ツ)_/¯] [@¯\_(ツ)_/¯] NVARCHAR(20) AS SELECT [¯\_(ツ)_/¯] FROM [¯\_(ツ)_/¯].[vw_¯\_(ツ)_/¯] WHERE [¯\_(ツ)_/¯] = [@¯\_(ツ)_/¯];
GO

So, is there a way to use unicode stored procedure parameter names?

Well, identifiers are always Unicode / NVARCHAR, so technically you can't create anything that doesn't have a Unicode name 🙃.

The problem you are having here is due entirely to the classification of the character(s) being used. The rules for regular (i.e. non-delimited) identifiers are:

First letter must be:

A letter as defined by the Unicode Standard 3.2.
underscore (_), at sign (@), or number sign (#)

Subsequent letters can be:

Letters as defined in the Unicode Standard 3.2.
Decimal numbers from either Basic Latin or other national scripts.
underscore (_), at sign (@), number sign (#), or dollar sign ($)

Embedded spaces or special characters are not allowed.
Supplementary characters are not allowed.

I bolded the only rules that matter in this context. The reason that the "First letter" rules are not relevant here is that the first letter in all local variables and parameters is always the "at sign" @.

And to be clear: what is considered a "letter" and what is considered a "decimal digit" is based upon the properties that each character is assigned in the Unicode Character Database. Unicode assigns many properties to each character, such as: is_uppercase, is_lowercase, is_digit, is_decimal, is_combining, etc, etc. This is not a matter of what we mortals would consider letters or decimal digits, but which characters have been assigned these properties. These properties are often used in Regular Expressions to match on "punctuation", etc. For example, \p{Lu} matches any upper-case letter (across all languages / scripts), and \p{IsDingbats} matches any "Dingbats" character.

So, in your attempt to do:

DECLARE @¯\_(ツ)_/¯ INT;

only the _ (underscore or "low line") and ツ (Katakana Letter Tu U+30C4) characters fit into those rules. Now, all of the characters in ¯\_(ツ)_/¯ are fine for delimited identifiers, but unfortunately it seems that variable / parameter names and GOTO labels cannot be delimited (although cursor names can be).

So, for variable / parameter names, since they cannot be delimited, you are stuck with using only characters that qualify as being either "letters" or "decimal digits" as of Unicode 3.2 (well, according to the documentation; I need to test if classifications have been updated for newer versions of Unicode since classifications are handled differently than sort weights).

HOWEVER #1, things are not as straight-forward as they should be. I have now been able to complete my research and have found that the stated definition is not entirely correct. The precise (and verifiable) definition of which characters are valid for regular identifiers is:

First character:

Can be anything classified in Unicode 3.2 as "ID_Start" (which does include "Letters" but also "letterlike numeric characters")
Can be _ (low line / underscore) or ＿ (fullwidth low line)
Can be @, but only for variables / parameters
Can be #, but if schema-bound object, then only for Tables and Stored Procedures (in which case they indicate that the object is temporary)

Subsequent characters:

Can be anything classified in Unicode 3.2 as "ID_Continue" (which includes "decimal" numbers, but also "spacing and nonspacing combining marks", and "connecting punctuation marks")
Can be @, #, or $
Can be any of the 26 characters classified in Unicode 3.2 as format control characters

(fun fact: the "ID" in "ID_Start" and "ID_Continue" stands for "Identifier". Imagine that ;-)

According to "Unicode Utilities: UnicodeSet":

Valid starting characters

[:Age=3.2:] & [:ID_Start=Yes:]

-- Test one "Letter" from each of 10+ languages, as of Unicode 3.2
DECLARE @ᔠᑥᑒᏯשፙᇏᆇᄳᄈლဪඤaൌgೋӁｳﺲﶨ   INT;
-- works

-- Test a Supplementary Character that is a "Letter" as of Unicode 3.2
DECLARE @ INT;-- Mathematical Script Capital W (U+1D4B2)
/*
Msg 102, Level 15, State 1, Line XXXXX
Incorrect syntax near '0xd835'.
*/

Valid continuation characters

[:Age=3.2:] & [:ID_Continue=Yes:]

-- Test various decimal numbers, but none are Supplementary Characters
DECLARE @६৮༦൯௫୫９ INT;
-- works (including some Hebrew and Arabic, which are right-to-left languages)

-- Test a Supplementary Character that is a "decimal" number as of Unicode 3.2
DECLARE @ INT; -- MATHEMATICAL DOUBLE-STRUCK DIGIT FOUR (U+1D7DC)
/*
Msg 102, Level 15, State 1, Line XXXXX
Incorrect syntax near '0xd835'.
*/
-- D835 is the first character in the surrogate pair D835 DFDC that makes up U+1D7DC

HOWEVER #2, not even searching the Unicode database can be that easy. Those two searches do produce a list of valid characters for those categorizations, and those characters are from Unicode 3.2, BUT the definitions of the various categorizations changes across versions of the Unicode Standard. Meaning, the definition of "ID_Start" in Unicode v 10.0 (what that search is using today, 2018-03-26) is not what it was in Unicode v 3.2. So, the online search cannot provide an exact list. But you can grab the Unicode 3.2 data files and grab the list of "ID_Start" and "ID_Continue" characters from there to compare to what SQL Server actually uses. And I have done this and confirmed an exact match to the rules I stated above in "HOWEVER #1".

The following two blog posts detail the steps taken to find the exact list of characters, including links to the import scripts:

The Uni-Code: The Search for the True List of Valid Characters for T-SQL Regular Identifiers, Part 1
The Uni-Code: The Search for the True List of Valid Characters for T-SQL Regular Identifiers, Part 2

Finally, for anyone that just wants to see the list and is not concerned with what it took to discover and verify it, you can find that here:

Completely Complete List of Valid T-SQL Identifier Characters
(please give the page a moment to load; it's 3.5 MB and almost 47k lines)

Regarding "valid" ASCII characters, such as / and -, not working: the issue has nothing to do with whether or not the characters are also defined in the ASCII character set. In order to be valid, the character has to have either the ID_Start or ID_Continue property, or be one of the few custom characters noted separately. There are quite a few "valid" ASCII characters (62 of the 128 total — mostly punctuation and control characters) that are not valid in "Regular" Identifiers.

Regarding Supplementary Characters: while they certainly can be used in delimited identifiers (and the documentation does not appear to be stating otherwise), if it is true that they cannot be used in regular identifiers, that is most likely due to them not being fully supported in built-in functions prior to Supplementary Character-Aware Collations were introduced in SQL Server 2012 (they are treated as two individual "unknown" characters), nor could they even be differentiated from each other in non-binary Collations prior to the 100-level Collations (introduced in SQL Server 2008).

Regarding ASCII: 8-bit encodings are not being used here since all identifiers are Unicode / NVARCHAR / UTF-16 LE. The statement SELECT ASCII('ツ'); returns a value of 63 which is a "?" (try: SELECT CHAR(63); ) since that character, even if prefixed with an upper-case "N", is certainly not in Code Page 1252. However, that character is in the Korean Code Page and it produces the correct result, even without the "N" prefix, in a Database with a Korean default Collation:

SELECT UNICODE('ツ'); -- 12484

Regarding the first letter affecting the outcome: this is not possible since the first letter for local variables and parameters is always @. The first letter that we get to control for these names is actually the 2nd character of the name.

Regarding why local variable names, parameter names, and GOTO labels cannot be delimited: I suspect this is due to these items being a part of the language itself and not something that will find its way into a system table as data.


How to create an aggregate to find min and max value of an integer column and return output in text format? I have a function where I am specifying the table and column but I require an aggregate that can work for any table and column for similar output
create function min_max_val()
returns text
 LANGUAGE plpgsql
 AS $$
  declare
  mmv text;
  begin
  select format('%s - %s',min(id),max(id) )min_max_vals into mmv from towns;
  return mmv;
 end;
$$ ;

    min_max_vals
---------------------
 1000001 - 11000000

you need to dynamic sql as seen below
as seen in the last query, you need to use the result of the query and also address the return values

CREATE TABLE towns ("id" int)

INSERT INTO towns VALUEs (1),(2),(3)

select format('%s - %s',min(id),max(id) )min_max_vals  from towns;

| min_max_vals |
| :----------- |
| 1 - 3        |

create function min_max_val( cols text,tables text)
returns SETOF RECORD
 LANGUAGE plpgsql
 AS $$

  begin

      RETURN QUERY EXECUTE 'select format(''%s - %s'',min('||$1||E'),max('||$1||E') ) AS min_max_vals from '||$2||E';';

 end
$$ ;

SELECT min_max_vals
FROM
    min_max_val('id','towns')
    AS t( min_max_vals tEXT)

| min_max_vals |
| :----------- |
| 1 - 3        |

db<>fiddle here


Precedence constraint does not run in SSIS package Hope you are doing well.
I have a Precedence constraint that does not work . Putting it into more clear way ,  the package is executed successfully up until the components in which I have the precedence constraint as you can see here:

I call a procedure in the component before the "precedence and constraint" component and when I execute the procedure in SSMS it gives me the correct result . But I have no  idea why it does not work in here.
Here are some other pictures related to this issue:

When you run the stored procedure from the Execute SQL Task add the OUTPUT keyword to the parameter that will be returned.  For instance

EXEC dbo.OpenExcel ?, ?, ? OUTPUT


Upgrade SQL Server 2012 Express to Evaluation Edition Is there a way to upgrade an instance of SQL Server 2012 Express to SQL Server Evaluation Enterprise?

I tried using the SQL Server Installation Center and clicking Maintenance, and then Edition Upgrade. I selected to upgrade the Express Instance to Evaluation, but I get an error on the Edition Upgrade Rules step, which says "The selected SQL Server instance does not meet upgrade matrix requirements." I also checked out the Supported Version and Upgrade Paths on Supported Version and Edition Upgrades and Express 2012 to Enterprise 2012 is supported, so it would make sense that Express 2012 to Evaluation Enterprise 2012 should be supported especially since they are both free and Express has less features than Enterprise.
Am I doing something wrong? Is there a better way to perform this upgrade? Thanks,

You can not Upgrade from SQL Server Express Version to SQL Server Evaluation.

See this msdn link for all upgrade path...

Supported Version and Edition Upgrades...

Install them side by side and they you copy wizard to move DB.


I can't restart Vertica database I have database in Vertica on 4 nodes (RedHat OS). It was working until I decided to restart my DB. After nodes were shut down, they can't be UP anymore.

I try to start DB via adminTools: nodes are starting to initialize, but then they shut down. Using last good epoch or admintools -t start_db with --force key don't help.

In logs (dbLog, vertica.log) I see only one type of error:

SP_connect: unable to connect via UNIX socket  to /tmp/4803 (pid=12533):
Error: Connection Refused.

It is enough space in /tmp and iptables service is disabled.

Also I tried to create new database, so it was created and worked well.

kill all processes from Vertica :

ps -ef | grep vertica

This is avaialbe on all nodes.

Then  start your database without the force option

admintools  -t  restart_node  --hosts= --database=--password=

While starting the database make sure you tail the logs to capture the errors.

to get more solutions and Vertica fixes see this link


Is there possible chache query or save it in memory in MySql? Mayby Should I use something other? I have complicated query (some joins and ordering), that I want to execute very often. Results of query can change very rarely.

Query gets words form given language and category sorted by frequency, so it have two parameters - language and category.

I heart about materialized views, or databases in memory, bu I do not have experience enough to consider cons and props in reasonable way.

I want to have possibility execute this query for these parameters, and next not execute it until I decide manually, that it must be refreshed. In practice, all application based on executing this query many times, so I do not know if can I create one view in memory, when database use innodb.

I can change mysql on something else, and innodb too if is it recommended.

Let's try the Query cache first.  Add these to my.cnf, then restart mysql:

query_cache_type = ON
query_cache_size = 50M

If that does not work adequately, we can discuss alternatives.


Update/fix or recreate old database My employer is currently using a database build in Borland Visual dBase 5.5 that has been running on a Windows 95 machine for the last 20 years. I am trying to figure out my best course of action to bring this database into the 21st century, but my knowledge of databases is beginner at best and I'm looking for advice. The problem is compounded by the fact that the original designer had a massive stroke before she was able to complete it. It was functional enough for my employer to use it and he has been by using work-arounds for the few things that just do not function 100% correctly. There are a good number of quality of life features that I'd like to implement as well.

Current options I've come up with:

Move the database to a virtual machine on my Win10 machine, learn Visual dBase 5.5 and attempt to fix all the things that do not work correctly. Pros: Would not be starting from scratch. Cons: My understanding of VMs is that there will be difficulties transferring data from VM to host since the VM OS is pre-Win 2000.
Learn a newer database system and rebuild the database, hopefully being able to import the existing data. Re-entering data is an option. It would be me doing it, I don't want to but I will. Pros: Will be functional on current OS and database system/software will still have support. Cons: A lot more work creating the database and possibly re-entering data.

Other info that may be helpful:

There are multiple databases, two of which have 13,000 records each and are currently gaining 1,000-1,500 new records per database per year.
We are using the native Crystal Reports for direct printing of documents, but I would like the ability to edit the individual documents prior to printing when necessary if possible.

I have some programming background, mostly C++, but no real database creation/support knowledge. However, I'm willing to dive into whatever I need to work on this project, just not sure what is the best route at this point. I'm also open to suggestions for software/languages if I need to just start over.

Any suggestions are greatly appreciated!

Apparently there are updated copies of software called "dBase" but they seem to need special care, and money, to run on a modern OS.  Visual dBase 5.5 itself provides object-oriented and event-driven programming, but I think probably it's mainly based on dealing with one record (row) at a time in the data file (database or "table").  It provides the data storage, and also the program that a user needs to work with the data.

A modern equivalent system might consist of separate programs, a "database server" whose function is only to store data reliably and output it as required, and a "client" or "front end" which presents data in  user-friendly style, typically using the "SQL" language to send and retrieve data on the server (by sets, not individual rows, usually).  Another category of tool, and sometimes an expensive one, is "reporting".

Amongst questions to ask: how many people need to use the data simultaneously?  If it's more than one, then probably a client-and-server solution is best.  If one, you might be able to do the whole thing in Microsoft Excel.

Your employer may be able to buy a database application which serves the needs of your industry, and save the effort of re-learning the one that you've got or writing your own - your time presumably costs money as well.  But it won't be cheap.  A buy-in will probably be based on one of the industry-standard database servers that are known here, and you can probably convert your data into forms that the system will accept.  And the tool may provide definable fields for data that only your employer has thought of storing.

So, you could start by identifying any suitable database products for your industry, find out what database server they may use, and whether the server comes as a cheap or free download for tryout use - and then first of all, get that server, and try to import an initial copy of your data, your way, into the server.  That should move you towards converting the data for use in the new database product, or else writing your own program again, if you think you can do it.  If this exercise doesn't work, then, look at a different software and server package - or look at hiring a database consultant to help you.

But if you urgently need to replace the existing system, then using an updated version of dBase may be easier, after all.  It won't be an identical system to what you've got, but I suspect it doesn't represent twenty years of progress...  so maybe it won't be so different after all.


MongoDB replica set - no election after primary node failed For testing I killed the primary node of my two-node replica set while the cluster was under heavy write load.

I was expecting the secondary node would take over after a few seconds, but this isn't happening.

shard0:SECONDARY> rs.status()
{
    "set" : "shard0",
    "date" : ISODate("2014-10-22T15:11:52Z"),
    "myState" : 2,
    "members" : [
        {
            "_id" : 1,
            "name" : "10.128.42.177:27020",
            "health" : 1,
            "state" : 2,
            "stateStr" : "SECONDARY",
            "uptime" : 10270,
            "optime" : Timestamp(1413990107, 52),
            "optimeDate" : ISODate("2014-10-22T15:01:47Z"),
            "self" : true
        },
        {
            "_id" : 2,
            "name" : "10.128.42.188:27020",
            "health" : 0,
            "state" : 8,
            "stateStr" : "(not reachable/healthy)",
            "uptime" : 0,
            "optime" : Timestamp(1413990103, 49),
            "optimeDate" : ISODate("2014-10-22T15:01:43Z"),
            "lastHeartbeat" : ISODate("2014-10-22T15:11:50Z"),
            "lastHeartbeatRecv" : ISODate("2014-10-22T15:01:43Z"),
            "pingMs" : 0,
            "syncingTo" : "10.128.42.177:27020"
        }
    ],
    "ok" : 1
}

As you can see at the time of printing rs status the last heartbeat received was already 10 minutes old.

What am I doing wrong?

You are missing a node, either another data bearing node or an arbiter.

Here is why. A replica set needs a quorum > 50% of the original replica set members. Additionally, if a single node could decide to become primary, every network partitioning, every failing switch would result in a split brain situation, where two nodes assume they are primary.

Add an arbiter. They are cheap in terms of CPU, RAM and disk space, so you can run it on a small VM. Run it with

mongod --replSet shard0 --smallfiles --nojournal --noprealloc

which further decreases the need of space.

Do NOT run the arbiter on one of the other nodes - it would leverage it's purpose.


PostgresSQL: Cannot get to console under different user name I normally get to the console in UBUNTU by typing:

sudo -u postgres psql

But I need to delete a database owned by a different user.

When I type:

sudo -u username psql

It tells me "database "username" does not exist.

But... I wasn't trying to connect to a database, I was trying to connect as a user.

psql assumes you want to connect to the database named as the current user if you don't provide any login details.

But you can ovverride the defaults using parameters to psql, there is no need to use sudo in order to connect as a different username.

Simply run:

psql -U username -d databasename

where username is the user you want to use and databasename the database you want to connect to.

If you don't know which database you can connect to, use

psql -l

to show them all.

All this and a lot more is nicely documented in the manual: http://www.postgresql.org/docs/current/static/app-psql.html


How to make multiple counts in one query? I count records with queries like

SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%something%'
SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%another%'
SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%word%'

For each count, mysql needs to walk thoughout the table, and this is a big problem if having long table and numerous queries.

I wonder if there is a way to make all counts in one query. In this case, when mysql walks over each row, it will process all counts, and no need to scanning the entire table over and over again.

To get a count for each of those you can try

SELECT
    COUNT(CASE WHEN `col1` LIKE '%something%' THEN 1 END) AS count1,
    COUNT(CASE WHEN `col1` LIKE '%another%' THEN 1 END) AS count2,
    COUNT(CASE WHEN `col1` LIKE '%word%' THEN 1 END) AS count3
FROM `table1`;


Should I use many single field indexes, instead of specific multi column indexes? This question is about the effectiveness of a SQL Server indexing technique. I think it is known as "index intersection".

I'm working with an existing SQL Server (2008) application that has a number of performance and stability issues. The developers did some odd things with indexing.  I’ve not been able to get conclusive benchmarks on these issues, nor can I find any really good documentation on the internets.

There are many searchable columns on a table. The developers created a single column index on EACH of the searchable columns.  The theory was that SQL Server would be able to combine (intersect) each of these indexes to efficiently access the table in most circumstances.
Here is a simplified example (real table has more fields):

CREATE TABLE [dbo].[FatTable](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [col1] [nchar](12) NOT NULL,
    [col2] [int] NOT NULL,
    [col3] [varchar](2000) NOT NULL, ...

CREATE NONCLUSTERED INDEX [IndexCol1] ON [dbo].[FatTable]  ( [col1] ASC )
CREATE NONCLUSTERED INDEX [IndexCol2] ON [dbo].[FatTable] ( [col2] ASC )

select * from fattable where col1 = '2004IN'
select * from fattable where col1 = '2004IN' and col2 = 4

I think multiple column indexes targeted to search criteria are much better, but I may be wrong. I have seen query plans that show SQL Server doing a hash match on two index seeks. Perhaps this makes sense when you don’t know how the table is searched? Thanks.

What you need are covering indexes, ie. indexes that can satisfy a query on their own. But a 'covering' index has one problem: it is covering a specific query. So in order to develop a good indexing strategy, you need to understand your workload: what queries are hitting the database, which ones are critical and which ones are not, how often each type of query is run, etc etc etc. And then you balance this against the write and update cost of each index, and there you have your indexing strategy. If it sounds complicated that is because it is complicated.

However you can apply some rules of thumb. The MSDN covers the basics quite well:

Index Design Basics
General Index Design Guidelines
Clustered Index Design Guidelines
Nonclustered Index Design Guidelines

There is also a myriad of articles contributed by the community, eg. Webcast Recording – DBA Darwin Awards: Index Edition.

And to answer your question specifically: separate indexes on each column can work, provided that each column has a high selectivity (many distinct values, each values appearing only a few times in the database). The resulted access plan using hash join between two index range scans usually works quite well. Columns with low selectivity (few distinct values, each value appearing many times in the database) make no sense to be indexed on their own, the query optimizer will simply ignore them. However, low selectivity columns many times make good composite keys when they are paired with a high selectivity column.


PL/SQL: Selecting multiple values from the same column We have a table in which all kinds of different Words are stored with a unique id each. In other tables, certain fields which should contain words instead only contain the id of these words. Let's look at an example.
This is our example "words" table:
+---------+--------+
| word_id |   word |
+---------+--------+
|       1 |  Apple |
|       2 |  Melon |
|       3 |    Car |
|       4 |   Boat |
+---------+--------+

In another table (example: "rows"), there are references to that table:
+--------+----------+------------+
| row_id | fruit_id | vehicle_id |
+--------+----------+------------+
|      1 |        1 |          4 |
|      2 |        2 |          3 |
|      3 |        1 |          3 |
|      4 |        2 |          4 |
+--------+----------+------------+

For my SSRS Report, I need to select many rows of the "words" table and insert the value of the "word" column into the report. For now I have solved it by creating a different SQL query for each field using report parameters to select the id, but I was wondering if I could make the report more efficient by cutting down the number of SQL queries.
How I did it for now using our example:
SELECT
    rows.id,
    words.word AS FRUIT
FROM
    rows
INNER JOIN
    words ON
    rows.fruit_id = words.word_id
WHERE
    words.word_id = :FRUIT_ID
----------------------------------------
SELECT
    rows.id,
    words.word AS VEHICLE
FROM
    rows
INNER JOIN
    words ON
    rows.vehicle_id = words.word_id
WHERE
    words.word_id = :VEHICLE_ID

The ideal output for row = 1 would be:
+--------+-------+---------+
| row_id | fruit | vehicle |
+--------+-------+---------+
|      1 | Apple |    Boat |
+--------+-------+---------+

Sadly, I have not found a way to select multiple values of the same column in just one SQL statement.
I would be very grateful for any ideas or solutions, Thanks!
EDIT: I know the "words" table setup is not ideal and can have its own problems, however, I cannot do anything about them since the database is to be used as-is and I am not allowed to change anything.

You can reference the same table multiple times in your query with an alias to solve this issue.
For example:
SELECT ref.row_id, f.word as fruit, v.word as vehicle
  FROM Rows ref INNER JOIN Words f ON ref.fruit_id = f.id
                INNER JOIN Words v ON ref.vehicle_id = v.id
 WHERE f.id = :PRODUKTART_ID
   and v.id = :VEHICLE_ID


Aggregate query on multiple values of same field I am using SQL-Server 2012, I have a table of credit card transactions as follows:

CardScheme  RespCode
=========================
Visa        Declined
MasterCard  Approved
MasterCard  Approved
MasterCard  Declined
Visa        Declined
MasterCard  Approved
MasterCard  Declined
Visa        Approved
Visa        Approved
MasterCard  Approved

I need to write a query to get the following results

CardScheme  Declined  Approved
==============================
Visa        2         2
MasterCard  2         4

Please help

Since you are using SQL Server you can use the PIVOT function to convert rows into columns:

select cardScheme, Declined, Approved
from yt
pivot
(
  count(RespCode)
  for RespCode in (Declined, Approved)
) piv;

See SQL Fiddle with Demo


Table-Valued Parameter as Output parameter for stored procedure Is it possibile to Table-Valued parameter be used as output param for stored procedure ?

Here is, what I want to do in code

/*First I create MY type */
CREATE TYPE typ_test AS TABLE
(
     id int not null
    ,name varchar(50) not null
    ,value varchar(50) not null
    PRIMARY KEY (id)
)
GO

--Now I want to create stored procedu whic is going to send output type I created,
--But it looks like it is inpossible, at least in SQL2008
create  PROCEDURE [dbo].sp_test
         @od datetime
        ,@do datetime
        ,@poruka varchar(Max) output
        ,@iznos money output
        ,@racun_stavke  dbo.typ_test   READONLY --Can I Change READONLY with OUTPUT ?
AS
BEGIN
    SET NOCOUNT ON;

    /*FILL MY OUTPUT PARAMS AS I LIKE */

    end

No, unfortunately table value parameters are read-only and input only. This topic in general is covered very well in How to Share Data between Stored Procedures, which presents all the alternatives. My recommendation would be to use a #temp table.


Implementing subtype of a subtype in type/subtype design pattern with mutually exclusive subclasses Introduction
In order for this question to be useful for future readers I will use the generic data model to illustrate the problem I face.
Our data model consists of 3 entities, which shall be labeled as A, B and C. In order to keep things simple, all their attributes will be of int type.
Entity A has following attributes: D, E and X;
Entity B has following attributes: D, E and Y;
Entity C has following attributes: D and Z;
Since all entities share common attribute D, I have decided to apply type/subtype design.
Important: Entities are mutually exclusive! This means that entity is either A or B or C.
Problem:
Entities A and B have yet another common attribute E, but this attribute is not present in the entity C.
Question:
I would like to use the above described characteristic to further optimize my design, if possible.
To be honest, I have no idea how to do this, nor where to start trying, hence this post.

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

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

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

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


No connection could be made because the target machine actively refused it. MYSQL work bench? I have created php and Mysql site.Its Working Locally Xampp well. Finally I moved My site to server..i have Used Mysql Workbench. connected client database in Mysql Workbench in My system.when I Run the code its showing Error:

Validating Information....error with query:
SELECT ID,fname,lname,email,userlevel,user_group,orgID
FROM students
WHERE username='test'
AND password='123456'
No connection could be made because the target machine actively refused it.

You should check mysql user permission, specially the host field.
Mysql documentation


mysql need help optimizing query I have a table containing servers logs, and another table containing rules to match certain and drop them.

The logs tables contains around 1 million rows, and there are about 20-30 rules.

The query runs very slowly, I wonder is there any way I can make it run faster. I tried adding indexes to logs.message, but it does not help, I also read that you cannot index a "LIKE" column.

I am a total newbie to database, so please forgive me if I am missing any important concepts. Thanks in advance.

CREATE TABLE `logs` (
  `log_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `criticality` varchar(255) NOT NULL,
  `hostname` varchar(255) NOT NULL,
  `source` varchar(255) NOT NULL,
  `message` varchar(4096) NOT NULL,
  `record_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `record_by` varchar(255) NOT NULL,
  PRIMARY KEY (`log_id`),
  KEY `idx_message` (`message`(255))
) ENGINE=InnoDB AUTO_INCREMENT=233523 DEFAULT CHARSET=utf8$$

CREATE TABLE `rules` (
  `rule_id` int(11) NOT NULL AUTO_INCREMENT,
  `content` varchar(255) NOT NULL,
  `type` enum('MATCH','DROP') NOT NULL DEFAULT 'DROP',
  PRIMARY KEY (`rule_id`)
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8$$

select hostname, criticality, source, message, record_date
from eventlog.logs l1
where not exists (
    SELECT l.message, r.rule_id
    FROM eventlog.logs l,
         eventlog.rules r
    where l.message like r.content
      and l.log_id = l1.log_id
      and r.type = 'DROP'
) and (criticality = 'High'
        or criticality = 'Medium')
  and record_date > sysdate() - Interval 2 Day
order by l1.message;

UPDATE 1
explain results, it took around 10 seconds to finish the query.

+----+--------------------+-------+--------+---------------+---------+---------+--------------------+---------+-----------------------------+
| id | select_type        | table | type   | possible_keys | key     | key_len | ref                |  rows   | Extra                       |
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+---------+-----------------------------+
|  1 | PRIMARY            | l1    | ALL    | NULL          | NULL    | NULL    | NULL               | 2101642 | Using where; Using filesort |
|  2 | DEPENDENT SUBQUERY | r     | ALL    | NULL          | NULL    | NULL    | NULL               |      16 | Using where                 |
|  2 | DEPENDENT SUBQUERY | l     | eq_ref | PRIMARY       | PRIMARY | 8       | eventlog.l1.log_id |       1 | Using where                 |
+----+--------------------+-------+--------+---------------+---------+---------+--------------------+---------+-----------------------------+

I agree with @ypercubeᵀᴹ, the comma join in the exists clause can be removed:

select hostname, criticality, source, message, record_date
from eventlog.logs l1
where not exists (
    SELECT 1
    FROM eventlog.rules r
    where l1.message like r.content
      and r.type = 'DROP'
)
and criticality in ('High', 'Medium')
and record_date > sysdate() - Interval 2 Day
order by l1.message;

I also replaced the selected columns in the subquery with 1 since it really does not matter what you select, and replaced the OR predicate with IN.


1822, "Failed to add the foreign key constraint. Missing index for constraint I have 3 tables: users, photos, avatars (photo of user_1 that currently see user_2)
when user deleted, all his photos and avatars, that shown to other users also removes
What I do wrong?

cursor.execute("""CREATE TABLE IF NOT EXISTS users(
id int AUTO_INCREMENT PRIMARY KEY,
user_id INTEGER NOT NULL UNIQUE,
goal VARCHAR(255) DEFAULT NULL,
gender VARCHAR(255) DEFAULT NULL,
dob DATE DEFAULT NULL,
country VARCHAR(255) DEFAULT NULL,
city VARCHAR(255) DEFAULT NULL)""")

cursor.execute("""CREATE TABLE IF NOT EXISTS photos(
id INTEGER AUTO_INCREMENT,
user_id INTEGER NOT NULL,
photo VARCHAR(255) NOT NULL,
KEY (id),
PRIMARY KEY (user_id, photo),
FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE)""")

cursor.execute("""CREATE TABLE IF NOT EXISTS avatars (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
user_id INTEGER,
avatar VARCHAR(255),
shower_id INTEGER,
FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE,
FOREIGN KEY (avatar) REFERENCES photos (photo) ON DELETE CASCADE,
FOREIGN KEY (shower_id) REFERENCES users (user_id) ON DELETE CASCADE)""")

But I get an error

1822, "Failed to add the foreign key constraint. Missing index for constraint 'avatars_ibfk_2' in the referenced table 'photos'")

mysqlsh.exe --version
C:\Program Files\MySQL\MySQL Shell 8.0\bin\mysqlsh.exe   Ver 8.0.19 for Win64 on x86_64 - for MySQL 8.0.19 (MySQL Community Server (GPL))

Your problem with the table photos

is that you want to make qa reference to photo varchar(255) NOT NULL

But in mysql you need that column to be INDEXED
So make your query like this

cursor.execute("""CREATE TABLE IF NOT EXISTS users(
id int AUTO_INCREMENT PRIMARY KEY,
user_id INTEGER NOT NULL UNIQUE,
goal VARCHAR(255) DEFAULT NULL,
gender VARCHAR(255) DEFAULT NULL,
dob DATE DEFAULT NULL,
country VARCHAR(255) DEFAULT NULL,
city VARCHAR(255) DEFAULT NULL)""")

cursor.execute("""CREATE TABLE `IF NOT EXISTS photos` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `photo` varchar(255) NOT NULL,
  PRIMARY KEY (`user_id`,`photo`),
  KEY `id` (`id`),
  KEY `photo` (`photo`),
  CONSTRAINT `photos_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
""")

cursor.execute("""CREATE TABLE IF NOT EXISTS avatars (
id INTEGER AUTO_INCREMENT PRIMARY KEY,
user_id INTEGER,
avatar VARCHAR(255),
shower_id INTEGER,
FOREIGN KEY (user_id) REFERENCES users (user_id) ON DELETE CASCADE,
FOREIGN KEY (avatar) REFERENCES photos (photo) ON DELETE CASCADE
,FOREIGN KEY (shower_id) REFERENCES photos (id) ON DELETE CASCADE
)""")

there was another problem, that you referende the same userid to user_id and shower_id, that also gets you an error, bit i beleive you are missing only a showwer table.


How to dump an advanced revelation database? Retail store I work with has an ancient inventory/accounting/receiving system that is based off Advanced Revelation.  They run the DOS and Windows clients.  I'd like to get a dump of their data to work on migration or at least create other views of their data, without paying $$$ for the vendor do to this service.

I can find very little documentation of Advanced revelation.  Can anyone help?

Advanced Revelation is a multi-valued database based on the original PICK operating system.  You can find information on PICK and it's spin-offs here:

https://en.wikipedia.org/wiki/Pick_operating_system

I've been developing on PICK and like systems for over 35 years, including Advanced Revelation.  Manuals are a little difficult to find online. You may also have some luck finding more information here:

http://www.jes.com


SQL Server Database Mail - Why can't I run an execute against xp_sysmail_activate? SQL Server 2008 R2 Standard x64

I can't send e-mail from this SQL Server (out of MANY that are configured pretty much identically AFAIK). I have created the mail profile and account. However, any time I tried to send an e-mail with sp_send_dbmail (with my account - in sysadmin role) or Database Mail->"Send Test E-Mail", nothing happens. Nothing goes into sysmail_log. The SQL Server error below is generated:

 The activated proc '[dbo].[sp_sysmail_activate]' running on queue 'msdb.dbo.ExternalMailQueue' output the following:  'The EXECUTE permission was denied on the object 'xp_sysmail_activate', database 'mssqlsystemresource', schema 'sys'.'

Details

msdb.dbo.sysmail_mailitems has a record for each mail
The database mail log is empty  (msdb.dbo.sysmail_log)
msdb.dbo.sysmail_sentitems is empty
I have run sysmail_stop_sp and sysmail_start_sp
"is_broker_enabled" is set to "1" in msdb

How is the execute permission denied when I am part of the sysadmin role? Any suggestions on what could be happening?

You aren't the one getting the error, the activated stored procedure is.  That procedure is running under a different account than yours.

By default that procedure will run as what ever account is setup as the dbo of the msdb database, unless someone changed it of course.  Best guess is that the account which is dbo of the msdb database isn't a member of the sysadmin fixed server role, or the activation principal of that queue has been changed (querying sys.service_queues will tell you this).


Replacing Database in High Availability Group Enviroment Looking for some advice regarding replacing a database within a SQL 2012 High Availability Group environment setup as synchronous with auto failover. A little bit of background on the reason for this.

Every couple of months we get a MDB database from a supplier which then need to be imported into a SQL database. This is done by 2 step job that first truncates current database and then recreates and then a SSIS package is run to fill the database.

As I'm trying to automate the full process any suggestions and advice would be greatly appreciated.

I know I need to first remove the database from the availability group and then apply the changes, but was interested to find out what the best approach with the secondary is and best practice. Im guessing I can either delete the database from the secondary and then add it back into the Availability group or is it better to take a backup of primary and restore over secondary before adding back to HA

With SQL Server 2012, there is only one way to synchronize a database when it is added to an availability group--backup and restore.  So you will need to drop the database from the secondary and restore the primary's backup (or restore with overwrite) when adding the database to the availability group.

With SQL Server 2016 and later, automatic seeding is available for initial synchronization, so the backup/restore process can be skipped if seeding is enabled on the AG.


How to detect bad execution plans automatically? I want to automate a way to detect when a stored procedure plan changed for worse. I am planning to do it using a list of critical SPs and checking when their plans changed. Then compare the execution metrics with the previous executions collected in extended events.

Does anyone have any recommendations to monitor plans without using a lot of server resources?

I've had that problem for only on query:
1. Scheduled job was looking in query cache for particular query.
2. When query is found it looked for bad pattern in plan. For instance: Index Seek for index XYZ.
3. If bad pattern is found, that query will be automatically deleted from query cache by it's plan-handle using DBCC FREEPROCCACHE


Duplicate key errors in Postgres queue-like table when rows should be locked by SELECT FOR UPDATE SKIP LOCKED I have a table called queue with items that need to be processed:
CREATE TABLE public.queue (
    id serial NOT NULL
        CONSTRAINT queue_pkey
            PRIMARY KEY
);

Another table process represents processed items from the queue (e.g. a report has been generated). In reality, there are more such tables (there are more processes that need to be performed on an item). There is one-to-one relation between queue and process – each item can be processed just once.
CREATE TABLE public.process (
    id            serial  NOT NULL
        CONSTRAINT process_pkey
            PRIMARY KEY,
    queue_item_id integer NOT NULL
        CONSTRAINT process_queue_item_id_key
            UNIQUE
        CONSTRAINT process_queue_item_id_8953ec7b_fk_datastore
            REFERENCES public.queue
            DEFERRABLE INITIALLY DEFERRED
);

Here are some test data:
BEGIN;
TRUNCATE TABLE queue, process
    RESTART IDENTITY CASCADE;
INSERT INTO queue
SELECT GENERATE_SERIES(1, 10000);
COMMIT;

The worker that processes the items is implemented as follows. The code is in Django (Python framework), but I am convinced that my error is not caused by Django or its ORM.
(For simplicity, there is no termination condition.)
while True:
    with transaction.atomic():
        queue_items = Queue.objects \
                          .filter(process=None) \
                          .order_by() \
                          .select_for_update(skip_locked=True, of=('self',))[:8]

        print('Generating report...')
        time.sleep(0.1)

        Process.objects.bulk_create(
            (Process(queue_item=q)
             for q in queue_items)
        )

Here is a transcript of the SQL queries that travel to the database:
-- while True:

BEGIN;

SELECT queue."id"
FROM queue
         LEFT OUTER JOIN "process"
                         ON (queue."id" = "process"."queue_item_id")
WHERE "process"."id" IS NULL
LIMIT 8 FOR UPDATE OF queue SKIP LOCKED;

-- print('Generating report...')
-- time.sleep(0.5)

INSERT INTO "process" ("queue_item_id")
VALUES (1),
       (2),
       (3),
       (4),
       (5),
       (6),
       (7),
       (8)
RETURNING "process"."id";

COMMIT;

If I start one worker, the queue is processed perfectly fine. If I run two or more workers, I start getting this error:
duplicate key value violates unique constraint "process_queue_item_id_key"
DETAIL:  Key (queue_item_id)=(**) already exists.

How another transaction could create rows in process for items in a queue when those rows are locked?
What I tried:

I tried to rewrite SELECT query with EXISTS:

SELECT "queue"."id"
  FROM "queue"
 WHERE NOT (EXISTS(SELECT U0."id", U0."queue_item_id" FROM "process" U0 WHERE U0."queue_item_id" = "queue"."id"))
 LIMIT 8
   FOR UPDATE OF "queue" SKIP LOCKED

without success, the same error occurs.

If I arrange the rows randomly, the error occurs much later (almost at the end of the queue).

SELECT "queue"."id"
  FROM "queue"
  LEFT OUTER JOIN "process"
    ON ("queue"."id" = "process"."queue_item_id")
 WHERE "process"."id" IS NULL
 ORDER BY RANDOM()
 LIMIT 8
   FOR UPDATE OF "queue" SKIP LOCKED

I put a breakpoint in the middle of the transaction and in another transaction I checked that the row locking in my opinion works correctly:

SELECT id
FROM queue
WHERE id NOT IN (
    SELECT id
    FROM queue
        FOR UPDATE SKIP LOCKED
);

My Postgres version: PostgreSQL 13.1, compiled by Visual C++ build 1914, 64-bit.
Each worker has its own connection to Postgres with default isolation level (read committed).

When a process commits, the locks it held are immediately released to all.  But the rows it inserted are not visible to other snapshots which started before it committed.  So the just-unlocked rows are eligible for someone else to lock, when that someone else is not able to see the inserted rows that would cause them not to select the just-unlocked row.  When you select a row FOR UPDATE, the anticipation is that you will update that row.


Toad 9.7.2 for Oracle hangs on Windows 7 I am using Toad 9.7.2 on Windows 7 64 bit.

It is generally working fine but there is one problem. When I click on certain tables in "Schema Browser" and then click on "Data" tab to see records in them then Toad hangs.

This is only happening for some tables and most of them are working fine. If I run SELECT query to retrieve data from those problemetic tables then it also runs fine. Problem only happens when I try to view records by clicking on "Data" tab.

I thought may those tables contain huge number of records but that's not the case. One of the table only contain 25 records but still Toad still hangs and then I have to kill the process.

Any idea why this is happening and how to solve this?

EDIT

Found one clue. This is only happening for those tables which have Parent table.

You know there is another tab called "Referential" which shows both Child Tables i.e. "table is referenced by" and Parent Tables "table references". So if a table has parent table then Toad hangs when I click on "Data" tab for that table.

I fixed the issue by going into Configure TOAD Options --> Schema Browser --> Data and Grids and there I unchecked Enable FK Lookup


Investigating rollbacks in Postgres A Postgres monitoring script, check_postgres.pl, is warning me that a large number of rollbacks are occurring on a database server. How can I investigate what queries are being rolled back? I have tried searching the Postgres log file but it doesn't contain any instances of the word "rollback" and I can't see anything in the Postgres manual about enabling rollback logging.

I wouldn't expect the mere presence of a rollback to be a problem.  There are all kinds of reasons this might happen and nearly all are harmless.  A good rule of thumb is that if the application requested a roll back, it is harmless at least for the DBA...

The key is to log statements in errors (which would force rollbacks) and investigate those.  The other possibility which is possibly concerning is in serialization failures (if using serializable transactions) but here the application has the responsibility of handling the rollback.


Query distinct then select option then choose and query choosed as html result on page Structure of database "diary":

id------user_name-----day-----month------post
1--------erik-------- 3--------dec-------something
1--------erik---------3--------dec-------12345
1--------erik---------2--------dec-------kkkkk
1--------erik---------6--------jan-------ok
2--------john---------1---------dec------testing

Now I need to get unique month for user $login_session and show him select option. I managed to get distinct select option query from the database.
So here it is, and it's working:

$sql = "SELECT DISTINCT  month FROM `diary`
    where user_name = '$login_session' order by month";
$result = mysql_query($sql);

while($row = mysql_fetch_assoc($result)){
    echo '<option value="'.$row['month'].'">'.$row['month'].'</option>';
}

When user erik login he got:

dec
jan

...as option. The problem is when user login and see top option value on select to list his data. Example:

Let's say he choose dec, I need to get his output in html form:

 id----day ------- month ------- posts ------------
  1----3 ------- dec -------  something  -------
  1----3 ------- dec -------  12345 -------
  1----2 ------- dec -------  kkkkk  -------

Basically, I am trying to list logged user all posts by month select.

I manage to solve the problem, here is the code for others who need help.

$sid=$_GET['month'];
$sql = "SELECT * FROM diary where user_name = '$login_session'
    and month= '$sid' ORDER BY day";
$result = mysql_query($sql);
while ($rs=mysql_fetch_object($result)){

and then just wrap in tables:

echo $rs->day;
echo $rs->post;
}


How can I fix "Cannot perform a differential backup ... a current database backup doesn't exist"? We recently switched to the FULL recovery model, with full backups being done every weekend, and differentials being taken every day.

The problem is, the differential backups don't always seem to work. The error message recorded in the job history is

  Executed as user: username. ... 9.00.3042.00 for 32-bit
  Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
  Started:  11:20:12 AM  Progress: 2013-01-25 11:20:13.90     Source:
  {450389BA-54C2-4892-9CD0-0126CA9B0ED8}      Executing query "DECLARE
  @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp".: 100% complete  End
  Progress  Error: 2013-01-25 11:20:14.40     Code: 0xC002F210
  Source: Back Up Database (Differential) Execute SQL Task
  Description: Executing the query "BACKUP DATABASE [MyDatabase] TO
  DISK = N'E:\Database Backups\MyDatabase_backup_201301251120.diff'
  WITH  DIFFERENTIAL , NOFORMAT, NOINIT,  NAME =
  N'MyDatabase_backup_20130125112014', SKIP, REWIND, NOUNLOAD,  STATS =
  10  " failed with the following error: "Cannot perform a differential
  backup for database "MyDatabase", because a current database backup
  does not exist. Perform a full database backup by reissuing BACKUP
  DATABASE, omitting the WITH DIFFERENTIAL option.   BA...  The package
  execution fa...  The step failed.

The full backup job completes successfully every time, and I can view it in msdb.dbo.backupset using the query found here, so I know it exists. And it looks like after a reboot, the differential back up does complete successfully up until the next full backup.

Here's a look at the job history I have:

1/16 - Full Backup - Success
1/17 - Differential Backup - Success
1/18 - Differential Backup - Success
1/19 - Differential Backup - Success
1/20 - Full Backup - Success
1/21 - Differential Backup - Failure
1/21 - Differential Backup - Failure
1/22 - Full Backup - Success
1/22 - Differential Backup - Failure
1/22 - Reboot
1/23 - Differential Backup - Success
1/23 - Reboot
1/24 - Differential Backup - Success
1/25 - Full Backup - Success
1/25 - Differential Backup - Failure

The reboots were due to an unrelated error where @@SERVERNAME was null, so I held off posting this question until I found out if fixing the @@SERVERNAME fixed the problem, but it didn't and now I have no idea where to start.

What can cause SQL Server not to recognize a full backup for a database, and how can I fix it?

Also I'm not sure if this is related, but the differential backups seem rather large, and I am wondering if they're running the differential off the full database backup taken in the very beginning (on 1/16) instead of the most recent full backup. The full backups are about 260GB, and the differentials from 1/23-1/24 are at 30GB each.

The jobs were setup using the SQL Server Maintenance Plan Wizard, and I'm using SQL Server 2005

The [View T-SQL] button for the Maintenance Plan returns this code:

Full Backup:

BACKUP DATABASE [MyDatabase]
TO DISK = N'E:\Database Backups\MyDatabase_backup_2013_01_25_140607_2543780.bak'
WITH NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD, STATS = 10,
    NAME = N'MyDatabase_backup_2013_01_25_140607_2523778'

Differential:

BACKUP DATABASE [MyDatabase]
TO DISK = N'E:\Database Backups\MyDatabase_backup_2013_01_25_140537_4073936.diff'
WITH DIFFERENTIAL, NOFORMAT, NOINIT, SKIP, REWIND, NOUNLOAD,  STATS = 10,
    NAME = N'MyDatabase_backup_2013_01_25_140537_4053934'

This is very closely related to another question I have, however it was suggested in chat that I post a new question instead of editing the other one due to the questions being different and the existing answers on the other question.

If something is taking a snapshot behind the scenes that breaks SQL differential backups causing a full to need to be taken before a new differential.  You can either stop whatever is taking the snapshot or ensure you're taking a full backup afterwards before you try to do a differential.  If you use Ola Hallengren's Maintenance Solution that can automatically change a diff to a full when it runs into this scenario.


check if tables has same columns I have a Database that creates day tables and some of the tables don't have the same number of columns and in some cases the columns aren't the same.  What I'm needing to do is consolidate all the tables into a single table.  I'm not quite sure how to do this, but I'm assuming I need to make sure all the tables have the same columns and then add all the data from the multiple tables to the single table should be fairly easy.

so my first step, find out which tables are different and which ones are the same.

what I need is to find out if a table has the same columns as another.  not the data, but rather like the column names and possibly the data type for that column.

any help would be greatly appreciated.  Thank you.  I'm manually going through these and checking the content, but would definitely like something that would be much quicker.

UPDATE

the table's have a date related naming scheme.  here's one for an example: repo_2012_08_04

A simple approach if all you care about is column names and merely identifying any tables that have the same naming scheme but have columns that don't exist in the "model" or "ideal" table (also, I kept this simple, assuming all tables are in dbo):

USE tempdb;
GO

CREATE TABLE dbo.repo_2012_08_04(a INT, b INT); -- model
CREATE TABLE dbo.repo_2012_08_05(a INT, b INT); -- same schema as model
CREATE TABLE dbo.repo_2012_08_06(a INT, c INT); -- different
CREATE TABLE dbo.repo_2012_08_07(a INT, b INT, c INT); -- different
GO

DECLARE @model SYSNAME = N'repo_2012_08_04';

SELECT t.name FROM sys.tables AS t
WHERE name LIKE N'repo[_]%'
AND name <> @model
AND EXISTS
(
  SELECT 1 FROM sys.columns AS c
    WHERE [object_id] = t.[object_id]
    AND NOT EXISTS
    (
      SELECT 1 FROM sys.columns
      WHERE [object_id] = OBJECT_ID(N'dbo.' + QUOTENAME(@model))
      AND name = c.name
    )
);
GO

DROP TABLE repo_2012_08_04, repo_2012_08_05,
           repo_2012_08_06, repo_2012_08_07;
GO

Results:

repo_2012_08_06
repo_2012_08_07