How do I extract text from string between underscores with mysql? I need to derive a column based on text from another column.
For instance, in the 'channel' column I have this text:

"RbkSEM_google_ecom-_-Branded-_-Core-_-Realdock"

I need to pull out "Branded" and put it into a column called 'derived_channel'

The positioning of the text I need to pull out will always be after the 3rd underscore and before the 4th underscore.

Thanks for help!

If you have the following:

table is called mytable
column channel has RbkSEM_google_ecom--Branded--Core-_-Realdock
you want to pull out Branded from between the -- tokens
place result in derived_channel

Here is your query

UPDATE mytable SET derived_channel =
LEFT(SUBSTR(channel,LOCATE('--',channel)+LENGTH('--')),LOCATE('--',SUBSTR(channel,LOCATE('--',channel)+LENGTH('--'))) - 1);

Here is an example using user-defined variables

SET @st = 'RbkSEM_google_ecom--Branded--Core-_-Realdock';
SET @dm = '--';
SET @rv = LEFT(SUBSTR(@st,LOCATE(@dm,@st)+LENGTH(@dm)),LOCATE(@dm,SUBSTR(@st,LOCATE(@dm,@st)+LENGTH(@dm))) - 1);
SELECT @rv;

Here is the example executed

mysql> SET @st = 'RbkSEM_google_ecom--Branded--Core-_-Realdock';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @dm = '--';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @rv = LEFT(SUBSTR(@st,LOCATE(@dm,@st)+LENGTH(@dm)),LOCATE(@dm,SUBSTR(@st,LOCATE(@dm,@st)+LENGTH(@dm))) - 1);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @rv;
+---------+
| @rv     |
+---------+
| Branded |
+---------+
1 row in set (0.00 sec)


Can I enforce one field being dependent on another? I would like to know if it's possible to have two fields (in this case bits) in the same table where one is dependent on another. For example Check2 may only be true if Check1 is true.

Note: I'm trying to implement a constraint in an attempt to accomplish this, but so far failing.  I admit I have limited experience with constraints. This is a new database with little data, and there are no entries currently in the table where the conditions aforementioned are not true. That is, there is no record where Check2 is true.

You can use a CHECK constraint with a CASE expression. If check2 is enabled, make sure check1 is enabled too, otherwise you can default to a no-op ("checking" that check1 = check1). For simplicity I'm assuming these columns are not NULLable.

CREATE TABLE dbo.foo
(
  check1 BIT NOT NULL DEFAULT 0,
  check2 BIT NOT NULL DEFAULT 0,
  CONSTRAINT ck_both CHECK (check1 = CASE WHEN check2 = 1 THEN 1 ELSE check1 END)
);

INSERT dbo.foo DEFAULT VALUES;             -- 0,0 succeeds
INSERT dbo.foo(check1,check2) VALUES(1,0); -- succeeds
INSERT dbo.foo(check1,check2) VALUES(1,1); -- succeeds
GO
INSERT dbo.foo(check1,check2) VALUES(0,1); -- fails

To add this constraint to your existing table:

ALTER TABLE dbo.tablename
  ADD CONSTRAINT ck_BothBits
  CHECK (check1 = CASE WHEN check2 = 1 THEN 1 ELSE check1 END);

If the columns are NULLable (hopefully they aren't), you can say something like this (untested):

ALTER TABLE dbo.tablename
  ADD CONSTRAINT ck_BothBits
  CHECK (COALESCE(check1,0) = CASE
    WHEN check2 = 1 THEN 1 ELSE COALESCE(check1,0) END);

With that constraint in place:

INSERT dbo.foo(check1,check2) VALUES(NULL,NULL) -- succeeds
INSERT dbo.foo(check1,check2) VALUES(1,0);      -- succeeds
INSERT dbo.foo(check1,check2) VALUES(1,1);      -- succeeds
INSERT dbo.foo(check1,check2) VALUES(1,NULL);   -- succeeds
INSERT dbo.foo(check1,check2) VALUES(0,NULL);   -- succeeds
INSERT dbo.foo(check1,check2) VALUES(NULL,0);   -- succeeds
GO
INSERT dbo.foo(check1,check2) VALUES(0,1);      -- fails
GO
INSERT dbo.foo(check1,check2) VALUES(NULL,1);   -- fails


Error importing back a postgres db after dumping I dumped my postgres db using pgAdmin in plain format because I wanted to search for some text on it. After doing the search, I decided to import the db back from the sql query window but I get this error

ERROR:  syntax error at or near "330"
LINE 48882: 330 643 644 1 2012-01-11 16:04:20.468 2012-01-17 22:33:23.99...

This is the part that gives the error. I know I didn't modify anything here. It is what it dumped for me

COPY account_account (id, parent_left, parent_right, create_uid, create_date, write_date, write_uid, code, reconcile, currency_id, user_type, active, name, level, company_id, shortcut, note, parent_id, currency_mode, type) FROM stdin;
330 643 644 1   2012-01-11 16:04:20.468 2012-01-17 22:33:23.993 1   40300010    f   \N  8   t   Rent Income 5   1   \N  \N  329 current other
55  89  94  1   2012-01-05 23:50:08.524 2012-01-10 12:32:51.934 1   12000000    f   \N  18  t   Long Term Investments   3   1   \N  \N  24  current view

Here is the code that creates the table

CREATE TABLE account_account (
    id integer NOT NULL,
    parent_left integer,
    parent_right integer,
    create_uid integer,
    create_date timestamp without time zone,
    write_date timestamp without time zone,
    write_uid integer,
    code character varying(64) NOT NULL,
    reconcile boolean,
    currency_id integer,
    user_type integer NOT NULL,
    active boolean,
    name character varying(128) NOT NULL,
    level integer,
    company_id integer NOT NULL,
    shortcut character varying(12),
    note text,
    parent_id integer,
    currency_mode character varying(16) NOT NULL,
    type character varying(16) NOT NULL
);

I am so confused here. Please what could be the problem

The problem is that you cannot COPY FROM stdin with pgAdmin (no stdin available in the query window), but you can easily restore the dump with psql

psql <database> -h <host> -p <port> -f <dump-file>

Hope that helps.


Is there a UI tool for moving data from two different tables? The schemas in the two databases are different. Eg.

Database 1 / users
- id
- name
- phone

Database 2 / users
- id
- first_name
- mobile

Is there a tool that lets me visually create the statements or do the transform / copying the data?

Using MySQL by the way :)

Why use a UI tool when the query is this short?

INSERT INTO db1.users (id, name, phone)
    SELECT id, first_name, mobile FROM db2.users;

Caveats:  You probably should not copy id if it is AUTO_INCREMENT.  I was guessing which direction you wanted to copy.


Why SQL Server port number is not default 1433? Installed latest SQL Server. It is listening on port number 62628 according to server log. Why is the port number is not the default of 1433?

How to change the port number back to the default 1433?

It was installed on Windows 10. The server name is MachineName\SQLEXPRESS. I did not give a name.

Contributed by AlwaysLearning:

Configure the Windows Firewall to Allow SQL Server Access:
By default, named instances (including SQL Server Express) use dynamic ports. That means that every time that the Database Engine starts, it identifies an available port and uses that port number. If the named instance is the only instance of the Database Engine installed, it will probably use TCP port 1433. If other instances of the Database Engine are installed, it will probably use a different TCP port.


How to pass a variable to a stored procedure? Calling the following procedure in DO $$ bloc seems to not work correctly .

Procedure :

CREATE OR REPLACE PROCEDURE pLog(P_VAR_LOG text)
AS $$
BEGIN
  IF D_SetLog THEN

      RAISE NOTICE '[LOG %] %',to_char(sysdate,'DD/MM/YYYY - HH24:MI:SS'),P_VAR_LOG;

  END IF
;

END;
$$ LANGUAGE plpgsql;

I tried to declare the PROCEDURE in the declare bloc but it's not working.

Here is the DO $$ bloc definition :

DO $$
DECLARE

CRITERE1 CURSOR FOR
    SELECT
            DOSSIER.ID_DOSSIER,
            DOSSIER.ED_ETAT
    FROM
        IMM_DOSSIER DOSSIER,
        IMM_CHANTIER CHANTIER
    WHERE
        DOSSIER.ID_DOSSIER = CHANTIER.ID_DOSSIER
        AND DOSSIER.ED_ETAT <> 'CLOS'
        AND CHANTIER.DATE_FIN_REELLE is null
        AND CHANTIER.DATE_FIN_SOUHAITEE is not null
        AND CHANTIER.DATE_FIN_SOUHAITEE < LOCALTIMESTAMP

UNION

    SELECT
            DOSSIER.ID_DOSSIER,
            DOSSIER.ED_ETAT
    FROM (
            SELECT
                DEVIS.ID_DOSSIER,
                MIN(TRAVAUX.DATE_FIN_SOUHAITEE) DATE_FIN_SOUHAITEE
            FROM
                IMM_DEVIS DEVIS, IMM_TRAVAUX_DEVIS TRAVAUX
            WHERE
              DEVIS.ID_DEVIS = TRAVAUX.ID_DEVIS
              AND TRAVAUX.DATE_FIN_REELLE is null
              AND TRAVAUX.DATE_FIN_SOUHAITEE is not null
            GROUP BY DEVIS.ID_DOSSIER
        )
        DOSSIER_CIBLE,
        IMM_DOSSIER DOSSIER
    WHERE
        DOSSIER.ID_DOSSIER = DOSSIER_CIBLE.ID_DOSSIER
        AND DOSSIER.ED_ETAT <> 'CLOS'
        and DOSSIER_CIBLE.DATE_FIN_SOUHAITEE < LOCALTIMESTAMP;

-- Def des enregistrements pour les curseurs
V_C_CRITERE1 RECORD;

V_INSERT INTEGER := 0;

D_CodeRet INTEGER;

D_SetLog    BOOLEAN;

/*****************************************************************************
**  NOM MNEMONIQUE  : pLog
*****************************************************************************/

/*****************************************************************************
**  MAIN
*****************************************************************************/
BEGIN

    D_SetLog    := TRUE
;

    CALL pLog('Suppression des anciennes alertes : ')
;
END $$;

When i execute this sql script i get the following :

SET
DROP SEQUENCE
WARNING:  there is no transaction in progress
COMMIT
CREATE SEQUENCE
WARNING:  there is no transaction in progress
COMMIT
CREATE PROCEDURE
DO

The NOTICE is not being displayed and can't figure out why.

There are no global variables in PostgreSQL, sou you'd have to pass d_setlog as parameter to the procedure.

An alternative is to use a custom placeholder parameter in PostgreSQL:

The calling function does

SET mylog.active = true;

and the called procedure tests

IF current_setting('mylog.active', TRUE) = 'true' THEN ...

Note that such parameters are always strings.


case when IsNull Alternatives? Is there a way, to optimize this query, where several IsNull in relation with dates are used? Unfortunately the query is a way slow and actually I have no glue, how to make it way faster.

