PostgreSQL network compression for result set? I was reading about network compression with PostgreSQL to see if there was anything that offered that which would permit big result sets to transfer over the wire to remote web servers. It seems there is nothing of this sort. Is there a solution out there to do that, that isn't SSL?

SSL

It seems at one point there was a solution involving SSL. Having read the threads pertaining to that, it seems that the solution is no longer advisable as it leaves the system vulnerable to a CRIME worldwide bug.

SSL Compression doesn't work
sslcompression / PGSSLCOMPRESSION not behaving as documented?

Though not advisable, it seems that you can re-enable SSL Compression at least currently with OPENSSL_DEFAULT_ZLIB but that it will be removed in the future and that it's currently disabled at compiled time in openssl.

no-zlib         [default]
no-zlib-dynamic [default]

This part of the documentation describes how you can do it by first setting up an SSH tunnel (outside of Postgres) and then connecting to that. If you add the -C flag to the ssh command, it should enable compression.

https://www.postgresql.org/docs/current/static/ssh-tunnels.html


mysqld not starting up I installed mysql on ubuntu. mysqld does not startup with the following errors -

2017-04-16T00:22:35.320639Z 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2017-04-16T00:22:35.321014Z 0 [Note] InnoDB: Buffer pool(s) load completed at 170415 19:22:35
2017-04-16T00:22:35.321036Z 0 [Note] Plugin 'FEDERATED' is disabled.
mysqld: Table 'mysql.plugin' doesn't exist
2017-04-16T00:22:35.321354Z 0 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
2017-04-16T00:22:35.322301Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2017-04-16T00:22:35.322490Z 0 [Warning] Failed to set up SSL because of the following SSL library error: SSL context is not usable without certificate and private key
2017-04-16T00:22:35.323247Z 0 [Note] Server hostname (bind-address): '*'; port: 3306
2017-04-16T00:22:35.323361Z 0 [Note] IPv6 is available.
2017-04-16T00:22:35.323379Z 0 [Note]   - '::' resolves to '::';
2017-04-16T00:22:35.323411Z 0 [Note] Server socket created on IP: '::'.
2017-04-16T00:22:35.323450Z 0 [ERROR] Could not create unix socket lock file /var/run/mysqld/mysqld.sock.lock.
2017-04-16T00:22:35.323464Z 0 [ERROR] Unable to setup unix socket lock file.
2017-04-16T00:22:35.323476Z 0 [ERROR] Aborting

2017-04-16T00:22:35.323497Z 0 [Note] Binlog end
2017-04-16T00:22:35.323545Z 0 [Note] Shutting down plugin 'ngram'

Please help.

Thanks

It does not solve the problem as such but you should be able to bypass it by adding the skip-external-locking to my.cnf under [mysqld]

Should at least get the server starting while you find out the root of the problem.

https://dev.mysql.com/doc/refman/5.7/en/external-locking.html


Update query using two tables I want to update the ProductStock field in tblProduct when this query is run. The ProductStock should minus the Quantity field in tblOrderProduct. (ignore the * sign, I just wanted to see if multiplication worked). The user should be prompted to enter the OrderId beforehand
Thanks

You must specify the condition in a new column where you select the OrderID column.
So, in the 3rd column select Table tblOrderProduct and Field OrderID and place [Enter Order ID] on the Criteria line of this column.
Replace the * by -. This will subtract the Quantity from the ProductStock.
Well, and you don't need the Quantity column. Remove it.
In the SQL view you will see
UPDATE tblProduct
INNER JOIN tblOrderProduct ON tblProduct.ProductID = tblOrderProduct.ProductID
SET tblProduct.ProductStock = [ProductStock]-[Quantity]
WHERE (((tblOrderProduct.OrderID)=[Enter Order ID]));


Pagination while sorting a non unique text column What are the ways to paginate a table on a non unique text column?

On a unique timestamp column, one can create an index on it and execute the following to move forward

SELECT * FROM users WHERE created > '<last element created timestamp>' ORDER BY created ASC LIMIT 20;

If it is a numerical primary key

SELECT * FROM users WHERE id > 100 ORDER BY id ASC LIMIT 20;

But what can one do when the table is ordered by non unique text column (first_name for example)?

First thing that comes to mind is the OFFSET method, but there are performance penalties on large tables.

Alternatively, one could use a cursor, but the internet agrees that it not recommended way on a public web application.

One solution that performs well would be being smart with indexes.

First, create an index on two columns, one being your non unique text column and a second one being your id column (it can be any column that can be differentiated between non unique text column rows):

CREATE INDEX user_name_uuid_idx
    ON public.user USING btree
    (name COLLATE pg_catalog."default" ASC NULLS LAST, uuid ASC NULLS LAST)
    TABLESPACE pg_default;

NOTE: the order of columns does matter. Use the same order that is used in the select query

Then use this query to get x page.

SELECT * FROM user WHERE (name, uuid) > ('Misty Wyman', '070d905d-2155-492f-b811-2c9d30015299') ORDER BY name, uuid LIMIT 20;


ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES) I know this might be a duplicate of some other posts. But those solutions didn't work for me and I've lot of issues within those answers. Neither could I comment nor post in there, I'm posting a question.

In my case, I logged into mysql as 'root' and tried to import a sql file into the database. I then exited, did nothing and when again tried to login with the same user. But this time it was showing as

bash-2.05$ mysql -u root -p
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

And I tried all different solutions(suggestions from other posts) like mysql -u root -ppwd -h127.0.0.1 mysql -u root -ppwd --protocol=TCP. These suggestions didn't work for me. I couldn't login as the root and without that I will not be having permissions to change any privileges. I'm sure user and password are the correct ones.

Apart from all this, I'm using this database for a drupal website. The website gives me an error as

PDOException: SQLSTATE[28000] [1045] Access denied for user 'root'@'localhost' (using password: YES) in lock_may_be_available() (line 167 of /home/apache2/htdocs/website/includes/lock.inc).

I couldn't trace what's going wrong with this.

If you have access to restart the mysql daemon you can follow the mysql directions for recovering your root account.


Create View using table and column names from data in a table, then build SQL I need to create a view using data in a table for the table name and column name.  Preferably I’d like to do it with set processing. I have a sample of doing this for one record in the table with a script of stored procedure, which won’t do for the application.  The resulting view pulls data from a data logger for display on a dashboard, so it needs to be current and fast.  That’s why I think a view would work best, but I’m having trouble coming up with the SQL.

I have a table that looks like the following.  This is an example of two records, there are tens of thousands of them.  There will be one or more duplicate table names, which corresponds with one or more column names in the LOGGER_FIELD column.

From data in this table, I’d like to create a view that would have this sql:

select TmStamp, site_id, value
from
     (select TmStamp, G0025890, G0025891
      from   [BIG SLOUGH (STN 953)_HOURLY]
      where TmStamp = (select max(TmStamp) from [BIG SLOUGH (STN 953)_HOURLY])) p
unpivot
(value FOR site_id IN (G0025890, G0025891))AS unpvt;

The table [BIG SLOUGH (STN 953)_HOURLY] looks like:

The table name in the FROM is tabname in the view, and the values in LOGGER_FIELD are the column names.  The ending view will look like:

You can use a dynamic query.

create table my_table (tabname varchar(200), site_id int, logger_field varchar(200));

insert into my_table values
('[big_slough]', 2589, 'G0025890'),
('[big_slough]', 2590, 'G0025891');

create table [big_slough]
(
    TmStamp datetime,
    RecNum int,
    Batt_Volt decimal(18,2),
    G0025890 decimal(18,2),
    G0025891 decimal(18,2)
);

insert into [big_slough] values
('20170725 08:00:00', 0, 13.25, 32.52, 29.63229),
('20170725 09:00:00', 1, 13.48, 32.51, 29.61947),
('20170725 10:00:00', 2, 14.07, 32.50, 29.61757),
('20170725 11:00:00', 3, 14.14, 32.49, 29.61356),
('20170725 12:00:00', 4, 13.43, 32.49, 29.61185),
('20170725 13:00:00', 5, 13.34, 32.50, 29.60452);

GO

8 rows affected

create procedure sp_generate_view
(
@tabname varchar(200)
)
as
begin

    declare @cols nvarchar(max);
    declare @cmd nvarchar(max);

    set @cols = stuff((select ',' + quotename(logger_field)
                       from my_table
                       for xml path(''),
                       type).value('.', 'nvarchar(max)'),1,1,'');

     set @cmd = 'select TmStamp, site_id, value
                 from
                      (select TmStamp, ' + @cols + '
                       from   ' + @tabname + '
                       where TmStamp = (select max(TmStamp)
                                        from ' + @tabname + ')) p
                 unpivot
                 (value FOR site_id IN (' + @cols + ')) AS unpvt';

    exec (@cmd);

end
GO

✓

exec sp_generate_view @tabname = '[big_slough]';
GO

TmStamp             | site_id  | value
:------------------ | :------- | :----
25/07/2017 13:00:00 | G0025890 | 32.50
25/07/2017 13:00:00 | G0025891 | 29.60

dbfiddle here


Executed a Stored Procedure in the Production Server and the following warning message is shown I executed a stored procedure on the production server; and though it shows that query has been successfully completed it doesn't allow me to close SQL Server Management Studio and the below warning message is shown. Please advice I'm more concerned because this is the production server.

When you are closing SSMS, it check for

IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION

Or you can use sys.dm_exec_requests

SELECT open_transaction_count FROM sys.dm_exec_requests WHERE session_id = @SessionId

In this case you have an open transaction so on connection closure process SQL Server will roll back that transaction. In this case you need to take a look at the stored procdure dbo.GenActiveItemPriceList and identify what is this transaction is which is not commited and it's furter impact.


Is SQL Server 2012 predicate precedence overridden by all-at-once concept? Is SQL Server 2012 predicate precedence overridden by the all-at-once concept?

I have been reading Querying Microsoft SQL Server 2012 by Itzik Ben-Gan, Dejan Sarka, and Ron Talmage. Chapter 3 lesson 1 Page 67 describes predicate precedence. Towards the bottom of the page it is written that the SQL Server all-at-once concept will override predicate precedence based on cost.

Below is a cut and paste from the book in PDF format:

  The reality, though, is different. SQL Server does internally support a short-circuit concept; however, due to the all-at-once concept in the language, it is not necessarily going to evaluate the expressions in left-to-right order. It could decide, based on cost-related reasons, to start with the second expression, and then if the second expression evaluates to true, to evaluate the first expression as well.

If this is correct then predicate precedence as described in this book and an article by Itzik Ben-Gan that he posted to SQLmag http://sqlmag.com/t-sql/short-circuit are irrelevant. The all-at-once concept will always choose the predicate precedence.

I believe that if the expressions are not evaluated according to precedence, left to right, then precedence would in fact be superseded by the all-at-once concept.

