Pick one single row each time from either table? I will run a query and get a list of aID and based on that I will try to match below tables.For each aID I just want one record either from tblGAlert or tblEAlert based on gDateTime and eDateTime depends on which time comes first.

CREATE TABLE IF NOT EXISTS `tblGAlert` (
  `gAlertID` int(11) NOT NULL AUTO_INCREMENT,
  `eID` int(5) NOT NULL,
  `aID` int(5) NOT NULL,
  `gEntryStatus` enum('Do','Ad','Ej') NOT NULL,
  `gDateTime` datetime NOT NULL,
  `gInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `gMessage` varchar(255) NOT NULL,
   PRIMARY KEY (`gAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `tblEAlert` (
  `eAlertID` int(11) NOT NULL AUTO_INCREMENT,
  `eID` int(5) NOT NULL,
  `aID` int(5) NOT NULL,
  `eDateTime` datetime NOT NULL,
  `eInsertDateTime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `eMessage` varchar(255) NOT NULL,
  PRIMARY KEY (`eAlertID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Sample data I say for tblGAlert

1,12,1122,'Do',2013-07-13 14:30:19,2013-07-13 15:30:19,''
2,13,1122,'Ad',2013-07-13 14:35:19,2013-07-13 15:35:19,''
3,13,1122,'Ad',2013-07-13 14:38:19,2013-07-13 15:39:19,''
4,14,1122,'Ej',2013-07-13 14:45:19,2013-07-13 15:55:19,''
5,14,1122,'Ej',2013-07-13 14:50:19,2013-07-13 15:56:19,''

Same data for tblEAlert.

1,1,1122,2013-07-13 14:33:19,2013-07-13 15:35:19,''
2,5,1122,2013-07-13 14:36:19,2013-07-13 15:36:19,''
3,6,1122,2013-07-13 14:37:19,2013-07-13 15:39:19,''
4,7,1122,2013-07-13 14:48:19,2013-07-13 15:55:19,''
5,8,1122,2013-07-13 14:52:19,2013-07-13 15:56:19,''

The output I want to merge them together in such a way arrange by the gDateTime and eDateTime. Any idea how to merge them ?

I have tried below script but the answer I get is not right and it hogs my db server.

SELECT X . *
FROM (

SELECT gAlertID,  'gType' AS PTYPE, gDateTime AS DATE
FROM tblGAlert
WHERE tblGAlert.aID =2494
UNION
SELECT eAlertID,  'eType' AS PTYPE, eDateTime AS DATE
FROM tblEAlert
WHERE tblEAlert.aID =2494
)X
ORDER BY X.`date`

First of all, this is my first post on dba.stackexchange.com, so I apologize in advance if there is any etiquette that I fail to follow, and am open to your input as to how I can improve future posts.

Best database design practices would call for consolidating the two very similar tables into just one table, like sa555 described. The issue you are currently facing is the direct consequence of having these two tables separate.

However, since you are working with a live system, I would recommend asking yourself and/or the rest of your development team (if applicable) the following question:

Will the future benefits of consolidating these two tables outweigh the time and effort put into the process of consolidating the two tables? Keep in mind that any applications depending on these two tables being separate will need to be updated also.

If the benefits outweigh the costs, I would recommend consolidating the two tables, just as sa555 recommended.
If not, I have included the MySQL code below to create a stored procedure that will return the most recent row from either table. Just remember that this is a mere surface-level fix, and that your database still contains a design flaw that could impede future development.

Here are some important notes on the procedure's edge-case behavior:

If both tables are empty, the procedure will not return a result set at all.
If only one of the tables contains records, the most recent record from the non-empty table will be returned.
If the most recent records from both tables have the exact same date and time, the record from tblGAlert will be returned. You can return the tblEAlert record by changing the inequality from ">=" to ">". (comments will explain where to do this)
Since both tables have different column names, you may want to canonicalize the column names in this procedure's result set using column name aliases (e.g. SELECT eDateTime AS AlertDateTime). The comments I have inserted into the code will direct you as to where to insert the aliases.

SQL Query (creates stored procedure):

DELIMITER $$
CREATE PROCEDURE GetLatestAlert()
BEGIN
-- Select latest gAlert record
SELECT gDateTime, gAlertID INTO @gAlertDateTime, @gID FROM tblGAlert ORDER BY gDateTime DESC LIMIT 1;

-- Select latest eAlert record
SELECT eDateTime, eAlertID INTO @eAlertDateTime, @eID FROM tblEAlert ORDER BY eDateTime DESC LIMIT 1;

-- We still want to be able to return a record in the event
-- that one of the tables does not contain a record, so we make
-- the default @gAlertDateTime and @eAlertDateTime values equal to the earliest
-- MySQL date possible, instead of the default value of null (if
-- no records were returned from that respective table). This is
-- because the NULL value cannot be compared using the ">" operator
-- that appears later on in this procedure.
IF @gAlertDateTime IS NULL THEN
    SET @gAlertDateTime = "1000-01-01 00:00:00"; -- MySQL earliest date value
END IF;

IF @eAlertDateTime IS NULL THEN
    SET @eAlertDateTime = "1000-01-01 00:00:00"; -- MySQL earliest date value
END IF;

-- Before comparing the dates, make sure one or both of the tables contain(s) a record.
IF @gID IS NOT NULL OR @eID IS NOT NULL THEN

  -- Compare the dates and return the latest row.
  -- Note: if one of the tables does not contain a record,
  -- the default values for @gAlertDateTime and @eAlertDateTime are the earliest
  -- possible date in MySQL, which will automatically make the
  -- logic choose the table that contains the record.
  IF @gAlertDateTime >= @eAlertDateTime THEN
     -- Change the inequality from ">=" to ">" in order to return the tblEAlert record
     -- in the event that both records have the same date and time.

     -- NOTE: You may want to canonicalize the column names for the tblGAlert and
     -- tblEAlert results by adding column aliases. This way the application using
     -- the data will get consistent column names.
     SELECT * FROM tblGAlert WHERE gAlertID = @gID;
  ELSE
     -- If you canonicalize the column names, insert the aliases here as well.
     SELECT * FROM tblEAlert WHERE eAlertID = @eID;
  END IF;

END IF;

-- If no records were returned from either table, this procedure
-- will not return a resultset at all.
END; $$

Now just execute CALL GetLatestAlert(); and the most recent row from either table will be returned.


Create a function from another function Is it possible to create a new function from another plpgsql function? Something like this:

CREATE OR REPLACE FUNCTION func_test()
RETURNS VOID AS
$BODY$
BEGIN
  CREATE OR REPLACE FUNCTION func_test2()
  RETURNS INT AS
  BEGIN
  $$
    SELECT 1;
  $$
  END
END
$BODY$
LANGUAGE plpgsql;

When I execute the code above I get:

ERROR:  unexpected end of function definition at end of input
LINE 13: $BODY$`

Yes, totally possible. You just have some random syntax errors.

And I took the freedom to replace the term "stored procedure" in your query with "function", since Postgres does not have stored procedures. Just functions - doing almost, but not quite, the same.

This would work:

CREATE OR REPLACE FUNCTION func_test()
  RETURNS VOID AS
$func$
BEGIN
  CREATE OR REPLACE FUNCTION func_test2()
     RETURNS INT AS
  $$
    SELECT 1
  $$ LANGUAGE sql;
END
$func$ LANGUAGE plpgsql;

Or:

CREATE OR REPLACE FUNCTION func_test()
  RETURNS VOID AS
$func$
BEGIN

CREATE OR REPLACE FUNCTION func_test2()
  RETURNS INT AS
$$
BEGIN
   RETURN (SELECT 1);
END
$$ LANGUAGE plpgsql;

END
$func$ LANGUAGE plpgsql;

Compare:

Can I make a plpgsql function return an integer without using a variable?


How to link a customer to a country when the city/state isn't known We have a situation where medical systems are sold to hospital groups, but at the time of the order, we don't always know into which state and city the system will be installed. We always know the country, as hospital groups never span countries.

We want to be able to save as much information as we know, so if we know the city, state and country, we want to save all three. If we only know state and country, we want to save those two. If we only know the country, we want to save just that.

Now, I can see three ways of doing this, and was hoping someone would be able to comment as to which is preferable...

1) Have three tables, Countries, States and Cities, each of which is linked from the previous, so any city will have a StateID, and any state will have a CountryID. That way, if we know the city, we can work up the links and find out the state and country. This is neat, and preserves data integrity, but has the disadvantage that if we don't know the city, we can't save it.

One way we thought of to get around this was for each country to contain a placeholder state, that wouldn't be shown in the UI, and for each state to hold one placeholder city. That way, if we only know the country, the placeholder state for that country would be saved, as would the placeholder city for the state. This neatly gets around the problem, but requires placeholder states and cities to be inserted. This isn't a performance issue, as there wouldn't be enough of them to be a problem, but feels a bit like a hack.

2) Another approach is to have a single self-referencing Locations table, that contains a LocationTypID that identifies if the location is a country, state or city, as well as a ParentLocationID, which (if non-null) would refer to the containing location (ie states contain cities and countries contain states). That way, we can save the most granular location that we know.

The disadvantage of this is that the querying would be messy. We are using an ORM, which would work very well with the first approach, as you can choose a country, then navigate to the Regions property to see the associated regions, then for any region, use the Cities property. Approach #2 would require a manual query every time you wanted to get a different entity.

This approach is very flexible, but is probably more flexible than we need, and probably not worth the extra effort required to implement it.

3) The final suggestion was for the Systems table to have a CountryID, a StateID and CityID, and populate whichever one(s) is/are known. This is simple, and doesn't require any placeholder entries, but has one huge disadvantage (in my non-expert opinion), and that is that it seems to involve repeated data which can easily become invalid.

For example, suppose CountryA contains StateA, which in turn contains CityA, whereas CountryB contains StateB, which in turn contains CityB - imagine the same with Cs. There is little to stop someone mistakenly setting a system to be in CountryA, StateB and CityC, even though CityC is not in StateB, and StateB is not in CountryA.

Having played around with this a bit, my feeling is strongly towards the first approach, despite the requirement for placeholder rows, but I would like to hear what the experts have to say.

Anyone any comments?

Use a variation of number 2 - a location table with a involuted (self-referencing)relationship, but add something to make the hierarchy management easier.  For example, use nested sets/visitation numbers (see here) or an adjacency list.

The advantages of this approach are that:

You have a single foreign key from your transaction to your location table.  This keeps your queries simple insofar as you don't need multiple joins and either/or logic.
The foreign key can point to as vague or as specific a location as you need.
You can easily traverse the hierarchy (since you'd be using a technique like nested
sets) to get the level of geography of interest.
You can accommodate countries that have more or less than one
geographical division between the country and the city, without
having to create dummy entries or duplicating data.

I don't know what your ORM is, but it should be able to handle a self-referencing relationship and a partitioning attribute (i.e. GeographyLevel = { country, state, city })


Tablespace resize in Oracle 10g I'm trying to resize a couple of tablespaces but the following query is only valid in Oracle 11g.

alter tablespace 2 DATAFILE '/appli2/oracle/xxx/oradata02/xxx_undo_01.dbf' resize 3g;

In oracle v10g i try with this query, picked from oracle documentation :

alter DATABASE DATAFILE '/appli2/oracle/xxx/oradata02/xxx_undo_01.dbf' resize 3g;

I work with Oracle 10g and this query throws error message according to a non valid ALTER DATABASE modifier.

Error messages:
ERROR at line 1:
ORA-02140: invalid tablespace name

Thanks in advance.

Resizing tablespaces/datafiles is not as straightforward as you might intuitively expect.
The following post does an amazing job at explaining all the ins and outs, and how to do it easily through Enterprise Manager:
http://www.oracle-base.com/articles/misc/reclaiming-unused-space.php


Stored Procedure against Linked Server We have an AX server that we would like a group of users to be able to retrieve data from but limit their access to just the data needed. Normally this could be done with a stored procedure and give the the group permission to only execute the stored procedure and use the "execute as" to have the procedure run under an account that does have access to the underlying tables. However AX controls the lifetime of stored procedure so we can't just create an arbitrary stored procedure and call it good.

To remedy the situation, we would instead have the stored procedure on another SQL Server and use a Linked Server so that the users could get the data they need but not have direct access to the tables of the AX server.

The problem is in setting this up we keep getting:

  Msg 7437, Level 16, State 1, Line 3
  Linked servers cannot be used under impersonation without a mapping for the impersonated login.

So the question is: How do we get this scenario to work with Linked Servers or is there a better way to have users get the data they need without direct access to the AX server?

To use the Linked Server, have you set up the user account on the AX server as well as the other?  You need it to be in place on both in order to use account impersonation.

Personally, I would go with option (b), if it's not too complicated.  Depending on what you need, you could consider providing an SSRS report to expose the data they need and no more.


How To Ignore SQL Update Notification I have a very weird situation.

One of our SQL Servers has a software update notification pending for Microsoft SQL Server 2008 R2 Service Pack 1 (KB2528583). However, this server is already at SP2. Here is the result of the @@Version query:

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4033.0 (X64).

If the SQL Server is already at SP2 why do I have an SP1 update notification?
Is there a way to ignore this update notification for just this one machine?

It could also be that you applied the update to SQL Server but didn't apply it to other services (SSAS, SSRS, etc.) or to other components (like Management Tools). Run SP2 again (or upgrade to something even newer!) and see which components it finds eligible for an update - I bet there is at least one.


Cannot find reason why differential backups size reduced Sql Backup job summary:

Hi, I have a backup job that take full backup first day of the month and take differential backup every 2 hours between 9am and 5pm everyday.

Problem:
While testing disaster recovery plan I found that after few differential backups size of differential backup reduced and when I include reduced sized differential backup(red square) I get this error: in restore

Any idea why size of differential backup reduced? and how I can restore this differential backup?

Please help and thanks in advance for reading and answering this question.

Update
As mentioned job is taking full backup start of month there is no full backup between differential backup size of differential backup is increasing till position 91 However, as you see in image no full backup is taken before position 92.

The most likely cause by far is that a full backup was taken out of sequence (between that differential and the previous full backup) - that makes the differential smaller as there are less differences to include between it and this extra backup than between it and the expected previous backup.

If this is the case then to be able to restore the unexpectedly small differential you need to find a copy of the extra backup.

This is usually because someone has taken a copy for use elsewhere, perhaps for testing a support issue or new release. IT is important that everyone who has access to be able to take backups knows that they need to be careful of this because it breaks your DR plans and so forth.

There is a safe way to take an extra backup: use the COPY_ONLY option. This takes the backup as normal but does not interfere with your main full/diff/log backup sequence.


Totals on alias columns I have a table in MS Access that has the following fields/data:

School number  Traditional Forms  Sparring         Traditional Weapons  Combat Weapons
-------------  -----------------  ---------------  -------------------  ---------------
2598
3286           2nd                Competed         Did Not Compete      2nd
1642           Competed           Did Not Compete  3rd                  Did Not Compete
1642           3rd                3rd              2nd                  Competed
3116           1st                1st              1st                  1st
2598           Competed           2nd              Did Not Compete      3rd
2396           Did Not Compete    Competed         Did Not Compete      Competed
3286           Competed           Competed         Competed             3rd
3116           1st                1st              1st                  1st
3169           Did Not Compete    Did Not Compete  Competed             Competed

I have a query that will count individual entries by school as follows:

SELECT [tourney report].[School Number], COUNT([School Number]) as Entries
FROM [tourney report]
WHERE (Sparring IN ('1st','2nd','3rd','Competed') OR [Traditional Forms] IN ('1st','2nd','3rd','Competed') )
GROUP BY [School Number]

This gives me the total number of entries in either Sparring or Forms per school. I can repeat this for each of the other categories (weapons/combat weapons). What I would like to get is a combination query that shows total entries per school, which is a total of each of the forms/sparring (Forms and sparring count as one entity), weapons, combat totals. Example:

School number   Forms/Sparring  Weapons Combat  Total Entries
1234                22            15      20       57

My further attempts resulted in putting the following query together, which gives me most of what I need:

SELECT [tourney report].[School Number],
       SUM( IIF( ([tourney report].[Traditional Forms] = "Did Not Compete" OR LEN([tourney report].[Traditional Forms]) = 0) AND ([tourney report].[Sparring] = "Did Not Compete" OR LEN([tourney report].[Sparring]) = 0) , 0, 1) ) AS [Total Form and Sparring],
       SUM( IIF( ([tourney report].[Traditional Weapons] = "Did Not Compete" OR LEN([tourney report].[Traditional Weapons]) = 0), 0, 1) ) AS [Total Weapons],
       SUM( IIF( ([tourney report].[Combat Weapons] = "Did Not Compete" OR LEN([tourney report].[Combat Weapons]) = 0), 0, 1) ) AS [Total Combat]
FROM [tourney report]
GROUP BY [School Number]

This gives me the totals for each of the categories that I want, but not the total. I tried the following:

SUM( [Total Form and Sparring] + [Total Weapons] + [Total Combat]) as [All Entries]

That, however, gives the error

  Subqueries cannot be used in the expression (SUM( [Total Form and Sparring] + [Total Weapons] + [Total Combat]) as [All Entries])

How can I get the total of all the sum columns as a fourth total?

I am not sure if Access supports this but generally SQL allows you to solve this with the help of a derived table. If you use your present query as a derived table, you can reference all the computed columns exposed by it simply by their aliases, like this:

SELECT
  [School number],
  [Total Form and Sparring],
  [Total Weapons],
  [Total Combat],
  [Total Form and Sparring] + [Total Weapons] + [Total Combat] AS [Total Entries]
FROM
  (  /* this is the query that gives you most of what you need */
    SELECT
      [tourney report].[School Number],
      SUM( IIF( [tourney report].[Traditional Forms]   IN ('', 'Did Not Compete')
            AND [tourney report].[Sparring]            IN ('', 'Did Not Compete'), 0, 1) ) AS [Total Form and Sparring],
      SUM( IIF( [tourney report].[Traditional Weapons] IN ('', 'Did Not Compete'), 0, 1) ) AS [Total Weapons],
      SUM( IIF( [tourney report].[Combat Weapons]      IN ('', 'Did Not Compete'), 0, 1) ) AS [Total Combat]
    FROM
      [tourney report]
    GROUP BY
      [School Number]
  ) AS derived

You can see that the aggregate results are both returned as their own columns and used to create another computed column, the total of all entries.

As you can also see, with the above query I have also taken the liberty of slightly rewriting your conditionals. I just wanted to make them more compact. But there, again, I am not entirely sure if Access will accept that syntax. If not, you can always revert the syntax to yours.


When the old execution plans changed in plan cache Can any one share demo for when the execution plans changing in plan cache. For example as I know if the table has a modifications of 20% + 500 rows then the table columns and index statistics are updated automatically. So my question is once they updated whether the old execution plan changed to new execution plans with the new update stats on the table or not.

Please provide any demo for checking of same.

The are a lot of caveats about execution plan generation and reuse, so the old saying 'it depends' definitely comes into play.  I think you will find some very valuable information from these links (they include examples of various tests):

https://www.brentozar.com/archive/2015/01/updating-statistics-cause-recompile-no-data-changed/

https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx

https://www.brentozar.com/archive/2013/12/recompile-hints-and-execution-plan-caching/


mysql replication using galera I want to learn how to use Galera cluster replication (MariaDB).

Can anyone give an advice that I can start from specific point?
Is it compatible with vsphere?

For your first question:

Multiply Things you must know before using Galera Cluster.

Galera Cluster is not for scale write
Galera Cluster is not load balancer. It must integrate with HA Tools for this option
It's virtually synchronized Multi-master Replication
You need minimum of 3 nodes/servers(or 2 nodes and 1 abrirator)  to avoid split/brain.
You can't use MyISAM all of tables must be InnoDB.

So if you want to use Galera Cluster I thinks this article is good for start.


Netezza migration to Azure Synapse - Stored Procedures? I have looked everywhere for useful documentation that comprehensively gives me the differences between Netezza stored procedures and functions so I can find Azure Synapse equivalents.
Is there any site or link that documents this sort of thing without having to look up each individual incompatibility by running the procs in Azure and having them fail?
I guess I would like to proactively fix these rather than reactively fixing them to save time
Any ideas?

Azure Synapse Pathway is a new tool currently in preview which can look at existing Netezza code for you, attempt to convert it and flag up errors.
Download the tool and run it against your Netezza installation.  Start here:
https://docs.microsoft.com/en-us/sql/tools/synapse-pathway/azure-synapse-pathway-overview?view=azure-sqldw-latest
As a wizard, it's probably at best going to give you 80/20, but will at least offer a start.


Most used tables in MySQL database server Can somebody please help me with a query that lists the most used tables in a MySQL database? Or how can i find out the most used tables in the database?

Thanks

show open tables might help you or you can view queries from general log if enabled. You can also check binary logs from mysqlbinlog utility for further analysis.

Hope it help.


MYSQL: How to optimize a date sorted table with a triple primary key index I have been maintaining a MYSQL database for a few years, dating from back when i took database managing classes. Its biggest table has since grown (15 million entries for 1,8 GB) and this query began to take minutes to load (pkey1 is a DATETIME, pkey2 and pkey3 are int(11)) :

SELECT * FROM table
WHERE pkey2 = ...
  AND pkey3= ...
  AND HOUR(pkey1) = 0
ORDER BY pkey1 DESC

The index of this table is (pkey1,pkey2,pkey3) BTREE, and the column count is 20.

The ORDER BY used to be ASC and it was running quite slowly, I then changed it to DESC (since i will change this request for a pagination, if this cannot be optimized), and migrated from 5.7x to 8 to be able to recreate this table with a DESC index. It's now even slower.

The questions are :

Would changing the DATETIME to a DATE (and thus remove HOUR(0), no longer needed) resolve the problem in a satisfying enough way ? I don't need it anymore, especially if it causes problems.
If not, what can you advise me to do ?

To go further :

Allegedly because of this table, my whole system (weak: 2Gb RAM, Single core, 20GB SSD) is slowed down, and even a simple database connection is delayed by several seconds. If this table is not the case for the slow down (even though the triggering of this request seems to be the problem), here are the alarming variables i got from PHPmyadmin :

As you can see, things are probably wrong.

Thank you for the read !

Your query is not sargeable. Using Hour() function on the first column (pk1) of the composite primary key is inhibiting the usage of the Primary Key for index lookup; and thus (in absence of any other proper index), it is most likely doing full table scan. The general rule of thumb to follow while defining a working index is:

First priority should be given to all the columns, which are inside the WHERE clause, and are connected by AND clause, and are compared to a constant value using either =, IS NULL, or <=>. So, in your query, there are two columns following this: Data_Type and Status.
Second priority can be given to following three scenarios:

Column having Range condition.
Columns in a specific order in the GROUP BY clause (if existing).
Columns in a specific order in the ORDER BY clause (if existing)

Now, coming to your questions:

  Would changing the DATETIME to a DATE (and thus remove HOUR(0), no
  longer needed) resolve the problem in a satisfying enough way ? I
  don't need it anymore, especially if it causes problems.

Yes, you should do that; if the time part is irrelevant to you. In MySQL 8+, the size of datetime is 5 bytes, while the size of date is 3 bytes. So you are effectively going to save considerable amount of space as well.

With this change, your new query would simply be:

SELECT * FROM table
WHERE pkey2 = ...
  AND pkey3= ...
  -- removed AND HOUR(pk1) = 0
ORDER BY pkey1 DESC

In this scenario, if you refer the thumbrule discussed above, you need to define a new Composite Index (pkey2, pkey3, pkey1) (the ordering is changed; you will need to keep this index besides the already defined Primary Key).

ALTER TABLE table_name ADD INDEX (pkey2, pkey3, pkey1);

This should be able to help you considerably.


Is storing attributes in separate tables instead of columns a standard practice? This question is a little open-ended and subjective, but hopefully it's still okay for me to ask on here.

I was contracted by a small business to do a project and as part of it received a database dump of their website. I was appalled when I saw the database had 542 tables in it because every attribute is stored in a separate table.

For example, there are tables like this used for identifying customers:

field_data_field_first_name
field_data_field_last_name
field_data_field_email
field_data_field_phone_number

The field_data_field_first_name table has the following structure:

entity_type
bundle
deleted
entity_id
revision_id
language
delta
field_first_name_value
field_first_name_format

That goes on for a total of 542 tables.

Is structuring a database like this a standard practice?

I'm relatively inexperienced with database design, so while I feel whoever designed this database did a rather poor job, I'd like a second opinion before approaching the company about it.

As an aside, they have an index on every column in every table, which seems bad. It probably goes without saying that the database is incredibly slow. It has an e-commerce portion and it takes between 1-2 minutes to query unfilled orders from the database.

Is structuring a database like this a standard practice?

No.

  I'd like a second opinion before approaching the company about it.

Are you explaining poor performance? Complaining? Suggesting a re-design or replacement? Did anyone ask for your professional or personal opinion of the system? Is it going to affect project estimations and cause issues?

Why you are going to approach the company should form the basis of how you bring this up and what justification you need to bring to the table. If it's unsolicited advice for a small company, you might approach it more diplomatically by determining if this design issue is known, if it's causing issues, and if someone there is attached to, and proud of, their baby.

There's plenty of poorly designed systems and weird implementations out there... and while it's easy to shake our heads and second guess it's not always helpful to the client or ourselves. ;)


After a point-in-time restore, is there any way I can get the transactions during restore time? The hypotethical scenario:

Imagine that I have to do a point-in-time restore of a Azure SQL database, the original one is called DatabaseA and the restored is after restore called DatabaseA_restored.

During the time of restoring DatabaseA_restored there are transactions happening in DatabaseA (inserts, updates, deletes) because we cannot afford the downtime.

Is there anyway I can now cherry pick transactions/table changes and apply them to DatabaseA_restored so that I do not loose the relevant difference during restore?

In Azure SQL DB today, the answer is "no" - at least not the way you are asking the question.  You can't go hack the log since it is a managed service and it (the log format) can change over time and thus is not exposed to you.  Instead, you can achieve the same end by setting up active geo-dr for the database.  You let it sync up and you can break the connection when you want so that you have an exact copy with all of the most recent transactions at the point where you want to have it.


Oracle database created whats next? Create Schema or Create Tablespace? I'm a programmer that's being thrown to the DBA/Sysadmin island all by myself with a volleyball that I named Wilson. I'm trying to survive here.

I'm supposed to create a database for an application. The server where this will be running previously had a database for a pretty similar app. I don't know much about Oracle so I reused the ZFS filesystems and left them how they were created (because honestly, I didn't knew why they were created that way, but I'm pretty sure it was for a good reason).

app/oradata_smart_ora1    858M  12.2G   858M  /oradata/SMART/ora1
app/oradata_smart_ora2   7.18M  18.0G  7.18M  /oradata/SMART/ora2
app/oradata_smart_ora3   7.18M  36.0G  7.18M  /oradata/SMART/ora3
app/oradata_smart_ora4   60.6K   400G  60.6K  /oradata/SMART/ora4
app/oradata_smart_redo1   400M  2.61G   400M  /oradata/SMART/redo1
app/oradata_smart_redo2   200M  2.80G   200M  /oradata/SMART/redo2
app/oradata_smart_redo3   200M  2.80G   200M  /oradata/SMART/redo3

Since I reused the filesystems I created my database and placed the controlfiles in the same places where the old database files were (/oradata/SMART/ora1,/oradata/SMART/ora2,/oradata/SMART/ora3).
Thinking like MySQL works I created app/oradata_smart_ora4   60.6K   400G  60.6K  /oradata/SMART/ora4 specifically to store the database there.

The databases startups and mounts no problem. Now is where I'm stuck. I've read this, this,this,this,this and much more but I still have doubts.

Note that this server will manage with millions/billions records throughout its lifetime.

Now that my Database is created, whats the next step? Create the Schema or Tablespace?
Tablespace Questions: Tablespace datafile(s) is where actual data from tables is stored? how many are needed? Default or Temporary? How much space will I need for it? Autoextend?

I'm really sorry for all these questions but I really want to do this right while following the best practices for Oracle. If you need more information for your answer let me know.

You've missed one place to get an overview of Oracle: the Concepts Guide. It covers all the major topics (including backup and recovery, which is quite important and doesn't appear in the list of links you've posted).

  Whats the next step? Create the Schema or Tablespace?

Both! They're orthogonal. Users are logical entities that access your database. Tablespaces are a storage concept. A user can have access to multiple tablespaces, and a tablespace can store data from multiple schemas. You need both, and you need to grant access to the appropriate tablespace to the users you create. (See e.g. here for the difference between user and schema.)

  Tablespace datafile(s) is where actual data from tables is stored?

Yes, all your database's data and indexes are stored in tablespaces. The main storage structures are:

Ordinary tablespaces store normal, persistent data. That's going to be the largest part of your database, space-usage wise.
Temporary tablespaces store non-persistent data - global temporary tables that get purged at the end of sessions or transactions, temporary storage for things like on-disk sorts, etc.
Undo tablespace(s) and redo log files: that's what Oracle uses to provide ACID guarantees.
Control files: they describe your database (name, files, log sequence and checkpoint information, even some backup info).

(The system tablespace is an ordinary tablespace, except that you shouldn't store anything in it - consider it as Oracle internal and off-limits for ordinary use.)

In addition, your should take great care of your redo log files, the "most crucial structure for database recovery". They are "hot" (lots of writes) and should be on their own disks/luns.

  How many [tablespaces/datafiles] are needed?

As much as you need. There's no general rule here. The number of datafiles will depend on how much data you need to store, operating system limits, Oracle datafile size limits, your storage (hard disks/volumes) constraints, backup/recovery considerations (e.g. having only one humongous Bigfile datafile might not be the best idea), ...

How you structure your tablespaces is up to you too. Having a tablespace per "application" in your tablespace can be good approach to get started. You can always create more tablespaces later if needed (but keep in mind that moving an object from one tablespace to another can be time-consuming, and might require either downtime or pretty complex operations).

  Default or Temporary?

Both! You need space to store your data persistently, and you also need some amount of temporary storage for your database's operation.

  How much space will I need for it?

Anywhere between a few megabytes and several terabytes – only you can know here. To estimate the space you need for a table, create a table with the same structure, fill it up with some sample data (should be more or less statistically representative of what you'll be storing in it) and measure the space usage. Then extrapolate. Don't forget to include the space required indexes (and materialized views)!

  Autoextend?

I'd say yes, use autoextend features, but set limits. You probably shouldn't let Oracle try to autoextend past the actual available space on your filesystems. And monitor space usage. (Keep in mind that datafile extension is relatively costly. Don't set the autoextend size too small.)

For ZFS specifically, Oracle has a whitepaper you might be interested in: Configuring ZFS for an Oracle Database (270k PDF).


SQL Server link to MySQL reports "using password: no" I would like to access a MySQL database from within SQL Server.  I created a system DSN, and it tests OK.  I created a linked server in SQL Server:

Provider = Microsoft OLE DB Provider for ODBC Drivers
Product name = MySQL
Data source = (the name of the DSN)
Security = one entry for each of my local logins, with my MySQL user name and password

However, if I try to test it I get this error message:

Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked
server "X".
OLE DB provider "MSDASQL" for linked server "X" returned message "[MySQL][ODBC
5.3(w) Driver]Access denied for user 'me'@'localhost' (using password: NO)".
(Microsoft SQL Server, Error: 7303)

The password is most definitely set, and works just fine in MySQL Workbench.  It's an admin on MySQL.  Both machines are local.  I tried adding my creds as the default ("for a login not in the list, use this security context"); no change.

I set up my DSN as 64-bit, matching my OS, using "MySQL ODBC 5.3 Unicode Driver", which I gather was installed with the MySQL engine and/or MySQL Workbench.  The 32-bit ODBC app offers the usual Portuguese drivers (why Portuguese?!) - Access, dBase, Excel, Paradox, etc.

I just ran into this installing a linked server to MySQL with the "MySQL 8.0" connector. The same error message in the OP occurred when trying the 64-bit versions of both the ANSI and the Unicode connectors on 64-bit Windows Server 2016 host/SQL Server 2019 Standard.
In my case, the issue had to do with the connection string, specifically the PWD=MyPa$$word portion. Replacing it with the equivalent password=MyPa$$word fixed the issue.
Credit to this MSSQL Tips article, which has a chart of the possible connection string parameters.


Index is seeked and scanned - is there a risk for seek to fail after modification? I have an index that has been seeked 230.000 times and scanned 130.000 times.
In order to improve the queries that have scanned, because they use an additional column I guess, I would like to change the index and add the column that is  additionally used by the index-scanning query.

In the relevant index-scanning query both of the columns are used within a join operation as the joining columns.

Do I have to expect that the queries that so far seeked the index will afterwards fail to seek and scan the index instead? Should I instead add a seconde, new index?

CREATE TABLE [dbo].[ArtCatVRC](
[AVid] [uniqueidentifier] NOT NULL,
[ACatalID] [uniqueidentifier] NOT NULL,
[ACategID] [uniqueidentifier] NOT NULL, CONSTRAINT [PK_A] PRIMARY KEY NONCLUSTERED ([AVid] ASC, [ACatalID] ASC, [ACategID] ASC) WITH ... ON [PRIMARY] ) ON [PRIMARY]

Query (simplified):

Select V.Col1, V.Col2, C. ColN From ... AS C inner join ... AS V LEFT OUTER  JOIN dbo.ArtCatVRC AS R ON C.ID = R.ACategID AND R.ACatalID IN (V.X, V.Y, V.Z)

The used (scanned) index:

CREATE NONCLUSTERED INDEX [ACategId] ON [dbo].[ArtCatVRC] ( [ACategID] ASC )

After every execution of the above query, SYS.DM_DB_INDEX_USAGE_STATS increments SCANS +1 to this index.

The idea is to add the column R.ACatalID to the index, so that joining with both columns will change to seek the index.

Exec. Plan:

StmtText
--------
Stream Aggregate(GROUP BY:([cat].[ID]))
|--Filter(WHERE:([DATABASE].[dbo].[ArtCatVRC].[AVid] as [R].[AVid] IS NULL))
           |--Merge Join(Right Outer Join, MANY-TO-MANY MERGE:([R].[ACategID])=([cat].[ID]), RESIDUAL:([DATABASE].[dbo].[T_ACateg].[ID] as [cat].[ID]=[DATABASE].[dbo].[ArtCatVRC].[ACategID] as [R].[ACategID] AND ([DATABASE].[dbo].[ArtCatVRC].[AVid] as [R].[AVid]=[DATABASE].[dbo].[T_ACatVM].[SS] as [viewMap].[SS] OR [DATABASE].[dbo].[ArtCatVRC].[AVid] as [R].[AVid]=[DATABASE].[dbo].[T_ACatVM].[SAVid] as [viewMap].[SAVid] OR [DATABASE].[dbo].[ArtCatVRC].[AVid] as [R].[AVid]=[DATABASE].[dbo].[T_ACatVM].[AVid] as [viewMap].[AVid])))
                |--Index Scan(OBJECT:([DATABASE].[dbo].[ArtCatVRC].[IX_ACategID_AVid] AS [R]), ORDERED FORWARD)

                |--Sort(ORDER BY:([cat].[ID] ASC))
                     |--Nested Loops(Inner Join, OUTER REFERENCES:([viewMap].[ACatalID]))
                          |--Clustered Index Seek(OBJECT:([DATABASE].[dbo].[T_ACatVM].[IX_AVid] AS [viewMap]), SEEK:([viewMap].[AVid]={guid'00000000-0000-0000-0000-111122223333'}) ORDERED FORWARD)
                          |--Index Seek(OBJECT:([DATABASE].[dbo].[T_A_Categ].[IX_ACateg_ACatalID_Deleted_ID_DisOrd] AS [cat]), SEEK:([cat].[ACatalID]=[DATABASE].[dbo].[T_ACatVM].[ACatalID] as [viewMap].[ACatalID] AND [cat].[Deleted]=(0)) ORDERED FORWARD)

I would start by adding a clustered index to the table. Since the primary key is unique I would just update that from nonclustered to clustered. To do that you will have to drop and recreate the constraint so if you have foreign keys defined you will need to drop those constraints first then put them back once you have the primary key put back on.

A few references on clustered indexes

Clustered indexes vs heaps

RID lookups

If all of your queries against the table include acatalid and acategid in the join or where clause then adding acatalid to the existing index may help.

However if the queries don't always include both columns in the where clause you may see a benefit in creating another index on acatalid.

I say these changes may help as the statistics that SQL server has on the table could be generating an execution plan with the scan because it sees the cost of scanning through the table as less than doing a seek.


Critical tablespace usage UNDOTBS (96%) This tablespace is in my case 8GB.
I'm on oracle 10g and  got critical warning:

Tablespace usage critical: UNDOTBS (96%)

I also executed this command:

select sum (bytes/1048576) MB
FROM
DBA_SEGMENTS
WHERE
tablespace_name = 'UNDOTBS' ;

It gives me 7625,625MB.

Could I extend this tablespace with this command:

ALTER TABLESPACE "UNDOTBS" ADD DATAFILE '/path/to/ts.db' SIZE 4194304000;

Can I do it on running system and wont I destroy anything?

Most probably this is fake alarm. the space UNDO tablespace is allocated differently then on other tablespaces. Oracle simply does not clean out old versions of block unless necessary. There are no real data in this tablespace.

You UNDO space was really full you would get ORA-01555 Snapshot too old, or other types of errors. Check the view v$undostat.

PS: what type of monitoring tool are you using?


How to check and change function volatility in Postgres? I have a function that I think is defined as volatile, but I now know I want it stable.

How can I check the function volatility (volatile, stable, immutable) of a certain function?

When a function is defined as volatile, can I change the volatility to stable by using a create or replace function query? Or does the function needs to be dropped and created again but this time as stable?

create or replace function my_function()
returns int as $$
    select a
    from my_table
    order by 1
    limit 1
$$ language sql stable;

I checked the documentation for this, but it doesn't mention changing volatility.

I checked the documentation again, and the answer to my second question is, yes you can alter it using the alter function query.

alter function my_function() stable;

Also, I see that checking for function volatility has been asked and answered before.

https://stackoverflow.com/questions/54452415/how-to-check-whether-a-function-is-immutable


What are the origins of the Identity Column? Who were the first creators of the Identity column? What problem were those creators originally trying to solve? Is there a story behind it?

Hi Bob and welcome to DBSE.

I assume (correct me if I'm wrong) that your question is not about the specific properties of SQL Server or Oracle IDENTITY, but about the use of IDENTITY as surrogate keys in general, which will be true for other implementations such as AUTO_INCREMENT, SEQUENCE, IDENTITY, GUIDs, OID as a design choice.

If that is what you mean, I salute you for this inquiry.
Most people new to SQL never stop to ask these questions and just assume 'this is how it's done' because everybody seems to be doing it this way.

This is a controversial topic, and you will find people with strong opinions both ways. A quick search for "Natural Vs. Surrogate keys / artificial keys" will yield you enough reading material for years.

Wikipedia has a nice article that will get you started.
Joe Celko also wrote quite a few excellent articles about this topic, you can start with this one, and this one.

I also find this video session on the subject to be entertaining and educational.

I am of the opinion that both are valid solutions.
However, I also see that surrogate keys are used indiscriminately by most, just because finding the correct keys and modeling it properly is a significant effort, that requires mastering the relational model, which is hard.

IMHO, that is unfortunately also the root cause of many data consistency, modularity, performance, coding, and manageability challenges that later end up very hard and very expensive to correct.

Ask anyone who has ever tried to merge two data sets that use surrogate keys.
At that point, you have no choice but to look for the natural key. But, if the natural keys were never explored, or properly constrained with UNIQUE constraints, the inconsistencies and duplicates are typically in abundance.

In my designs, I find that I use natural keys for the majority of the tables, and carefully plan for the edge cases where a surrogate is beneficial.
These design choice are very much a minority opinion.

HTH


Upgrading PostgreSQL 9.3 to 9.5 I want to upgrade version 9.3 to 9.5 but after some steps I find that I upgraded to version 10, but I'm looking to go from 9.3 to 9.5.

When I type the pg_lsclusters command I find myself with three:

I used sudo pg_dropcluster 9.5 main --stop and sudo pg_dropcluster 10 main --stop to stop both, and sudo pg_upgradecluster 9.3 main command to set the 9.3 cluster to the latest version.

But I want to go to 9.5 instead of 10. The result after what I'm typing is:

Once you drop cluster for version 9.5, you may upgrade your 9.3 to 9.5 with this command:

sudo pg_upgradecluster -v 9.5 9.3 main

If you omit -v 9.5 it will upgrade the cluster to the latest postgresql version you have installed. For more information, type man pg_upgradecluster.

Now, since you already upgraded to 10, you have to stop the new cluster (version 10) and start the 9.3 again. You may do this with commands:

sudo pg_ctlcluster 10 main stop
sudo pg_ctlcluster 9.3 main start


Non-overlapping rectangles constrained to a boundary I am trying to model placement of parts on a circuit board. Without any
meaningful constraints, my basic schema looks like this:

create table part (
    part_id bigserial primary key,
    name text not null,
    width double precision not null,
    height double precision not null
);
create table board (
    board_id bigserial primary key,
    width double precision not null,
    height double precision not null
);
create table board_part (
    board_id bigint not null references board,
    part_id bigint not null references part,
    position point not null
);

(SQL Fiddle, Visualization)

For b and b2 any board_parts, I want to enforce the following
constraints:

b lies on the board:

box(b.position, point(b.part.width,b.part.height))
    <@ box(point(0,0), point(b.board.width,b.board.height))

b and b2 do not overlap if they lie on the same board:

b.board_id != b2.board_id or
not (box(b.position, point(b.part.width,b.part.height))
        && box(b2.position, point(b2.part.width,b2.part.height)))

How can I achieve this (without too much data duplication)? Changing the schema is fine.

Here is my best attempt (SQL Fiddle), taking
inspiration from
Erwin's answer to my previous question.
It enforces the constraints I wanted, but has a lot of duplicate data in the board_part table. I imagine I could write a function to fill in the board_width, board_height, part_width, and part_height fields automatically, but it still feels wrong having so much duplicate data around. Also, keying to the width/height fields feels like a hack.

Basic answer

I suggest to use the geometric type box and combine that with an exclusion constraint (Postgres 9.2+). Should be the perfect solution to your problem. The GiST index this is implemented with (automatically) also supports certain queries.

Combine it with with equality on board_id to hold multiple boards in a single table. You'll need the additional module btree_gist. Once per database:

CREATE EXTENSION btree_gist;

To confine parts to a board add a CHECK constraint. You need the confining box of the board in the board_part table (redundantly). Could look like this:

CREATE TABLE board_part (
   board_id bigint NOT NULL references board
  ,board_box box NOT NULL
  ,part_id bigint NOT NULL references part
  ,part_box box NOT NULL
  ,EXCLUDE USING gist (board_id WITH =, part_box WITH &&)
  ,CHECK (part_box <@ board_box)
);

&& .. "overlaps" operator; <@ .. "contained" operator

The drawback: you need the dimensions of each part and box in table board_part redundantly.

Advanced answer

To avoid redundant storage, I like this new idea better: Fake IMMUTABLE functions returning the box for an id and build constraints upon these functions. Your tables can make do with just the columns of your original design.

I ran a complete test to verify it works.

Complete schema

Base tables:

CREATE TABLE part (
   part_id serial PRIMARY KEY
  ,part text NOT NULL
  ,wide int NOT NULL
  ,high int NOT NULL
);

CREATE TABLE board (
  board_id serial PRIMARY KEY
 ,board text NOT NULL
 ,wide int NOT NULL
 ,high int NOT NULL
);

IMMUTABLE Functions:

CREATE OR REPLACE FUNCTION f_boardbox(_board_id int)
  RETURNS box AS
'SELECT box(point(0,0), point (b.wide, b.high))
 FROM public.board b WHERE board_id = $1'
LANGUAGE sql IMMUTABLE;

CREATE OR REPLACE FUNCTION f_partbox(_part_id int, _wide int, _high int)
  RETURNS box AS
'SELECT box(point($2,$3), point($2 + p.wide, $3 + p.high))
 FROM public.part p WHERE part_id = $1'
LANGUAGE sql IMMUTABLE;

Replace public with the actual schema of your tables.

Main table:

CREATE TABLE board_part (
  board_id int NOT NULL REFERENCES board
 ,part_id int NOT NULL REFERENCES part
 ,wide int NOT NULL
 ,high int NOT NULL
 ,EXCLUDE USING gist (board_id WITH =, f_partbox(part_id, wide, high) WITH &&)
 ,CHECK (f_partbox(part_id, wide, high) <@ f_boardbox(board_id))
);

Of course, if you update dimensions for a board_id or part_id in use, you partly void the index and / or constraint. You would need to recreate any index or constraint building on the promise that the return value of IMMUTABLE function never change. More explanation for the technique in this related answer on SO:
Does PostgreSQL support “accent insensitive” collations?

However, you can avoid this expensive operation with triggers to update only affected rows:

Triggers

CREATE OR REPLACE FUNCTION f_board_upaft()
  RETURNS trigger AS
$func$
BEGIN
   UPDATE  board_part
   SET     board_id = board_id       -- enough to trigger CHECK constraint
   WHERE   board_id = NEW.board_id;  -- limit to relevant rows

   RETURN NULL;
END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER board_upaft
AFTER UPDATE OF wide, high ON board  -- limit to relevant columns
FOR EACH ROW EXECUTE PROCEDURE f_board_upaft();

CREATE OR REPLACE FUNCTION f_part_upaft()
  RETURNS trigger AS
$func$
BEGIN
   UPDATE  board_part
   SET     part_id = 0
   WHERE   part_id = NEW.part_id;

   UPDATE  board_part
   SET     part_id = NEW.part_id
   WHERE   part_id = 0;              -- enough to update EXCL. constraint

   RETURN NULL;
END
$func$
  LANGUAGE plpgsql;

CREATE TRIGGER part_upaft
AFTER UPDATE OF wide, high ON part
FOR EACH ROW EXECUTE PROCEDURE f_part_upaft();

part_id = 0 is a special row in table part with size 0. Needed for the trigger.

Test data:

INSERT INTO board (board, wide, high)
VALUES ('b1010',10,10), ('b2030',20, 30);

INSERT INTO part (part_id, part, wide, high)
VALUES (0,'p0',0,0);          -- special row needed for trigger!

INSERT INTO part (part, wide, high)
VALUES ('p11',1,1), ('p33',3,3);

INSERT INTO board_part (board_id, part_id, wide, high)
VALUES (1,1,3,3), (1,1,5,5)   -- no overlap, inside board
      ,(2,1,3,3);             -- different board, no overlap

Test

These must raise exceptions if the triggers & constraints do their job:

UPDATE part SET wide = 6 , high = 6
WHERE  part_id = 1;            -- violates EXCL. & CHECK constraint

UPDATE part SET wide = 3 , high = 3
WHERE  part_id = 1;            -- violates EXCL. constraint

UPDATE board SET wide = 2 , high = 2
WHERE  board_id = 1;            -- violates CHECK constraint

Its not possible to install extensions on SQLfiddle, so simplified exclusion constraint without btree_gist: SQLfiddle to play with.


What is the best way of checking the current user privileges on a particular table from another schema? In my software, at runtime, I need to check if the connected user has the privileges on some tables, across 2 different schemas.
From what I found doing research, I came across these 2 views
ALL_TAB_PRIVS_RECD
ALL_TAB_PRIVS
Should I use one of these views, or is there another better way to do it?
Thanks

ALL_TAB_PRIVS is a superset of ALL_TAB_PRIVS_RECD. You probably want ALL_TAB_PRIVS as it includes tables that the current user owns.


querying a JSOn object in separate columns from Postgres table I have a table with column type as json.
select request_id,cast(job_info as varchar)  from requests_table where cast(job_id as varchar) like '%4665-b583-3b92218db8ce%';

Here job_info and job_id are the json type column.
It yield me data like this:
          request_id   |                 job_info
--------------------------------------+-------------------------------------------------------------------------------------------------------------------
 f373085d-0124a161b2d7 | ["test-cluster", "test-cluster-id-11111"]
(1 row)

I am expecting job_info data should get retrieved as two separate column.
f373085d-0124a161b2d7,test-cluster,test-cluster-id-11111

You can extract each array element using the->> operator:
select request_id,
       job_info ->> 0 as cluster_1,
       job_info ->> 1 as cluster_2
from requests_table
where cast(job_id as varchar) like '%4665-b583-3b92218db8ce%'


How to find High CPU queries CPU utilization is very high and the SQL Server process is consuming a lot of CPU. We have run few queries and notice that there are a few SPIDs taking a long time to complete and these queries may be causing the high CPU pressure.  Is there any way to get queries which are taking a lot of CPU of each SQL Server process is consuming?

I want to put an alert when ever the CPU reaches to 90% above then mail an alert with high consuming queries and to get exact one among all. I got the below process to get the high cpu thread which is using the CPU more but I need it through script to get the information. How to do this by script?

SQL Server 2014
OS: Windows 2012 Server

Please look at the three main queries in the following answer (of mine):

SQL Server performance: PREEMPTIVE_OS_DELETESECURITYCONTEXT dominant wait type

The first query looks at currently running queries.

The second two queries (labeled "Query #1" and "Query #2") work in combination to find high-cost queries over a period of time.

To get alerts, look into options such as Solarwinds Free WMI Monitor or Spiceworks Network Monitor.

You need monitoring software of some nature. Those were 2 that I found for Windows that were free. Most of the free ones appear to be for Unix, or are at least Unix-based. You can also look at Splunk and Zabbix. You can use the queries I provided in the linked answer with at least some of those products. But they should do the "alert on high CPU for X minutes" thing.

You could also write your own system that polls on a scheduled, is scheduled, stores data in SQL Server, ages out old data, calculates % variance over a configurable time-frame, and takes action upon that condition being met. This is a lot more work as you now have even more code to maintain (but is still a viable option if the above doesn't work out for you).


How to output query to file using SQLite3 Is there an equivalent to Oracle's spool for sqlite3?

The sqlite3 command-line tool has the .output setting.


Getting the data from a foreign key table I have a table that relates a user to client using both their table id's

UsersClients Table

   ---------------------
   | userid | clientid |
   ---------------------
   | 2      | 32       |
   | 3      | 43       |
   | 2      | 24       |
   | 2      | 12       |
   ---------------------

The userid relates to the id field with in my Users Table
The clientid relates to the id field with in my Clients Table

I have linked them all up OK.

My question is how can I use this key table to get the details.  For instance, I am the user with an ID of 2. By simply doing a query on the above UsersClients Table with the userid of 2, I will simply get the following results 32,24,12.

Now, in order to get the then client details from clients table for each of the returned results, I would query the clients db with something like

   SELECT * FROM Clients WHERE id IN(32,24,12);

But as I have the above UsersClients Table linking up to the relevant tables that I can approach this better, so I don't have to first scrape the UsersClients Table for each client ID then scrape the clients table for the relevant details.

A simple JOIN query would be in order

SELECT B.* FROM UsersClients A
INNER JOIN Clients B ON A.clientid = B.id
WHERE A.userid = 2;

Give it a Try !!!


Get records between time span I would like to retrieve records between time spans,All i have is this snippet of string indicating the time span

9:00 AM - 10:00 AM

below is the table schema

CREATE TABLE [dbo].[Samples](
[RID] [int] IDENTITY(1,1) NOT NULL,
[PersonName[nvarchar](20) NOT NULL,
[NOT_AVL_DATE] [datetime] NULL,
[NOT_AVL_FOR] [int] NULL
)

brief:
Rid is Primary key, PersonName is Employee/Staff/Technician Name, NOT_AVL_Date is the datetime the  staff wont be avilable, NOT_AVL_FOR represents time span the staff wont be avilable.
1 - Leave for Full day
2-  Leave for First half of the day
3 - Leave for Second half of the day

Requirement:
Given timeSpan i want the Staff who are not on leave for that time span. Could someone construct a Full Statement , my head is whirling thinking about this..thanks for any help

Deeptechtons, we really would like to see your answer, but since you haven't posted it yet, here is how it could be done.  This SQL is for Oracle not SQL Server, so it would have to be translated.  There are details missing from your post, so some assumptions used are likely invalid.

with AllEmployees As
   (select distinct PersonName from samples)
select * from AllEmployees
minus
select PersonName from samples
where trunc(not_avl_date) = trunc(sysdate)
and (not_avl_for = 1
   or (not_avl_for = 2 and :timespan LIKE '%AM%')
   or (not_avl_for = 3 and :timespan LIKE '%PM%')
);

Sample data:

create table samples (rid Number(3), PersonName Varchar2(20),
   not_avl_date date, not_avl_for integer);

insert into samples values (1,'Peter',sysdate-2, 1);
insert into samples values (2,'Peter',sysdate-1, 2);
insert into samples values (3,'Paul',sysdate-1, 3);
insert into samples values (4,'Sue',sysdate, 3);
insert into samples values (5,'Bob',sysdate-2, 2);
insert into samples values (6,'Zach',sysdate+1, 1);


How do I get the types of columns for postgresql? I am trying to get the type of the column. For instance I have two columns for names and ids and they are text and integer respectively. I have 3 of them:

name    id
george  5
takashi 2
hans    8

Is that possible to get a result like that?

name id
text int
text int
text int

I have seen this code below and it did not work for me

select column_name,data_type
from information_schema.columns
where table_name = 'table_name';

Based on the query you already have,

SELECT (SELECT data_type
               FROM information_schema.columns
               WHERE table_schema = 'public'
                     AND table_name = 'table_name'
                     AND column_name = 'name') "name",
       (SELECT data_type
               FROM information_schema.columns
               WHERE table_schema = 'public'
                     AND table_name = 'table_name'
                     AND column_name = 'id') "id";

would produce the output you want (but only a single row, which is enough per your comment).

SQL Fiddle


mongodb read write locks I'm currently investigating the possibility of using mongodb. This would contain the a collection of 100million+ documents. I need a process that will be constantly running deleting documents older than some expiry data. I will have new documents constantly going into this. I will also have big updates occuring that may need to update X million document to change the expiry date because a tenant has changed their retention policy. At the same time a front end will be reading this data to show a report which can be filtered.

Reading the mongodb documentation it looks like when I do a write I'll be locking out the reads until the writes have completed. Is there a better way to prevent the expiry of old documents and updating of expiry dates blocking the front ended display this data in a report?

I need a process that will be constantly running deleting documents
  older than some expiry data.

This you can do it by creating a Time to live(TTL) on the expiration date. TTL indexes are special single-field indexes that MongoDB can use to automatically remove documents from a collection after a certain amount of time or at a specific clock time.

  Reading the MongoDB documentation it looks like when I do a write I'll
  be locking out the reads until the writes have completed.

MongoDB wired tiger storage engine gives document level locking. So, in this case, only the documents that are being updated/inserted will be locked. Other documents are available to read.


PostgreSQL replication with periodic log shipping I'm using Windows Server 2008 R2 with 64-bit PostgreSQL 9.2 on both test machines. What I wish to achieve is that one server will be a master and other will be a slave. This is just for tests, finally the servers will work in 1 master - N slaves scenario. Once for each 24h, all changes from the master are meant to be replicated on the slave(s).

I decided to use native replication without streaming (no need for that). Ideally I wish just to deliver manually (via some script) WALs that are needed to the slave, restart the machine and let it update itself.

So I set up my master:

postgresql.conf:
wal_level = hot_standby
archive_mode = on
archive_command = 'copy "%p" "C:\\archive\\%f"'

And my slave machine:

postgresql.conf:
hot_standby = on

recovery.conf:
restore_command = 'copy "C:\\archive\\%f" "%p"'

Then I started the master machine, I wrote a few things to database. I executed the pg_start_backup('base'); command. Then, replaced whole 'data' folder on the slave with files from the master (just without the configs and postmaster.pid file). Now, I executed pg_stop_backup(); and copied files between my master's and slave's archive folders. After starting the slave's PostgreSQL service everything went good - database was there.

I deleted WALs from archive folders, did a small change in the master's db, again start_backup with stop_backup, copied the WALs, and... it does not work.
I even tried the simplest case - once it worked after first copying, I restarted the service. Again failure. Each time it logs:

WARNING:  WAL was generated with wal_level=minimal, data may be missing
HINT:  This happens if you temporarily set wal_level=minimal without taking a new base backup.
FATAL:  hot standby is not possible because wal_level was not set to "hot_standby" on the master server
HINT:  Either set wal_level to "hot_standby" on the master, or turn off hot_standby here.
LOG:  startup process (PID 4320) exited with exit code 1
LOG:  aborting startup due to startup process failure

I double checked my configs and they are fine; Wal level is clearly set to hot_standby. Any idea what am I doing wrong?

This was most probably an issue on my part. I decided to do everything again, but this time making a full streaming replication following the excellent guide here: https://www.youtube.com/watch?v=Zq4zS0ZbXsc

Then, I stripped this solution from the streaming part (removed connection from hba file and a line from recovery.conf). Just in case someone would want to achieve a similar solution - do not compare WAL files by their 'Last Modified' date! You are interested in copying WALs newer by their 'Create' date. This is due to the fact that PostgreSQL creates some files and then for its own reason changes their 'Last Modified' dates to the ones in the past.


Create index with a commentary I would like to add some comments to my indices. So I added some additional information to the index creation script, expecting to see this comment again as soon as I open the script again. But it was gone.

Sample:

CREATE NONCLUSTERED INDEX test3
/*
 Created on ...
 Reason: ...
 Improvement: 95 - 99,8% ...
*/
ON [dbo].[T]
(
/* equal */
[ID], [Nr], [D],
/* inequal */
[S], [OD])
INCLUDE
([A], [C] )

After selection SCRIPT => CREATE SCRIPT in SSMS all comments are gone.

Does anyone know a solution to keep them alive?

There is no way to store text in the definition of an index.

You can use Extended Properties to store descriptive information, however it won't be automatically displayed when you "script" the object.

USE tempdb;

CREATE TABLE dbo.t1
(
    ID INT NOT NULL
);

CREATE INDEX IX_t1
ON dbo.t1(ID);

/*
    This will add a comment named "Comments" to the IX_t1 index
*/
EXEC sys.sp_addextendedproperty @name=N'Comments'
    , @value=N'This is a test index to store comments'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N't1'
    , @level2type = N'INDEX'
    , @level2name = N'IX_t1';

/*
    This displays all comments associated with indices in the current database
*/
SELECT SchemaName = s.name
    , ObjectName = o.name
    , IndexName = i.name
    , PropertyName = xp.name
    , PropertyValue = xp.value
FROM sys.schemas s
    INNER JOIN sys.objects o ON s.schema_id = o.schema_id
    LEFT JOIN sys.indexes i ON o.object_id = i.object_id
CROSS APPLY sys.fn_listextendedproperty(NULL, N'SCHEMA', s.name, N'TABLE',
    o.name, CASE WHEN i.name IS NULL THEN NULL ELSE N'INDEX' END, i.name) xp
ORDER BY s.name
    , o.name
    , xp.name;

Results of above query:

/*
    This provides the ability to delete Extended Properties
*/
EXEC sys.sp_dropextendedproperty @name = N'Comments'
    , @level0type = N'SCHEMA'
    , @level0name = N'dbo'
    , @level1type = N'TABLE'
    , @level1name = N't1'
    , @level2type = N'INDEX'
    , @level2name = N'IX_t1';

/*
    Clean up my test bed
*/
DROP TABLE dbo.t1;

The best-practices solution for this problem is to store object definitions inside a version-control system.


Avoid downtime of Microsoft SQL Server during restarts We are running an SQL Server Standard 2016. From time to time Microsoft updates need to be installed and the server will be restarted.
We have been asked to look into keeping the database available during that restart time.
So we don't need another database server for performance reasons, the database should "simply" be available during the restart process of the regular Windows Updates.
What would be the preferred setup in this situation?

If you have a running query or an open transaction, SQL Server doesn't have a way to move that query or transaction over to another server.
When your SQL Server instance (we'll call Instance A) restarts:

Running queries will be aborted
Open, uncommitted transactions will be rolled back
New queries will get a connection timeout

We can't fix #1 and #2, but we can help #3 in a couple different ways.
If the application needs to do writes, you can lower the amount of downtime by using a high availability feature like failover clustering, Always On Availability Groups, or database mirroring. This lets you patch the standby server (Instance B) first, ahead of time. When it's time to patch Instance A...you don't. You just do a failover from Instance A (unpatched) to Instance B (patched). Your users experience a short 5-60 second outage, depending on server size, running transactions, server tuning, etc. Then, you can take your time patching Instance A, and assuming the servers are the same size, you don't even need to fail back. You can keep running on Instance B until it's the next time for patching.
If the application only needs to do reads, you can use scale-out features like read-only replicas on Always On Availability Groups. Depending on how you configure the AG and your application, connection string changes may be required, like pointing to the AG listener and specifying read-only intent rather than specifying Instance A's name by itself.
If the application does a mix of reads and writes, and you're willing to put in some work on the application side, you can stay online during failovers using the techniques Stack Overflow uses.


View still depends on column even after I have removed the explicit (as well as transitive) dependencies? Assume I have the following table and view:

CREATE TABLE my_table (a int, b text, c text);
CREATE VIEW my_view AS SELECT * FROM my_table WHERE b = 'foo';

The table has some data:

INSERT INTO my_table VALUES (1, 'foo', 'first');
INSERT INTO my_table VALUES (2, 'foo', 'second');
INSERT INTO my_table VALUES (3, 'foo', 'third');

I realise that column b always has the same value 'foo'. I decide that the entire column is unnecessary, and wish to drop it.

Because I like to be explicit about my dependencies, to make sure I don't accidentally drop more than I should, I avoid using the CASCADE keyword, opting instead to redefine the dependent view manually:

CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_table;

The view now no longer has any explicit dependency on column b, so the column should be safe to delete.

ALTER TABLE my_table DROP COLUMN b;

However, as I try to drop column b, I get the following error message

ERROR:  cannot drop table my_table column b because other objects depend on it
DETAIL:  view my_view depends on table my_table column b
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

Why does this error message appear even after removing the dependency on the column to be dropped?

TL;DR: The view definition statement is only evaluated once, not each time the view is queried.

It appears that I have misunderstood how DDL statements like CREATE OR REPLACE VIEW are evaluated.

I had assumed that the definition of the view (or at least, its SELECT clause) was reevaluated verbatim upon querying the view. From that point of view, the expanded form of

CREATE OR REPLACE VIEW my_view AS SELECT * FROM my_table;

would be

CREATE OR REPLACE VIEW my_view AS SELECT a, b, c FROM my_table;

before the column b was dropped, but

CREATE OR REPLACE VIEW my_view AS SELECT a, c FROM my_table;

after column b was dropped.

However, after my attempt to drop column b failed, I decided to inspect the interpreted definition of my_view.

postgres=# \d+ my_view
                 View "public.my_view"
 Column |  Type   | Modifiers | Storage  | Description
--------+---------+-----------+----------+-------------
 a      | integer |           | plain    |
 b      | text    |           | extended |
 c      | text    |           | extended |
View definition:
 SELECT my_table.a,
    my_table.b,
    my_table.c
   FROM my_table;

As it turns out, it seems the view definition is evaluated only once, and that the * wildcard from the SELECT clause was expanded to column list a, b, c upon evaluation. It seems this * wildcard is not re-evaluated as the underlying table has changed, contrary to my initial beliefs.

I assume this means I must redefine the view after the table's column list has changed.


mysqldump stops at 4GB I'm trying to dump a MySQL (5.7) database of a about 150 GB, out of which most data (about 90 GB) is in a single table. I tried using MySQL Workbench (6.3) to do this through the GUI. The process starts, and most tables finish, but the big ones eventually fail.

I got recommended to use the commandline tool mysqldump.exe instead, and I tried that. This seems to work, but also eventually fails on the big tables, and reports:

mysqldump: Got Errno 28 on write

I'm trying to dump to an external drive with plenty of space.

One curious thing, is the failure always seems to happen exactly when the file in question hits 4 GB (or 3,99GB). I've tried looking around for this, but no luck!

I'm running Windows 10, 64-bit, mysqldump is 64-bit, I have 16 GB of RAM in the system. I don't feel like I should hit any 4 GB limits, but I'm probably missing something very basic?

Hope you guys can help me out!

The command in running to do the dump looks like this:

mysqldump -t -u root -p [database] [table] > J:\table_name.sql

The file size limit for a FAT32 filesystem is 4 Gigabytes. You will be hitting this limit.


Retrieve index column I have this index schema: CREATE index comments_idx ON comments (books)

How can I retrieve column name ("books") through SQL query?

The documentation says:

  PRAGMA index_info(index-name);

  This pragma returns one row each column in the named index. The first column of the result is the rank of the column within the index. The second column of the result is the rank of the column within the table. The third column of output is the name of the column being indexed.

> pragma index_info(comments_idx);
seqno       cid         name
----------  ----------  ----------
0           0           books


Regex replace with empty string results in NULL characters in between Replacing a character with regexp_replace results in NUL characters in between.

SELECT REGEXP_REPLACE(aColumn,'c', '')
INTO OUTFILE 'Replaced.csv';

for abcd results in:

ab d (infact: abNULd)

This does not occur with just REPLACE

using MySQL 8


Error on SELECT - Primary filegroup is full A SQL Server 2008 R2 database view unions several transaction table. The query has been slow over the couple of months and the dev team is planning to recreate the query. I need to ETL the results into a flat file and purge them from the DB. When I tried to query the view (of 5M records) this error showed up:

  Msg 1105, Level 17, State 2, Line 1
  Could not allocate space for
  object 'dbo.SORT temporary run storage:  140744470495232' in database
  'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by
  deleting unneeded files, dropping objects in the filegroup, adding
  additional files to the filegroup, or setting autogrowth on for
  existing files in the filegroup.

What is the recommended file growth I should set, how is this error managed or solved?
Thanks in advance.

The quickest fix would be to restart the SQL Server and Tempdb will be recreated with default size and empty files.

But if it's a production server you can't really restart it when you want. A real fix would be to add a new file on a different drive and run your queries.

An example would be (new file of starting size 1 MB, increase 100 MB, limit 500 MB):

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'newtempfile', FILENAME = N'e:\newtempfile.ndf' , SIZE = 100MB, MAXSIZE = 500MB , FILEGROWTH = 100MB )
GO

And then, when you have time, check what's using Tempdb so much. But likely you'll still need space for TempDB, so you'd better plan space for this and assign enough space for this db, as it's very important for a healthy system (you can simply look at it as a RAM part).

PS1: check if you don't have a Cartesian product anywhere in your select statements, because that number seems a bit high.

PS2: If you have enough free space on the TempDB drive, check if the files didn't get to their limit and autogrowth is disabled. If yes, enable autogrowth (not by percent, but by some specific size you feel comfortable with).

PS3: a good solution would be to break that ETL process into smaller transactions. Instead of removing 1 Bil records at once, do it by 1000 mill .. or play with batch sizes until you feel safe with the length of the process and the size of the files. You'll likely obtain the result faster and not have huge increases in space at once.


How to select records from two tables transactionally I have table Messages that has fields, and another table called SentMessages, which has a one-to-one relationship to the Messages table.

Messages stores information about the message, including its text, and subject, and the recipient, etc.

create table Messages
(
    Id bigint not null identity(1, 1) primary key,
    Text nvarchar(max),
    Subject nvarchar(400)
    -- other fields
)

SentMessages stores information related to the transportation of the message to the clients.

create table SentMessages
(
    Id bigint not null primary key,
    SentOn datetime not null,
    DeliveredOn datetime null,
    -- other fields
)
go
alter table SentMessages with check add constrait [FK_SentMessages_Messages]
foreign key([Id])
references Messages ([Id])

They are designed to be separate, so please no advice on merging them.

Now the logic is to find all messages that are not sent yet, and send them. Basically a simple not in clause in a single-threaded scenario would do the trick:

select *
from Messages
where Id not in
(
    select Id
    from SentMessages
)

But this database is used by many concurrent threads, therefore it's possible for a message to be taken by more than one thread, hence be sent twice or even more.

If they were a single table, I could use update select statement to select unsent messages in a single transaction.

What options do I have though to make selection transactional, so that a message won't be sent twice?

You can't have the cake and leave it whole; on one hand, you want to allow maximal concurrency, and on the other hand, you want each process to be 'isolated' and not visible to other processes.
SQL Server offers Service Broker exactly for these queuing / asynchronous processing challenges. Check it out, it is an awesome infrastructure that will most likely work better than any custom user application solution.

Before we had Service Broker, what we used to do for similar challenges was create a 3rd table, call it "Email Send Queue" or something like that.
Let it hold just the IDs of the messages that need to be sent, so every time you enter it into 'messages', also enter the ID to the queue.

When a process needs to pick a message to send, you open an explicit transaction in the REPEATABLE READ isolation level, and hold it open until the sending process completes.
Every new process that needs to pick a job, uses the

SELECT TOP 1 ID FROM EmailSendQueue WITH (READPAST) ORDER BY ID ASC

This way, the reading process will simply skip locked rows, and take the next one that is not yet locked, i.e. - not yet being processed.
Even if you were able (and you can BTW...) hold explicit locks on the original tables, without the READPAST trick, it would be a recipe for blocking hell.
Here is a sample, simplified, untested code to do the trick.

CREATE TABLE [EmailSendQueue]
(
[MessageID] BIGINT NOT NULL
-- Better use a heap here to prevent page level resrouce contention
PRIMARY KEY NONCLUSTERED
REFERENCES [Messages] (ID)
-- In case someone deletes a message that has not been sent yet,
-- and to prevent deleting a message which is in process
ON DELETE CASCADE
ON UPDATE NO ACTION
);

-- New messages into queue when ready to send - allows delayed send logic as well...
INSERT INTO [EmailSendQueue]([MessageID])
VALUES (1), (2), (3);

-- First Process picks up #1
-- Second process will pick up #2, if #1 is still locked, or is completed...
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN TRANSACTION;

DECLARE @MessageID BIGINT;

SELECT TOP (1) @MessageID = [MessageID]
FROM [EmailSendQueue] WITH (READPAST, XLOCK)
ORDER BY [MessageID] ASC;

DECLARE @StartTime DATETIME = GETDATE();

-- Process and send message
EXECUTE [SendMessageProcedure] @MessageID;

-- Send successful
IF @@Error = 0
BEGIN
-- Now the message has been sent, and can go into SentMessages
    INSERT INTO [SentMessages] ([ID], [SendOn], [DeliveredOn])
    VALUES (@MessageID, @StartTime, GETDATE());
    DELETE FROM [EmailSendQueue] WHERE [MessageID] = @MessageID
END;

-- If send unsuccesful, you can do nothing, or introduce some kind of retry logic
-- You can add a 'Retry' column to the queue, and increment it on every attempt
-- A scheduled backgrond process can delete all messages that  exceed a retry threshold
-- This will prevent 'poisoning' the queue with error messages

COMMIT TRANSACTION;


Restoring a database backup to a local machine in MySQL I have downloaded my .sql file of my database server. It is in the D: drive of my Windows machine.

I want to restore the back up in my this machine. I used:

mysql database -u root < backupfile.sql

Where database is my new database name in this machine. My confusion is that my backupfile.sql is in the D: drive and thus I guess there is error.

ERROR 1064 <42000>:You have an error in your SQL syntax;Check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql database -u root < backupfile.sql' at line 1.

You should login to mysql like this

C:\> mysql -uroot -p <hit enter>
Enter password:

Next, select the database you want to load the data into

mysql> CREATE DATABASE IF NOT EXISTS mynewdb;
mysql> USE mynewdb

Then, run the script

mysql> source D:\backup\backup.sql

Give it a Try !!!


Is my table is stored as heap or b-tree? I have a SQL Server 2005 database. It’s .MDF & .LDF can be downloaded at https://www.dropbox.com/s/uj2mw410jm2m078/MyDB.rar?dl=0

Now I want to know whether two table are stored as heap or b-tree(linked list), so I run the following commands:

USE CR_YPLS8_NEW_QB;

select
    sys.objects.name,
    sys.objects.object_id,
    sys.partitions.index_id,
    sys.system_internals_allocation_units.first_page,
    sys.system_internals_allocation_units.first_iam_page
from
    sys.objects, sys.partitions, sys.system_internals_allocation_units
where
    (sys.partitions.object_id = sys.objects.object_id) and
    (sys.system_internals_allocation_units.container_id = sys.partitions.partition_id) and
    ((name = 'cardfa') or (name = 'pcspkc'));

Where 'cardfa' and 'pcspkc' are the two user tables I want to investigate.

The result is below:

Based on https://technet.microsoft.com/en-us/library/ms189051%28v=sql.105%29.aspx, since both table’s index_id is 1, they are all stored as b-tree(the data pages in the leaf level is in linked list).

However, in the result table, the first row has first_page value (filenum, pagenum) = (1, 0x6F2A8) which is an invalid pointer.

The second row has first_page value (filenum, pagenum) = (1, 0x5941) = (1, 22849).

After I using the following commands:

DBCC TRACEON(3604);
DBCC page(CR_YPLS8_NEW_QB, 1, 22849, 3);

I get the following result:

After checking each column in each slot, I find the data is completely different from the actual data stored in 'cardfa' table. So the first_page for the second row is also invalid.

And I then find the 'cardfa' table is actually stored in heap and can be referenced by first_iam_page instead of first_page, which is inconsistent with the search result. Why?

Thanks

If your table has a clustered index it is stored as a b-tree otherwise it is a heap.


Find when a specific database went offline and the reason for same Find when a specific database went offline and the reason for same and when did it recover back.

All I know there is a SCOM alert set for the server and I got a notification as Database is offline and later when I checked I found it as online. So, I want to check at what time it went offline and how and when did it come online?

At minimum, please check sql logs, for example, if I purposely take a db offline using

alter database [TestDB] set offline with rollback immediate;

I can see in SQL Server log, the following message

  Date      10/24/2016 12:28:57 PM Log      SQL Server (Current - 10/24/2016
  12:00:00 AM)

  Source        spid51

  Message Setting database option OFFLINE to ON for database 'TestDB'.


Replacing a table with new version I am a programmer changing the way a process works, and will be using a different table to do this. The process is just sending emails from a web application. Currently the database scripts run during a batch each night and create entries in a table that contain the subject, and the entire body of the email.
I am moving to a process where only the variable elements will be held in the table.

I am just wondering what the accepted, or most common strategy is for doing this. I've spoken to a few of our database people and they suggested to create an entirely new table and use this going forward.

I am fine with doing it this way, and was wondering if it's a good idea to alter the current table's name, to something like EmailTableOld and create my new table with the original name EmailTable so I don't have to create additional permission scripts and set up new synonyms while removing the old ones.

Is this a good idea? I have identified everything that refers to the table and there are only a few things mostly security granting scripts, and the scripts that actually create email content. The table does not reference or is referenced by any other tables currently. The entire contents of the old table must be kept.

Any suggestions etc are welcome

sp_rename or swapping schemas are the typical ways to accomplish this. Neither really saves you anything about permissions scripts because whether you renamed the new object or swapped it into the dbo schema, it still needs to have permissions applied to match the old version of the table.

For a long time I've preferred swapping schemas simply because I used to do this in an automated job and the "Caution: renaming objects blah blah" warning messages were filling the SQL Server Agent logs. That message is not suppressible but the ALTER SCHEMA...TRANSFER statement does not raise such a message. I have asked for a way to suppress this warning but it was rejected.

(Of course another avenue to consider is to archive the current data and alter the current table to meet the new requirements - but I don't know if that's something you haven't considered, or if it has and there's a reason you can't do that.)


Can I query stored procs in all databases with one query without dynamic sql If I want to query all databases on a server to see if a stored procedure is present, I can combine a sp_executesql and run a cursor over a query similar to the following:

SELECT
     'select ' + '''' + name + '''' + ', name from [' + name
   + '].sys.procedures WHERE name = ''usp_MyProc'' COLLATE SQL_Latin1_General_CP1_CI_AI '
FROM sys.databases
-- I get a collation error from the following
WHERE name NOT IN ('ReportServer', 'ReportServerTempDb');

Can I do the same thing without dynamic sql and without the cursor?

You can do it without the cursor (or without a cursor-like operation). For example you can do it slightly differently like this:

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + CHAR(13) + CHAR(10)
  + N'select ''' + db.name + ''', p.name
        from ' + QUOTENAME(db.name) + N'.sys.procedures AS p
        WHERE p.name = N''usp_MyProc''
        COLLATE SQL_Latin1_General_CP1_CI_AI;'
    FROM sys.databases AS db -- WHERE ...;

EXEC sp_executesql @sql;

You can also avoid multiple result sets going back by doing this:

CREATE TABLE #x(d SYSNAME, n SYSNAME);

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + CHAR(13) + CHAR(10)
  + N'insert #x select ''' + db.name + ''', p.name
        FROM ' + QUOTENAME(db.name) + N'.sys.procedures AS p
        WHERE p.name = N''usp_MyProc''
        COLLATE SQL_Latin1_General_CP1_CI_AI;'
    FROM sys.databases AS db -- WHERE ...;

EXEC sp_executesql @sql;

SELECT d, n FROM #x ORDER BY d, n;

If you're really only after a single, specific stored procedure though, see Martin's answer.


How to copy part of a database with inconsistent privileges? I have a Postgres database and I would like to copy certain tables from a single schema into a new empty database.

So far I have copied a schema-only dump from from the old DB and applied it to a new empty DB.

I have also created a data-only dump of the specific tables I want using -t [table-name].

Unfortunately the tables are owned by different roles, so when I try to restore the DB or modify the schema prior to data loading, I am getting all kinds of errors.

My guess is that I need to do some kind of check/modification of roles prior to creating the dumps.

What is the correct process for duplicating part of a DB including roles? - keeping in mind tables could have different owners.

You could perform the pg_dump with the parameters --no-owner and --no-acl, so no permissions or links to any users are presents in the dump.


Why does adding a TOP 1 dramatically worsen performance? I have a fairly simple query

SELECT TOP 1 dc.DOCUMENT_ID,
        dc.COPIES,
        dc.REQUESTOR,
        dc.D_ID,
        cj.FILE_NUMBER
FROM DOCUMENT_QUEUE dc
JOIN CORRESPONDENCE_JOURNAL cj
    ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
WHERE dc.QUEUE_DATE <= GETDATE()
  AND dc.PRINT_LOCATION = 2
ORDER BY cj.FILE_NUMBER

That is giving me horrible performance (like never bothered to wait for it to finish).  The query plan looks like this:

However if I remove the TOP 1 I get a plan that looks like this and it runs in 1-2 seconds:

Correct PK & indexing below.

The fact that the TOP 1 changed the query plan doesn't surprise me, I'm just a bit surprised that it makes it so much worse.

Note: I've read the results from this post and understand the concept of a Row Goal etc.  What I'm curious about is how I can go about changing the query so that it uses the better plan.  Currently I'm dumping the data into a temp table then pulling the first row off of it.  I'm wondering if there is a better method.

Edit For people reading this after the fact here are a few extra pieces of information.

Document_Queue - PK/CI is D_ID and it has ~5k rows.
Correspondence_Journal - PK/CI is FILE_NUMBER, CORRESPONDENCE_ID and it has ~1.4 mil rows.

When I started there were no other indexes.  I ended up with one on Correspondence_Journal (Document_Id, File_Number)

Since you get the correct plan with the ORDER BY, maybe you could just roll your own TOP operator?

SELECT DOCUMENT_ID, COPIES, REQUESTOR, D_ID, FILE_NUMBER
FROM (
    SELECT dc.DOCUMENT_ID,
           dc.COPIES,
           dc.REQUESTOR,
           dc.D_ID,
           cj.FILE_NUMBER,
           ROW_NUMBER() OVER (ORDER BY cj.FILE_NUMBER) AS _rownum
    FROM DOCUMENT_QUEUE dc
    INNER JOIN CORRESPONDENCE_JOURNAL cj
        ON dc.DOCUMENT_ID = cj.DOCUMENT_ID
    WHERE dc.QUEUE_DATE <= GETDATE()
      AND dc.PRINT_LOCATION = 2
) AS sub
WHERE _rownum=1;

In my mind, the query plan for the ROW_NUMBER() above should be the same as if you had an ORDER BY. The query plan should now have a Segment, Sequence Project and finally a Filter operator, the rest should look just like your good plan.


Postgres: modify existing search_path (preserve current values) Sometimes I would like to extend the existing search path, rather than replace it, say:

To start with say it is already set like so:

SET search_path TO schema_b, schema_c, public;

I want to add my schema to the front of the line:

SET search_path TO schema_a + search_path;   --doesn't work

I'm thinking in analogy to what I'd do in BASH:

PATH=path_a:$PATH

Bonus question, perhaps related: is there a way I can store the current path temporarily, so I can change it to something totally different, and then restore it without having to know what it was?

SELECT set_config('search_path', 'fred,'||current_setting('search_path'), false);

The false says it's not a transaction-LOCAL setting.

For the bonus question, you can store the value in a custom setting:

SELECT set_config('tmp.search_path', current_setting('search_path'), false);

From version 9.2 on, you don't even have to define this setting in postgresql.conf.


Is there a performance hit by breaking out tables into different schemas in SQL Server? Is there any kind of performance hit when querying by placing tables in different schemas in SQL Server.

For instance if I had 2 tables, Table1 and Table2, and put Table1 into the test schema and Table2 into the history schema, and then I tried to link the tables in a query, is this implicity slower than having the 2 tables in the same schema?

No there is no performance hit if the tables are in two different schemas.


Postgresql json column to view I have a table that looks like this

id |   json_column
---+------------
 1 | {"text1":"a", "text2":"b", "text3":"c", ....}
 2 | {"text1":"b", "text2":"c", ....}

and I want it to be like this

text1 | text2 | text3 | ....
a     | b     | c     | ....
b     | c     | d     | ....

one variant I found to get this result is using json_populate_record() and creating a new type x as (text1 text, text2 text, text3 text,....) so i can execute

select (json_populate_record(null::x, json_column)).* from table

Is there another way to do this without creating a type like above? All new columns can have the type text, so is there a shortcut maybe?

My problem is that I have 50 keys in one json field in the form of aaaa_bbbb_cccc_d so creating one type for this by hand would take way to much time

I am using 9.5.4

There is no way that saves you from specifying all JSON attributes in the select list, whether implicitly through the "dummy type" or explicitly, e.g. using something like this:

select json_column ->> 'text1' as text1,
       json_column ->> 'text2' as text2,
       ...
from the_table;

What you can do, is to make this simpler by automatically creating a view with all attributes based on the distinct attributes in the JSON documents.

The following code will re-create a view with all distinct keys from the JSON column:

do
$$
declare
  l_keys text;
begin
   drop view if exists v_json_view cascade;

   select string_agg(distinct format('json_column ->> %L as %I',jkey, jkey), ', ')
     into l_keys
   from the_table, json_object_keys(json_column) as t(jkey);

   execute 'create view v_json_view as select '||l_keys||' from the_table';
end;
$$
;

You will need to run the above every time the list of keys in all json documents changes. Theoretically this could be done in a trigger but that's probably not a good idea if you run many updates on that table.

If the total number of JSON keys is somewhat "stable", you could schedule a cron job to re-create that view in regular intervals.

You are also limited by the maximum number of columns in a table or view. If you have more (distinct) keys than approx. 1600 (maybe less) the above will fail.


What data type are the numbers in a SQL Server statistics object? Specifically I'm most interested in:

The 'All density' number in the density vector
Numbers in the histogram

E.g. are they floats / decimals? What precision / scale / length do they have?

The motivation for this is that I think I might be seeing rounding errors, so would like to know how precisely we should expect cardinality estimations to match with these stats.

I'd be happy for an answer for any version of SQL Server, but 2012 and 2014 are most relevant to me.

Below are the field names and datatypes of each of the result sets as reported by SQL Server (I tested on SQL Server 2012, 2014, and 2016, though I would expect these to remain consistent across SQL Server versions):

DBCC SHOW_STATISTICS() WITH STAT_HEADER

[Name]                  NVARCHAR(128)
[Updated]               NVARCHAR(20)
[Rows]                  BIGINT
[Rows Sampled]          BIGINT
[Steps]                 SMALLINT
[Density]               REAL
[Average key length]    REAL
[String Index]          NCHAR(3)
[Filter Expression]     NVARCHAR(MAX)
[Unfiltered Rows]       BIGINT

DBCC SHOW_STATISTICS() WITH DENSITY_VECTOR

[All density]           REAL
[Average Length]        REAL
[Columns]               NVARCHAR(4000)

DBCC SHOW_STATISTICS() WITH HISTOGRAM

[RANGE_HI_KEY]          INT -- Datatype varies based on datatype of first key column
[RANGE_ROWS]            REAL
[EQ_ROWS]               REAL
[DISTINCT_RANGE_ROWS]   BIGINT
[AVG_RANGE_ROWS]        REAL

To be clear, these datatypes are the result set datatypes from the DBCC commands; there is no indication of the actual datatypes used to store that info. However, it seems reasonable to assume that the datatypes of the result set are the same as those used the store the info.

Also, for anyone curious as to how I was able to determine these datatypes: I got the info from a SQLCLR stored procedure I created as part of the SQL# library. It is called DB_DescribeResultSets and is similar to sys.dm_exec_describe_first_result_set but handles multiple result sets, temp tables, dynamic SQL, etc (because it runs the query instead of just parsing it). The DB_DescribeResultSets stored procedure is not available in the Free version, though. However, you could do the same thing in any .NET app. It just requires running a query through a SqlDataReader and then using the GetSchemaTable method to get the result set schema.

I ran DB_DescribeResultSets as follows:

EXEC SQL#.DB_DescribeResultSets
  @TheQuery = N'DBCC SHOW_STATISTICS(N''msdb.dbo.sysjobs'',
                                     nc1);',
  @RowNumberToGetValuesFrom = 1,
  @ResultSetNumberToDescribe = 0, -- 0 = all result sets
  @ShowHiddenFields = 1,
  @ResultSet = ''; -- this is an XML OUTPUT param and can't have a default in SQLCLR