case
when IsNull(A.TD, '1900-01-01') > IsNull(B.TD, '1900-01-01') then IsNull(A.TD, '1900-01-01')
when IsNull(A.TD, '1900-01-01') < IsNull(B.TD, '1900-01-01') then IsNull(B.TD, '1900-01-01')
when IsNull(A.TD, '1900-01-01') = IsNull(B.TD, '1900-01-01') then S.TE end as DZ

Thank you.

As sp_BlitzErik noted in his comment, he has an excellent post on this topic.

However, his query was something of a special case, and some of the things he did only worked because he wanted a COUNT. As you only present an expression from your WHERE clause, we can't make any particular assumptions about the rest of your query. I do recommend looking Erik's post over, in any case. It's very good.

Given what you've presented, I come up with the following:

case
when A.TD IS NULL and B.TD IS NULL THEN S.TE
when A.TD IS NULL and '1900-01-01' > B.TD THEN '1900-01-01'
when A.TD IS NULL and '1900-01-01' < B.TD THEN B.TD
when A.TD IS NULL and '1900-01-01' = B.TD THEN S.TE
when A.TD > '1900-01-01' and B.TD IS NULL THEN A.TD
when A.TD < '1900-01-01' and B.TD IS NULL THEN '1900-01-01'
when A.TD = '1900-01-01' and B.TD IS NULL THEN S.TE
when A.TD > B.TD then A.TD
when A.TD < B.TD then B.TD
when A.TD = B.TD then S.TE end as DZ

Basically, ISNULL(A, n) = m can be broken down into two parts:

(   (A IS NULL and n = m)
 OR A = m
)

As you know, SQL technically uses trinary logic - an expression can be TRUE, FALSE, or NULL. For two expressions joined by OR to evaluate to TRUE, one of them must be true - the other can be either FALSE or NULL, it doesn't matter.

So, if A is not NULL, the first expression will always be FALSE, and the result of the second expression (which will not be NULL as long as m is not NULL) will determine the value of the pair.

And, if A is NULL, the second expression will be NULL, and the first expression (which will not be NULL as long as neither n nor M is NULL) determines the value of the pair.

Since we're looking at multiple comparisons on two expressions, things get a bit more complicated, but not necessarily unmanageably so.

Note that you may be able to simplify the expression. If your dates can never be lower than the hardcoded date, then you don't need to see if A.TD or B.TD is less than that date.

You may also want to rearrange the various WHEN clauses. When it doesn't hurt maintainability, I'm inclined to put the clause that represents the most likely occurrence first; in your cases, if NULLs are rare, that may be the last three checks. I'm not sure if query optimizer perform the checks in the order they're presented, but (if they do) it's a good idea to minimize the number of checks that have to be done before the right one is hit.

A final note: you didn't specify what DBMS you're using, and it's possible that something I've presented wouldn't work globally. I mostly use MS SQL Server, and this would work there.


Is it a good practice to create a database for each year? In a HR database i need to do statistics for each year, is it a good practice to create a database for each year?

Thank you.

It really depends how your HR/, Payroll application presenting it to the users. I can imagine that can be done in completely ergonomic way, so basically it would be only integration problem for some kind of external analytics. And it probably may have advantage, past year databases may be put on read only mode, and that current year databases would be smaller than big, aggregate database..
But saying this, I would like to say, I've never seen any good system for such kind of database model.
Some random thoughts:

It is troublesome in every day use, as it is quite common some operations have to change databases records even in the past, at least for short time during years change
It is very hard to use such systems in external analytics, based on normal tools: instead writing queries on database with time periods as filters, you have to manage several database connections and aggregate statistical data outside, probably using temporary tables or even additional data warehouse
Some reports may be required, for time periods which not agree with years change. For example for accounting purposes.
Exporting duch data into other systems, for example when changing HR platform to other products, is nightmare in such scenario.
Upgrade. Usually is performed on present database. Will you upgrade historical ones as well? If yes - what I advantage of having it separately? If not - you will end with several incompatible database schemas.
Maintenance. It may be a lot of effort put to maintain several databases instead of only one. It is known issue.


redundant foreign keys in PostgreSQL - are there any indexing benefits? Note: this is not the same as this SO question which is about adding redundant information in the tables themselves.

Does the final statement in the below series of statements buy me anything in terms of indexing / query optimization or is it totally useless and an anti-pattern? From a referential integrity viewpoint it is useless but might there be some benefit in indexing, or query processing speed for instance, e.g. in a JOIN only between the "child" and "grandfather" tables?

CREATE TABLE grandfather (i INTEGER);
ALTER TABLE  grandfather ADD PRIMARY KEY (i);

CREATE TABLE father  (i, j INTEGER);
ALTER TABLE  father ADD PRIMARY KEY (i, j);
ALTER TABLE  father ADD CONSTRAINT father_2_grandfather FOREIGN KEY (i) REFERENCES grandfather(i);

CREATE TABLE child (i, j, k INTEGER);
ALTER TABLE  child ADD PRIMARY KEY (i, j, k);
ALTER TABLE  child ADD CONSTRAINT child_2_father      FOREIGN KEY (i, j) REFERENCES father(i, j);
-- is the below statement totally useless ?
ALTER TABLE  child ADD CONSTRAINT child_2_grandfather FOREIGN KEY (i)    REFERENCES grandfather(i);

(For similar relations simple hierarchy are more sensible... (id, parent))

Apart from that the last foreign key only slows down a bit the DML queries.


Filter ingredients choices for recipe database by category, or dynamic search? Requirement:  There are thousands of ingredients in given commercial kitchen.
To simplify user interface,  when entering a new recipe, only ingredients relevant to the Food Category selected by user should be shown.

For example, when entering a new cookie recipe in a form, show only ingredients used in generally used in Cookies recipes

Simple Solution (?):
A RECIPE belongs to one  FOOD_CATEGORY.  For example: Peanut Butter Cookie ---> Cookies
INGREDIENT <- ManyToMany -> FOOD_CATEGORY

Will this suffice to meet the above requirement, and are there any "gotchas"?

Perhaps a simpler solution is to use a Ajax style dynamic search box that filters all ingredients as you begin to type.

More broadly, is there a methodology that drives Database design from the UI to prevent any potential surprises?

For example, after my initial design, I realized a MultiSelectBox with too many ingredients simply wouldn't work.

That data model seems right for meeting the requirements you listed.

Your other questions are more about UI than about data modeling. Yes, a dynamic search box could work and might be the best solution here, especially since you don't want to make it impossible to use an unusual ingredient in a recipe. (Some of the best recipes are made with unusual ingredients!)

Regarding “driving database design from the UI”: My opinion is that you start with product and UI design, and then you build whatever database is needed to implement that. If you don't know exactly what you're trying to build as your first iteration, you're going to have a hard time with the database. But don't build more than you need for that first iteration. You can always migrate the database to a new schema later, when your requirements change.


Shrink a SQL Anywhere 12 database Is there any way to shrink SQL Anywhere databases?

With SQL Server, it was an easy task. Just do it, have a faster database again that has a size which can be transferred over a slow internet connection.

Can this be achieved with Sybase?

The question is not whether you should shrink or not - it is how it is done. So please don't quote things like "you should not" from Pinal.

You can't shrink SQL Anywhere databases online.

The "correct" way to do it is to unload/reload it in a new database.

It's easy to do it via Sybase Central. Via command line it is the dbunload command with the -an command line switch. See Unload utility (dbunload) in the documentation.

A shrunk database is not by default faster than an "unshrinked" database.


Take schema ownership as a DB owner I'm trying to take ownership of the "public" schema in the database I'm the owner of (but not superuser). I'm running alter schema public owner to username, where username is my user name. I'm getting the following error: ERROR: must be owner of schema public.
Is it possible for me as a non-superuser database owner to take ownership of a schema, (or generally any object) in my database?

No. This is not possible. You can change the ownership of any object in PostgreSQL only if you are Superuser or if you are the owner of same object.
You should ask your DBA or owner of the schema to do change the owner.


Datetime hourly precision What would be the best to store date + hour + timezone in SQL? i dont need a higher precision (minutes, seconds, ms..) the primary reason for this is to reduce memory use and calculation time on big datasets. or should'nt this a concern at all?

In SQL Server, always store dates using a datetime, datetimeoffset or datetime2 datatype.  Storing them as anything else invariably results in needless CPU spent converting the column into some kind of date.

The possible exception to this might be if you want to store just the year, which could be a tiny integer.  However, since you need hour and timezone precision, use the datetimeoffset data type.  datetimeoffset(0) will give you precision down to the second, which you don't require, however you'll be guaranteed to never have an invalid date stored, and you'll know that the data is time-zone compatible.

datetimeoffset(0) occupies 8 bytes, unless you're storing a lot of dates in memory, this shouldn't be considered wasteful in light of the advantages conferred by using the correct data type.

You may be interested in reading this answer, which talks about some common problems storing date/time data incorrectly.


How write mysql query corresponding this tables This is my database tables

I want to get this result:

How can I write select query to get that result?

You have to use dynamic SQL (PREPARE, EXECUTE) for that

SET @sql = NULL;

SELECT GROUP_CONCAT(DISTINCT CONCAT(
  'SUM(CASE WHEN DATE_FORMAT(o.date, ''%Y%m'') = ',
  DATE_FORMAT(date, '%Y%m'),
  ' THEN op.count ELSE 0 END) `', DATE_FORMAT(date, '%m/%Y'), '_Count`,',
  'SUM(CASE WHEN DATE_FORMAT(o.date, ''%Y%m'') = ',
  DATE_FORMAT(date, '%Y%m'),
  ' THEN op.count * op.price ELSE 0 END) `', DATE_FORMAT(date, '%m/%Y'), '_Sum`'))
  INTO @sql
  FROM `order`;