I believe I understand what the authors are trying to teach now (thanks to everyone's comments). I believe they are stating that if I am writing SQL that contains logic that depends on short-circuiting then the logic might be flawed due to the all-at-once concept which will evaluate predicates based on cost and not by order of precedence. So, the answer to this question is yes. The all-at-once concept does override predicate precedence.

I am reading the book right now in preparation for the 70-461 exam and I wanted to confirm with other writers of SQL if I truly did not have control over the order in which the predicates of my SQL statements are evaluated. It turns out that I do not. A CASE does not even guarantee the order according to this Connect item: Aggregates Don't Follow the Semantics Of CASE.

The Microsoft Technet article All-at-Once Operations in T-SQL by Saeid Hasani provides a more detailed explanation of the all-at-once operations concept. The Technet article does not state that the cost of a predicate is the reason why some predicates are evaluated before others which is the reason given in the book Querying Microsoft SQL Server 2012.


How to do hierarchical queries without CTE tables or recusive queries in Snowflake? (parent, child relationships) I have several tables in Postgres that we are migrating to Snowflake. Some of these table are hierarchical (a foreign key point to the same table), normally to query this data I use CTE tables and/or recursive SQL statements.

As far as I can tell Snowflake does not support these operations. Are there any other queries that can be made to mimic this or work around this?

"Currently, Snowflake does not fully support common table expressions (CTE) in DDL operations."

Snowflake documentation: https://docs.snowflake.net/manuals/sql-reference/constructs/with.html

see: https://www.postgresql.org/docs/current/static/queries-with.html

Snowflake currently supports recursive CTEs.

I just build an sql to parse an account tree to consolidate amounts at the branch level.

From the source account tree data, main is used to build each branch & immediate sub branch/leaves combination

with main as (
  select a.scode as src , b.scode as tgt
  from accttree a
    join accttree b on (a.totalinto=b.scode)
), tree as (
  select src, NULL as bridge, tgt
  from main
  --where hprop = 15365
  union all
  select m.src, m.tgt as bridge, t.tgt as tgt
  from main m
    join tree t on (m.tgt = t.src)
)
--Tree gives the branch and leaf relation for the entire set of branches
Select *
from tree


Create SQL Script for Statistics I wanna create script my whole table, including all statistics and indexes.

In SSM, When I right click table and create script, it gives me script for Indexes for not for statistics.

If I right click indexes, then there is an option to generate script, but this option is disabled when I right click Statistics.

Can anyone tell me how do I generate script for statistics.

Thanks

In SSMS, open Tools -> Options and go to the "SQL Server Object Explorer" item in the left-hand pane. Expand that and you should see a "Scripting" subitem. From there, navigate to the "Table and View Options" section and you should see an option to say whether statistics are scripted or not.


SQL OrderBy When using an In() query Example query

SELECT * FROM Table WHERE ID in (5,3,4)

How do I adjust this query so that the order of the returned entries follows the input of the ID's (IE, 5 first, 3 second, 4 third).

Edit - To be clear the ID's a dynamically generated list that is in the right order.

Your best bet is going to be to split the list into a temp table.

I'm using Jeff Moden's DelimitedSplit8k.  You can find the code here.  Note it includes an ItemNumber column that is the values in order.

DECLARE @DelimString varchar(50) = '4,5,3'

SELECT * INTO #DelimList FROM dbo.DelimitedSplit8k(@DelimString,',')

SELECT Table.*
FROM Table
JOIN #DelimList
    ON Table.Id = #DelimList.Item
ORDER BY #DelimList.ItemNumber

This has the added benefit of being faster for large numbers of values in the delimited list.


DBCC CLONEDATABASE missing schemas I have used DBCC CLONEDATABASE to create a copy of a database without any data. I have now found that some schemas were not created, thus some objects have no schema. Its hard to reproduce this as it is database specific, i.e. I have cloned 9 successfully but 1 with this issue. All the schemas would be owned by dbo.

Because I can't get this reproduced, why were certain schemas not be created in the clone, would anyone have any idea what to check or if there is a way to debug the dbcc command?

I'm using SQL Server 2016 SP2 CU1.

The database was originally created before SQL Server 2005. If I go select name, schema_id, schema_name(schema_id) SchemaName, * From sys.objects in certain objects the schema name is null and the schema id isn't in sys.schemas. A few tables and synonyms are effected, and I can't twirl down either the tables node or synonyms node in object explorer of ssms (killed session error!).  Also to clarify this is only an issue in the cloned database. I also tried recreating the synonym and transferring ownership of the table but I get an error saying the table can't be found.

Sounds like you had objects in SQL Server 2000, and their owner was dropped, leaving those objects "orphaned." When the database was upgraded to any version >= 2005, the "owner" was changed to a schema_id, but for those owners that had been dropped, that schema didn't exist.

To fix this, you can create a bunch of dummy schemas until you have one that correlates to each missing schema_id, and then run a statement like this for each object in each missing schema:

ALTER SCHEMA dbo TRANSFER dummy_schema.object_or_synonym;

The only complication might be if you have objects with the same name but different schema_id values; these will collide. Once you've sorted it all out, then you can drop all the dummy schemas.

At some point you might consider creating a new empty database in a modern version, copying your tables and data to it, and then getting rid of the old one. Might eliminate more surprises like this in the future.


Accumulate values into an array with Postgres Currently I have this query:
select
    sum(case when my_table.property_type = 'FLAT' then my_table.price else 0 end) as Flat_Current_Asking_Price,
    sum(case when my_table.property_type_mapped = 'SEMIDETACHED' then my_table.price else 0 end) as Semidetached_Current_Asking_Price
from
    my_table;

So if my_table has the values:
property_type | price
--------------+-------
FLAT          | 5000
SEMIDETACHED  | 9000
FLAT          | 6000

the query will return:
Flat_Current_Asking_Price | Semidetached_Current_Asking_Price
--------------------------+-----------------------------------
11000                     | 9000

How can I replace the sum to accumulate the values into arrays to get?
Flat_Current_Asking_Price | Semidetached_Current_Asking_Price
--------------------------+-----------------------------------
{5000, 6000}              | {9000}

If your PostggreSQL version is 9.4 or later use FILTER clause:

select
    array_agg(my_table.price) filter(where my_table.property_type = 'FLAT' ) as Flat_Current_Asking_Price,
    array_agg(my_table.price) filter(where my_table.property_type = 'SEMIDETACHED') as Semidetached_Current_Asking_Price
from
    my_table;


SQL query to find Employees Skiils,Companies and projects This is my ER diagram :

Actually I tried to write it with recursive CTE but I did not succeed
I was wondering If you could help me with that
Thank you

Sample Data

EmployeeInfo:

EmployeeID   FirstName
---------   ---------
  1            A
  2            B

Skill:

SkillID   SkillName
--------  ---------
  1         Java
  2         Python
  3          CSS

Company:

CompanyID    CompanyName
---------    -----------
   1             X
   2             Y

Project :

Projectcode   ProjectDesc
----------    ------------
1                GTHY
2                GYHU

I need to write a query to give me this result (The Expected Result)

EmployeeID  EmployeeName   Skills       Companies     Projects
---------   ------------   ------       ---------     ---------

   1           A        Java,Python,Css    X,Y            GTHY

I mean The EmployeeID and Name , His skills , Companies he had worked on and projects he had participated in

You can solve this types of problems with a simple "For XML Path" clause in sql.
You can do this by:

SELECT
E.EmployeeID,
E.FirstName+' '+E.LastName AS EmployeeName,
(
    SELECT SkillName+', ' FROM Skill WHERE SkillID IN
    (
        SELECT SkillID FROM EmployeeSkill WHERE EmployeeID = E.EmployeeID
    ) FOR XML PATH('')
) AS Skills,
(
    SELECT Name+', ' FROM Company WHERE CompanyID IN (
        SELECT CompanyID FROM EmployeeCompany WHERE EmployeeID = E.EmployeeID
    ) FOR XML PATH('')
) AS Companies,
(
    SELECT ProjectName+', ' FROM Project WHERE ProjectID IN
    (
        SELECT Project FROM EmployeeProject WHERE EmployeeID = E.EmployeeID
    ) FOR XML PATH('')
) AS Projects
FROM EmployeeInfo AS E

UPDATE

You can try this to remove comma(,) at last of skills, companies & project..

SELECT EmployeeID,
EmployeeName,
left(Skills,LEN(Skills)-1) AS Skills,
left(Companies,LEN(Companies)-1) AS Companies,
left(Projects,LEN(Projects)-1) AS Projects
FROM(
    SELECT E.EmployeeID AS EmployeeID, E.FirstName+' '+E.LastName AS EmployeeName,
    (
        SELECT SkillName+', ' FROM Skill WHERE SkillID IN
        (
            SELECT SkillID FROM EmployeeSkill WHERE EmployeeID = E.EmployeeID
        ) FOR XML PATH('')
    ) AS Skills,
    (
        SELECT Name+', ' FROM Company WHERE CompanyID IN
        (
            SELECT CompanyID FROM EmployeeCompany WHERE EmployeeID = E.EmployeeID
        ) FOR XML PATH('')
    ) AS Companies,
    (
        SELECT ProjectName+', ' FROM Project WHERE ProjectID IN
        (
            SELECT Project FROM EmployeeProject WHERE EmployeeID = E.EmployeeID
        ) FOR XML PATH('')
    ) AS Projects
    FROM EmployeeInfo AS E
) AS Tmp


Is there an easy way to SELECT FROM another session's temp table? Is there an easy way to select the data from another session's temp table? I need to do so ad-hocly to debug an issue that I have limited visibility on otherwise.
E.g. if I have access to TEMPDB, is it possible to access the table object itself?

I saw this post from Paul White on one way to do it, but it's a lot more involved then I was hoping for: Viewing Another Session's Temporary Table

I cannot switch the code to use a global temp table for debugging purposes.

#temp is session scope

CREATE TABLE (Transact-SQL) - Temporary Tables

A local temporary table, #table_name, exists only for the duration of a user session or the procedure that created the temporary table.

Do the official microsoft way no tricks, it would end in a mess


Issues setting up a publisher for transactional replication on a SQL Server with a custom instance I'm trying to set up transactional replication of a fairly large database (Business Activity Monitor database from BizTalk Server 2013R2) (>500 GB).

I have an active/passive cluster which holds the main database, another server configured to act as distributor and an active/passive server cluster that will be subscriber. These are all different machines in the same LAN, and they're all running SQL Server 2014 Standard Edition.

I've set up the distributor and set up the publisher as, well, a publisher.

I then continued setting up the publisher with all articles and filters needed, but when trying to set it up and starting a snapshot I ran into the following error:

Cannot promote the transaction to a distributed transaction because there
is an active save point in this transaction.
Changed database context to 'BAMPrimaryImport'. (Microsoft SQL Server, Error: 3933)

I've tried looking for open transactions using DBCC OPENTRAN but it returned nothing.

What I do see however is that Linked Server is set up in which the server is linked back to itself and the instance name is not default but rather an alias.
I'm not sure why this is (I've not set the environment up myself back in the day but I've just inherited it) and I'm not sure this has anything to do with it.

Has anyone else seen these issues and/or have any ideas on how to get this to work?

I'm afraid you are not going to make it work. Biztalk uses internally distributed transactions and thus, DTC.
Replication procedures use save points.
These 2 things are not compatible as far as I could read.

References:

https://feedback.azure.com/forums/908035-sql-server/suggestions/33422404-replication-and-availability-groups-with-per-datab
https://am2.co/2018/02/replication-availability-groups-2/
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/466202d8-93f3-41c2-9cc8-a0b7eb594687/cannot-promote-the-transaction-to-a-distributed-transaction-because-there-is-an-active-save-point-in?forum=transactsql


Does ROLLBACK TRAN honor LOCK_TIMEOUT value? I have a following stored procedure on my system.

BEGIN TRY
  BEGIN TRAN
  SET LOCK_TIMEOUT 10000
  UPDATE tbl
  COMMIT TRAN
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
    ROLLBACK TRAN
END CATCH

UPDATE tbl went into timeout and ROLLBACK TRAN also failed because of timeout.

Question is: is this possible?

No, this is not possible.

SQL Server will always finish the ROLLBACK, no matter what. Even if you cut the power to the system in the middle of the ROLLBACK, the ROLLBACK will continue after the power is returned. ROLLBACK also ignores any connection interruptions and LOCK_TIMEOUT settings. In fact, a connection interruption will start a ROLLBACK if there is an open transaction at the time of interruption.


How to get OUTPUT $action, Inserted.*,Deleted.* records in In SSIS I was just wondering, in MERGE in T-SQL, you can output the OUTPUT $action, Inserted.* and Deleted.* records. I want to ask how can I get OUTPUT $action, Inserted.*, Deleted.* records in SSIS?

You can't, ish, is the short answer.

You have 3 ish components in a data flow task that can modify data. OLE DB/ADO.NET Destination, OLE DB Command and a Script Component.

I'm ignoring Script Component for this answer as it's the swiss army knife of components. If you want to do something badly enough, the Script Component will allow you to. Whether cutting down a sequoia tree with the miniature saw is realistic is another matter.

OLE DB Destination is that, a destination. It doesn't push rows out, it only consumes them. Yes, technically it can push rows but that's only through the error output path which still won't solve the problem of getting access to the OUTPUT virtual tables.

OLE DB Command sounds promising. It can add additional columns to your data flow. The big, BIG, BIG, downside is that it is not a set based component. It's going to operate on each individual row that flows through. If there's tens, hundreds, maybe thousands of rows, this singleton behaviour probably isn't going to kill your systems. MERGEing lakhs+ of data is probably going to result in you having a bad time.

If you're deadset on this approach, recognizing the failings with MERGE, the way to make it SSISey, would be to have two data flow components.

The first writes all of your data to a destination table. Take your existing DF and write all the data to a new table.

The second, new Data Flow, uses your MERGE statement with the accompanying virtual tables directed as needed. I talked about a delete first pattern which used the OUTPUT table over on that SO question so the MERGE shouldn't be any different.


How do you generate a build script for all views I would like to create a build script that can run through and recreate all views (and stored procedures too, but one step at a time).

I am trying to create a baseline of all of our user databases (around 50 of them) for our developers to run on their local machines, basically recreate all of the databases from the ground up. I have gotten past all the preliminary steps of recreating all dbs, schemas, roles, tables, indexes, constraints,...yada yada. I have relied heavily on the sp_foreachdb function and it works like a charm. Now I am stuck on stored scripts, like views and procs mostly due to the length of some of the scripts.

Reason I need to recreate the entire database setup is because people are dumb and left cross database joins and dependencies in legacy products for us. (yay!) So how can we expect our developers to pull the database down to their local machines for development and testing if first they need stuff from 3-4 other databases. And it doesn't end there, those databases depend on other things to exist. Ugh, circular dependencies.

If I only had one database I would hook up my handy dandy RedGate tool SQLCompare and pull the stuff into a script that they could run. Problem is the number of user databases we have and the desire to automate it.

If anyone can offer some advice that could help me recreate ALL script objects on ALL Databases, I would gladly accept it. The thought of manually scripting out a single database's scripts 50+ times is daunting to say the least. What am I not thinking of?

Thanks Friends!

Among other things, have a look at the object_definition() function and the sys.objects catalog view. See them in action here.

But yea... you should probably just use your SQLCompare tool, most RedGate tooling is the gold standard for the functionality if you've got access to it.


SQL 2000 - tsql to find out failed jobs in last 24 hours I want to find out which sql agent jobs failed in last 24 hours...
How can I find it using TSQL for SQL 2000?

Regards
Manjot

SELECT
    j.[name]
  , jh.run_date
  , jh.run_time
  , jh.sql_severity
  , jh.message
FROM
    msdb.dbo.sysjobhistory jh
INNER JOIN msdb.dbo.sysjobs j
ON  j.job_id = jh.job_id
WHERE
    jh.run_status = 0 -- Failure
AND jh.run_date > DATEADD(DAY, -1, GETDATE())
ORDER BY
    jh.run_date DESC


How to know which instance is default in Configuration Manager if default port number is changed? In MS SQL Server Configuration Manager I can see all the instances. I recognize the default one (by saying a default instance I mean the one that listens for 1433) by looking at TCP/IP's IP Addresses property which shows IP addresses and used ports from Network Configuration settings. But named instances can use 1433 too. If the port number of the default instance is changed how can I identify which one is the default in Configuration Manager(I know that other options are available without Configuration Manager).

Thanks for your support.

I recognize the default one (by saying a default instance I mean the one that listens for 1433) by looking at TCP/IP's IP Addresses property which shows IP addresses and used ports from Network Configuration settings

That is not the correct way to identify default instance in SQL Server. A default instance can be identified by going to SQL Server configuration manager (SCCM). On SSCM page click on SQL Server Services on the left pane. Now on the right you can see all the instances. Default instance is one which is like SQL Server(MSSQLSERVER. For named instances it would be like SQL Server (Instance_Name) where instance_name is name of the instance. Something like below. Source is this SO Thread.

If you cannot open SCCM go to run and type services.msc. Look for SQl Server service. Again on services pages as well the SQL Server(MSSQLSERVER) will be service of default instance.

If you want to get the information using TSQL you have Serverproperty T-SQL command to help you

select serverporperty(InstanceName)

  InstanceName: Name of the instance to which the user is connected.

  Returns NULL if the instance name is the default instance, if the
  input is not valid, or error.

  NULL = Input is not valid, an error, or not applicable.

  Base data type: nvarchar(128)

So you can see this is not absolute way, but if the TSQL syntax is correct and you get NULL means you are connected to default instance. Other disadvantage of this method is you have to run this query on each instance to get the information.

Port number hardly has anything to do with default or named instance of SQL Server, it has more to do with securing SQL Server connection. A default instance can listen on port apart from 1433. When you install SQL Server by default it will listen on port 1433, as a good practice it is advised to change it to listen on other port.


Modeling an entity-relationship diagram about Clients, Orders, Payments and Bills There is a part of an entity-relationship diagram creation that troubles me, I don't know if it's ok. I tried to follow these instructions:

A client makes an order (which has products).
When the client pays, he or she gets a bill with information about the order.

My attempts

Is this one ok?

Or this one?

First of all you have to define your entities.

So I see 4 entities:

Client
Order
Product
Payment

With these 4 entities you can create your bill from the information that is already stored in the database.

After you have decided on your entities you need to normalise them before you can go near the ER diagram.  With the ER diagrams that you provide, I do not see 1:1 or 1:M relationships.  I can see that order has a 1:M relationship with product and that client has a 1:1 relationship with Order.  Perhaps there is just a 1:1 relationship between client a payment or can there be many payments?

In any case, the ER diagram should start with client as they start the transaction.  I can see a ternary relationship between order, product and payment.

Does this help?  I hope it does help you to think more about it.  I would not be happy with either diagram because they do not contain 'product'.  Can you try and add product and the type of relationships?


SSISDB Transaction Log Backup I have several SSIS packages jobs running, and some months ago my disk got full because the size of the SSISDB database.

I noticed that the cleanup_server_retention_window was set to 365 days, and I changed it to one day. (It is a development server, and at this point I really dont care about the history).

The (big) problem, obviously, is now that the transaction log grows a lot and fast.

To prevent this, I start performing a full backup every week and a transaction log backup every day, and the size of the database is now controlled.

However, some more experienced guys are telling me that this is not the best aproach to this issue, but I cant see any problem with it..

I would like to know if there is a better solution for this.

Change the recovery model to SIMPLE and be done with it since this is a Development server and you mentioned you don't care about the history.

For more information on recovery models and transaction logs, see this excellent post from Gail Shaw.


Conditional multi table insert My application parses a web resource for data constantly. The data is then used to build a domain object. The domain object is represented in my database across several tables. The domain object is defined by the class below, Order, and its db table equivalent is named order. The OrderItem class that is part of its composition is stored in the table order_item;
class Order
{
    public string Id;
    public int Amount;
    public List<OrderItem> Items;
}

class OrderItem
{
    public string Id;
    public string Name;
    public decimal Price;
}

After parsing a web resource, I would like to insert the Order into the database if it doesn't exist. The way I would do this without the 'if it doesn't exist' constraint is to add an insert statement for the order object, and a separate insert statement for every orderitem object it contains, as part of a transaction. I am not sure how to structure it with the unique constraint, because if i do insert ignore/or on duplicate update, then the order items will be inserted regardless. What is the best way to handle these scenarios? I am using mysql and c#;

... if it doesn't exist ...

See
INSERT IGNORE ...

And
INSERT INTO .. ON DUPLICATE KEY UPDATE ...

Both require a suitable UNIQUE key to catch its "existence".


Retrieve 16 digit card numbers from a string I have a large database table, which contains records of users accounts. Back in 2011 a lot of notes were added to the accounts, which contained the full debit card details.

How can I find all 16 digit card numbers in the table? They are all entered differently.


What grants should I give to allow `COPY ... TO STDOUT`? Context
I want to set up a logical replication between two postgresql instances.
I created a replication user on the master instance :
CREATE ROLE replication WITH REPLICATION PASSWORD '$REPLICATIONPASSWORD' LOGIN;

Then a publication / subscription :
-- on the master instance :
CREATE PUBLICATION mypub FOR TABLES table1, table2, ...

-- on the slave instance :
CREATE SUBSCRIPTION mysub CONNECTION 'dbname=mydb host=localhost port=1234 user=replication password=...' PUBLICATION mypub;

Issue
In postgres' logs, I see error messages indicating that the replication user can't copy the tables :
-- on the slave side : message repeated for each table
ERROR:  could not start initial contents copy for table "public.table1": ERROR:  permission denied for table table1

-- on the master side : message repeated for each table
ERROR:  permission denied for table table1
STATEMENT:  COPY public.table1 TO STDOUT

Connecting with psql to the master server with the replication user I do see that COPY table1 TO STDOUT is rejected.
Question
What other privileges should I add to the replication user to allow COPY ... TO STDOUT ?

The replication user needs to read the tables:
GRANT SELECT ON table1, table2, ... TO replication;

This must of course be run on the logical replication primary server.


How to cast in oracle -Spatial Database? Im trying to get data inside the table which is below.

   CREATE TABLE Eshot (
    eshotNumber NUMBER,
    eshot_geo_location SDO_GEOMETRY);

Using this query returns unsupported data type;

Select e.eshot_geo_location from Eshot e;

Inserted values example

INSERT INTO Eshot VALUES(476,SDO_GEOMETRY(2001, 8307,
SDO_POINT_TYPE(38.3678709,27.1824648,NULL), NULL, NULL));

How to make cast to grab data correctly? Thanks in advance

Use an up-to-date client and tool. I remember some old clients and tools that do not support all datatypes. For example, if I remember correct, Toad 9.0 for Oracle does not support the TIMESTAMP (or maybe it was caused by and old Oracle client, I do not use Toad, just seen this once at a client on-site). Below works just fine from 12.1.0.2 SQL*Plus or SQL Developer (4.1.5.21).

SQL> desc oe.customers
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CUSTOMER_ID                               NOT NULL NUMBER(6)
 CUST_FIRST_NAME                           NOT NULL VARCHAR2(20)
 CUST_LAST_NAME                            NOT NULL VARCHAR2(20)
 CUST_ADDRESS                                       OE.CUST_ADDRESS_TYP
 PHONE_NUMBERS                                      OE.PHONE_LIST_TYP
 NLS_LANGUAGE                                       VARCHAR2(3)
 NLS_TERRITORY                                      VARCHAR2(30)
 CREDIT_LIMIT                                       NUMBER(9,2)
 CUST_EMAIL                                         VARCHAR2(40)
 ACCOUNT_MGR_ID                                     NUMBER(6)
 CUST_GEO_LOCATION                                  MDSYS.SDO_GEOMETRY
 DATE_OF_BIRTH                                      DATE
 MARITAL_STATUS                                     VARCHAR2(20)
 GENDER                                             VARCHAR2(1)
 INCOME_LEVEL                                       VARCHAR2(20)

SQL> select CUST_GEO_LOCATION from oe.customers where rownum = 1;

CUST_GEO_LOCATION(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_OR
--------------------------------------------------------------------------------
SDO_GEOMETRY(2001, 8307, SDO_POINT_TYPE(-91.66643, 41.977151, NULL), NULL, NULL)


MySQL: Enabling innodb_undo_tablespaces We recently tried setting innodb_undo_tablespaces=2 for our mysql 5.7 server. But during the restart the server returned with:

[ERROR] InnoDB: Expected to open 2 undo tablespaces but was able to find only 0 undo tablespaces. Set the innodb_undo_tablespaces parameter to the correct value and retry. Suggested value is 0

It turns out this is already documented :
"The number of innodb_undo_tablespaces must be set prior to initializing InnoDB. Attempting to restart InnoDB with a greater number of undo tablespaces than you specified when you first created the database will result in a failed start and an error stating that InnoDB did not find the expected number of undo tablespaces."

What does "first created the database" mean? Does the documentation mean a fresh installation of mysql where there are no databases within the server? Is there a way to bypass this requirement?

Probably refers to "fresh installation".

The workaround is painful...

Dump all the data.
Stop mysqld.
Blow away ibdata1 and iblogs.
Change the setting in my.cnf.
Restart.  (This should feel like a 'fresh' install.)
Reload the data.

Do you have some indication that changing that setting will help you significantly?  If so, please explain.  It is a very new feature, and is there for a reason, but not many users need it.

Also

from the 5.7 documentation, - dev.mysql.com/doc/refman/5.7/en/innodb-undo-tablespaces.html

  The innodb_undo_tablespaces configuration option is deprecated and will be removed in a future release." and "The number of undo tablespaces can only be configured when initializing a MySQL instance and is fixed for the life of the instance."

– Thanks to @OutfastSource


Mysql getting first and last value from timestamp column I am not getting the right result from my below query:

SELECT t.id as TaskId, cl.name as ClientName, p.name as ProjectName
     , t.name as TaskName, tp.name as TaskType
     , t.createdate as DateCreated,t.enddate as DueDate
     , u.firstname as TaskOwner, pr.name as Priorities
     , t.hours as AllotedHours, t.workedhours as LoggedHours
     , tl.firstEntry, tl.lastEntry
FROM tasks t
inner join (
    select distinct tasks_id,complete, createdate
        ,  min(createdate) as firstEntry, max(createdate) as lastEntry
    from tasklogs
    WHERE complete in (90)
      and createdate > '2018-08-25'
    GROUP BY createdate
) AS tl
    ON tl.tasks_id = t.id
inner join projects p
    on p.id = t.projects_id
inner join tasktypes tp
    on tp.id = t.tasktypes_id
inner join users u
    on u.id = t.users_id
inner join priorities pr
    on pr.id = t.priorities_id
inner join clients cl
    on cl.id = p.clients_id
where p.projecttypes_id=1 ;

I am supposed to get a single result with firstEntry (first log) and lastEntry (last log) columns, but I'm getting multiple rows for each selection:

As you see in the below image I am getting 3 rows, I need one row with highlighted First entry of 90 and last entry of 90 complete. Maybe my query is the wrong way to do that?

full answer required more understanding of all tables structure, but You need start from debug Your inner query:

select
    distinct tasks_id,
    complete,
    createdate,
    min(createdate) as firstEntry,
    max(createdate) as lastEntry
from tasklogs
WHERE complete in (90) and createdate > '2018-08-25'
GROUP BY createdate

it wrong (it wrong and with sql_mode='', and total), with GROUP BY createdate, all 3:

createdate
min(createdate)
max(createdate)

would be the same

as variant - may be You need GROUP BY tasks_id, and include only 3 column:

task_id
min(createdate)
max(createdate)


What exactly mean refused in Oracle listener? What are exactly the reasons of the listener refuse some attempt of connection in database?

Other question is, the amount of refused connection is lsnrctl services command is all refused connection since when the listener has been started?

lsnrctl does show the number of connections refused since the listener was started. Connections can be refused for several reasons, usually because some part of the connect string is incorrect, missing, or malformed, like a bad service_name setting. A connection might also be rejected if the server has Valid Node Checking enabled and the client IP address isn't white-listed. Here is a list of common errors and some troubleshooting tips: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/netag/troubleshooting-oracle-net-services.html#GUID-F521AB0A-B67C-432B-B899-8F856EB51CAE

TNS-12500/ORA-12500: TNS: listener failed to start a dedicated server process
ORA-12514: TNS:listener does not currently know of service
requested in connect descriptor
ORA-12520: TNS:listener could not find available handler for requested type of server
ORA-12521: TNS:listener does not currently know of instance requested in connect
descriptor
ORA-12525: TNS:listener has not received client's request in time allowed
ORA-12533: TNS:illegal ADDRESS parameters
TNS-12540/ORA-12540: TNS:internal limit restriction exceeded and TNS-00510: Internal limit restriction exceeded
TNS-12541/ORA-12541: TNS:no listener
TNS-12549/ORA-12549: TNS:operating system resource quota exceeded and TNS-00519: Operating system resource quota exceeded
TNS-12560/ORA-12560: TNS:protocol adapter error occurred


Postgresql with just index storage I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated.

The only query I perform on this table is an equality predicate on the key to fetch the corresponding value, which makes use of the B-tree.

The storage consumed by PostgreSQL is terrible here. It stores the OID, key, value in the table and stores key, value in the index. I am in essence storing everything twice!

How do I configure this table so that it is space efficient? Ideally, how can I store the tuple just once in the B-tree.

I think you should give a try for TimescaleDB. It is a Postgres extension for huge tables. Splitting table by key in 1000 sections gives you tens of millions records in each section. It will work fast without indexes at all.


select members with the longest running absence streak I have a database containing a table that stores attendance data, and its columns are member_id and datetime_attended.

And another table storing members info, which is made up by the columns id and name.

Each time a member attends an event, a new row containing the member.id and the current timestamp is inserted into to the attendance table.

I would like to formulate a mysql query to pull an ordered list of the top 7 members who have the longest running absence streaks (the streaks must be currently running streaks -- past absence streaks are ignored), as well as their most recent attendance dates.

After much searching, reading and many query attempts, I've yet to stumble on a working query that's even worth sharing here, as all my attempts fell embarrassingly short of the goal (and they would probably just be confusing and frustrating to read). But the general idea I've been working at is to JOIN the two tables ON member.id with GROUP BY and ORDER BY parameters to sort and extract the single most recent attendance entry per member, then use another ORDER BY and LIMIT to get the 7 longest running streaks from the result.

This is my first question post, so I apologize if this question is lacking in any way.  Thank you.

This is your "general idea" translated to SQL

select m.id, m.name, max(a.datetime_attended) as max_attened
from attendance as a
join members as m
  on m.id = a.member_id
group by m.id, m.name
order by max_attened
limit 7


Returning 0 if value is NULL SELECT
    p.id
  , ig.NumReceived
  , og.NumShipped
  , coalesce(
        (p.StartingInventory - og.NumShipped + ig.NumReceived),
        p.StartingInventory
      ) as OnHand
  , p.ProductName
  , p.StartingInventory
  , p.MinimumRequired
FROM
  products p
        left outer join (
            select productid, sum(NumReceived) as NumReceived
            from incoming
            group by productid
        ) as ig on p.id = ig.productid
        left outer join (
            select productid, sum(NumberShipped) as NumShipped
            from outgoing
            group by productid
        ) as og on p.id = og.productid

I managed to get coalesce() to function correctly in this statement for (p.StartingInventory-og.NumShipped+ig.NumReceived) but my intention is to return 0 if ig.NumReceived or og.NumShipped return 0 as well. By which I mean, retrieving NumShipped or NumReceived will display 0. coalesce((p.StartingInventory-og.NumShipped+ig.NumReceived) works as it should but the line i'm referring to is ig.NumReceived, og.NumShipped

I've also tried IFNULL(). I've tried encompassing just the ig.NumReceived as well as the select statement. I'm using MySql 5.7.22

my intention is to return 0 if ig.NumReceived or og.NumShipped return 0 as well

If so, convert

coalesce((p.StartingInventory-og.NumShipped+ig.NumReceived), p.StartingInventory) as OnHand

to, for example,

coalesce(  p.StartingInventory - og.NumShipped + ig.NumReceived,
           case when ig.NumReceived = 0 or og.NumShipped = 0
                then 0
                else p.StartingInventory
                end
        ) as OnHand


Oracle Equivalent of PostgreSQL CLUSTER PostgreSQL has the CLUSTER command to group rows physically on disk. By grouping on information where "neighboring" rows (for lack of a better term) are often accessed together, performance improves since fewer disk blocks need to be read in a given query. Does Oracle have anything similar? Would it even help performance on a large table that is almost never updated if there is such an option?

Similar concept in Oracle is called Index-Organized Tables (IOT).

Difference with PostgreSQL CLUSTER is that in PostgreSQL CLUSTER command reorganizes table once and later table still grows as it wants to. In Oracle IOT keeps its structure as ordered by index.

Unlike Phil I'm seeing IOTs now and then. The most often they are used when you need to retrieve many (think hundreds) rows by index.


Optimise huge Postgres table for fast read I'm about to create a Postgres table with 1.5 billion rows. The table will just have a single TEXT column.

The table is effectively a blacklist. When a user of my software saves certain data, this table is looked-up to make sure the value they're saving doesn't exist in it.

What can I do to optimise Postgres or that table to make that "read" as fast as possible? The table will only ever be written to approximately once a year.

Add a second column which will hold a hash of the text value.  Create the index on the hash.  Even if there is a hash collision there will be only a few rows to read and perform a full comparison on the text vlaues.


resuming server replication on PostgreSQL servers I got one main server and other two servers are replicating from the main.

I got full disk space on the main server (I have an archive_command that put wal archives into the /wal_archive/ directory).

It got full, and the PostgreSQL log shows the error:

archive command failed:

    2019-06-04 09:52:49.079 EEST [3365] LOG:  archive command failed with exit code 1
    2019-06-04 09:52:49.079 EEST [3365] DETAIL:  The failed archive command was: test ! -f /wal_archive/000000010000028C0000003B && cp pg_xlog/000000010000028C0000003B /wal_archive/000000010000028C0000003B

I cleaned up the /wal_archive/ directory with pg_archivecleanup, but PostgreSQL still throws the same error.

I have tried to reload PostgreSQL config without making changes to the config itself and without restart using:

/etc/init.d/postgresql reload

but still the same, in log, PostgreSQL throws an error.
How should I resume wal_archive copying to the wal_archive directory?

Should I change archive_command to true, reload, and change archive back to original again?

I'm trying to avoid restarting the server itself.

It seems like archiving managed to partly write the WAL archive file before the space ran out.

Then the test in your archive_command will notice that there is already a fike of that name and will fail.

In this case the solution would be to manually remove that partially archived WAL segment so that the next attempt to archive it can succeed.

You might want to improve your archive_command by removing the file if cp fails (while still returning a non-zero return code).


Best way to associate multiple records with one another What is the best database design to associate multiple rows with another another.

Let's say we have a table called businesses.

How would I associate Business A, Business B, and Business C, to the point where I could retrieve the related models from within each business.

i.e.
If a user is viewing Business C, I could retrieve Business A and Business B.
If a user is viewing Business B, I could retrieve Business A and Business C.
If a user is viewing Business A, I could retrieve Business B and Business C.

I've been thinking about many-to-many relationship, but I would have to create 3 records to store this relationship such as:

Business_1  | Business_2
--------------------------
Business A  | Business B
Business A  | Business C
Business B  | Business C

Is that the best way?

Are the linked businesses mutually exclusive groups or an extended network?

To extend your example, say we have businesses A, B and C and also X, Y and Z. If {A, B, C} form a closed group where the user can get from any one to each of the others but not to X, Y or Z then you have a closed group. If, however, the user can navigate from any of A, B or C to, let's say, X and then onto Y or Z you have an extended network.

For the closed group scenario adding a Group_Identifier to the Businesses table would suffice. The data then becomes

Business_Name  | Group_Identifier
---------------------------------
Business A     | One
Business B     | One
Business C     | One
Business X     | Two
Business Y     | Two
Business Z     | Two

To retrieve related entities

select
  businesses_name
from businesses
where group_identifier = <the current row's identifier>
and businesses_name != <the current row's name>

If a single business can participate in several groups you have what is known as a graph. Your own and Shooter's solutions implement this scenario. However, these model a directed graph - there is a "from" and a "to" end to each combination. To find all possible links you must read the table twice - once using the Business_1 column and once using the Businesses_2 column - or double up each row - "A to B" and "B to A". (There are specialised graph database products which can handle this, but that's beyond the scope of this question.)

An alternative implementation is to have a new Business_Links table. It has two columns Business_Name and Group_Identifier. Note that this in not the same as the sample data above. That is an extension of the Businesses table. It will have one row per business. This is a new table. There may be many rows per business.

Let's say {A, B, C} form a group and so do {C, D, E}. "C" is in both groups. The data is:

Business_Name  | Group_Identifier
---------------------------------
Business A     | One
Business B     | One
Business C     | One
Business C     | Two
Business D     | Two
Business E     | Two

To find the linked businesses, read the groups of the current business, then read the other business that are also in those groups. I'm not familiar with MySQL syntax but it will be something like

select
  business_name
from business_links
where group_identifier in (
  select
    group_identifier
  from business_links
  where business_name = <current name>
)
and business_name != <current name>

Reading this table will be efficient with an index on business_name and one on group_identifier. Trying to maintain a minimal set of groupings may be tricky if the bussiness often change groupings.


Database which can be setup without admin user I'm looking for a database (relational would be preferred, but NoSQL is also okay) which can be run without an admin user. I want to setup the database with an admin user, but then delete the admin user forever and run the database only with non-admin users.
Is there anything like that for Postgres, MongoDB, MySQL, etc.?

EDIT: The goal is to make the database 100% safe from unrestricted deletions. The normal users should only be able to insert data, deletions and edits should never be possible, also not for the admin.

Thanks for the help!

The reason you have Superusers is to clean up the mess made by regular Users.

Trying to take these powers away from Superusers is like asking them to service your car and promptly throwing away all their spanners and wrenches.

You say that you don't want data deleted.

Simple solution.
Don't give people the permission to do delete data.

Argument: "But Table owners can do anything to that table"

True.  So don't give Users table-owner-level accounts.

Argument: "Superusers can do anything"

True.  So don't give Users superuser-level accounts.

Only you should have access to this, to fix other people's messes.

Review your security model.  Determine who needs what level of access to what, then apply it.  You may well get some "resistance" to start with, but stick to your convictions.  You're heading in the right direction.


How to insert (file) data into a PostgreSQL bytea column? This question is not about bytea v. oid v. blobs v. large objects, etc.

I have a table containing a primary key integer field and a bytea field. I'd like to enter data into the bytea field. This can, presumably, be done by one of the PL/ languages, and I may look into doing this with PL/Python in the future.

As I am still testing and experimenting, I would simply like to insert data from a file (on the server) using "standard" SQL statements. I am aware that only administrators with write permission on the server would be able to insert data in the way I would like to. I'm not concerned about that at this stage as users would not be inserting bytea data at present. I have searched the various StackExchange sites, the PostgreSQL Archives and the Internet generally, but have not been able to find an answer.

Edit: This discussion from 2008 implies that what I want to do is not possible. How are bytea fields used then?

Edit: This similar question from 2005 remains unanswered.

Solved: The details provided here on the psycopg website provided the basis for a solution I've written in Python. It may also be possible to insert binary data into a bytea column using PL/Python. I don't know if this is possible using "pure" SQL.

as superuser:

create or replace function bytea_import(p_path text, p_result out bytea)
                   language plpgsql as $$
declare
  l_oid oid;
begin
  select lo_import(p_path) into l_oid;
  select lo_get(l_oid) INTO p_result;
  perform lo_unlink(l_oid);
end;$$;

lo_get was introduced in 9.4 so for older versions you would need:

create or replace function bytea_import(p_path text, p_result out bytea)
                   language plpgsql as $$
declare
  l_oid oid;
  r record;
begin
  p_result := '';
  select lo_import(p_path) into l_oid;
  for r in ( select data
             from pg_largeobject
             where loid = l_oid
             order by pageno ) loop
    p_result = p_result || r.data;
  end loop;
  perform lo_unlink(l_oid);
end;$$;

then:

insert into my_table(bytea_data) select bytea_import('/my/file.name');


SUPER privilege not defined for master user in Amazon MySQL RDS I have created one medium instance on amazon rds in asia pecific (singapore) region. i have created my master user with master password. and it is working/connecting fine with workbench installed on my local PC. When, I am going to create function on that instance, it show me following error

  ERROR 1418: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable)

At my instance, my variable (log_bin_trust_function_creators) shows OFF. now when I go to change with variable using

SET GLOBAL log_bin_trust_function_creators = 1;

it gives me another error

  Error Code: 1227. Access denied; you need (at least one of) the SUPER privilege(s) for this operation

I don't know how to solve this error.

Can anybody help???

You can try to change log_bin_trust_function_creators; however, there is an alternative approach that seems more appropriate when you consider the meaning of that variable:

It controls whether stored function creators can be trusted not to create stored functions that will cause unsafe events to be written to the binary log.
A setting of 0 also enforces the restriction that a function must be declared with the DETERMINISTIC characteristic, or with the READS SQL DATA or NO SQL characteristic. If the variable is set to 1, MySQL does not enforce these restrictions on stored function creation.

All that option does is assume that you know what you are doing, without making you assert that you do by using one of the three characteristics in your CREATE statement... but if you don't properly declare the function, you may miss out on potential optimizations.

misdeclaring a routine might affect results or affect performance

Taken together, this implies that the most correct approach is to declare your stored functions with DETERMINISTIC or READS SQL DATA or NO SQL as appropriate, and, if these do not correctly describe your function's behavior, then your function still may result in unsafe statements being written to the binary log, because these options are also "trusted:"

Assessment of the nature of a routine is based on the “honesty” of the creator: MySQL does not check that a routine declared DETERMINISTIC is free of statements that produce nondeterministic results.

Curious aside: astute observers will note that I omitted something from the documentation's description:

If set to 0 (the default), users are not permitted to create or alter stored functions unless they have the SUPER privilege in addition to the CREATE ROUTINE or ALTER ROUTINE privilege.

Since nobody gets SUPER in RDS, and assuming this is not an error in the official documentation, this seems like it must be an AWS customization of MySQL behavior.


Reflexivity axiom for inferring functional dependencies As you know there are three Armstrong's Axioms for inferring all the functional dependencies on a relational database. (X, Y and Z are set of attributes)

Reflexivity: If X ⊆ Y, then Y → X
Augmentation: If X → Y, then XZ → YZ for any Z
Transitivity: if X → Y and Y → Z, then X → Z

I understand the augmentation and transitivity for example if we had such schema:

SOME_SCHEMA(a, b, c, d)

with such functional dependencies:

a → b
b → c

By using augmentation we could get ac → bc or by using transitivity we could get a → c and much more, however I am not sure how to infer more functional dependencies using reflexivity axiom? What does it really mean that some attribute is a subset of some other attribute?

Could you show me an example using my schema or creating your own, please?

Thank you in advance!

P.S. Not sure what tag I should use for this question, feel free to change it and remove this line.

"What does it really mean that some attribute is a subset of some other attribute?"

It means nothing.  Functional dependencies exist between SETS of attributes.

Whenever the treatment is supposed to be formal and precise (as in, e.g., when stating the very definition of the axioms), that's how X,Y and Z are supposed to be interpreted.

Unfortunately, people tend to always write down concrete examples of FD's without making the SET property explicit.  As in, e.g., AB->C, where A,B and C are attributes.  Formally, that should actually be {A B} -> {C}, but those braces don't add very much.

And your formulation of the augmentation axiom is incorrect precisely on that count.  If X,Y and Z are sets of attributes, then the only correct formulation of the augmentation axiom is

X -> Y   ===>   (X UNION Z) -> (Y UNION Z)

The reflexivity axiom provides a formal definition of what it means to be a trivial FD.  The various normal forms are defined in terms of nontrivial FDs, hence it must be pinned down what that means exactly.  The reflexivity axiom does that.


How to add in carriage-return/line-feeds without starting a new row in a tabbed txt SQLite import I'm using a tab-delimited .txt file to save my data before importing it into a SQLite database with the SQLite Manager add-on for Firefox.

I've tried a couple of ways to add carriage-return/line-feeds within my columns but failed mostly, ending up with SQLite treating them as multiple rows.

I added a carriage-return/line-feed in SQLite Manager and then looked at the format which seems to be just simple returns but copy-and-pasting that into a text file for later importation doesn't work.

How can I add carriage-return/line-feed into columns within a text file that I can later import into SQLite?

In general, fields containing newlines should be enclosed in double quotes:

123,test,456
234,"test with
multiple
lines", 567

When using quoted fields, any quote inside the field has to be escaped by another quote (or a \, unix style), and the field itself has to be quoted too:

123,"24"" monitor",456
234,"test with
multiple
lines", 567
345,no quotes needed,678


Windows OS level scheduled disk defragment tasks and SQL data volumes Is it considered safe to run Windows OS level scheduled tasks for disk defragment against SQL volumes?
I've inherited the management of a SQL Server from a previous DBA, and they had a Windows Scheduled Task setup to defragment the SQL Server data volumes.  I'm wondering if I can safely disable those jobs.

Windows-Verified Disk Defragmentation solutions won't cause corruption in your databases, however in most scenarios performing physical defragmentation of SQL Server data and log files is unnecessary.
I say "most scenarios" since most production SQL Servers will have a dedicated drive setup for SQL Server data and log files.  However, if your SQL Server data and log files exist on the system drive, or some other physically attached spinning rust disk, then you might want to consider defragmentation if the volume is heavily fragmented, and you can prove that fragmentation is having a negative effect on SQL Server performance.
If you have a locally attached SSD for storage of SQL Server data and log files, or you are using a SAN or other RAID array, then defragmentation of the physical files will have zero effect on performance.  SSDs are random access, and so are almost totally unaffected by file fragmentation.  RAID arrays spread stripes of each file across disks by design, and who knows what's happening at the SAN level depending on how advanced it is - for instance it might be doing data-de-duplication.
The short answer is, it's almost never necessary to defragment drives containing SQL Server data and log files.
This Microsoft Support page has further details about defragmenting SQL Server volumes.


Where should I put nvarchar(max) dimensions in my data warehouse? I am designing the ERD for a new data warehouse.

SQL Server 2016
Clustered Columnstore Indexes for the "fact" tables
Loose star schema

I put "fact" in quotes and say "loose star schema" because, with clustered columnstore, I can put many dimensions right into the "fact" table without the typical concerns over row width. Many of my dimensions are going into the "fact" tables.  I am creating some dimension tables and surrogate keys, but only if the dim has attributes in addition to the dim description itself.

This brings me to some very wide, high cardinality, fields that are nvarchar(max). Without going too in-depth, think of these fields as denormalized lists. I need the list denormalized for the grain of one of my data sources.  I do have it normalized in another fact table, but not one I am surfacing in this data source.

Users need these fields to search for keywords in the data mart I am surfacing.  In my current design they are in the clustered columnstore "fact" table. Users will frequently query the fact tables without touching the nvarchar(max) fields.

Is there a place to put wide dimensions into my data warehouse that is more correct than the clustered columnstore table?

Joe Obbish informed me that we cannot currently put nvarchar(max) into CCIs. Would it be the best practice for me to create a table of LOBs as an extension of my "fact" table?

We may add other languages in the future. Currently and for the foreseeable term, the nvarchar column contains just English.

Microsoft recommends using a CCI for large data warehouse tables with a few caveats including:

Don't use a clustered columnstore index when:

The table requires varchar(max), nvarchar(max), or varbinary(max) data types. Or, design the columnstore index so that it doesn't include these columns.

Simply put, your options are to forgo columnstore entirely, create a nonclustered columnstore index on the table that doesn't include the VARCHAR(MAX) column, or to move the LOB columns to a separate table. You said that end users will sometimes access the tables without querying the VARCHAR(MAX) columns so I would try to use columnstore when possible so those queries can get the full benefit.
If I was designing this my first attempt would be to test your workload with nonclustered columnstore indexes that include every column except the VARCHAR(MAX) ones. That is a separate index so you will incur extra storage for the columns, but if you see the typical CCI compression ratio it will only be 10% extra. It's the simplest design and will put you in a good position to take advantage of SQL Server 2017's availability to include VARCHAR(MAX) columns in clustered columnstore indexes. Niko Neugebauer wrote a blog post about some testing on CCIs with LOB data in SQL Server vNext which you can find here.


Autogrowth of database and log file I am confused what this autogrowth property does for any database or log file in SQL Server.

According to my understanding:

If we set autgrowth for database as restricted then its size will increase with data up to this limit and after stops increasing its size which will result in database failure.
If we set autgrowth for database as unrestricted then it will grow until disk space is full.
If we set autgrowth for log file as restricted then logs size will increase upto specified limit and then it will start discarding old logs and new logs will be there in logs.
If we set autgrowth for log file as unrestricted the then it will grow until disk space is full.

Please help me to know if this assumption is correct and if it is wrong then please correct me.

If we set autgrowth for log file as restricted then logs size will increase up to specified limit and then it will start discarding old logs and new logs will be there in logs.

This is wrong. If it has to grow it will grow until the max size and after which further growth request will result in failure. You are confusing things with log truncation, which is the process which allow log physical file to be reused (so it doesn't have to grow). I have an article explaining how log grows and what is the role of truncation in this, see How to Shrink SQL Server log.


Duplicated table for each website user I have started working on a new database and found something that stands out as abnormal.

I have a table that holds contacts similar to below

contacts
+----+-------------+-------------+----------------------+
| id | first_name  | second_name |    email_address     |
+----+-------------+-------------+----------------------+
|  1 | jane        | Doe         | Jane.doe@example.com |
|  2 | John        | Doe         | john.doe@example.com |
+----+-------------+-------------+----------------------+

Each user has access to specific users on the website. The current way our system handles this is to duplicate the table and append the users id to the end of the table name, it then only inserts records relevant to the user.

contacts_201
+----+-------------+-------------+----------------------+
| id | first_name  | second_name |    email_address     |
+----+-------------+-------------+----------------------+
|  1 | jane        | Doe         | Jane.doe@example.com |
+----+-------------+-------------+----------------------+

I always thought in situations like this a link table would be used like this to resolve what is a many to many relationship.

contactMemLink
+------------+--------+
| contact_id | mem_id |
+------------+--------+
|          1 |    201 |
+------------+--------+

Extra information:

There are around 24000 records in the contact table
There are around 100 users on the website
Each user has the possibility of having every user assigned to it.

My Question

Is there any reason on why the database would have been designed this way based on best practices?

IMHO

The original layout looks like it is a self-built implementation of Row Level Security (RLS).  RLS allows the developer to limit the available rows based on who is logged in (and other pieces of information).

The reason for the original design could have been done because:

The designer was unaware of RLS
The RDBMS did not support RLS
DBA Stack Exchange wasn't available to the developer at the time of implementation
The developer copy+pasted code unaware of the potential problems

constructing the SQL statements for contacts_201 screams "Possible SQL Injection"

The solution for (2) would be based on these words of wisdom:

  use the right tool for the job.

In other words, the RDBMS (that doesn't support RLS) should have been changed to an RDBS that supports the Business Requirement (ie one that does support RLS).  This could be a simple as doing an upgrade.

note "Database Agnostic Code is a Myth" could have been the major reason to not fix (2).


How can I see the plan for ONE statement in a stored procedure? I've got some SQL which I've run several times a day in development, generally getting execution time of 3-8 minutes.

Now I've wrapped it in a stored procedure (with no parameters), the performance is impossible.  THe first "heavy" statement, which involves only c.27k rows, never finishes.

Actual execution plan is not an option, because I can't even get this statement to finish.  So I have to look at estimated execution plan.

Trouble is I get a separate "sub-pane" in the Execution Plan pane for every statement.  I can't comment out preceding statements as they're necessary for the problem statement to run (though I've commented out all following statements).

How can I view just the plan for the problem statement on-screen?  It's obviously a huge plan.  All the other statements' plans (which I'm not interested in) take up an enormous amount of screen space, so it's impossible to scroll through the important one and find the chokepoints.  But there seems to be now way to "Show just the plan for this statement, and take up the full screen".

Am I missing something?

I don't think there's a way to do that in SSMS.

You should use Sentry One Plan Explorer for this.  It's a free tool that provides an alternative way of viewing SQL Server execution plans.

I've run this code on my local copy of the Stack Overflow 2010 sample database:

CREATE PROCEDURE #sp_TestQuery
AS
SELECT COUNT_BIG(*) FROM dbo.Users u WHERE u.Id > 10;
SELECT MAX(p.CommentCount) FROM dbo.Posts p WHERE p.Id > 1000;
SELECT MIN(v.BountyAmount) FROM dbo.Votes v WHERE v.Id > 100000;
GO

EXEC #sp_TestQuery;

Running the EXEC... line produces the problem you mentioned in SSMS:

One option I have here is to at least resize the different panes, by dragging the border between them.  If I wanted to mostly see the middle query:

But that's still not ideal.  The default view in Plan Explorer shows me a list of statements at the top, and I can click on each statement to view the graphical plan below it.  Here's the middle query in Plan Explorer:


filter records within MySQL replication I'm new to replication process.

In one of Rails project it's necessary to share records of one table between two applications.

Let's say share records between first application DB A and second application DB B.
I decided to move that logic on DB level.

Is is possible to configure MySQL replication in order to copy only particular records (e.g. records where need_to_replicate_to_B_flag = true)?
Or I should find another way to implement this?

You can filter on databases and/or tables only.  See the manual on binlog/replicate-do/ignore-db/table

The filtering can be before leaving the Master (binlog-...) or as it comes into the Slave (replicate-...).

The main intent of Replication is for the Slave to be an identical copy of the Master.  Your request is deviating quite far from that.


SQL Create Script DDL Table from View How do I script a Create Table DDL for a View?

Example View:

create view dbo.CustomerTransaction
as
select
   cust.CustomerId,    -- this is int
   cust.CustomerName,  -- varchar(25)
   tr.PurchaseAmount   -- decimal(10,2)
from dbo.Customer cust
inner join dbo.Transaction tr
   on cust.CustomerId = tr.TransactionId

Expected Result:

create table dbo.CustomerTransaction
(
     CustomerId int,
     CustomerName varchar(25),
     PurchaseAmount decimal(10,2)
)

Since your question tag says SQL Server 2016, you could take advantage of sys.dm_exec_describe_first_result_set which became available in SQL Server 2012.

  This dynamic management function takes a Transact-SQL statement as a
  parameter and describes the metadata of the first result set for the
  statement.

--Demo set up
drop table if exists dbo.customer
drop table if exists dbo.[transaction]

create table dbo.Customer(CustomerId int, CustomerName varchar(25))
create table dbo.[Transaction](TransactionId int, PurchaseAmount decimal(10,2))
DROP VIEW IF EXISTS CustomerTransaction
GO
create view dbo.CustomerTransaction
as
select
   cust.CustomerId,    -- this is int
   cust.CustomerName,  -- varchar(25)
   tr.PurchaseAmount   -- decimal(10,2)
from dbo.Customer cust
inner join dbo.[Transaction] tr
   on cust.CustomerId = tr.TransactionId
go
-------------------------------
--The solution
SET NOCOUNT ON;

DECLARE @sql NVARCHAR(MAX)
    ,@cols NVARCHAR(MAX) = N'';

SELECT @cols += N',' + NAME + ' ' + system_type_name
FROM sys.dm_exec_describe_first_result_set(N'SELECT * FROM dbo.CustomerTransaction', NULL, 1);

SET @cols = STUFF(@cols, 1, 1, N'');
SET @sql = N'CREATE TABLE #tmp(' + @cols + ');'
SET @sql = replace(@sql,',',',')
print @sql

CREATE TABLE #tmp(CustomerId int,CustomerName varchar(25),PurchaseAmount decimal(10,2));


How do you view PostgreSQL messages (such as RAISE NOTICE) in DBeaver? I think this is a DBeaver UI/Configuration question, but I cannot see where the messages are when executing a script (Alt-X).

In PGAdminIII, I would execute a script and see the NOTICE output:

In DBeaver, the same script does not output in "Statistics" tab.  But I am not certain if this is where I should be looking for the messages:

On DBeaver 5.2.0 you can use Ctrl+Shif+O or the button Show server output console on the left side of the script window.


How to export data in Oracle database into CSV file with UTF-8 format? I have an CSV file with WE8ISO8859P1 encoding generated from Oracle Stored Procedure. Is there any script line to define the output of CSV file in UTF-8 encoded format?

 CREATE OR REPLACE
PROCEDURE export_main(dir VARCHAR2, file_name VARCHAR2)
  IS
 select_stmt VARCHAR2(100) := 'SELECT MTYPE || '','' || MNO FROM MAIN';
    cur INTEGER;
    file UTL_FILE.FILE_TYPE;
    row_value VARCHAR2(4000);
    ret INTEGER;
  BEGIN
    -- Open a cursor for the specified SELECT statement
    cur := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(cur, select_stmt, DBMS_SQL.NATIVE);
    ret := DBMS_SQL.EXECUTE(cur);

    -- All columns were concatenated into single value in SELECT
    DBMS_SQL.DEFINE_COLUMN(cur, 1, row_value, 4000);

    -- Open the file for writing
    --file := UTL_FILE.FOPEN(UPPER(dir), file_name, 'w', 32767);
file := UTL_FILE.FOPEN_NCHAR(UPPER(dir), file_name, 'w', 32767);
    -- Export rows one by one
    LOOP
       ret := DBMS_SQL.FETCH_ROWS(cur);
       EXIT WHEN ret = 0;

       -- Get the value
       row_value := NULL;
       DBMS_SQL.COLUMN_VALUE(cur, 1, row_value);

       -- Write the row to the file
      -- UTL_FILE.PUT_LINE(file, row_value);
UTL_FILE.PUT_LINE_NCHAR(file, TO_NCHAR(row_value));
    END LOOP;

     UTL_FILE.FCLOSE(file);
     DBMS_SQL.CLOSE_CURSOR(cur);

    EXCEPTION WHEN NOT_LOGGED_ON THEN
    DBMS_OUTPUT.PUT_LINE ('A program issues a database call without being connected to Oracle.');
  END;

Oracle database is encoded with WE8ISO8859P1 and postgres DB is encoded with UTF-8

I need to import CSV file into Postgres DB table using stored procedure where in postgres by default encoding is UTF-8

UTL_FILE.PUT_LINE_NCHAR Procedure

  This procedure writes the text string stored in the buffer parameter
  to the open file identified by the file handle. With this function,
  you can write a text file in Unicode instead of in the database
  character set.

So you could try something like this:

-- Write the row to the file
UTL_FILE.PUT_LINE_NCHAR(file, TO_NCHAR(row_value));


MySQL auto increment problem with deleting rows / archive table A hosted server is running "maintenance" each weekend. I am not privy to the details.

In a database on this server there is a MyISAM table. This table never holds more than 1000 rows and usually much less. It is MyISAM so that the auto increment does not reset (and with so few rows it really doesn't matter). Rows are regluarly deleted from this table and moved to an archive table (1M rows).

The problem is lately the auto increment has "rolled back" slightly after each maintenance.

Is there any easy way to verify the auto increment of the insert table by reading the max id from both the insert and the archive table?

I'd rather not verify before each insert unless that is the only solution.

Here are the basic table layouts:

CREATE TABLE x
(
    xid int(10) unsigned NOT NULL AUTO_INCREMENT, //snip
    PRIMARY KEY (xid)
) ENGINE=MyISAM AUTO_INCREMENT=124 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE xhistory
(
    xid int(10) unsigned NOT NULL DEFAULT '0', //snip
    PRIMARY KEY (xid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Far from perfect workaround: (this was somewhat urgent, I had to manually update over 100 rows)

select xid from xhistory where x=?

Check if just inserted row in x exists in history. If it does:

select greatest(max(x.xid),max(xhistory.xid)) as newval from x,xhistory

Find a new id.

INSERT INTO x SELECT * FROM x AS iv WHERE iv.xid=? ON DUPLICATE KEY UPDATE xid=?

And update our row with this id.

You can check the AUTO_INCREMENT value from INFORMATION_SCHEMA like this:

SELECT AUTO_INCREMENT FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='x';

you should also see the AUTO_INCREMENT with

SHOW CREATE TABLE mydb.x\G

SUGGESTION

If the table has barely 1000 rows, you could manually compress it and force to have MAX(id) each time. Suppose the table looks something like this:

USE mydb
CREATE TABLE x
(
    id INT NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (id)
);

Try doing OPTIMIZE TABLE manually as follows:

InnoDB or MyISAM tables with no non-unique keys

USE mydb
CREATE TABLE x_new LIKE x;
INSERT INTO x_new SELECT * FROM x ORDER BY id;
ALTER TABLE x RENAME x_old;
ALTER TABLE x_new RENAME x;
DROP TABLE x_old;

MyISAM tables with non-unique keys

USE mydb
CREATE TABLE x_new LIKE x;
ALTER TABLE x_new DISABLE KEYS;
INSERT INTO x_new SELECT * FROM x ORDER BY id;
ALTER TABLE x_new ENABLE KEYS;
ALTER TABLE x RENAME x_old;
ALTER TABLE x_new RENAME x;
DROP TABLE x_old;

This should preserve all id's and assign AUTO_INCREMENT appropriately.

It should work just fine with MySQL Replication.

CAVEAT

If the ids are different for any reason between Master and Slave, blame MySQL (I mean blame Oracle) : http://dev.mysql.com/doc/refman/5.5/en/replication-features-auto-increment.html

If the aforementioned suggestion does not rectify this, there is only one thing left to do and it is guaranteed to work. What is it?

Run this on the Master Only:

mysqldump -uroot -p... --triggers mydb x > mydb_x.sql
mysql -uroot -p... -Dmydb < mydb_x.sql

With a table of 1000 rows, this should

go pretty quickly
replicate and produce an exact copy on the slave
restore auto_increment behavior to Master and Slave

Give it a Try !!!

UPDATE 2013-02-11 19:30 EDT

Let's assume that x and xhistory have identical layouts. Let's also add some columns:

CREATE TABLE x
(
    xid int(10) unsigned NOT NULL AUTO_INCREMENT,
    col1 ... ,
    col2 ... ,
    col3 ... ,
    PRIMARY KEY (xid)
) ENGINE=MyISAM AUTO_INCREMENT=124 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE xhistory
(
    xid int(10) unsigned NOT NULL DEFAULT '0',
    col1 ... ,
    col2 ... ,
    col3 ... ,
    PRIMARY KEY (xid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Perhaps you could just use the REPLACE command. It mechanically operates as either an INSERT or UPDATE via DELETE and INSERT.

REPLACE INTO xhistory SELECT * FROM x;
TRUNCATE TABLE x;

Previously existing rows get deleted from xhistory and then inserted from x into xhistory. New rows are simply inserted.


Formatting query to respond to past column values I'm writing a query that follows the process of forms being processed through various SQL functions - they receive status numbers as they go through the process. I want to pull forms that are in a very specific situation: That (at most recent date) have code 599, but have NOT HAD code 550 in the past. For instance if I have the table:

If I performed a query of grabbing the counts that satisfy the table above, the return should be 1. Again, I am trying to figure out how to write a query that pulls forms that most recently have 599, but has not also had 550 in the past. Assume the table name is just "table."

My best attempt can only get the count of forms where the most recent date is 599, but I'm struggling to account for the past 550:

select t1.* FROM table t1
JOIN
(
    select id, MAX(create_date) AS MAXDATE
    FROM table
    GROUP BY id
) t2
ON T1.id = t2.id
AND t1.create_date = t2.MAXDATE
where status = '599'

and not exists (
  select 1 from yourtable t3
  where t3.id = t1.id
    and t3.create_date < t1.create_date
    and t3.status = '500'
)


Replacing a specific string in oracle I've a database value 'INS. Company Cancelled' where multiple values are separated by '.'
I'm trying to replace 'INS' with 'INSEXP' my expected result is 'INSEXP. Company Cancelled'
I've tried below two queries to update the field my output is like 'INSEXP. Company Cancelled. Company Cancelled'
update my_table
SET column_name = (select replace(column_name, 'INS', 'INSEXP')
                    from my_table WHERE seq_num = 123)
WHERE seq_num = 123;

update my_table
set column_name = replace(column_name, 'INS', 'INSEXP')
WHERE seq_num = 123;

Could someone please tell me what I'm doing wrong here?

You want to do something like this (see fiddle here):
CREATE TABLE test
(
  t_id INTEGER PRIMARY KEY,
  x VARCHAR (50),
  seq_num INTEGER NOT NULL
);

INSERT INTO test VALUES (1, 'INS. Company Cancelled - 127', 127);
INSERT INTO test VALUES (2, 'INS. Company Cancelled - 128', 128);
INSERT INTO test VALUES (3, 'INS. Company Cancelled - 126_1', 126);
INSERT INTO test VALUES (4, 'INS. Company Cancelled - 126_2', 126);

The record below has 126 as the seq_num and the fragment INS.  isn't at the beginning of the target string:
-- Note: 126 as seq_num!
--
-- BUT, also note that the `INS.` isn't at the beginning of the string!
--

INSERT INTO test
VALUES
(5, 'stuff... ___INS. Company Cancelled - 126', 126);

Then:
SELECT REPLACE(x, 'INS. ', 'INSEXP. ') AS "Check:" FROM test;

Result:
Check:
INSEXP. Company Cancelled - 127
INSEXP. Company Cancelled - 128
INSEXP. Company Cancelled - 126_1
INSEXP. Company Cancelled - 126_2
stuff... ___INSEXP. Company Cancelled - 126

The problem here is that the substring INS.  is being replaced everywhere seq_num is 126 whereas we only want it done where INS.  occurs at the beginning of the target string!
So, now we try REGEXP_REPLACE:
SELECT
  REGEXP_REPLACE(x, '(^INS)(\. )', '\1EXP\2') AS "Check:"
FROM test
WHERE seq_num = 126;

Result:
Check:
INSEXP. Company Cancelled - 126_1
INSEXP. Company Cancelled - 126_2
stuff... ___INS. Company Cancelled - 126

So, we can see that REGEXP_REPLACE changes the first two INS.  substrings, but not the last one - where it doesn't occur at the beginning of the string!
So, now for the UPDATE:
UPDATE test t SET t.x = REGEXP_REPLACE(t.x, '(^INS)(\. )', '\1EXP\2')
WHERE t.seq_num = 126;

And then (to check - always check!):
SELECT t.* FROM test t ORDER BY t.t_id;

Then:
T_ID                                   X       SEQ_NUM
   1    INS. Company Cancelled - 127               127
   2    INS. Company Cancelled - 128               128
   3    INSEXP. Company Cancelled - 126_1          126
   4    INSEXP. Company Cancelled - 126_2          126
   5    stuff... ___INS. Company Cancelled - 126   126

Et voilà! We have replaced the correct substring from the correct part of the target string - all due to the power of regular expressions!
You should also look into the REGEXP_REPLACE() functionality (the oracle-base site is excellent for all things Oracle!). I've only recently started mastering them and it's amazing what they can let you do in a one-liner that would take tons of code otherwise.
Explanation of the regex:
Now, here we specify the beginning of the string using the ^ anchor, so the replacement only takes place at the start of the string x - this specificity can be very helpful (indeed critical, as we can see in this case) when substituting text.
The ( and ) brackets are for "capturing groups" of text - so we can refer to <start_of_string> followed directly by INS followed by . (dot is also a special character - it's a single character wildcard, so it's escaped by \).
So, the group (^INS) is represented by the place-holder \1 - remember the backslash is a special character in regexps! And the (\. ) group represents a . (full stop or period) followed by a space - because . without the backslash is a wildcard for one character in regexps!
Caveat: There will be a performance penalty to pay here - naturally enough - more complex functionality requires more processing, but I would strongly urge any database programmer/DBA &c. to master regular expressions - there are many tutorials out there.
As an aside (and this is more for me really), in exploring this issue, I came across this page (again, from Tim Hall's fabulous oracle-base site) - it explains 3 different ways of updating from queries!
The one I liked the best is the MERGE (3rd method) functionality (fiddle):
MERGE INTO test tt
  USING test st
  ON (tt.t_id = st.t_id AND tt.seq_num = 126)
WHEN MATCHED THEN
  UPDATE SET tt.x = REGEXP_REPLACE(st.x, '(^INS)(\. )', '\1EXP\2');

The end result is the same!
In this snippet, tt = target_table & st = source_table - of course, in this case, they're both the same and the simpler code above works - but if you're going to be UPDATEing from table_1 to table_2, you'll need mechanisms like this! A fiddle for this code is to be found here.
My second favourite was what Oracle calls an Inline View (but most of us normal folks call a sub-query) - it looks like this (fiddle here):
UPDATE
  (SELECT target_table.t_id, target_table.x, target_table.seq_num,
          REGEXP_REPLACE(source_table.x, '(^INS)(\. )', '\1EXP\2') y
          FROM test target_table, test source_table
          WHERE target_table.t_id = source_table.t_id
          AND target_table.seq_num = 126) ilv

  SET ilv.x = ilv.y;

And finally, there is what the page (and Oracle?) calls the sub-query method - it  uses the EXISTS predicate thus (fiddle):
UPDATE test t1
  SET (t1.x) =
  (
    SELECT REGEXP_REPLACE(t2.x, '(^INS)(\. )', '\1EXP\2')
    FROM test t2
    WHERE t1.seq_num = 126 AND t1.t_id = t2.t_id
  ) WHERE EXISTS (SELECT t.t_id, t.seq_num FROM test t WHERE t.t_id = t1.t_id
                                                       AND t.seq_num = 126);

Interestingly, there is also a performance analysis after each one, which suggest to me that none is the best in all cases and therefore we should test (with realistic datasets) our queries for performance before implementing them in production (don't we always? :-) )!
I'm giving a +1 to the question because I learned a lot from answering it - I hope you did too!


Calculating new date with date/time operators fails with variables I have two fields, one timestamp (calldate), and one bigint (duration - no idea why it's this big).

Through a query I would like to construct a 3rd field that is the timestamp plus the bigint as seconds.

With 9.3 I don't have access to make_interval, but the functions and operators seem useless.

select calldate + interval + duration || ' seconds' as "completion_date";
select calldate + interval + '900 seconds' as "completion_date";

The above is abbreviated a bit, but the 2nd line works while the 1st line fails. I'm unable to pass or construct the interval in a way it will accept. I was hoping to use the make_interval function that would return an interval type, but alas I'm using 9.3. I've tried many, many, different variations using () to try and isolate it as well as casting values.

Nothing works. How can I add the duration in seconds to a timestamp value?

To construct the interval, multiply the number with the 1 second interval:
 duration * interval '1 second'

select calldate + duration * interval '1 second' as "completion_date";

or:

select calldate + duration * '1 second'::interval as "completion_date";

Postgres docs have a page about Date/Time Functions and Operators, where there are similar examples:

Operator    Example                     Result
  *         900 * interval '1 second'   interval '00:15:00'
  *         21 * interval '1 day'       interval '21 days'


Row estimates always too low I have a query which involves a full-text search like this:

SELECT TOP 30 PersonId,
              PersonParentId,
              PersonName,
              PersonPostCode
FROM dbo.People
WHERE PersonDeletionDate IS NULL
      AND PersonCustomerId = 24
      AND CONTAINS(ContactFullText, '"mr" AND "ch*"')
      AND PersonGroupId IN(197, 206, 186, 198)
ORDER BY PersonParentId,
         PersonName;

This generates two main plans, one is very fast in all cases, the other is very slow in most cases.

I have experimented with this query such that the FT search is not included and what I found is that the row estimates are always way lower than they should be.

If I run update statistics...with fullscan I still see extremely inaccurate row estimates from NC index seek operations in the execution plan.

When the row estimates are low enough, a loop join is selected, which is normally very slow (30+ seconds). Higher estimates seem to produce a good plan involving a merge join instead of a loop join.

Why is SQL Server still not estimating the rowcounts despite still having up to date statistics?

The plan: https://www.brentozar.com/pastetheplan/?id=rkXtE0jzX

When I remove the CONTAINS part, thereby omitting the FullText search, the query is fast, but the row estimate for the index seek is still 1 estimated, 2195 actual.

On @Kin's advice, I used CONTAINSTABLE, which ran instantly and produced the following plan: https://www.brentozar.com/pastetheplan/?id=S1hKainzQ
Interesting that there is no Full Text search operator.

Containstable requires RANK to produce the same result set in this case I've used AND RANK > 0 in the WHERE to produce the results I want, which produces this plan: https://www.brentozar.com/pastetheplan/?id=B1U7AA2zm

My only question now is about why row estimates are still inaccurate but I care less now that my FT queries seem significantly faster and more reliable. Very pleased!
https://www.brentozar.com/pastetheplan/?id=B1U7AA2zm

@EvanCarroll stats histogram here: https://pastebin.com/p7s0NvX5

Some follow up info - before/after execution plans for some typical FT search queries for the application being supported

A

Before: https://www.brentozar.com/pastetheplan/?id=SJlAAAN7X (5 seconds)
After: https://www.brentozar.com/pastetheplan/?id=H1ltkkSmm (<1
second)

B

Before: https://www.brentozar.com/pastetheplan/?id=Sy-gxJBQm (40
seconds)
After: https://www.brentozar.com/pastetheplan/?id=Sy2VxJrm7 (1
second)

C

Before: https://www.brentozar.com/pastetheplan/?id=r1z5e1rQ7 (2
seconds)
After: https://www.brentozar.com/pastetheplan/?id=r1oplkSQm (<1
second)

D

Before: https://www.brentozar.com/pastetheplan/?id=B1kHf1BQQ (2
minutes 20 seconds)
After: https://www.brentozar.com/pastetheplan/?id=r1D5z1SQm (11
seconds)

(summarizing my comments and putting as answer)

A query rewrite will solve the issue of getting low row estimates.  As Joe Chang explains in his blog post  Query Optimizer Gone Wild - Full-Text

  CONTAINS is "a predicte used in a WHERE clause" per Microsoft documentation, while CONTAINSTABLE acts as a table.

You get a much better plan (merge join) using CONTAINSTABLE vs the actual plan using contains uses a nested loop join with low row estimates.

You can rewrite the query as :

SELECT TOP 30 p.PersonId,
              p.PersonParentId,
              p.PersonName,
              p.PersonPostCode
FROM dbo.People p
left join containstable (ContactFullText, '"mr" AND "ch*"') cf on cf.[yourKey] = p.PersonId
WHERE p.PersonDeletionDate IS NULL
      AND p.PersonCustomerId = 24
      --AND CONTAINS(ContactFullText, '"mr" AND "ch*"')
      AND p.PersonGroupId IN(197, 206, 186, 198)
      AND [RANK] > 0
ORDER BY p.PersonParentId,
         p.PersonName;