SET @sql = CONCAT(
  'SELECT c.name,p.title,', @sql,
  '  FROM `order` o JOIN order_product op
       ON o.id = op.order_id JOIN client c
       ON o.client_id = c.id JOIN product p
       ON op.product_id = p.id
    GROUP BY o.client_id, c.name, op.product_id, p.title');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Here is SQLFiddle demo

To simplify things on the calling end you can wrap it up into a stored procedure like this

DELIMITER $$
CREATE PROCEDURE sp_monthly_report()
BEGIN
  SET @sql = NULL;

  SELECT GROUP_CONCAT(DISTINCT CONCAT(
    'SUM(CASE WHEN DATE_FORMAT(o.date, ''%Y%m'') = ',
    DATE_FORMAT(date, '%Y%m'),
    ' THEN op.count ELSE 0 END) `', DATE_FORMAT(date, '%m/%Y'), '_Count`,',
    'SUM(CASE WHEN DATE_FORMAT(o.date, ''%Y%m'') = ',
    DATE_FORMAT(date, '%Y%m'),
    ' THEN op.count * op.price ELSE 0 END) `', DATE_FORMAT(date, '%m/%Y'), '_Sum`'))
    INTO @sql
    FROM `order`;

  SET @sql = CONCAT(
    'SELECT c.name,p.title,', @sql,
    '  FROM `order` o JOIN order_product op
         ON o.id = op.order_id JOIN client c
         ON o.client_id = c.id JOIN product p
         ON op.product_id = p.id
      GROUP BY o.client_id, c.name, op.product_id, p.title');

  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END$$
DELIMITER ;

Sample usage:

CALL sp_monthly_report();

Here is SQLFiddle demo


How do we create spatial index on views if it is possible? Is it possible to create spatial index on sql server 2008? If yes how do we create it on table with schemabinding?

Unfortunately I don't believe you can.  XML and Spatial Indexes can only be created tables.  Not views.

Edit:

Here's some proof for you straight from BOL: Restrictions on Spatial Indexes

  Spatial Indexes cannot be specified on indexed views.


Why is it important for every table to have a primary key? So I have some tables in Sql Server that are essentially a list of sales, things like:

ProductID
SalesOrderID
ProductFamilyID
ProductCost
ProductSource

and so on. In this case, none of the columns are necessarily unique, so I can't create a primary key from any combination of them. In fact, the only constraint that I really have on the table is that I need every row in the table to be a unique combination of the columns. So I'm assuming something like a unique index would be the way to go there.

The only primary key I could add is something like an autoincrement primary key. But what would be the actual use of that, database wise? What are the possible problems with not creating a primary key for a table like this?

The big benefit is that the key is very narrow and naturally in order. Every non-clustered index you add by definition also contains the primary key in order to do look ups. So even though it doesn't appear to have much benefit, using an identity column as a PK would still be helpful.

Also, if you ever expand on to this and add other tables that need to reference this table, you just need the one column for the foreign key.


Optimizing real-time Database table in Microsoft SQL server 2008 We are using a web interface to monitor our jobs state and its started and completed timestamps. We have >200,000 job instances running on each day for different job run-users. We maintain the state of all jobs executed in last 6months in a database table. Database server used in Microsoft sql server 2008. With the growing number of jobs or records in table, time taken for updating the records in a table is also going slower than before.

We wanted to redesign our table and schema to update jobs state in real time scenario without causing any delay to job execution. We are thinking of splitting this single table to multiple tables for each user so that updating one user job state should not cause any delay in updating other user job state. To add a note here: the number of users is less than 25 but the number of jobs for each user is more than 50,000. Also, there is no dependency between each user jobs.

Will this table splitting be a better or an optimized way to get the table update or search faster?
Or Will row locking feature in microsoft sql sever solve this case?
Do we have any best optimized way to handle this case?
Will the index be the reason for slowness?

Table:
Job id [primary key] | Job name | started time | End time | status

We have 2 indexes: one based on job start and end time. Second index is only based on job name.

Thanks.

The best strategy for your case is to properly index the table, update statistics and defrag it. That should solve the problem.

There is no inherent limitation on row count in SQL Server. With the right indexes it should not start to become slower.

You need to look at your select and update queries and build useful indexes for them.


SQL query output to Sybase temp table I need to query a MSSQL 2008 db for 4 columns of data where one column (an ID) is used to join a number of tables from a Sybase ASE 15 database then output all 4 columns from the SQL query with a number of columns from 5 other tables in Sybase.

Currently we query SQL and output to a csv, then load that data to an Access db.
 Then use that to Access table to query the Sybase tables using a join on the ID from the SQL query.  I need to take Access out of the equation and have all this run either in MSSQL or Sybase and have that triggered by a SSRS report to display the final Sybase query in a SSRS report.

I am able to make a linked server from SQL to Sybase or vise versa, and am able to use openquery from SQL to query Sybase.  How do I use my results from the SQL side to then query the Sybase side, I need the small subset of IDs from the SQL query to pull from the Sybase tables but cannot seem to get the SQL results to the Sybase side in a temp table or anything to then query from Sybase with the openquery call.

Is my only option to use a SQL bcp command to output the query to a csv file then use a separate bcp (Sybase version) to load the SQL csv to Sybase then return the results?  If so how can that be done from SSRS? What are my options here, am I missing an opportunity?

NOTE: I work with Sybase ASE, including working with proxy tables and interserver communications/queries; I do not work with MSSQL or SSRS; so fwiw ...

If the MSSQL data is driving the main query then I'm guessing 1 of 2 scenarios are possible with normal interserver communications:

from MSSQL: for each MSSQL row of interest, perform an interserver join with the remote Sybase tables; the overhead for performing a large number of interserver calls could add up quickly
from Sybase: pull all MSSQL rows of interest in one interserver request, then with the MSSQL data set residing in Sybase conduct the joins with the local ASE tables; the overhead for a single interserver join is going to be much less than the overhead for scenario #1, definitely much faster than the current method of using Access as an intermediary stopping point, and likely faster than any bcp-based operation)

NOTE: While newer versions of ASE have the ability to push/pull parts of interserver queries to/from remote servers, I'm not sure if/how this feature works when the remote server is a non-ASE RDBMS (eg, MSSQL).

For scenario #2 you have a couple options, all based on having a proxy table defined in ASE that references the remote MSSQL table:

perform a standalone query to pull the MSSQL data into a local ASE table (could be a permanent or #temp table), eg, select/into or insert/select, then perform your local query using this MSSQL-populated table to drive the query
reference the proxy table in your multi-table query, and if the optimizer has enough info to properly cost the join it will hopefully perform an initial/single pull from the proxy table (most likely into a local worktable) and then use that data to drive the rest of the local joins


Fastest engine for Mass Updates (only updates done on table) I have a table of 2 columns and 60 million rows. I want to update the table by incrementing one field by 1 on various different rows (This is the only type of operation that will ever be done on the table). The updates that occur happen all at once everytime, so I am usually updating 30-40 million out of the 60 million rows once a day. This table is only used by one user so I am not worried about multiple users trying to connect to the table at the same time or anything like that. Which engine (and parameters) would be fastest to perform just updates and nothing else on (well there will be one select statement once in a while, but I am not concerned about the performance of select statements)? I am currently using MyISAM as I am not worried about ACID, but it currently is quite slow for mass updates. Essentially I am trying to find the fastest way to do MASS Updates.

Your question is somewhat like a question I answered back on Sep 06, 2013 : mySQL for analytics and updating same row

In that answer, I recommended the following

Adding a third column to the table to represent a slot
Select an arbitrary value for the number of slots
Making a composite PRIMARY KEY

hour boundary
slot number

Doing an INSERT with ON DUPLICATE KEY UPDATE to increment the counter

Since you are doing 30-40 million updates, you may need to choose a very high number of slots. In your case, I would go with 250,000 slots. The high the number of slots, the lower the incidence of collision (trying to update the same slot).

As far as the choice of storage engine, you cannot stay with MyISAM. Why ? MyISAM will do a full table lock for every update. Doing 40 million updates require 40 million table locks. My posted answer works well with InnoDB's row level locking. Even if you implement the slot paradigm, MyISAM will still do 40 million table locks with 40 million updates.

Definitely, you must go with InnoDB. You also need to tune InnoDB.

Larger Buffer Pool (increasing innodb_buffer_pool_size)
Larger Log Buffer (increasing innodb_log_buffer_size to 128M)
More Write I/O Threads (setting innodb_write_io_threads to 8 or more)

If you are using MySQL 5.1, upgrade to MySQL 5.6.

Give it a Try !!!

UPDATE 2014-09-15 00:21 EDT

I learned the concept of slots for counters from page 140 of the Book

My post (dated Sep 06, 2013) is the first post in the DBA StackExchange to mention this book for your specific issue.
 Someone else mentioned this technique one month later in this same forum (counter table in mysql dates Oct 18, 2013)

I just tried googling for this concept search for mysql counter slot

I found some posts:

Baron Schwartz, one of the authors of the book, has a URL to that very reference
Avoid locks when storing counters in MySQL

The main point I am making is that this concept is not new. You can implement it.

Give it a Try !!!


How to put this particular constraint on this link table in MySQL? I have the following three tables in MySQL: Store, Groups, StoreGroup. A store can belong to several groups. StoreGroup is the link table that records the groups a store belongs to. The Groups table has the following fields:

+------------+----------------------------------+------+-----+---------+----------------+
| Field      | Type                             | Null | Key | Default | Extra          |
+------------+----------------------------------+------+-----+---------+----------------+
| id         | int(11)                          | NO   | PRI | NULL    | auto_increment |
| name       | varchar(255)                     | YES  | UNI | NULL    |                |
| category   | enum('Area','Level','Temp')      | YES  |     | NULL    |                |
| expireDate | datetime                         | YES  |     | NULL    |                |
+------------+----------------------------------+------+-----+---------+----------------+

The link table StoreGroup that records each store's groups has the following fields:

+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| storeID | varchar(16) | NO   | PRI | NULL    |       |
| groupID | int(11)     | NO   | PRI | 0       |       |
+---------+-------------+------+-----+---------+-------+

However, even though a store can belong to several groups, it has a constraint that it can belong to only one group in one group category. For example, a store with storeID ABC can belong to both groups with groupID 1001 and 1002 since 1001 is from 'Area' category and 1002 is from 'Level' category. But it cannot be assigned to groupID 1003 because both group 1001 and 1003 are from the same group category 'Area' as shown below:

SELECT * FROM Groups;

+------+--------------+----------+---------------------+
| id   | name         | category | expireDate          |
+------+--------------+----------+---------------------+
| 1001 | City A       | Area     | NULL                |
| 1002 | Branch       | Level    | NULL                |
| 1003 | City B       | Area     | NULL                |
+------+--------------+----------+---------------------+

So when one wants to insert the record into StoreGroup table, the following is OK:

INSERT INTO StoreGroup VALUES('ABC', 1001);
INSERT INTO StoreGroup VALUES('ABC', 1002);

But the following should fail because storeID 'ABC' cannot belong to both groups 1001 and 1003 since 1001 and 1003 both are from category 'Area':

INSERT INTO StoreGroup VALUES('ABC', 1003);    --This should fail.

So how to modify the table of StoreGroup or Groups to add this constraint so that assigning store to groups with same category will fail?

The solution is to add the group category column in the StoreGroups table and then add a unique constraint on (storeId, groupCategory). The table definitions would be like this:

CREATE TABLE Groups
  ( groupID INT NOT NULL PRIMARY KEY
  , name VARCHAR(255) NOT NULL
  , groupCategory NOT NULL VARCHAR(30)
  , exprireDate DATE NULL
  , UNIQUE (groupCategory, groupID)       -- this is needed for the FK below
  ) ;

CREATE TABLE StoreGroups
  ( storeID VARCHAR(16) NOT NULL
  , groupID INT NOT NULL PRIMARY KEY
  , groupCategory NOT NULL VARCHAR(30)
  , PRIMARY KEY (storeId, groupID)
  , FOREIGN KEY (groupCategory, groupID)
      REFERENCES Groups (groupCategory, groupID)
  , UNIQUE (storeID, groupCategory)              -- your rule enforced
  ) ;

The UNIQUE constraint enforces that there cannot be 2 StoreGroups with the same storeID and the same groupCategory.


what will cause a sql server AG readonly replica to stop serving readonly clients? I understand that if a failover happens (among any nodes in an AG), then all nodes lose their existing connections.
And I assume that if a primary suspends data movement, that will have no effect on secondaries (just a potential pile of logs).
But suppose the primary node goes down?
Or a database is removed from the AG on the primary?
Will the secondary replicas keep doing their thing in those scenarios?

But suppose the primary node goes down? Or a database is removed from the AG on the primary? Will the secondary replicas keep doing their thing in those scenarios?

I believe you are taking about unexpected shutdown or similar scenario where primary server not available in this case first WSFC will see if it is in position to update cluster registry to set the status like not synchronizing if the server shutdown is immediate Registry may not get updated with the state. When primary is down it would not send log blocks to secondary and if secondary is also done redoing and undoing all the log records it will also not do any work but wait for log records to be pulled when primary comes online. But let us say when the primary went down secondary was behind it, primary it will continue its process of replaying the log records to come alike primary.

Or a database is removed from the AG on the primary

Database will NOT be removed from AG unless user does that, so my above statement does not apply to this.


Does the size of the primary key contribute to table size? I have a table (InnoDB) with data length around 36G and index length 23G.

It had a composite primary key across three columns.

As an initial attempt to reduce the size of this table, I dropped the primary key (wasn't actually needed anyway). Yet, the data length and index length are the same.

Is this to be expected?

UPDATE
innodb_file_per_table is enabled on this database

UPDATE

mysql> show create table event;

CREATE TABLE `event` (
`owner_id` varchar(36) NOT NULL,
`key_id` varchar(255) NOT NULL,
`value` varchar(255) NOT NULL,
KEY `owner_id_idx` (`owner_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8

SHORT ANSWER

It is really the other way around : The size of the table contributes to the size of the primary key. Therefore, YES it was to be expected.

LONG ANSWER

The Primary Key resides within gen_clust_index, a.k.a. the Clustered Index.

According to the MySQL Documentation on the Clustered Index

  How the Clustered Index Speeds Up Queries

  Accessing a row through the clustered index is fast because the row
  data is on the same page where the index search leads. If a table is
  large, the clustered index architecture often saves a disk I/O
  operation when compared to storage organizations that store row data
  using a different page from the index record. (For example, MyISAM
  uses one file for data rows and another for index records.)

From this description, the InnoDB Storage Engine has each row sits inside the Clustered Index.

In light of this, two questions need answering:

Why does dropping the PRIMARY KEY not shrink the Clustered Index?
Why does OPTIMIZE TABLE not shrink the Clustered Index?

The answer to both questions is but one. The MySQL Documentation on the Clustered Index says:

  If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB
  internally generates a hidden clustered index on a synthetic column
  containing row ID values. The rows are ordered by the ID that InnoDB
  assigns to the rows in such a table. The row ID is a 6-byte field that
  increases monotonically as new rows are inserted. Thus, the rows
  ordered by the row ID are physically in insertion order.

Think about it : OPTIMIZE TABLE is rendered ineffective since you made no attempt to delete any rows. Since you did not delete any rows, all the space occupied by rows will still sit within the Clustered Index. You merely traded-in one key (your Primary Key) for another (the 6-byte row ID).

What are the ways to shrink the Clustered Index ?

I have one of three(3) techniques you may need to do

TECHNIQUE #1 : Reducing the Length of your Columns

shrink INTs to SMALLINTs or TINYINTs
shrink VARCHAR(255) to VARCHAR(128)

TECHNIQUE #2 : Delete Old Rows

Look for rows with really old timestamps, then either

Delete the Old Rows
Archive the Old Rows in another table

TECHNIQUE #3 : Dropping Columns From the Table

This is true in theory
Nobody wants to lose data in the Process
Archive the Table Before Dropping Columns

CAVEAT

Before you apply one of these three(3) techniques, please backup your table.

EPILOGUE

Once you have applied one of these three(3) techniques, then you can do one of the following in order to shrink the Clustered Index (for let's say, mydb.mytable):

OPTIMIZE TABLE mydb.mytable;
ALTER TABLE mydb.mytable ENGINE=InnoDB;

Give it a Try !!!

UPDATE 2013-06-17 07:34 EDT

Here is your last comment

  So if the original primary key was greater than 6-bytes wouldn't I expect to see a decrease in the clustered index size?

Even if PRIMARY KEY is now CHAR(255), each row sits inside the Clustered Index. Each page in InnoDB is 16K. The 6-byte row ID (which InnoDB still uses internally anyway) is still embedded within the same pages. Dropping the PRIMARY KEY is more of a logical dropping than a physical one. That's why I said earlier

  You merely traded-in one key (your Primary Key) for another (the 6-byte row ID)

The physical act of dropping the PRIMARY KEY may entail running around to every row in tombstoning (flagging) each row not to use some internal column combination for the PRIMARY KEY but to revert back to the row ID. That would hardly change the usage of a 16K InnoDB Page, regardless of the Row Length Being Long (which may allow for a few rows within the Page) or Short (which may allow for many more rows within the Page).

Running OPTIMIZE TABLE to shrink it still won't make any noticeable change because the flagging mechanism still exists for the Clustered Index. The exact same number of rows, given the average length of your rows in the table, keeps the Clustered Index the same size.

Look over the three techniques I mentioned before and the MySQL Documentation on the Clustered Index. Given the number of rows in the table and your current table definition, if you cannot reduce column sizes, remove columns, delete rows and archive them elsewhere, then there is nothing you can do to shrink the table.


Postgres - how to tell when the last VACUUM FULL was run? If I run the following
VACUUM myTable;

this gets recorded in the postgres system tables and can be seen with
select last_vacuum, vacuum_count from pg_stat_all_tables where relname= 'mytable';

However, doing a VACUUM FULL seems to go unrecorded.
How can I tell when the last VACUUM FULL was run against a table?

PostgreSQL doesn't track that information.


Update staging table records in SQL Server 2017 with INSERT trigger and table-valued function, using INSERTED records I am loading hundreds of order files (positional flat files), via SSIS, into staging tables, and ultimately into production tables.  As part of the staging table population process, I would like to update 2 fields in the staging table that are not part of the original flat file (NewListPrice and NewPriceVersion), however I need to use 3 columns of the newly-inserted record in my function (Company, CustomerNumber and SLIN/UPC).   I have created both a table-valued function and an INSERT trigger on my staging table, but it is definitely not working as intended.

CREATE TRIGGER [Staging].[INSERT_New_Pricing_tg]
    ON [Staging].[OrderDetail01_tb]
    AFTER INSERT
    AS
    BEGIN
        SET NOCOUNT ON

        UPDATE Staging.OrderDetail01_tb
            SET NewListCost = A.CustomerPrice,
                NewPriceVersion = A.NewPriceVersion
            FROM (SELECT LU.CustomerPrice,
                            LU.NewPriceVersion
                    FROM inserted I
                    CROSS APPLY (SELECT * FROM dbo.Find_New_Price_fn(I.Company, I.CustomerNumber, I.UPC)) LU) A
    END

CREATE FUNCTION [dbo].[Find_New_Price_fn](@COMPANY varchar(2) = NULL, @CUSTOMER_NUMBER varchar(6) = NULL, @SLIN varchar(20) = NULL)
    RETURNS @NEW_PRICE_DETAILS TABLE (CustomerPrice varchar(10) NULL, NewPriceVersion varchar(50) NULL)
    AS

    BEGIN
        IF (@COMPANY IS NULL) OR (@CUSTOMER_NUMBER IS NULL) OR (@SLIN IS NULL)
            RETURN
        ELSE
            BEGIN
                DECLARE @ITEM_NUMBER varchar(6),
                        @CARTON_UPC varchar(20)

                -- look up the item number in the Cross-Reference table, using the SLIN
                SELECT @ITEM_NUMBER = LTRIM(RTRIM(ItemCode))
                FROM dbo.ItemXRef_tb IX
                WHERE MarkerNumber = '8001' -- customer master group number
                    AND Company = @COMPANY
                    AND ReferenceItemCode = @SLIN

                IF @ITEM_NUMBER IS NULL
                    RETURN
                ELSE
                    -- find the item in the Items table, based on the item number returned
                    SELECT @CARTON_UPC = LTRIM(RTRIM(CartonUpc))
                    FROM Items_tb
                    WHERE Company = @COMPANY
                        AND ItemNumber = RIGHT('000000' + LTRIM(RTRIM(@ITEM_NUMBER)), 6)

                    IF @CARTON_UPC IS NULL
                        RETURN
                    ELSE
                        -- find the new price of the item using the CartonUPC of the item
                        INSERT INTO @NEW_PRICE_DETAILS(CustomerPrice, NewPriceVersion)
                            SELECT RetailerNetPrice,
                                    PriceVersion
                            FROM PriceFile_tb PF
                            WHERE StoreID = CONCAT((SELECT CAST(ExternalPrefix as varchar) FROM Lookups.CompanyPrefixes_tb WHERE CompanyAbbreviation = @COMPANY), RIGHT('000000' + LTRIM(RTRIM(@CUSTOMER_NUMBER)), 6))
                                AND ProductUPCCode = LTRIM(RTRIM(@CARTON_UPC))
                                AND PriceEffDateFirst = FORMAT(GETDATE(), 'yyyyMMdd')
            END
            RETURN
    END

I am fully aware, and completely expect, that not all records will return NewListPrice and NewPriceVersion values.  I have tried calling the function directly with values I know will return a result, and values I know that won't return a result, and the function works as expected in these cases, however it does not work when I put the function call in my trigger.  I am not sure where I am going wrong with this.

With Scott Hodgin's help (see comment thread above), I was able to figure out the issue:

First issue:  Since I certainly was using an OLE DB destination, I needed to add FIRE_TRIGGERS in the Advanced Editor (Microsoft, make this easier to find)

Second issue: once the INSERT trigger was firing, it was updating every record in the table, not just the ones where the function returned a value.  Of course it was; there was no WHERE clause or INNER JOIN to limit what needed to be update.  I modified my INSERT trigger to include the fields from the INSERTED table, and added a WHERE clause as follows:

CREATE TRIGGER [Staging].[INSERT_New_Pricing_tg]
    ON [Staging].[OrderDetail01_tb]
    AFTER INSERT
    AS
    BEGIN
        SET NOCOUNT ON

        UPDATE Staging.OrderDetail01_tb
            SET NewListCost = A.CustomerPrice,
                NewPriceVersion = A.NewPriceVersion
            FROM (SELECT I.Company,
                            I.CustomerNumber,
                            I.UPC,
                            LU.CustomerPrice,
                            LU.NewPriceVersion
                    FROM inserted I
                    CROSS APPLY (SELECT * FROM dbo.Find_New_Price_fn(I.Company, I.CustomerNumber, I.UPC)) LU) A
            WHERE Company = A.Company
                AND CustomerNumber = A.CustomerNumber
                AND UPC = A.UPC
    END

Thank you very much Scott for starting me on the right path.  Without your help and that link, I never would have solved this.


Copy blob from one row to another I have a corrupt blob in a row.
I'd like to replace that with another blob from the same table.

How can I do that with sql statements only? (No file system access if at all possible.)

Poor man's change:

mysql> CREATE TABLE `foo` (
  `id` tinyint(4) NOT NULL AUTO_INCREMENT,
  `content` blob,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

mysql> INSERT INTO foo VALUES (NULL, 'bar'), (NULL, 'another_bar');

mysql> SELECT * FROM foo;
+----+-------------+
| id | content     |
+----+-------------+
|  1 | bar         |
|  2 | another_bar |
+----+-------------+
2 rows in set

mysql> SELECT content INTO @newContent FROM foo WHERE id=2;

mysql> UPDATE foo SET content=@newContent WHERE id=1;

mysql> SELECT * FROM foo;
+----+-------------+
| id | content     |
+----+-------------+
|  1 | another_bar |
|  2 | another_bar |
+----+-------------+
2 rows in set

And now I've done it without using session variables:

 UPDATE foo, (SELECT content AS selectContent FROM foo WHERE id=2)selectFoo
  SET content=selectContent
  WHERE id=1;


exporting DDL of all views Oracle How can I go about exporting DDL of all my schema views in single .sql file ?
I've read there is a function DBMS_METADATA.GET_DDL   But I can't figure out how to use it for my case.

I know how to do this for one view (Oracle Entreprise Manager->MySchema->Views->Selecting my view->show object DDL->Save..)

I'm using SQL*Plus

Try this for DBMS_METADATA.GET_DLL:

set long 200000 pages 0 lines 150
spool vw.sql
select
   dbms_metadata.GET_DDL('VIEW',u.view_name,DECODE(u.owner,'SYS','',owner))
from
   all_views u
where
   owner IN ('HR','SCOTT')
order by owner,view_name ;
spool off

You can either include the schemas you are interested in or exclude the schemas you do not want in the "owner IN" clause.


SQL Server pivot filter underlying tables by aliases specified in view I have table structure as follows:

CREATE TABLE [dbo].[Test] (
   [ID] [int] IDENTITY(1, 1) NOT NULL
   , [ProductID] [int] NOT NULL
   , [CountryID] [int] NOT NULL
   , CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TestData] (
   [ID] [int] IDENTITY(1, 1) NOT NULL
   , [TestID] [int] NOT NULL
   , [TimeSeries] [int] NOT NULL
   , [Values] [float] NULL
   , CONSTRAINT [PK_TestData] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 ) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestData]
   WITH CHECK ADD CONSTRAINT [FK_Test_TestData] FOREIGN KEY ([TestID]) REFERENCES [dbo].[Test]([ID])
GO

ALTER TABLE [dbo].[TestData] CHECK CONSTRAINT [FK_Test_TestData]
GO

Some test data:

INSERT INTO dbo.Test (ProductID, CountryID)
VALUES (1, 1), (1, 2), (1, 3), (1, 4)
    , (2, 1);

INSERT INTO dbo.TestData (TestID, TimeSeries, [Values])
VALUES (1, 2013, 0.5), (1, 2014, 1.9)
    , (2, 2013, 0.78), (2, 2014, 9.7)
    , (3, 2012, 0.77), (3, 2013, 5.6), (3, 2014, 1.54)
    , (4, 2011, 0.81), (4, 2014, 6.7);

And I've created Pivoted view out of it:

CREATE VIEW dbo.vw_PivotedTest
AS
SELECT ProductID, CountryID, [2011], [2012], [2013], [2014]
FROM (
    SELECT T.ProductID, T.CountryID, TD.TimeSeries, TD.[Values]
    FROM dbo.Test AS T
    LEFT JOIN dbo.TestData AS TD
        ON TD.TestID = T.ID
    ) AS S
PIVOT (
    AVG([Values])
    FOR TimeSeries IN ([2011], [2012], [2013], [2014])
    ) AS P;

And I'd query it like that:

SELECT ProductID, CountryID, [2011]
FROM dbo.vw_PivotedTest;

Now comes the question: is it possible for SQL Server to pick only rows from dbo.TestData, where TimeSeries value is only 2011 instead of scanning everything?

I gave a look at execution plan and it seems to be scanning whole table and doesn't filter these results out.

Not as it stands, you will have to add an index for it and that will never be able to scan only the 2011 column from the pivoted view - adding index onto that view is not possible and will give an error Msg 10114, Level 16, State 1, Line 17 Cannot create index on view "a.dbo.vw_PivotedTest" because it uses the PIVOT operator. Consider not indexing this view..

Then you need to create an index for the underlying data to get the Pivot operator to run faster. Let's look at if the Optimizer has something to say.

Your sample data is really small so run your query like this.

SELECT ProductID, CountryID, [2011]
 FROM dbo.vw_PivotedTest
 option (OPTIMIZE FOR UNKNOWN);

Which will optimize the query for a larger set, you can also add GO 50 after the insert statement into the dbo.TestData table to run the insert statements 50 times and thereby adding more data and then you can skip the option part.

You will then get the following index suggestion from the optimizer:

CREATE INDEX [<Name of Missing Index, sysname,>]
ON [dbo].[TestData] ([TestID])
INCLUDE ([TimeSeries],[Values]);

Which will help but will scan on the ProductID - which makes sense, you can also create that index like this

CREATE NONCLUSTERED INDEX idx2
ON [dbo].[TestData] ([TestID],[TimeSeries],[Values]);

But you will end up with the same queryplan and more expensive inserts so there is little to gain.


Looking for a way of moving millions of record without impacting the transaction log I need to move around 10 million records out of a table into a new "optimized" table, but I need to keep the database online.  What would be the best method in doing this without impacting the database performance?  Also, turning the Recovery Mode to Simple won't help because its a live OLTP database.

The purpose for the move is to add some new indexes.  The problem is there are a lot of records that are old and bulk out the table to around 80 million rows, so I can't just add a new index because the table is used regularly.

Would a SELECT INTO be the better option, or create the table before hand and then use an INSERT?

I regularly do such refactorings, without shutting the system down, and without stopping modifications against the table being refactored. I am moving data in small batches.

Copied from my blog: Refactoring large live OLTP tables without downtime

Refactoring tables does not have to be such a big deal. We do not have to shut modifications down, migrate all data into new structure, deploy modified modules, and do it all at once, while the system is down.

Doing all migration at once may be risky, and if we make a mistake, there is no easy rollback to the old version. This is why whenever we need to refactor a large table, we are using an alternative, low-risk, no-downtime, incremental approach. The method I am going to describe has been used in practice several times, and we did not have any problems with it.

All our transitions from old table structure to the new one were smooth and incremental. More to the point, we were able to set aside the migration at any time and switch to some other more important task, or leave for the day, or enjoy the weekend, all while the database was fully functional.

Typical refactoring scenario

I am totally making this up, I have never worked on bug tracking systems.

Suppose that we are storing tickets in a table dbo.Tickets, which, among other columns, has a column named AssignedTo. As such, each ticket can be assigned to only one person at any time.

However, the next version of our system will allow to assign a ticket to more than one person. Our plan is to create another table, dbo.TicketAssignments(TicketID, AssignedTo), migrate existing data to it, and change appr. 50 stored procedures affected by this change. To minimize risks, we are going to finish all database changes at least a few days before the new version of our system is released.

This means that the current version of our system is going to run against our refactored database exactly as it did against the old one. BTW, the table dbo.Tickets is quite large, and is heavily used all the time.

Before the migration. Prerequisites.

We shall need some extra disk space, approximately as much as the old table uses up. Besides, we need a server that is not struggling with its current workload, so that it can withstand some additional work for the duration of our migration.

Also we shall need a good test coverage on all modules using the table, including unit tests, stress tests, and performance baselines. Typically in our system we already have solid test coverage.

One more thing: because table structure is going to change, inserts directly into the table are not going to work any more. As a result, all test data should be populated via stored procedures that continue to work against the new table structure.

For example, we might use a stored procedure dbo.SaveTicket that has a parameter @AssingedTo to populate test data for our unit tests.

Creating new tables, changing modifications.

As our first step, we create two new empty tables:

dbo.TicketsV2, which has all the same columns as dbo.Tickets, except it does not have AssignedTo column;
dbo.TicketAssignments(TicketID, AssignedTo)

We also change all the procedures which modify dbo.Tickets, so that they write to both old and new tables.

This biggest risk in this step is introducing concurrency-related problems. We need to stress test our modifications well. If we have any problems, however, we can just run a rollback script, changing all the modifications back to their original version. Such rollback takes just a split second.

In fact, we never actually had any problems at this step, because our stress testing harness is quite solid.

Of course, our modifications get slower, but we have made sure that our hardware can handle it. We had not had actual problems with slow modifications either.

Migrating existing data

At this stage all the reports are running off the old table.

As all new changes get saved into both old and new tables, we are also moving over all existing data to the new structure. We want this migration to be non-intrusive, so we typically just run one WHILE loop, moving over like 1K-10K rows at a time, so that our migration does not hinder OLTP activity and reports.

Modifying the reports.

While the data is migrating, we can take our time changing our stored procedures to read from new tables. Because we have good unit testing coverage, we can refactor procedures with confidence - if we break something, we shall know it right away.

Because not all data has been migrated yet, we do not deploy the modified procedures.

Verifying that migration completed.

To verify that all data migrated correctly, we need to write a SQL query. To my best knowledge, there is no GUI tool that can efficiently compare large tables. Writing a query, however, is not that difficult.

This query is going to use a lot of resources. We need to be careful not to bring the server to its knees while the query is running. There are several ways to accomplish that.

Deploying modified reports.

Once we have complete and correct data in the new tables, we can start deploying new procedures. We do not have to deploy them all at once - we can deploy them five or ten modules at a time, even if some other procedures still access the old table.

There is one more problem our team need to be very careful with - our test server is not exactly identical to our production one. As such, we encounter a risk that our procedure runs fast in test environment, but is slow in production.

This is why we first deploy our changed procedures into a different schema, which (schema) is not exposed to our user. For example, instead of altering procedure dbo.GetTicketsForDay, we create a new procedure Internal.GetTicketsForDay. Only developers have privileges on Internal schema, so users cannot execute it yet. Once we have executed Internal.GetTicketsForDay in production environment and are happy with performance, we can deploy it as dbo.GetTicketsForDay.

Our only risk at this stage is that we can deploy poorly performing procedures. Our rollback strategy is simple - we just roll back to the original stored procedures that read from the old table.

Finalizing the migration.

Once all the reports access the new table, we can change our modifications, so that they no longer write to the old table. The old table can be archived out and dropped, reclaiming the additional storage which we needed for the migration.

Conclusion

As we have seen, we can refactor an OLTP table without downtime and with low risks, even if it is big and data migration takes a lot of time.

One more thing: a common reaction to such Agile war stories is a recommendation to "do it right the first time". It is so common that I would like to address it right now.

Of course, at the time when the previous version of bug tracking system, it was quite obvious that eventually we might need to add the ability to assign a ticket to more than one person. Of course, if we had the table dbo.TicketAssignments from the very beginning, we would not have to go through this complex refactoring. In other words, we should "do it right the first time", should we not?

In general, "doing it right the first time", developing a flexible database structure that we should not have to change later, makes a lot of practical sense, but not under all circumstances.

More specifically, when the previous version of bug tracking system was being designed and developed, there were hundreds of brilliant ideas, lots of great features that might be useful later on. Implementing all these hundreds of brilliant ideas would take years, and a much bigger team. It was just not possible. More to the point, it was not what our customers wanted - they wanted us to take care of their most important problems first, and quickly. They did not want to wait until we could provide more features.

Instead of trying to add as many features as possible and as a result delivering a half baked low quality product, the team concentrated on developing only must-have features, on performance, and on very high quality. As a result, the product had a very limited set of features, but it delivered everything it promised, and it was simple, very fast, and rock solid. As such, it was a success.

It would be quite easy to provide examples of over-engineered projects that tried to "do it right the first time" and miserably failed, but this is beyond the scope of this post...


Variable Assignment and Usage in Oracle Why does this fail in SQL Developer against an Oracle database and what would the an appropriate way of achieving the same?

DECLARE v_pNbr NUMBER := 100150;
begin
     select * from TARGETTABLE where PROCESSNBR = v_pNbr;
end;

  ORA-06550: line 3, column 2: PLS-00428: an INTO clause is expected in
  this SELECT statement

Coming from SQL Server I have gotten used to creating variables in SSMS query editor/page and then running them across multiple selects. Hence it allows one to understand the operations with a change to one instead of many. The examples I have seen seem to hint a historical terminal usage instead of editor which may preclude such a way of working.... Ergo what is accepted way of working with pl/sql as such.

You do not need PL/SQL for that, you can use plain SQL after initializing a bind variable, for example.

variable v_pNBr number;
exec :v_pNBr := 100150;

select * from TARGETTABLE where PROCESSNBR = :v_pNbr;

Or you can simply use:

select * from TARGETTABLE where PROCESSNBR = :v_pNbr;

And SQL Developer will prompt for the value of the variable.

PL/SQL code is executed on the database server and it does not display a resultset on the client. Yes, PL/SQL code can store results in an intermediate memory area on the server, then the client is responsible for getting and displaying that content. Another way is opening a cursor in PL/SQL and returning that to the client. Or you can create a PL/SQL table function.


Insert any type of text in a column How can I insert any type of text into a column of type text?

At the moment I am using this:

$$description$$

But a lot of my descriptions contain $ and other signs, so the insert will fail. Do I need to convert the text to something?

You are not limited to just $$ you can use anything enclosed in $, e.g. $_alex_delimiter_$

insert into foo
   (some_column)
values
  ($_alex_delimiter_$this is a long string that contains a single $ and two $$ and quotes '''''$_alex_delimiter_$);

Just pick something that won't be part of the text.


Move databases on same Mac I have just updated my MacBook to Mountain Lion. Since then, my MAMP installation does not work properly so I decided to use the build httpd/php stuff. For this, I have additionally installed a new mysql server (following this tutorial). Everything runs very well. Now my question is, how to use my old databases in my new server? I tried to create a new database which creates a folder "databasename". Into this folder I moved all the .frm files, corrected rights and owner and started mysql, but unfortunately, phpmyadmin does not recognize the tables... did I something wrong? Alternatively, I would just set the data directory of the new mysql server to my old directory, but I cannot find the my.cnf file :-( Where is it stored?

The best thing to do is to export the databases from the MAMP server and import them to your new database server.

Here's a guide with MySQL dump and restore: http://www.patrickpatoray.com/?Page=30


Oracle linked server shows all tables from all schemas in sql server management studio I have created a linked server to an oracle database ( 12c ) in sql server management studio. I am mapping the local user to a special remote user in the oracle db. Connection is working fine. The strange thing is that i am able to see all tables from all schemas.

I tried the following query

SELECT *
   FROM OPENQUERY(ORCL_LINK, 'SELECT  table_name, owner FROM  all_tables ORDER BY  owner, table_name')

As a result i see all schemas and all tables. When i run this query in my oracle database i onyl see tab

I thought that the linked server connection is running in the security context of the remote user but it seems it is not the case. Am i wrong?

Update 1

Oracle SQL Developer only shows me tables where i am the owner but when i do this query

SELECT * FROM USER_TAB_PRIVS WHERE privilege = 'SELECT';

i see many other tables. Those tables are visible in the linked server connection.

Just ran this on SQL Server.  As long as you have the correct USER as the credentials to the linked server, it will give you tables that the User Owns, as well as Tables that the user has SELECT access on.

To Prove this, revoke select on one of the tables to that user (On the Oracle side), and it will no longer show in the query you provided.

REVOKE SELECT ON TABLE FROM LINKUSER;

I just did so and got one less table.  As soon as I added the Grant back, the table returned again in the query.


Failing MERGE Statement: How to monitor with XEvents and specifically get parameter values for a prepared statement? My problem

I am running SQL Server 2017 and would like to investigate an error with a MERGE statement like the following

MERGE INTO table
USING
    (SELECT ? AS Search_Col) AS SRC
    ON table.blobTask_id = SRC.Search_Col
WHEN MATCHED THEN
    UPDATE SET
    x= ?,
    y= ?,
    z= ?
WHEN NOT MATCHED THEN
    INSERT (blobTask_id ,x, y, z, )
    VALUES (SRC.Search_Col, ?, ?, ?)

Error is

  Violation of UNIQUE KEY constraint 'IX_U_CCC_GDV_CONTENTCCC__blobTask_id'. Cannot insert duplicate key in object 'table'. The statement has been terminated.

I am aware that the MERGE statement comes with serveral gotchas. However I could outrule the typical issues ( no triggers, no concurrency on data modifications on the table).

Odd thing is that the Error just comes up just every once in a while and executing the statement again works without errors.

My diagnostic approach

I set up an Extended Events Session filtering for just the query_hash of the problematic statement. Jeremiah Peschka has a great blog post which I followed to do this.

Currently my XEvent Session looks like this:

CREATE EVENT SESSION [query hash] ON SERVER
ADD EVENT sqlserver.prepare_sql(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name)
    WHERE ([sqlserver].[query_hash]=(6577967268103212561.))),
ADD EVENT sqlserver.sp_statement_completed(SET collect_statement=(1)
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.sql_text)
    WHERE ([sqlserver].[query_hash]=(6577967268103212561.))),
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(package0.collect_system_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name)
    WHERE ([sqlserver].[query_hash]=(6577967268103212561.)))
ADD TARGET package0.event_file(SET filename=N'X:\DB_Data\query_hash.xel',max_file_size=(5),max_rollover_files=(5),metadatafile=N'X:\DB_Data\query_hash.xem')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=5 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=OFF)
GO

My question

Problem is that with this session I only get recordings like the following:

Somehow only sp_statement_completed Events show up.
In order to debug the problem it is vital to get to the parameters of the SQL.

Specific question: How do I modify the XEvent session to show me the param values?

I was hoping to get that information from the prepare_sql event but it doesn't show up in the session....perhaps due to my filter?

General request for help: If you know about other ways to effectively troubleshoot and alleviate the issue please let me know as well.

Your Help is much appreciated

Thank you in advance

Martin

Your merge statement is not correctly formed and will always insert all the records, so you will need to update it and tell it to insert the records not matched in the destination table using  WHEN NOT MATCHED like this:

MERGE table
USING
    (SELECT ? AS Search_Col) AS SRC
    ON table.blobTask_id = SRC.Search_Col
WHEN MATCHED THEN
    UPDATE SET
    x= ?,
    y= ?,
    z= ?
WHEN NOT MATCHED THEN
    INSERT (blobTask_id ,x, y, z, )
    VALUES (SRC.Search_Col, ?, ?, ?)

To get the exact statement and parameters for a statement that fails you'll need to capture events before the statement is executed, as the statement fails you will not get a completed statement.

 CREATE EVENT SESSION [a] ON SERVER
  ADD EVENT sqlserver.sql_statement_starting(
  ACTION(sqlserver.sql_text))


How to create an index on CASE expression in Postgres I am trying to create an index on CASE expression, as follows

CREATE TABLE test(i INT, j INT);

CREATE UNIQUE INDEX test_index ON test(CASE WHEN i=1 THEN j END);

Getting this error:

ERROR:  syntax error at or near "CASE"
LINE 1: CREATE UNIQUE INDEX test_index ON test(CASE WHEN i=1 THEN j ...
                                               ^

********** Error **********

ERROR: syntax error at or near "CASE"
SQL state: 42601
Character: 40

What am I doing wrong?

Postgres 9.5.2

You need to add extra parentheses around the CASE expression:

CREATE UNIQUE INDEX test_index
  ON test ((CASE WHEN i=1 THEN j END)) ;

As the docs state in CREATE INDEX:

  The key field(s) for the index are specified as column names, or alternatively as expressions written in parentheses.

Consider also using a filtered index, which is equivalent in terms of functionality but would be using less space, as it will be storing the j values only for rows with i = 1 and not the (possibly millions) or the rest NULL values:

CREATE UNIQUE INDEX test_index_2
  ON test (j) WHERE i=1 ;


Why do I get a BIGINT overflow on MySQL only for some queries? I've encountered a weird situation In MySQL5.7.

Of course, the below query is failed as I expected:

mysql> SELECT 18446744073709551615 + 1;
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(18446744073709551615 + 1)'

but the following query worked:

mysql> SELECT 18446744073709551616;
+----------------------+
| 18446744073709551616 |
+----------------------+
| 18446744073709551616 |
+----------------------+

How can it be possible? Is this expected behavior in MySQL or bug?

MySQL, in the absence of explicit data type information, tries to determine the appropriate data types for literal values based on the value limits. Since the scale of 18446744073709551616 exceeds the allowed range for UNSIGNED BIGINT, it must be a DECIMAL then, which has a much larger range. You can see this in action by creating a table from your SELECT:

create table test as  SELECT 18446744073709551615, 18446744073709551616

and describing it:

describe test

which results in

Field                   Type                    Null    Key     Default     Extra
----------------------- ----------------------- ------- ------- ----------- -----------
18446744073709551615    bigint(20) unsigned     NO              0
18446744073709551616    decimal(20,0)           NO              0

dbfiddle link


Access denied for user 'myuser'@10.% to database 'mydb' - Mysql Import SQL Script I have a database that I am trying to recreate at my web host. I used mysqldump to create a sql script. My web hosting company, IPage, has a hosted mysqladmin site where I am supposed to be able to import my script. When I run the script I get this error:

#1044 - Access denied for user 'myuser'@'10.%' to database 'mydatabase'

This user has full access to the database. Do I need to make some edits to my SQL script to get this working? Or is this a problem at my hosting site?

Thanks!

The message itself looks like an authentication issue.

Here is what you should do: If you can connect to the hosting company's mysql database from the mysql client, run this command:

SELECT USER() RequestedUserLogin,CURRENT_USER() AllowedUserLogin;

What will these functions give you ?

USER() reports how you attempted to authenticate in MySQL
CURRENT_USER() reports how you were allowed to authenticate in MySQL

It's the second function you need to be concerned with because it says you have the same user permissons as AllowedUserLogin.

@Abdul's answer reveals a deeper issue: You created the mysqldump which has the command to create the database. When you mysqldump with the --databases option, the create database mydb; is inserted before the use mydb;. You can hide the create database mydb; by doing

mysqldump -u... -p... --no-data --databases mydb > mydbschema.sql
mysqldump -u... -p... --no-create-info      mydb > mydbdata.sql

This will place the database and table creation statements in mydbschema.sql while the bulk INSERTs are placed in mydbdata.sql.

If you cannot run mydbschema.sql then have the hosting company create the database for you. You could also use whatever DB tools they have setup for you to create the database yourself.

You could then load the data using

mysql -umyuser -pmypassword -h(IP-of-DBServer) -Dmydb < mydbdata.sql

I also noticed the user mysql was expecting 'myuser'@'10.%' as the user to authenticate. You might need 'myuser'@'IP-of-DBServer' defined on the database mydb with this:

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'IP-of-DBServer';

Give it a Try !!!


How to minimize full ms-access window in vba? I have some code to minimize like docmd.minimize but it does not work.

Please let me know how to minimize whole window of access in vba?

Almost, but not quite.

The one you're looking for is DoCmd.RunCommand acCmdAppMinimize (minimize the Access window)

Also:

DoCmd.RunCommand acCmdAppMaximize

DoCmd.RunCommand acCmdAppRestore


The effect of a Primary Key on a blocking situation I'm studying about "Transaction and concurrency","Deadlock" and related stuff for some time. I have a question regarding this matter.

The situation is that two records (AccountNumber AW00012597 and AW00012596) are updated by two different sessions :

SESSION 60:

 begin transaction
 update CustomerTest
 set ModifiedDate = getdate()
 where accountnumber = 'AW00012597'

SESSION 63:

 begin transaction
 update CustomerTest
 set ModifiedDate = getdate()
 where accountnumber = 'AW00012596'

The problem is when I put a clustered index on column AccountNumber (Or set this column as PrimaryKey) , Both Transaction complete without any problem but when I drop the index (When the table doesn't have any indexes or primarykeys) session 63 gets blocked !!

I want to know what exactly is the reason behind this? Cause the transactions are updating different rows and they are holding "Exclusive Locks" on different rows (According to what I've read so far) . So what would be the effect of a clustered index?

Thank in advance

Community wiki answer - please edit to improve:

The reason is when you create an index, either specifically or by declaring it as primary key (which internally creates an index to enforce the constraint), it makes both update statements fast, and they finish quickly without blocking.

With no index, each query has to scan all the records to locate what you have specified in the where clause, and thus there is blocking, which I guess would eventually go. Or it's lock escalation: without the index, it has to escalate to a table lock, and that's what blocks. You really need to look at what resource is being held by the block/deadlock to see what is happening.


Not able to login to MySQL server through MySQL workbench I have installed MySQL 5.7 server on Linux machine and am able to login to the database within the server, but not able to connect to it from Workbench.

I have disabled Iptables. Port 3306 is listening.

**root@master:~#** lsof -i TCP:3306
COMMAND  PID  USER   FD   TYPE  DEVICE SIZE/OFF NODE NAME
mysqld  2563 mysql   27u  IPv4 1905031      0t0  TCP localhost:mysql (LISTEN)

**root@master:~#** service ufw status
* ufw.service - Uncomplicated firewall
   Loaded: loaded (/lib/systemd/system/ufw.service; enabled; vendor preset: enabled)
   Active: inactive (dead) since Mon 2017-02-06 11:28:42 UTC; 9min ago
  Process: 2947 ExecStop=/lib/ufw/ufw-init stop (code=exited, status=0/SUCCESS)
  Process: 2814 ExecStart=/lib/ufw/ufw-init start quiet (code=exited, status=0/SUCCESS)
 Main PID: 2814 (code=exited, status=0/SUCCESS)

Feb 06 11:28:01 movimento-hadoop-master systemd[1]: Starting Uncomplicated firewall...
Feb 06 11:28:01 movimento-hadoop-master systemd[1]: Started Uncomplicated firewall.
Feb 06 11:28:42 movimento-hadoop-master systemd[1]: Stopping Uncomplicated firewall...
Feb 06 11:28:42 movimento-hadoop-master ufw-init[2947]: Skip stopping firewall: ufw (not enabled)
Feb 06 11:28:42 movimento-hadoop-master systemd[1]: Stopped Uncomplicated firewall

Could anyone please help me?

If your MySQL Server and Workbench are on different machine then you need a mysql user to connect and you also need to comment the bind address in my.cnf

User creation

GRANT ALL PRIVILEGES ON *.* TO 'someUser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
FLUSH PRIVILEGES;

And then find the following line and comment it out in your my.cnf file, which usually lives on /etc/mysql/my.cnf or /etc/my.cnf on Unix/OSX systems.

Comment bind-address

Change line

 bind-address = 127.0.0.1

to

 #bind-address = 127.0.0.1 (commented)

And restart the MySQL server for the changes to take effect ,Now try this user from workbench


When SELECTing an expression that is an expression based index does the expression get processed? I am asking this mainly for postgres expression based indexes but would be interested to know for other SQL implementations that have expression based indexes. Example:

CREATE INDEX people_names ON people ((first_name || ' ' || last_name));
SELECT (first_name || ' ' || last_name) FROM people;

No, not currently for that example, for a few reasons.

First, the planner doesn't even entertain indexes unless they seem potentially useful to a WHERE or JOIN condition.  (This is to reduce planning time.) Since your example doesn't have either of those, it doesn't even consider the index.  You could fix this by adding where (first_name || ' ' || last_name) is not null to your query.

Second, the planner thinks it needs the inputs to the expression (even though it doesn't), and unless the inputs are present it won't use the index for an index-only scan.  You could fix this by adding the expression inputs to the index ON people ((first_name || ' ' || last_name),first_name, last_name) but that is certainly an unattractive work-around.

Finally, it might not realize that it can avoid recomputing the expression if the query is complex (although that isn't a problem for your example):

  An additional caveat, if the goal is to avoid recalculating f(x), is
  that the planner won't necessarily match uses of f(x) that aren't in
  indexable WHERE clauses to the index column. It will usually get this
  right in simple queries such as shown above, but not in queries that
  involve joins. These deficiencies may be remedied in future versions
  of PostgreSQL.


String replace using concatenated strings from various columns I'd like to remove a substring in a column via update statement. The substring to replace consists of multiple strings from other different columns but in strict order.

The specification says:

  replace(string text, from text, to text)

Example:

field1                     |field2       |field3      |field4
---------------------------+-------------+------------+---------------
(varchar)bla abla 123 ooops|(varchar)abla|(varchar)123|(varchar) ooops

The substring to replace consists of strings in field2, field3, field4 and blanks between field2 and field3, field3 and field4. In the given example: "abla 123 ooops".

So, after performing the update statement field1 should only contain the string "bla".

Note: I want to perform this on all rows and not to a particular one.

Depending on how you would want to deal with possible NULL values, concat_ws() is probably your safest and simplest way to go:

UPDATE tbl
SET filed1 = replace(field1, concat_ws(' ', field2, field3, field4), '')
WHERE filed1 IS DISTINCT FROM replace(field1, concat_ws(' ', field2, field3, field4), '')

concat_ws() ignores NULL values. With plain concatenation (||), one NULL field would make the whole pattern NULL. It was introduced with Postgres 9.1.

The added WHERE clause prevents empty updates. This enhances performance a lot if many rows wouldn't change anyway.


An SQL query to derive data from one to many rows based on another field I have an issue I can't get my head around. This is the table:

Table1

c1      c2      c3
1       A
2       A       DD
3       A       DD
4       B       HH
5       B
6       B

This is the desired outcome:

c1      c2      c3
1       A       DD
2       A       DD
3       A       DD
4       B       HH
5       B       HH
6       B       HH

I'm running PostgreSQL 9.4. Basically I want to use the non-null values in c3 to fill c3 based on the common data the rows have in c2.

Thanks for the help!

As far as I can tell, you want the "first" non-null value for the c3 column for the "group" that is defined through the c2 column.

This can be done with a window function:

select c1,
       c2,
       first_value(c3) over (partition by c2 order by c3 nulls last) as c3
from table_1

This does return the result you showed us.

Edit (after the question was changed)

To update the existing values you can use this:

update t1
  set c3 = tu.c3
from (
  select c1, c2, first_value(c3) over (partition by c2 order by c3 nulls last) as c3
  from t1
) tu
where t1.c1 = tu.c1
  and t1.c3 is null;


Migrating PostgreSQL DB from AWS to Alibaba Cloud I'm migrating PostgreSQL RDS instance from AWS located in Frankfurt to the RDS instance in Alibaba Cloud also located in Frankfurt.
I use Data Transmission tool from Alibaba Cloud. It connects both DB fine, there are no errors on the connections.
The only problem is with versioning:

I've tried downgrading the source DB, but it seems to be impossible. Does anybody have ideas how it could be done?

You can downgrade postgresql database from the command line.

Simply extract the database with a

pg_dump --host=... --port=... --dbname=.... --user=.... >dump

command. Then you will have your database in the file dump in your local directory.

Then insert the dump into your new database with the command

psql --host=... --port=... --dbname=.... --user=.... <dump

It won't work at first. Typically, you will have syntax errors or similar. But get a good text editor, and edit it all out, until it won't be clean.


Create a function column that is the result of OR between 3 columns I have a table with 3 columns (A,B,C).

In each row, only one of the columns is not null.

How do I create a function column or a computed column that will consist from all the non null values?

Something like: column(A or B or C)

The syntax for virtual columns is very simple:

ALTER TABLE tableX
  ADD abc AS (COALESCE(a, b, c)) ;

Tested at rextester.com.


Using wildcards in a like statement on an unindexed VARCHAR(MAX) column with more than 1 million records In order to troubleshoot a problem, I have a one-time question as to whether a specific varchar(max) field contains non-printing ASCII characters (other than white-space). The following is my straightforward idea about how to determine if there are such characters stored in our production database.

SELECT TOP 10 [CaseNoteId]
      ,[CaseId]
      ,[CaseNote]
  FROM [DB].[XY].[ReferralCaseNotes]
  WHERE CaseNote LIKE ('%[' + CHAR(1) + '-' + CHAR(8) + CHAR(11) + CHAR(12) + CHAR(14) + '-' + CHAR(31) + CHAR(127) + ']%')

My hesitancy to actually run this stems from using wildcards in the LIKE pattern, that there are over a million records in the table in question, the lack of a full-text index on this column, and that this will likely be an exhaustive search because we do not believe that any such characters exist.

I am a neophyte. How can I estimate whether running this query will be a significant load on our production system? Also, is there a better way to get at the same information?

Possible Improvements:

I'm not worried about data changing while my query runs. Can I change this query to look at a few rows at a time in a way that is beneficial?
Can I set this query to somehow be a background operation that doesn't get in the way of any other queries?
Can I run it for a limited time and determine what percentage of the table was searched, so that I can estimate the time required for a full search?
Would WITH(READPAST) improve my performance?

Why?

The database in question involves sensitive data, the government, and security folks making rules. Restoring a backup to a different server makes a ton of sense, but would cost the taxpayer several orders of magnitude more than makes any sense.

If the answer is, "Don't worry, you're just doing a SELECT," then I say, "Great!"

If snapshot isolation is enabled you will not have any blocking issues. If not, you should probably run the query under READ COMMITTED or even READ UNCOMMITTED. It is a common myth that a READ COMMITTED scan locks the table.
You can use Resource Governor for this. Or use a MAXDOP 1 hint. Controlling load of bulk operations is very hard with SQL Server. Depending on the situation you might be 100% fine leaving this running all day, or you might induce timeouts in other parts of the workload. It is not unreasonable to run the query for 10s and cancel it. Then determine whether the application workload was impacted or not.
I like to do progress estimation by dividing the table size (in MB) by the observed disk read rate (in MB/sec). This gives an estimation for the total scan time.

Fulltext search cannot help you because it works on a per-word basis. You'd need to plug in a custom stemmer that knows how to split special characters. Unrealistic. Your query is fine.


Merge two row records I have 2 tables

Person Table

PersonID   Firstname LastName
1              a         b
2              c         d
3              e         f
4              g         h
5              i         j

Incident Table

InicdentID PersonID
1             1
1             2
2             3
2             4
3             5

I want to STUFF Name with semicolumn for one Incident

IncidentID Name
1            ab;cd
2            ef;gh
3            ij

Try this

create table #person (
PersonID int,
FirstName varchar(5),
LastName varchar(5)
)

create table #incident (
IncidentID int,
PersonID int
)

insert into #person values (1,'a','b')
insert into #person values (2,'c','d')
insert into #person values (3,'e','f')
insert into #person values (4,'g','h')
insert into #person values (5,'i','j')

insert into #incident values (1,1)
insert into #incident values (1,2)
insert into #incident values (2,3)
insert into #incident values (2,4)
insert into #incident values (3,5)

 select t.IncidentID,
   stuff(
    (select ';' + p.firstname + p.lastname
      from #incident i
      join #person p
        on i.personid = p.personid
      where i.incidentid = t.incidentid
     for xml path('')),
     1,
     1,
     ''
   )
 from #incident t
group by t.IncidentID

Documentation on stuff() command


Adding sql users via linked servers I need to add a windows user and set role to sysadmin for a bunch of linked servers and cannot figure out the syntax to make the current linked server active for the query to add the user and role.

Any suggestions?  I have only seen one example of this on the web but it does not work for SQL 2008.

If you mean what I think you mean, no, you can't say something like USE [LinkedServer]; in an SSMS query window. There are alternatives:

You can change the current connection to a different server by right-clicking the query window, Connection, Change Connection. That will allow you to change connections to instances you can see, which may not be the same as the set of the linked servers the target can see, and it doesn't provide a way to do so programmatically.
Same issue with right-clicking a group of Registered Servers (Ctrl + Alt + G) and choosing New Query - those are registered servers on your client workstation, not linked servers set up in the target instance.
You can use SQLCMD mode and use the :CONNECT directive, but that presents the same issues as above.

I don't think either of those is what you're after. You can build the set of commands you would need to make the target instance execute a command against multiple linked servers this way:

DECLARE @sql nvarchar(MAX) = N'';

SELECT @sql += N'
  EXEC ' + QUOTENAME(name)
    + N'.master.sys.sp_executesql
      N''CREATE LOGIN [domain\user] FROM WINDOWS;
         ALTER ROLE [rolename] ADD MEMBER [domain\user];
         -- ...'';'
  FROM sys.servers
  WHERE is_linked = 1;

PRINT @sql;

You can use additional filters to remove certain linked servers using other criteria, or otherwise modify the commands, then copy and past them to execute. You can also just say the following if you trust it:

EXEC sys.sp_executesql @sql;

Since the PRINT command will only support 8k, which means a limit on the number of servers (actual number will depend on the length of the command and the length of the names of the linked servers).


pgpool replication not allowing md5 authentication I have installed both pgpool and postgresql via apt.

In pgpool.conf, I have made the changes necessary to use replication mode, as well as my two postgresql databases (currently all running on the same host).

I am unable to figure out what I need to add to my pg_hba.conf files in order to have pgpool connect to them. I keep getting this error

createdb: could not connect to database postgres: ERROR:  MD5 authentication
is unsupported in replication, master-slave and parallel modes.
HINT:  check pg_hba.conf

both of my pg_hba.conf files look like

local   all             postgres                                peer

local   all             all                                     peer
local   all             postgres                                md5

host    all             all             127.0.0.1/32            md5

host    all             all             ::1/128                 md5

local   replication     postgres                                peer
host    replication     postgres        127.0.0.1/32            md5

and my pool_hba.conf looks like

local   all         all                               trust
local   all         postgres                          md5
local   all         all                               md5

host    all         all         127.0.0.1/32          trust

What am I missing in order to have pgpool connect to my databases?

It looks like your default authentication mode for TCP/IP connections (host) is md5. So at a guess, you have PgPool connecting over TCP/IP.

Change the auth mode for loopback connections on tcp/ip, or use unix sockets with pgpool (usually by omitting the host setting).

If you're still stuck after reading:

19.1. The pg_hba.conf File and
Chapter 19. Client Authentication

in the PostgreSQL 9.4 documentation, and you can't find answers elsewhere (there are lots of related questions on Stack Overflow), let me know and I'll give you a step by step if I have a chance.


You have would have to join twice:
SELECT
  fam.family_id
 ,fam.parent_id_1
 ,parent1.address_id AS parent_1_address_id
 ,parent1.name AS parent_1_name
 ,parent1.job_id AS parent_1_job_id
 ,fam.parent_id_2
 ,parent2.address_id AS parent_2_address_id
 ,parent2.name AS parent_2_name
 ,parent2.job_id AS parent_2_job_id
FROM
  family fam
LEFT JOIN
  person parent1
    ON parent1.person_id = fam.parent_id_1
LEFT JOIN
  person parent2
    ON parent2.person_id = fam.parent_id_2
LEFT JOIN
  job job1
    ON parent1.job_id = job1.job_id
<... over and over ... >

You can use a common table expression to wrap some of the identical code together, but it's the same steps ultimately:
WITH person_detail AS
(
  SELECT
    person.person_id
   ,person.name
   ,<job columns>
   ,<address columns>
  FROM
    person person
  LEFT JOIN
    job job
      ON job.job_id = person.job_id
  LEFT JOIN
    address address
     ON address.address_id = person.address_id
)
SELECT
  fam.family_id
 ,fam.parent_id_1
 ,<parent_1 columns>
 ,fam.parent_id_2
 ,<parent_2 columns>
FROM
  family fam
LEFT JOIN
  person_detail parent1
    ON parent1.person_id = fam.parent_id_1
LEFT JOIN
  person_detail parent2
    ON parent2.person_id = fam.parent_id_2


Sql Server Index Data File Drive Location As part of my ongoing saga of migrating from one SAN to another, the new SAN vendor said that I need to have my index data file in the same drive as my primary data file. Otherwise, given the tools that come with the SAN, I would need to create a new LUN just for this database and create it as its own volume.

At any rate, is this an acceptable practice ... to put index filegroups (.ndf) on the same drive as the data file?

Thanks

It sounds like the vendor's snapshotting functionality is putting some pretty serious limitations on your flexibility for database layouts.  If you have performance issues (and it would be a very long way from the first time someone had database performance issues on a SAN) then you have very little flexibility to spread the database across multiple physical volumes.

Does the vendor insist you also put the log files on the same volume as well?  If so, then your log files will be on a busy shared volume with no write buffering from the SAN's cache due to Microsoft's policy I/O reliability certification programme for SQL Server.  MS requires SAN vendors to honour FILE_FLAG_WRITETHROUGH, which SQL Server opens its files with.  If your SAN vendor is going to force ALL files for a database to live on the SAME volume you could have performance issues.

I'd look into alternatives to the snapshot manager for DR before signing up to that.  What did you do on your old system?

EDIT Per the chat - if you can use mount points to organise your database files then one set of LUNs per database is probably better than forcing all the databases into the same format.


MySQL SELECT and SORT performance - v. large table I have a table that contains in the region of 4 million rows. This is searched using a form, and then the results are returned. However, the query seems to take an inexplicably large amount of time, and I'm hoping that there's a more efficient query to run.

SELECT *
FROM Example
WHERE STR_TO_DATE(IncorporationDate,'%d/%m/%Y') > '" . $from . "'
  AND STR_TO_DATE(IncorporationDate,'%d/%m/%Y') < '" . $to . "'
  AND PostCode REGEXP  '" . $postcode . "'
  AND `Returns.LastMadeUpDate`=''
ORDER BY PostCode ASC

As mentioned in comments (it is really answer for original question) - using function in WHERE - prevent any indexes using.

First of all, You can check - how many of Your queries use form

SELECT * FROM

this is really bad practice with applications, exactly because - if You add new column, some of Your code could be broken. For example:

INSERT into table2 SELECT * FROM table1

If You can fix this (or You not have problems code), You can:

convert IncorporationDate to Date format and use this column
or easy add 1 more column with proper Date type and use it in queries

But first - You must be sure, all other code will not be broken by new column.

Then You can use one of the tools from answer for Your other question:

Talend - http://www.talend.com
StreamSets - http://www.streamsets.com
Apache NiFi - https://nifi.apache.org,
https://hortonworks.com/apache/nifi/

now much more similar tools present on market, many of them free in parts cover 99% of life cases.
And ETL it more often solution rather than work with build functions only.

Clean
Transform
Load

For answer about next potential question - what with speed?
we load tables from 4M to 40M between instances - MySQL, MS SQL, Redshift
For "small" we use direct insert from ETL tools, for Big - prepare clean file for load, than direct load (Redshift from S3)

Just add live example - "how it could work"

One of Our scripts - run series of queries with search target records by substring from 2 columns + other parameters.
Changing the structure also not possible by the same reasons - a lot of place where need to make changes and no free developers resources. 2 table 3M rows per month.

Original time - 7.5 Hours
Modified process - prepare with Talend tables for past month with all optimised structure.
modification only this script - for exclude substrings from SELECT query, do not touch any other in system. SELECT from prepared tables, write result to same as in original.

Prepare - 5m, script execution - 12m, total time of whole process - 17minutes.


Giving all permissions to all users for a database in SQL Server Since the database is created under one user's log in, other users using this computer cannot access this particular database. How can I grant the same privileges to all users who log in to this computer for this database? Step by step instructions through SSMS or by a script will be appreciated greatly.

You want local Windows users to have the same permissions as the database owner of a specific database? How about:

Manually create logins for each Windows account:

-- repeat this whole block for each user account

USE [master];
GO
CREATE LOGIN [Domain\Account] FROM WINDOWS;
GO

  -- repeat just this portion for each database that account should belong to

  USE [your_database];
  GO
  CREATE USER [Domain\Account] FROM LOGIN [Domain\Account];
  GO

Add the users to the dbo role of each database to which you've added a user account above, using dynamic SQL (uncomment the EXEC line when you think it looks right):

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

SELECT @sql = @sql + N'
  EXEC sp_addrolemember N''db_owner'', N''' + name + ''';'
FROM sys.database_principals
WHERE principal_id BETWEEN 5 AND 16383
AND [type] IN ('U', 'S');

PRINT @sql;
--EXEC sp_executesql @sql;