Splitting to separate fields of a result set is a bit tricky if you have a varying number of elements per row. There is a SQLCLR stored procedure in the SQL# library (that I wrote) that, while not in the Free version, does break a delimited string of varying elements into result set fields based on a Regular Expression delimiter. The delimiter being RegEx makes it quite easy to treat "one or more spaces" as the delimiter: \s+. For example:

EXEC SQL#.String_SplitResultIntoFields N'
SELECT [TextRow] FROM #TestWrite;',
N'\s+', NULL, NULL;
/*
Field1    Field2     Field3     Field4    Field5
I         am         writing    SQL       Code.
SQL       keywords   include    join,     except,
*/

As you can see, it determines the number of fields for the result set based on the first row, which is why there is no "Field6" to contain the final word in the second row.

Of course, you could always seed the first row with dashes separated by spaces to force a certain number of fields, but there is no way to then filter out that initial row:

EXEC SQL#.String_SplitResultIntoFields N'
SELECT N''- - - - - - - -''
UNION ALL
SELECT [TextRow] FROM #TestWrite;',
N'\s+', NULL, NULL;
/*
Field1    Field2     Field3     Field4    Field5    Field6   Field7    Field8
-         -          -          -         -         -        -         -
I         am         writing    SQL       Code.
SQL       keywords   include    join,     except,   where.
*/

I suppose I can add an optional input parameter for @ForceResultSetFieldCount easily enough, but it's not there as of today.

If this request was only what is initially stated in the question (i.e. "I am looking for a query to find nth value in a list"), then that is not only trivial (even with the "one or more spaces" delimiter requirement), but that requires a regular expression function, RegEx_CaptureGroupCapture, and that is in the Free version of SQL#. For example:

SETUP

CREATE TABLE #TestWrite
(
  TestWriteId INT PRIMARY KEY IDENTITY(1, 1),
  TextRow VARCHAR(255)
);

INSERT INTO #TestWrite (TextRow)
  SELECT 'I am     writing SQL                   Code.'
  UNION ALL
  SELECT 'SQL  keywords include   join,    except, where.';

TESTS

As you can see below, you can either use a pattern of "one or more word characters", which will exclude both whitespace and punctuation (first example), or you can use a pattern of "one or more non-whitespace", which will include punctuation, etc (second example).

-- only get "word" characters:
SELECT SQL#.RegEx_CaptureGroupCapture(t.[TextRow], N'(\w+)', 1, 4, 1, NULL, 1, -1, NULL)
FROM   #TestWrite t;
/*
SQL
join
*/

-- get non-whitespace:
SELECT SQL#.RegEx_CaptureGroupCapture(t.[TextRow], N'([^\s]+)', 1, 4, 1, NULL, 1, -1, NULL)
FROM   #TestWrite t;
/*
SQL
join,
*/


Is the filestream in SQL Server backed up when using standard backup techniqe

Backups include both filestream data and relational data. Integrated management is one of the benefits of using filestream/filetable instead of storing files independently.

As suggested by the comments, practice recovery to ensure you can meet your RPO/RTO SLA objectives.


MySQL: Index when joining to tables not being used (Performance optimizing question)

Your where condition blogposts.title LIKE '%{de}%' will cause a full table scan on the blogposts table. It's likely MySQL figures scanning 6915 articles is more efficient.

As to how to improve it, you might add an index on blogposts using the date_created or date_published and add a range the to the where condition (something other than <=NOW())


If you installed a SQL server named instance, by defaut, it have to be contacted on a random port not ont the port 1433.
Please use 127.0.0.1\MSSQLSERVER01 or change the tcp/ip configuration and set a fixed port.
Verify that sql server agent is running. This is how a named instance is translated to the right dynamic or static port.


Is your database in full recovery mode?

If yes, are you doing transaction log backups?

If yes, do you have a backup utility like Quest LiteSpeed, Red Gate SQL Backup, or Idera SQLSafe?

If yes, those utilities can undelete objects from backup files (including the fulls and transaction logs) - but explaining how to use those is beyond the scope of what I can do here.  Contact the vendor for instructions.
If no, restore the full backup and transaction logs as a database with a different name.  (Don't overwrite the existing database.)  You'll be able to get an up-to-the-second copy of the object before the delete happened, but you'll need to use the stopat part of the restore command to specify when to stop restoring the commands.  You need to stop before the delete happened.

If no, go grab a copy of a log reader utility like Quest LiteSpeed or Apex SQL Log.  These utilities can connect to the database server, examine the log file, and help you undo transactions.  I'm not sure if the demo versions will work, but the paid ones definitely will.

If no (not in full recovery mode), restore the last full backup as a database with a different name.  (Don't overwrite the database you've already got.)  From there, you'll be able to restore whatever records were online at the time of the backup, but you'll lose all of the changes since.


Difference between fsync and synchronous_commit - postgresql

When fsync is OFF, the PostgreSQL server will never issue any fsync system call, leaving entirely to the operating system the decision as to which blocks to transfer from write cache to disk, and when. The database engine never knows what has been sync'ed or not. In case of a server crash, it has to be assumed that the database is in an inconsistent state.

When synchronous_commit is OFF, the server issues fsync call, but not necessarily immediately at commit time for each transaction. It may delay them after the commit, for a maximum of wal_writer_delay multiplied by 3. With a default configuration that would be 600ms. This allows to reduce the number of fsync calls, especially for workloads with lots of small transactions.
In case of a server crash, the transactions not yet sync'ed in the last 600ms before the crash may be rolled back, but the state of the database is always consistent.


How to get difference in value over 15min interval?

Just group your @LatestFifteenMinTickets table by DomainId and take the difference between MIN(SoldTickets) and MAX(SoldTickets). That will give you the number of sold tickets for each domain within the period collected in your table variable:

SELECT
  DomainId,
  SoldToday         = MAX(SoldTickets),
  SoldSince15MinAgo = MAX(SoldTickets) - MIN(SoldTickets)
FROM
  @LatestFifteenMinTickets
GROUP BY
  DomainId
;

Unless SoldTickets can decrease as well as increase (can tickets be returned?), in which case, rather than the minimum and maximum, you should probably take the first and last value. But in order to be able to do that, you should have the Date attribute in your table variable as well. Then the calculation could be organised using the analytic functions FIRST_VALUE and LAST_VALUE:

SELECT DISTINCT
  DomainId,
  SoldToday         = LAST_VALUE(SoldTickets) OVER (PARTITION BY DomainId ORDER BY [Date] ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING),
  SoldSince15MinAgo = LAST_VALUE(SoldTickets) OVER (PARTITION BY DomainId ORDER BY [Date] ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)
                   - FIRST_VALUE(SoldTickets) OVER (PARTITION BY DomainId ORDER BY [Date] ASC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM
  @LatestFifteenMinTickets
;


How to change MSSQL Management Studio theme?

Install the 2010 Color Theme Editor in VS 2010.
Find the files somehwere in C:\Users\xxxxx\AppData\Local\Microsoft\VisualStudio\xx.x\Extensions\ .. for me, it was in \shvn3avp.com for the current version of the plugin.
Copy those files to a new folder: C:\Program Files (x86)\Microsoft SQL Server\xxx\Tools\Binn\ManagementStudio\Extensions\VSTheme
Replace extension.vsixmanifest contents with:

<?xml version="1.0" encoding="utf-8"?>
<Vsix xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0.0" xmlns="http://schemas.microsoft.com/developer/vsx-schema/2010">
<Identifier Id="46b3817c-b0c3-42d1-8cea-d373f781b21e">
  <Name>Visual Studio Color Theme Editor</Name>
  <Author>Microsoft</Author>
  <Version>1.3</Version>
  <Description xml:space="preserve">Allows users to customize the color palette used for menus, toolbars, tabs, title bars, and other environment colors.</Description>
  <Locale>1033</Locale>
  <Icon>ThemeManagerIcon.png</Icon>
  <PreviewImage>ThemeManagerPreview.png</PreviewImage>
  <InstalledByMsi>true</InstalledByMsi>
  <SupportedProducts>
    <IsolatedShell Version="1.0">ssms</IsolatedShell>
    <VisualStudio Version="10.0">
      <Edition>Ultimate</Edition>
      <Edition>Premium</Edition>
      <Edition>Pro</Edition>
      <Edition>IntegratedShell</Edition>
    </VisualStudio>
  </SupportedProducts>
  <SupportedFrameworkRuntimeEdition MinVersion="3.0" MaxVersion="6.0" />
  <SystemComponent>true</SystemComponent>
</Identifier>
<References />
<Content>
  <VsPackage>ThemeManagerPackage.pkgdef</VsPackage>
</Content>

Import this setting:
http://studiostyl.es/schemes/vs2012-dark-theme-for-vs2010
On the Customize Color dialog that opens on the Theme menu provided by the extension import his theme

Result:

For 2015 and SQL Management Studio 17RC1

Install the extension on VS2015 instead. Follow the above steps.

The folder on my computer was C:\Users\xxxxxx\AppData\Local\Microsoft\VisualStudio\14.0\Extensions\owbugenu.gif

The xml is:

<!-- language: lang-xml -->
<?xml version="1.0" encoding="utf-8"?>
<Vsix xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.0.0" xmlns="http://schemas.microsoft.com/developer/vsx-schema/2010">
  <Identifier Id="B8CED2B3-FF48-4204-88E9-036CA39B83C8">
    <Name>Visual Studio 2015 Color Theme Editor</Name>
    <Author>Microsoft</Author>
    <Version>2.0</Version>
    <Description xml:space="preserve">Allows users to create and edit Visual Studio themes.  Themes can customize colors used for menus, toolbars, tabs, titlebars, the text editor, and other environment colors.</Description>
    <Locale>1033</Locale>
    <InstalledByMsi>true</InstalledByMsi>
    <SupportedProducts>
      <IsolatedShell Version="1.0">ssms</IsolatedShell>
    </SupportedProducts>
    <SupportedFrameworkRuntimeEdition MinVersion="4.0" MaxVersion="4.0" />
    <SystemComponent>true</SystemComponent>
  </Identifier>
  <References />
  <Content>
    <VsPackage>ColorThemeEditor.pkgdef</VsPackage>
    <VsPackage>Colors\Red.pkgdef</VsPackage>
    <VsPackage>Colors\Purple.pkgdef</VsPackage>
    <VsPackage>Colors\Green.pkgdef</VsPackage>
    <VsPackage>Colors\Tan.pkgdef</VsPackage>
    <VsPackage>Colors\DarkWithLightEditor.pkgdef</VsPackage>
    <VsPackage>Colors\LightWithDarkEditor.pkgdef</VsPackage>
    <VsPackage>Colors\SolarizedDark.pkgdef</VsPackage>
    <VsPackage>Colors\SolarizedLight.pkgdef</VsPackage>
    <MefComponent>ColorThemeEditor.dll</MefComponent>
    <VsPackage>Colors\ThemeNames.pkgdef</VsPackage>
    <VsPackage>PkgDefAssemblyCodeBase.pkgdef</VsPackage>
  </Content>
</Vsix>


Why would I get PREEMPTIVE_OS_AUTHORIZATIONOPS using tSQLt when Working at Home?

If you aren't going to be connected to the domain all the time, as it is a development instance, change the db owner from a domain account to a local account or a sql account.

Check this great blog out on the subject:

http://andreas-wolter.com/en/where-is-that-preemptive-wait-coming-from/

To summarise the blog post:

Having a db owner as a domain user causes kerberos ticket granting ticket requests (i.e. sql wants to authenticate using the domain account that is a db owner and kerberos has a thing called ticket granting ticket which lets sql impersonate the db owner)
If you have high latency (or issues connecting, as in this case) to your domain controller, these calls will be slow
SQL caches the calls for 10 minutes so these can appear intermittent

ed


Junction table Trigger after update in another table

Your problem is , that you made  some small errors
in the for loop you need to select those columns that fit the rowtype
I added as last a Version that is better as it only update one row and not all where b.id and a.id are present

CREATE Table a
("a_id" INT, "a_is_active" Boolean)

CREATE Table b
("b_id" INT, "b_is_active" Boolean)

CREATE Table ab
("ab_id" INT , "a_id" int, "b_id" int, "ab_is_active" Boolean)

INSERT INTO a VALUES (1,TRUE)

INSERT INTO B VALUES (1,TRUE)

INSERT INTO ab VALUES (1,1,1,FALSE)

DO
$BODY$
DECLARE
    b_is_active boolean;
    r            ab%ROWTYPE;
BEGIN
    for r in select ab.ab_id, ab.b_id,ab.a_id,_a.a_is_active AND _b.b_is_active AS ab_is_active
             from ab
                      join b _b on ab.b_id= _b.b_id
                      join a _a on ab.a_id= _a.a_id
        LOOP
                update ab set ab_is_active = r.ab_is_active
                where b_id = r.b_id AND a_id = r.a_id;
        END LOOP;
END;
$BODY$;

✓

SELECT * FROM ab

ab_id | a_id | b_id | ab_is_active
----: | ---: | ---: | :-----------
    1 |    1 |    1 | t

DO
$BODY$
DECLARE
    b_is_active boolean;
    r            ab%ROWTYPE;
BEGIN
    for r in select ab.ab_id, ab.b_id,ab.a_id,_a.a_is_active AND _b.b_is_active AS ab_is_active
             from ab
                      join b _b on ab.b_id= _b.b_id
                      join a _a on ab.a_id= _a.a_id
        LOOP
                update ab set ab_is_active = r.ab_is_active
                where ab_id = r.ab_id;
        END LOOP;
END;
$BODY$;

✓

db<>fiddle here


Queries of the second type fall under what some call a SQL antipattern (check out the nice book written by Bill Karwin). The second query almost resembles a Cartesian JOIN whose WHERE clause has to be evaluated on the fly.

The first is cleaner and the order of execution can be better managed.

You could compare both ways by

getting the EXPLAIN plan for each and seeing if execution time is the same
creating a third type that refactors the query and get its EXPLAIN plan and running time
see if all indexes are being evaulated the same for the three query styles

You are better off going for the JOIN syntax because you can generate result sets with LEFT JOINs and RIGHT JOINs that may be far different from (maybe more desirable than) INNER JOINs.


Write yourself a view that produces, in a single column, the value of price that you're interested in.  It might be a UNION of a few different criteria.  When you're done you'll have a view of all items with two columns: the id and the price.

Join that view to whatever else you need (perhaps including the original price), and voila.

Tip: skip IF and just use AND and OR, per the SQL standard.  And eschew LEFT JOIN unless you know -- and know why -- you can't use a proper inner join.  If the database is pervasively unreliable in that regard, it will lead to more pain than all the outer joins in the world can alleviate.


Maybe if the MySQL version supports, you can use an AFTER INSERT TRIGGER
Here is the code that will help for INSERT statements
    delimiter $$
CREATE TRIGGER mytrigger
    AFTER INSERT
ON tblstationerytranscation
    FOR EACH ROW
    BEGIN
    declare quan integer;
    set quan = new.quantity;

    if new.trsntype = 'issued' then
        set quan = -1 * quan;
    END IF;

        update tblstationerystock
        SET balance = balance + quan
        where stationery_code =  new.stationery_code;

end $$


How to create a citation/quote from a Postgres text field?

what you need is the ts_headline function. it does exactly what you need it seems. here is the documentation: http://www.postgresql.org/docs/9.4/static/textsearch-controls.html


What known strategies, if any, can I use to validate a Slowly Changing Dimension?

Checking whether the dates are contiguous
You don't say which DBMS you're using here, but you're using SSAS so I'm guessing SQL Server. If you're on a recent enough version, using LAG and LEAD in window functions can be really handy for this kind of task. You can order the rows by the start date or by an incremental ID if you have one, and then use these to check on values in the next or previous row.
So for instance, you could set up a window function which is partitioned by your business key/surrogate durable key (I'll just use the term durable key from here on out), and ordered by your start date (ascending). You could then use LEAD to check that the start date on the next row was equal to the end date + 1 on the current row. You can't use window functions in the WHERE clause, so perhaps you could create a case statement which flags up those rows which are for the same customer (i.e. have the same durable key), but where the next row's StartDate isn't equal this row's EndDate + 1. You could do the same in reverse to flag up the following row as well. An outer query could then only select the rows which need investigation.
Checking the SCD is working correctly
To some extent the checks you can do are going to depend on your particular situation, but I'll try to tackle this from a generic standpoint that might be helpful to you (if you still need help, 7 months later!), and which might help other people having similar issues. If you return and give more specific detail, let me know and I'll be happy to have a read through and improve my answer if possible.
One option might be to run some queries which select a few COUNT values on different aspects of the dimension. So for instance, you could group by the durable key, and get a count of how many rows exist for that durable key. If you don't have an understanding of how many changes are reasonable, you could take an average count and then look for customers which are over that average (or over that average by some percentage or set amount).
From there, having a dig through the data belonging to a handful of suspicious-looking customers might turn up obvious issues which are worth further investigation. Without specifics I don't know what issues you might be running into, but as an example lets say you notice a customer which has hundreds of rows, with a value which is changing back and forth between two values. From there, you could write up some queries to look for that problem in the rest of the table - for instance, coming up with a query which would let you look at the number of rows for each customer vs. the number of unique rows for each customer (looking only at the actual attribute columns, so not the dates or any other metadata columns you might have).
Checking on whether the customer is changing correctly over time will depend on what historic data you have held in your source(s) or staging area. If you do have historic data in one or both of those places, you might be able to compare the values with your dimension rows either directly by date (if said historic data includes dates), or perhaps by the order those changes happened in.

On a slight tangent, but worth mentioning: I'm guessing from the fact that you're not sure whether this dimension is behaving correctly that this is something you've perhaps inherited from another developer? If you're really worried about the size of this dimension and if you don't know the full reason for its current design, it might be worth checking whether all of the Type II SCD fields really should be Type II SCD.
Some data warehouse developers will tend to presume SCD II by default, which can really be problematic. Aside from the space issues, as the link goes into, the use of SCD II can lead to some unexpected results for the people consuming the data. This might not be relevant in your case (the SCD II attributes might be well-thought-out and suitable for your users' requirements), but if it is relevant it might be possible to alleviate your concern about the size of the dimension and also remove the need to do those data quality checks.


You really need to fix your design. Having 31 columns, one for every day and 6 columns, one for every week is not normalized and is going to cause a lot of pain on the road.

That aside, to solve the specific issue, you need dynamic SQL because the @daily = 'day28' is a string and you want it used as a column name:

-- the sql as a string
SET @sql = concat('select ', @daily, ' from csptargets;') ;

-- prepare a statement from the sql
PREPARE stmnt FROM @sql ;

-- execute it
EXECUTE stmnt ;

or:

-- the sql as a string
SET @sql = concat('select day',
                   date_format(curdate(), '%d'),
                  ' from csptargets;') ;

-- prepare a statement from the sql
PREPARE stmnt FROM @sql ;

-- execute it
EXECUTE stmnt ;


Add a index to dependents tables (for eficient deletes)

If you have

create table "table"(
  id integer primary key,
  added_at date
);

create table other(
  other_id integer primary key,
  table_id integer,
  foreign key (table_id) references "table" (id)
);

and you delete rows from "table" postgres must search the other table for each row that you deletes in the "table". If you add an index in the other table postgres can search in the index.

CREATE INDEX ON other (table_id);

Now you can eficiently delete rows in "table".


MySQL Replication: MASTER_LOG_POS never changes

No, this is correct.
In your MySQLDump you issue a flush-logs command, which creates a new Binary log.
The initial position in the binary log (after headings etc.) is 107, and always will be (unless you upgrade to a different version - then it may be something else).


Ola Scripts installation in "Multi-server" Jobs Folder

The first part is to understand that a Central Management Server and a Master Job Server are not the same. CMS is used to centrally manage instances, the master job server is used to deploy standardised jobs across mutiple servers. These two can be linked but are not directly related.

To have the jobs appear in Multi-Server Jobs, the jobs need to be configured as multi-server jobs. To do this, open the job and navigate to the Targets page. Select "Target Multiple Servers" and then select the target job servers to which you wish to deploy the job.

See this link for more info.

If you're deploying Ola's maintenance jobs on a Master Job Server, there are several considerations you should make before deploying:

All jobs have the same configuration, so your output file path must be accessible or identical on all servers. If it varies, you will get errors on the servers that don't match. NOTE: in later versions (2014+) of SQL, you can use the Agent macro SQLLOGDIR to use a local path for output log files that vary between servers in a master job server.
Backup jobs must be configured to backup to an identical path, either a local path that exists on all servers or a shared path that all servers can access.
Exclusions for databases, tables etc must be applied to all servers, as the jobs cannot be individually modified on servers.
Schedules are identical across servers, so if you're backing up to a shared location, you may encounter performance issues due to increased write activity at the same time.


Joining two Mysql VIEWs takes a very long time

Using FULLTEXT indexes has to be handled with great care. Why ? While FULLTEXT index searches do work, the MySQL Query optimizer tends to suggest full table scans if you do not express the query properly.

Let's take your query and look for 'tom'

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE c.castName LIKE '%tom%';

This joins everything. Then, it scans the resulting temp table and returns every row with tom.

Let's implement the FULLTEXT method

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE);

I can almost assure you that a full table scan will occur during the join because I have experienced it hundreds of times:

Oct 25, 2011 : FULLTEXT index ignored in BOOLEAN MODE with 'number of words' conditional
Jan 26, 2012 : Mysql fulltext search my.cnf optimization
May 07, 2012 : MySQL EXPLAIN doesn't show 'use index' for FULLTEXT
Jul 18, 2012 : Why full-text-search returns less rows than LIKE

I started writing posts about FULLTEXT and query refactoring ever since I saw a post from  webmasterworld.com entitled MySQL Match Against + a very expensive join. How do I optimize this?

Given the information I just mentioned let's take your query and refactor it to handle the FULLTEXT and stilldo a good join.

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink
FROM cast_movie as c JOIN film_info as f ON c.ImdbId = f.ImdbId
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE);

First, apply the MATCH ... AGAINST operator to cast_movie and return ImdbId and other fields

SELECT ImdbId,movieName,castName,movieImdbId FROM cast_movie
WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE)

Make that a subquery to provide keys to join and retrieve data from film_info

SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink FROM
(
    SELECT ImdbId,movieName,castName,movieImdbId FROM cast_movie
    WHERE MATCH(c.castName) AGAINST ('+tom' IN BOOLEAN MODE)
) as c JOIN film_info as f ON c.ImdbId = f.ImdbId;

This should produce a better result.

If your tables use InnoDB Storage Engine, remember to tune the InnoDB Fulltext options. Any fulltext option that does not start with innodb_ is meant for MyISAM (See my post MySQL FullText search on string shorter than 3 chars returns no rows)

Give it a Try !!!


How to optimize a query that includes counts of rows in multiple tables?

My first instinct is use a big old join, count distinct on the ids of the secondary tables, and let the optimizer do it's thing.  Note, the tables you want to count should have a unique primary key.  I've assumed they are a single column with the name "id" for both tables in the query below.

SELECT ...
FROM USER u, COUNT(distinct et.id) as et_count, COUNT(distinct uf.id) as uf_count
LEFT OUTER JOIN user_email_tracking et
    ON u.id = et.user_id
LEFT OUTER JOIN user_filter uf
    ON u.id = uf.user_id
WHERE u.stage = 10
    AND et.email_type_id = 4
GROUP BY u.id

That said, my other instinct is to put your subqueries into temporary tables with indexes. like...

CREATE TEMPORARY TABLE users (id INT PRIMARY KEY)

INSERT INTO users_temp (id)
SELECT id
FROM USER u
WHERE u.stage = 10

CREATE TEMPORARY TABLE email_tracking_count (user_id INT PRIMARY KEY, notified_count)

INSERT INTO email_tracking_count (user_id, notified_count)
SELECT u.id, COUNT(et.id)
FROM users_temp u
LEFT OUTER JOIN user_email_tracking et
        ON u.id = et.user_id
WHERE et.email_type_id = 4
GROUP BY u.id

CREATE TEMPORARY TABLE user_filter_count (user_id INT PRIMARY KEY, subscription_count)

INSERT INTO user_filter_count (user_id, subscription_count)
SELECT u.id, COUNT(uf.id)
FROM users_temp u
LEFT OUTER JOIN user_filter uf
        ON u.id = uf.user_id
GROUP BY u.id

SELECT ...
FROM users_temp ut
LEFT OUTER JOIN email_tracking_count etc
        ON ut.id = etc.user_id
LEFT OUTER JOIN user_filter_count ufc
        ON ut.id = ufc.user_id

That's a good bit longer, but I have a feeling it may perform better.  Try both out and let me know which works better.  I'll edit my answer accordingly.

disclaimer: I didn't run these queries, so theres probably a syntax error or typo somewher.


Only 2%?
Check the line terminators.  For the column, do something like SELECT HEX(LEFT(COL(100))).  For the file, do something like od or hd or take the cat to the vet (cat -vet).
A Rule of Thumb is that an average line of "text" is 30 characters, which would lead to about a 3% difference.  XML is rather verbose, and your data may be averaging 50 bytes per "line".


The numbers reported by log_statement_stats come directly from the getrusage system call (assuming your OS has one).  The numbers in square brackets are total for the session so far, the other numbers are deltas between the start and stop of the statement.

The filesystem block size is not normalized to be the same as the PostgresSQL block size.  It is in whatever units of block size your OS reports in getrusage.


Check if DB2 backup image contains any tablespace not using automatic storage?

Finally, I could judge if a backup image contains any tablespace not using automatic storage from db2ckbkp command by using a special number 0x1000 with Flags value

db2ckbkp -Tablespace NEWDB.0.DB2_01.DBPART000.20170317153802.001

// Here are some snippets:

NONAUTOTS
tbspInImage: T
ID: 7
flags: 0x101

Bitwise AND operation
     0x101
AND 0x1000
       0x0
// The zero means that this tablespace is non-automatic storage tablespace.

SYSCATSPACE
tbspInImage: T
ID: 0
flags: 0x3102

Bitwise AND operation
    0x3102
AND 0x1000
    0x1000
// The non-zero value means that this tablespace is automatic storage tablespace.

With reference to IBM Support


Can I have 'Query Store' automatically on when I create new databases?

Query Store is not active for new databases by default.

If you turn it on for model database, it will be on for any new databases you create in the same instance.  I tested in SQL 2017.

To turn it on in model database run following:

ALTER DATABASE model SET QUERY_STORE = ON;

I suggest you read this article and decide if you want to turn it on for all databases and settings you want to modify.

Best Practice with the Query Store


How to deal with Users Needing to Enter Mixed Data Types in MS SQL Server

The presence of an INSTEAD OF INSERT trigger does not prevent SQL Server from validating the data-types of the INSERT statement against the data-types of the target columns.

The error message:

  Msg 245, Level 16, State 1, Line 70
  Conversion failed when converting the varchar value 'A' to data type numeric.

indicates that SQL Server knows it can't even try to insert 'A' into the numeric field.  The trigger hasn't even ran at that point.

Having said that, you can use a trigger to put the absentee info into a another table, you just can't change the column type to a numeric value.

Here is an example:

USE tempdb;

/* drop the test tables, if they already exist */
IF OBJECT_ID('dbo.VarData') IS NOT NULL
DROP TABLE dbo.VarData;
IF OBJECT_ID('dbo.NumData') IS NOT NULL
DROP TABLE dbo.NumData;
GO

CREATE TABLE dbo.NumData
(
    NumDataID INT NOT NULL
        CONSTRAINT PK_NumData
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , Grade VARCHAR(30) NULL /* STILL a varchar value */
) ON [PRIMARY];

CREATE TABLE dbo.VarData
(
    VarDataID INT NOT NULL
        CONSTRAINT PK_VarData
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , NumDataID INT NOT NULL
        CONSTRAINT FK_VarData_NumDataID
        FOREIGN KEY
        REFERENCES dbo.NumData(NumDataID)
    , AbsentInd VARCHAR(1) NOT NULL
) ON [PRIMARY];
GO

/* drop-and-create the trigger */
IF OBJECT_ID('NumData_Insert',N'TR') IS NOT NULL
DROP TRIGGER NumData_Insert;
GO
CREATE TRIGGER NumData_Insert
ON dbo.NumData
INSTEAD OF INSERT
AS
BEGIN
    SET NOCOUNT ON;

    CREATE TABLE #NumDataInsert
    (
        NumDataID INT NOT NULL
    );
    /* only insert numeric values into the main table */
    INSERT INTO dbo.NumData (Grade)
    SELECT i.Grade
    FROM inserted i
    WHERE TRY_CONVERT(NUMERIC(4,2), i.Grade) IS NOT NULL;
    /* insert a NULL row into the main table, to obtain the NumDataID
        which allows us to match the Absentee rows to the associated row
        in the main table
     */
    INSERT INTO dbo.NumData (Grade)
    OUTPUT inserted.NumDataID INTO #NumDataInsert
    SELECT NULL
    FROM inserted i
    WHERE TRY_CONVERT(NUMERIC(4,2), i.Grade) IS NULL;

    /* insert any Absentee rows into the VarData table */
    INSERT INTO dbo.VarData (NumDataID, AbsentInd)
    SELECT ndi.NumDataID, 'Y'
    FROM #NumDataInsert ndi;

    SET NOCOUNT OFF;
END
GO

INSERT INTO dbo.NumData (Grade)
VALUES (97.2)
    , (42.0);

INSERT INTO dbo.NumData (Grade)
VALUES ('A');

SELECT *
FROM dbo.NumData;
SELECT *
FROM dbo.VarData;

The two SELECT statements above produce this output:

Even though this does not allow you to actually change the data-type of the column, it does allow you to prevent entering incorrect data into that column.  This will provide time for you to modify the code in the program to only enter numeric values into the NumData table, while preventing new "Absentee" rows being inserted into it.  Once you get the code changed in the client program, you'd ALTER the column to be numeric, and remove the trigger.

The below code still fails since SQL Server cannot coalesce alphabetic values into numeric values:

/* these still fail, since 'A' cannot be converted to match the
   datatype of the value, 97.2, which is numeric */
INSERT INTO dbo.NumData (Grade)
VALUES ('A')
    , (97.2);

INSERT INTO dbo.NumData (Grade)
VALUES (97.2)
    , ('A');

This will work, which is likely how the client program is inserting rows:

INSERT INTO dbo.NumData (Grade)
VALUES ('97.2')
    , ('A');


OEM 11g alerts and notifications arrive together

And that is why Enterprise Manager Database Control is inappropriate for this kind of monitoring.
Enterprise Manager Grid Control/Cloud Control runs on a seperate host, with a seperate repository database, and that is the tool for this job.


Oracle "insert /*+APPEND*/" into empty table

Without knowing far more about what you are doing its impossible to say. However, I can say what APPEND, essentially, does.
It will do a direct-path insert to the table which simply means it will write the data directly to data blocks and will bypass the buffer cache. So generally you will use the hint when loading a large dataset and definitely not for just a handful of records.
There are some things to consider such as serialisation, space re-use, etc. For a very quick list take a look at AskTom https://asktom.oracle.com/pls/apex/f?p=100:11:0::NO::P11_QUESTION_ID:1211797200346279484 .

It is another option you can consider using depending on the work you are doing.


Constraint to prevent overlapping ranges in an array field in PostgreSQL

I had a similar problem with an array of int4ranges, and solved it (so far to my satisfaction) by writing this for WiTopia who has kindly agreed to let me share. Please satisfy yourself it works before using it in production -- I am still doing that!

CREATE OR REPLACE  FUNCTION overlapping(therange int4range[])
RETURNS BOOLEAN as
$$
BEGIN

        RETURN (
                with
                      t1 as (select unnest(therange) as lefthand),
                      t2 as (select unnest(therange) as righthand)
                select (select array_length(therange, 1)) !=
                       (select count(*) from t1, t2 where t1.lefthand && t2.righthand)

       );

END;
$$ LANGUAGE plpgsql

I do a && (overlap) based join with the array and itself.   Each item will join with itself, and should join with any other item it overlaps as well, so the count of the result of the &&-join would be greater than the count of the original array if there are overlapping items.   If there are only non-overlapping items, then each item will join with itself only, and the result of the join will have one row for each element in the original array.

I'd be careful about using this once the arrays got big -- it's N^2 in concept!

After defining this function, I put this constraint in:

check (portsegments is null or not overlapping(portsegments))


Mysql: how to get unique values in column that can have a value or 0


I cannot do anything on replSet when auth is enabled

When I created admin users against mongos, I also created admin users for replication Set. Now I can administrate shards, config server, and mongos now.

Different shards can have different users in their admin databases. If keyfile is uique, new shard (running with auth and keyfile enabled) can be added to the running cluster.


Incorrect syntax near the keyword 'is' in SQL server vb6

Try this:

SELECT COALESCE(MAX(bill_bno) + 1, 1) AS bill_bno
FROM   rec_all
WHERE  rw = 0
       AND
       (CASE WHEN MONTH(bill_date) >= 1 AND MONTH(bill_date) <= 3
             THEN (YEAR(bill_date) - 1)
             ELSE YEAR(bill_date) END)
       =
       (CASE WHEN MONTH(Date) >= 1 AND MONTH(Date) <= 3
             THEN (YEAR(Date) - 1)
             ELSE YEAR(Date) END)

or this one:

SELECT COALESCE(MAX(bill_bno) + 1, 1) AS bill_bno
FROM   rec_all
WHERE  rw = 0
       AND
       (
       (MONTH(bill_date) >= 1 AND MONTH(bill_date) <= 3 AND (YEAR(bill_date) - 1) = (YEAR(Date) - 1))
       OR
       ((MONTH(bill_date) <> 1 OR MONTH(bill_date) <> 3) AND (YEAR(bill_date)) = (YEAR(Date)))
       )


Link in the error message exactly explain what happened.

  A replica set member becomes “stale” when its replication process
  falls so far behind that the primary overwrites oplog entries the
  member has not yet replicated. The member cannot catch up and becomes
  “stale.” When this occurs, you must completely resynchronize the
  member by removing its data and performing an initial sync.

To avoid this in future:

You need to investigate why secondary fall behind so much. Possible
more writes then normally expected.
Your oplog size might not be set up correctly. Once your secondary is
behind than the first entry in oplog it will never catch up as it has
no way to get those transactions.


How to get line that causes error when loading from csv to MySQL

Errors occured because of unescaped backshlashes. Values preceeded with \ were inrepereted as ascii signs, but they weren't. To avoid this error backslash - \ should be escaped by additional backslash - \ so occurences of backslashes should be changed to double backslashes - \\ .


Why would shrinking TEMPDB log lead to slowness?

I'd ask the person who told you that, to at least hear why they think it will decrease performance.

One reason is that your TLOG isn't going to stay at 0. Since you shrunk it so small, I presume you have Auto Growth set. Depending on how you configured it, it will grow a set amount each time, or a percentage of it's size each time. Thus, each time your transaction log needs to grow, SQL Server has to do work, which you could notice regarding performance.

A primary reason is detailed in the first link below:

  When the Auto Growth mechanism of the Transaction Log kicks in, SQL
  Server always has to zero-initialize the new chunk that is added to
  the end of the file. It doesn’t matter if your SQL Server instance is
  running with the privilege Instant File Initialization, or not – the
  Transaction Log is always zero initialized.

What makes more sense is setting a dedicated drive for this file, and setting the LOG file to the size of the drive. Why shrink to 0 when you know it's only going to grow again, leaving you in the same spot with it’s size, cause fragmentation, etc?

Why Transaction Log Auto Growths are Degrading your Performance
Percent or 1MB Growth in Use, Slow File Growths
Microsoft's Considerations for AutoGrow

tempdb uses the simple recovery model and clears the log when ever a checkpoint is issued. This automatically happens when the log usages reaches 70% and in the case of tempdb, it doesn't force dirty tempdb pages to disk. See Paul Randall's post on this. VLF's are made inactive if possible during the checkpoint as well. When you shrink the log, the VLFs are removed.

Paul Randal 8 Steps to better Log Throughput (see 7-8)
Pinal Dave Reducing High VLF Counts


How do I list available casts in SQL Server?

The Microsoft Docs page for CAST and CONVERT provides a great matrix of conversions in SQL Server.

The image included in the above page:

As far as I know, there is no built-in mechanism for displaying a list of valid casts.


SQL Server restore sequence from multiple full, diff and log backups

Personally, I like my backups to be timestamped something like MyDB-yyyymmddhhnn-Full.bak or MyDB-yyyymmddhhnn-Log.bak in the file name. I've never actually read the LSN from a backup file, neither have I read the LSN information from the system tables.

You can sort the files and pick out which ones you need quite easily.


I think you're looking at something like this:

Select A.Max_id,
harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
  FROM
  ( Select  backups.resource_id,
    Max(backups.id) As Max_id
      FROM backups  Group by backups.resource_id
      Order By backups.start_time Desc ) A
JOIN resource ON resource.id = A.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id

OR (order by outside, don't remember)

Select A.Max_id,
harddrives.id As id1,
  harddrives.serial_number,
  brands.name,
  harddrives.size,
  location.name As name1,
  encryption_type.name As name2,
  resource.name As name3,
  backups.start_time,
  backups.end_time
  FROM
  (  Select  backups.resource_id,
    Max(backups.id) As Max_id
  FROM backups  Group by backups.resource_id ) as A
JOIN resource ON resource.id = A.resource_id Inner Join
  harddrives On backups.backup_drive_id = harddrives.id Inner Join
  brands On harddrives.brand_id = brands.id Inner Join
  location On harddrives.location_id = location.id Inner Join
  encryption_type On harddrives.encryption_type_id = encryption_type.id
Order By   A.start_time Desc


Using the date of a result column as a partial input

Throw more detail and more sample data.

you cannot pull all the data.you have to pull data of some specific date which you pass as parameter.

here consider @DateTable  is parameter.or you pass detail what you want

you can try some thing like this,

declare @DateTable table(ReportDate datetime)
insert into @DateTable values('2017-06-02'),('2017-06-03')

create table #t (colTime datetime)
insert into #t
select  dateadd(hour,number,ReportDate)  from @DateTable
cross apply(select distinct number  from master..spt_values where number>0 and number<25
)ca

--select * from #t

--analyze this
--select *
--from #t t
--left join table1 v
--on t.colTime=dateadd(hour, datediff(hour, 0, v.ModifiedDate), 0)

select t.colTime
,count(id)
from #t t
left join table1 v
on t.colTime=dateadd(hour, datediff(hour, 0, v.ModifiedDate), 0)
group by t.colTime

drop table #t


The table you created as sys is created in the "sys" schema.

SELECT by default accesses the schema of the caller, so in your case, as SELECT is run as "account", it tries to access table "pay" in schema "account".

You'll need to either use a schema-prefixed query:

SELECT * FROM sys.pay

Or first create a synonym in "account" schema (as sys):

CREATE SYNONYM account.pay FOR sys.pay;

You could also use public synonyms, but I would rather not recommend them.


Can sp_MSforeachDB be used for DBCC CheckDB without errors being masked?

It sounds like sp_MSforeachDB is skipping your Test2 database. This is one of many known issues with sp_MSforeachDB as noted by Aaron Bertrand:

Execute a Command in the Context of Each Database in SQL Server

You can confirm by removing the NO_INFOMSGS parameter:

exec sp_MSforeachDB 'DBCC CHECKDB (?) WITH DATA_PURITY'

This should cause messages to be returned for every database. Search the results for your Test2 database. I bet it isn't there.

With DBCC CHECKDB all error messages are displayed by default. Specifying or omitting the WITH ALL_ERRORMSGS parameter has no effect.

You may like to look at Ola Hallengren's solutions for SQL Server Backup, Integrity Check, and Index and Statistics Maintenance.


No, not in SQL Server 2014.

You can have Availability Groups across multiple subnets, but they all must reside on the same domain (even if they span data centers, continents, etc). This official document states as much for Windows Server 2008 R2, but I can assure you the same remains true for more recently released versions of Windows (emphasis mine):

  Windows Server 2008 R2 requires that all the cluster servers must be in the same Active Directory domain. Therefore, SQL Server multi-subnet failover cluster requires that all the cluster nodes be in the same Active Directory domain even if they are in different subnets.

And from this document (emphasis theirs):

  Availability replicas must be hosted by different nodes of one WSFC cluster:  For a given availability group, availability replicas must be hosted by server instances running on different nodes of the same WSFC cluster.

This is enforced by the underlying Windows Server Failover Cluster (WSFC), not AGs directly. You can't have a Windows cluster traverse domains, and since AGs need to all be part of a single WSFC, there is no way to have them span domains.

For newer versions of SQL Server and Windows there is a new concept called a Domain Independent Availability Group which you can read about here. And for SQL Server 2017 there was a requirement to support Linux, which obviously doesn't have Windows domains, and you can read about that here.

Klaus Aschenbrenner talks about setting up a Basic Availability Group in SQL Server 2016 Standard Edition and Windows Server 2016, on a simple workgroup (no domain at all):

How to create a SQL Server Availability Group WITHOUT an Active Directory Domain


How to issue a DML with WHERE clause comparing a value which is trigger generated in table?

There is a function named make_timestamp() that you can use for this. It also makes your trigger function a lot shorter:

CREATE FUNCTION timestamp_insert()
  RETURNS trigger
  LANGUAGE plpgsql
AS
$$
begin
    new.timestamp := make_timestamp(new.year, new.month, new.date, new.hour, new.min, new.sec);
    return new;
end;
$$

The same function can be used in the UPDATE as well

UPDATE TEST
   SET value = ?
WHERE timestamp < make_timestamp(year, month, date, hour, min, sec);

If you are using Postgres 12, you don't need the trigger as it now supports generated/calculated columns:

CREATE TABLE TEST
(
  year int,
  month int,
  date int,
  hr int,
  min int,
  sec int,
  ts timestamp generated always as (make_timestamp(year, month, date, hr, min, sec)) stored,
  value double precision
);


Substitute names for IDs stored in two columns referencing the same table

You were almost there.

First of all, your query contains what seems to be typos: there are two references to `tabl1` in it that should most likely be `table1` instead, and the `orgTable` alias should probably be `myTable` (or the other way round; my query below assumes the former).

Now with that out of the way, you can get the other name by referencing it via the `myTable` alias that you assign to the second instance of table1:

SELECT `table2`.`type`,
       `table2`.`status`,
       `table1`.`name`  AS createdBy,
       `myTable`.`name` AS approvedBy
FROM `table2`
JOIN `table1` ON `table2`.`createdby` = `table1`.`id`
JOIN `table1` AS `myTable` ON `table2`.`approvedby` = `myTable`.`id`;

Let me also note that your query will arguably be clearer if you use aliases for both instances of table1, as well as avoid excessive use of backticks:

SELECT table2.type,
       table2.status,
       creator.name  AS createdBy,
       approver.name AS approvedBy
FROM table2
JOIN table1 AS creator  ON table2.createdby  = creator.id
JOIN table1 AS approver ON table2.approvedby = approver.id;


You are doing an insert into but you only specify one field (sub), then you select 2 fields, just as the error message says.

insert into  data(Sub) -- only one field
select  fullpath, case -- 2 fields
        when fullpath like "%subA%" then "subA"
        when fullpath like "%subB%" then "subB"
        else "Neither" end
from data

You would either need to add a fullpath field to the data table and do:

insert into  data(fullpath, Sub)
select  fullpath, case
        when fullpath like "%subA%" then "subA"
        when fullpath like "%subB%" then "subB"
        else "Neither" end
from data

Or you need to select one column

insert into  data(Sub)
select  case
        when fullpath like "%subA%" then "subA"
        when fullpath like "%subB%" then "subB"
        else "Neither" end
from data


SSIS: Conditional operator in expression builder ignores previous text

The solution is to encapsulate the conditional operation with parenthesis:

"Preamble
"
+
(@[$Package::myParameter] == ""  ? "" : "Middle Text")
+
"
The End"


The TL;DR versions:

You should assume that all mongod processes (including config servers) and all mongos processes need to talk to each other.  Naturally, this only applies to the actual database port (27017 default) and not the HTTP console (+1000) or REST if you have that enabled.

More verbosely:

The mongos will need to talk to every member of each shard (usually a replica set) for reads and writes.  The mongos also need to talk to the config servers - that is where it gets its config from, and how it does locking, keeps admin users and more.

The members of a replica set all need to talk to each other (replication/elections).  All primaries need to talk to the config servers as well as to other primaries in other shards.  Since all secondaries can become primary, then they all should have the same ports open as the primaries.

Also, make sure the permissions are bi-directional, without going into a lot of details, the origin of connections often surprises people (pull versus push in chunk migrations for example).

In fact, the only processes I can think of that you may get away with restricting ports on are the Arbiters - they will only need to be able to talk to the members of the set and can never become primary.  However, the mongos does ping it as part of a health check thread, so in the end I think the simplicity of a policy to cover all would be more benefit than the couple of ports you can omit for the arbiter special case.


You are correct,

CREATE FUNCTION foo()
RETURNS void
AS $$
  while(1) {}
$$ LANGUAGE plperl;

SELECT * FROM foo();

Having called pg_terminate_backend and pg_cancel_backend that plperl backend persists. Seems to be a bug that they already know about, from the lists  Tom Lane says,

  Even assuming that we can fix the garden-variety bugs like these, there's still a fundamental problem that an uncooperative user function
  (particularly one in plperl/pltcl/plpython) can indefinitely delay
  response to pg_terminate_backend.  Maybe that's okay, seeing that it can similarly hold off or disregard QueryCancel, but I'm not sure the people asking for this are really gonna be satisfied.  (One thing we
  should consider is making ERRCODE_ADMIN_SHUTDOWN unconditionally untrappable by plpgsql exception blocks, which'd at least fix the issue for plpgsql functions.)

I am not sure they have a solution to this or not. From a Craig Ringer,

  In general it's safe to use pg_terminate_backend as a "bigger hammer". SIGTERM as sent by  pg_terminate_backend() will often, but not always, cause a backend that can't respond to a cancel to exit.

He even goes into explaining some of the options that can cause the hang.

Anyway, let's try something

CREATE FUNCTION foo()
RETURNS void
AS $$
  $SIG{INT} = $SIG{TERM} = sub { die; };
  while(1) {}
$$ LANGUAGE plperl;

Then I ran

SESSION 1                           SESSION 2

SELECT pg_backend_pid();
 pg_backend_pid
----------------
          20465

SELECT * FROM foo();
-- LOOPING

                                    pg_terminate_backend(20465);

ERROR:  Died at line 2.
CONTEXT:  PL/Perl function "foo"

That seems to work, if it's an option.


Executing Child packages without using master package

I use the following as the guideline:

If you are simply using many packages to structure your solution in a more modular fashion, executing in-process is probably the way to go because you don't have the overhead of launching more processes. I'm happy to pass on that advice here.

Quoted from: http://consultingblogs.emc.com/jamiethomson/archive/2005/05/16/1414.aspx


We came up with this, which seems to work:

SELECT DISTINCT rp.name,
                ObjectType = rp.type_desc,
                PermissionType = pm.class_desc,
                pm.permission_name,
                pm.state_desc,
                ObjectType = CASE
                               WHEN obj.type_desc IS NULL
                                     OR obj.type_desc = 'SYSTEM_TABLE' THEN
                               pm.class_desc
                               ELSE obj.type_desc
                             END,
                s.Name as SchemaName,
                [ObjectName] = Isnull(ss.name, Object_name(pm.major_id))
FROM   sys.database_principals rp
       INNER JOIN sys.database_permissions pm
               ON pm.grantee_principal_id = rp.principal_id
       LEFT JOIN sys.schemas ss
              ON pm.major_id = ss.schema_id
       LEFT JOIN sys.objects obj
              ON pm.[major_id] = obj.[object_id]
       LEFT JOIN sys.schemas s
              ON s.schema_id = obj.schema_id
WHERE  rp.type_desc = 'DATABASE_ROLE'
       AND pm.class_desc <> 'DATABASE'
ORDER  BY rp.name,
          rp.type_desc,
          pm.class_desc


Can I improve my duplicate-detection queries?

In your query to create the temporary table, it's possible to do that in standard SQL without a sub-query, using a HAVING clause, e.g.:
CREATE TEMPORARY TABLE dupe_keys AS (
    SELECT count(*) AS cnt, year_, month_, dayofmonth, uniquecarrier, flightnum, origin
    FROM ontime
    GROUP BY year_, month_, dayofmonth, uniquecarrier, flightnum, origin
    HAVING cnt > 1
);

I don't know if that will speed up the temporary table creation, but it might be worth a try.


Using Change Data Capture with a frequently changing schema

The mechanism for schema changes is that you disable CDC for the table, and then re-enable it. This of course means you lose all the history, so you'll also want to build in a mechanism to offload that somewhere if you want history from before the most recent schema change.

Maybe a better answer is, once you've tested CDC initially, keep it disabled until you're comfortable that the schema is more stable.


SQLCLR TVF that calls web service is getting error 401: Unauthorized

A couple basic SQL Server checks: Is your database set to trustworthy?  Is the assembly set to allow UNSAFE execution?

If that seems to be working, there are a couple of tutorials out on the web that might help.  If none of the tutorials out there help, then it sounds like a permissions issue.  (Unfortunately, that gets tricky, as this thread discussing Kerberos versus NTLM authentication shows.)

One other check--as Maumen mentioned--make sure that you have anonymous access setup for IIS.

Ultimately, though, it sounds like something you want to avoid, if you can.  The performance and memory implications, the difficulty getting all the accounts working, and decreasing all the security settings would make me a bit nervous.


MyISAM table is not working as expected in MySQL

The command TRUNCATE TABLE is DDL. That would entail doing a full table lock regardless of storage engine. If other DB Connections are waiting to access the table, you can be sure there are MUTEXs.

For the sake of example, let's say the table you are truncating is mydb.mytable.

You can get around the MUTEX problem (as manifested by Waiting for Table...)

Instead of doing this:

TRUNCATE TABLE mydb.mytable;

make an empty table on the fly:

ALTER TABLE mydb.mytable RENAME mydb.mytablezap;
CREATE TABLE mydb.mytablenew LIKE mydb.mytablezap;
TRUNCATE TABLE mydb.mytablenew;
ALTER TABLE mydb.mytablenew RENAME mydb.mytable;
DROP TABLE mydb.mytablezap;

I still run TRUNCATE TABLE mydb.mytablenew;, even though it is empty, in order to reset the auto_increment value. The table should be immediately available for use once you execute  the ALTER TABLE mydb.mytablenew RENAME mydb.mytable;.

The last line, DROP TABLE mydb.mytablezap;, should only be handled by the one DB Connection that runs the above code.

Give it a Try !!!


This is to implement the feature found in the standard. (copied from a draft, date: 2011-12-21):

  4.15.11 Identity columns

  The columns of a base table BT can optionally include not more than one identity column. The declared type
  of an identity column is either an exact numeric type with scale 0 (zero), INTEGER for example, or a distinct
  type whose source type is an exact numeric type with scale 0 (zero). An identity column has a start value, an
  increment, a maximum value, a minimum value, and a cycle option.
  ...
  ...  The definition of an identity column
  may specify GENERATED ALWAYS or GENERATED BY DEFAULT.

It is a property of a column which basically says that the values for the column will be provided by the DBMS and not by the user and in some specific manner and restrictions (increasing, decreasing, having max/min values, cycling if the max/min value is reached).

Sequence generators (usually called just "sequences") are a related SQL standard feature: it's a mechanism that provides such values - and can be used for identity columns.

Note the subtle difference: a SEQUENCE is an object that can be used to provide values for one or more identity columns or even at will.

The various DBMS have so far implemented similar features in different ways and syntax (MySQL: AUTO_INCREMENT, SQL Server: IDENTITY (seed, increment), PostgreSQL: serial using SEQUENCE, Oracle: using triggers, etc) and only recently added sequence generators (SQL Server in version 2012 and Oracle in 12c).

Up to now Postgres has implemented sequence generators (which can be used to provide values for column, either with the special macros serial and bigserial or with nextval() function) but has not yet implemented the syntax for identity columns, as it is in the standard.

Defining identity columns (and the slight difference from serial columns) and various syntax (eg. GENERATED ALWAYS, NEXT VALUE FOR, etc) from the SQL standard is what this feature is about. Some changes / improvements may need to be done on the implementation of sequences as well, as identity columns will be using sequences.

If you follow the link identitity columns (from the page you saw), you'll find:

  identity columns

  From:     Peter Eisentraut
  To:   pgsql-hackers
  Subject:  identity columns
  Date:     2016-08-31 04:00:42
  Message-ID:  6adbacbf-73bc-dd1a-2033-63409180fd18@2ndquadrant.com

  Here is another attempt to implement identity columns.  This is a
  standard-conforming variant of PostgreSQL's serial columns.  It also
  fixes a few usability issues that serial columns have:

  need to set permissions on sequence in addition to table (*)
  CREATE TABLE / LIKE copies default but refers to same sequence
  cannot add/drop serialness with ALTER TABLE
  dropping default does not drop sequence
  slight weirdnesses because serial is some kind of special macro

  (*) Not actually implemented yet, because I wanted to make use of the
  NEXT VALUE FOR stuff I had previously posted, but I have more work to
  do there.

  ...

Update 2017, September: seems like the feature will be in Postgres 10, which is to be released in a few days/weeks:  What's New In Postgres 10: Identity Columns

Oracle have also implemented identity columns and sequences, in version 12c. The syntax is according to the standard, as far as I checked:
Identity Columns in Oracle Database 12c Release 1 (12.1)

  The 12c database introduces the ability define an identity clause against a table column defined using a numeric type. The syntax is show below.

GENERATED
[ ALWAYS | BY DEFAULT [ ON NULL ] ]
AS IDENTITY [ ( identity_options ) ]


Why does SQL Server say it can't convert varchar to numeric?

A conversion error will occur at run time when an attempt is made to convert the sales.pid value 'Colgate' value to numeric(9) to evaluate the join criteria. As to whether or not this actually happens depends on the order of evaluation in the execution plan.

Below is the sales table clustered index scan predicate from the Unicode literal execution plan, showing the conversion occurs before the 'number' condition is evaluated:

CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.) AND CONVERT_IMPLICIT(nvarchar(10),[tempdb].[dbo].[sales].[type],0)=N'number'

The plan with the non-Unicode literal has the same shape except the predicate order in the scan operator is reversed:

[tempdb].[dbo].[sales].[type]='number' AND CONVERT_IMPLICIT(numeric(9,0),[tempdb].[dbo].[sales].[pid],0)=(1.)

Although the non-Unicode literal (or parameter) may workaround the problem, the query will still be vulnerable to run time errors. This can be addressed with TRY_CAST, TRY_CONVERT, CASE expression, etc. but it would be better to fix the data model such as to ensure only like or compatible data types are compared.


Can we determine FDs if we are given some tuples?

No. The reason why database designers are interested in FDs is because they are a formalisation of Business Rules - rules about the data which accurately reflect the Business Domain to be modelled in the database. The only sound and practical way to identify FDs is by analysing and understanding a set of business requirements.

Suppose you are designing a database for a members-only club and you are given the data for all current members. You notice that for every member Sex = "Male". Can you conclude from the data that the club only admits men? Of course not. The only way to know that is by knowing what the club rules are. You could conclude from the data that the club is not a women-only club, but that's something different. Knowing what dependencies do not apply isn't the same as knowing what dependencies do apply.

You can of course use data to help you test a set of given FDs (by making sure that the FD holds for every row of some sample data) but you first have to know what FDs to try; you shouldn't expect to derive them from data alone.


As you mention you string value format is in 21/11/2105 then you can try following one

Alter table table name alter column column name datetime NULL

( null is optional if you want to allow null value to your field).

Note that if the column contain  string value like "abc" (other than date format value) then it will show error as conversion fail.


Mongo Shard Server Doubling as Another Shard's Replica Server

Normally network speed (read/write) is not bottleneck, so application servers can be separated from mongod primaries.

If you plan to make cluster and put application to primaries so that application writes only that shard where it's data lies it's going to be a little bit tricky to select right sharding key.

Only concern is the memory, normally you DON'T put any applications to mongod nodes, because mongod uses ALL the memory, eventually! Atleast at situation where your data+indexes are bigger than free memory (at start of machine).


Language setting for 'TZD' format in TO_CHAR (timestamp with time zone)

You can get the TZD abbreviations with this query:
SELECT tzabbrev, tzname, TZ_OFFSET(tzname)
FROM V$TIMEZONE_NAMES
WHERE tzname = 'America/Edmonton'

TZABBREV    TZNAME              TZ_OFFSET(TZNAME)
----------  ------------------  ------------------
LMT         America/Edmonton    -06:00
MST         America/Edmonton    -06:00
MDT         America/Edmonton    -06:00
MWT         America/Edmonton    -06:00
MPT         America/Edmonton    -06:00

I think they are fix and you cannot change it. Note, for time zone abbreviations there is no common or official standard, for every time zone you will find dozens of abbreviations, see https://www.timeanddate.com/time/zones/na.
If you need a language specific output you could use CASE or DECODE
DECODE(to_char(systimestamp at TIME zone 'AMERICA/EDMONTON', 'TZD'), 'MDT','HNR' ,'HAR')


Big data table processing with RDBMS like MySQL?

Basically you are talking about performance. There are so many factors/parameters responsible for performance. With no doubt 100 million records are feasible in RDBMS, it depends upon configuration you have made for your server. If we look for some parameters be like :

1) Hardware : Looking into your physical table size, proper RAM allocation is done or not. For feasible amount of RAM proper CPU cores are divided or not.

( Specifically of MySQL )

2) Sort Buffer : Whether optimal values are configured for Sort buffer global variables.

3) Temporary Table Size : Temporary table size should be suitable enough

4) Join Buffer : Join buffer should be configured with optimal value.

And many more parameters need to look in.

Hope this helps.


SQL Profiler Shows Update (EventClass) with No Errors, But Data in Table is Not Changed

Managed to fix this problem.  Turns out that the data access handler in the application code should have called commit after the UPDATE call, but this was missing.

Based on my limited understanding, the access handler (based on Interop.COMSVCSLib) is using a buffer(?) built in to SQL Server.  For some reason, I expected this buffer to be external to the database.

Thus SQL Server Profiler was capturing the UPDATE call even though the update was not being committed.  When the SELECT was called, presumably SQL Server was still waiting for the update to be committed and so caused the timeout.  When the UPDATE failed to be committed then SQL Server caused the data to be thrown away and the old data was preserved.

(If someone wants to write a more detailed answer that can help me understand this process better then I will mark it as the answer, since I don't really like answering my own questions).


Oracle 10gR2 - fix for SSL version following Poodle attack

I'd expect you to be able to set the SSL_VERSIONS to 3.1 (TLSv1) and successfully avoid the POODLE vulnerability.  https://docs.oracle.com/cd/B19306_01/network.102/b14268/asossl.htm#i1023429


Does my choice of pg_dump format impact restore speed?

Archive format has small impact on restore time.

That's because actually, when you restore from plain format, it is equivalent to:

cat backup.file | psql emptydb

and when you restore from "compressed" format, it is equivalent to:

pg_restore backup.file | psql emptydb

, and the cost of decompression with pg_restore is usually negligible, compared to write-intensive COPY/CREATE INDEX commands which happen during restore.

For large dumps, restoring from compressed dumps may be a bit faster (less disk reads).

It might be even faster with -j (--jobs) option. It can be used only with "compressed" format, and it allows paralell restore with N concurrent jobs (sessions).

For more details about -j option, see http://www.postgresql.org/docs/8.4/static/app-pgrestore.html. Example:

pg_restore -j 4 -d emptydb backup.file


WHERE clause seems to be messing up FIELD function

I didn't know MySQL understood "$campaignid" as a variable, in the examples I see "@" being used, as in "@campaignid".
Anyway, in stead of FIELD (which needs a list of arguments, not an argument containing a list) I'd use LOCATE, with sortValue containing "good_fit,bad_fit,added,undecided":
SELECT * FROM reservation_form_results
    WHERE campaign_id = $campaignId
    ORDER BY locate(status, '$sortValue')

"undecided" will have the highest location, preceded by added, etc.
db fiddle
PHP MySQL prepared statements


DBCC sqlperf(waitstats) contains signal wait time longer than last time waitstats was cleared

The signal wait time is aggregated across all cores. Any system with more than one core can have total signal waits longer than the last time you clear the wait stats.


In Rails: How is existing data affected by data type change?

I was able to set up a way to test this, and the existing data did convert from DateTime to Date types! As explained here, when doing this with MySQL as the adapter, using change_table triggers the following SQL call:

ALTER TABLE table_name MODIFY COLUMN column_name DATE

Two helpful warnings (courtesy of @SacWebDeveloper):

This is an irreversible change, so if anyone else is trying this and may need to reverse, be sure to use the reversible command or write out the up and down changes.
When converting from DateTime to Date, be aware that rounding uses fractions of a second. Example: '1999-12-31 23:59:59.499' => '1999-12-31', but '1999-12-31 23:59:59.500' => '2000-01-01'.


Does index occupy disk memory for rows for which index expression is NULL?

Let's simply try it!

I've created the table from your question, then did the following:

INSERT INTO mytable
SELECT json_object(ARRAY['myfield', i::text || 'bla'])::jsonb
FROM generate_series (1, 100000) t(i);

\di+ trgm_index_1

 Schema │     Name     │ Type  │ Owner  │  Table  │ Size  │ Description
────────┼──────────────┼───────┼────────┼─────────┼───────┼─────────────
 test   │ trgm_index_1 │ index │ avaczi │ mytable │ 19 MB │

So far, we have only rows where myfield is present.  Let's now add some other rows, too:

INSERT INTO mytable
SELECT json_object(ARRAY['other_field', i::text || 'bla'])::jsonb
FROM generate_series (100001, 200000) t(i);

After this, the reported index size has changed to 22 MB, meaning that the new rows are also accounted for.  If you want to exclude them, you have to create a partial index:

CREATE INDEX trgm_index_partial ON mytable
USING gin ((mydata#>>'{myfield}') gin_trgm_ops)
WHERE mydata ? 'myfield';

The size of this one is initially the same (19 MB) as the non-partial index, but after adding the second group of rows, it does not change:

\di+ trgm_index_*

 Schema │        Name        │ Type  │ Owner  │  Table  │ Size  │ Description
────────┼────────────────────┼───────┼────────┼─────────┼───────┼─────────────
 test   │ trgm_index_1       │ index │ avaczi │ mytable │ 22 MB │
 test   │ trgm_index_partial │ index │ avaczi │ mytable │ 19 MB │


Why Database use Buffer pool for write operation?

If you are using SQL Server, write operations pull pages into memory if they need to be modified and write the changes to the page in memory as well as the transaction log. At given intervals those changes, and other changes made to adjacent pages since the last disk write operation occurred, are written to disk. This allows the server to make more logical writes to the disk in one go, making that write operations much more efficient.

Here are some links with good explanations (again assuming you are using SQL Server):

When things are written to disk: http://technet.microsoft.com/en-us/library/ms189573%28v=sql.105%29.aspx
What happens during a write operation: http://technet.microsoft.com/en-us/library/aa337560%28v=sql.105%29.aspx


on SQL2014 SP1 changing sp_configure parameters seemed to solve problem :

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'access check cache bucket count',512;
exec sp_configure 'access check cache quota',2048;
GO
RECONFIGURE;
GO
sp_configure 'show advanced options', 0;
GO


How to append value of column with double quotes (add quotes around string)

Use the CONCAT function

SELECT NAME,ID,CONCAT('"',QTY,'"') QTY FROM `myTable`;

If you want single quotes, use the QUOTE function

SELECT NAME,ID,QUOTE(QTY) QTY FROM `myTable`;

Give it a Try !!!


Bacpac Import fails with error 'The Element or Annotation class SqlDatabaseOptions does not contain the Property class CatalogCollation'

This has been fixed as part of build 14.0.17230.0 (not 14.0.17320.0) of SSMS.
Please update to the latest version.
You can read more about here:
https://blogs.msdn.microsoft.com/azuresqldbsupport/2018/02/15/what-will-happen-with-catalog_collation-and-case-sensitive-vs-case-insensitive/


How to replicate MySQL table in different servers?

Start by splitting your confidential info out of the table.  Make a 'parallel' table ('vertical partitioning').  The new table would have the same PRIMARY KEY as the original, so they can easily be JOINed together.

To avoid replicating the new table, either use replicate_ignore_wild=... on the slave or put the table in a different database and use binlog_ignore_db=... on the master.

Or you could let it replicate, but change permissions so that most people do not have even SELECT privilege to that table/database.

That would be a minimal first step toward securing the data.  I say it is only a fist step because if the Master is hacked into or stolen, there is nothing to stop them from getting the confidential info.


This should work:

SELECT pos, SteamId
FROM
(
    SELECT (@pos := @pos+1) pos
        , Map, Time, Date, SteamID
    FROM `surf_times` S
        , (SELECT @pos := 0) p
    WHERE `Map` = "surf_mesa"
    ORDER BY `Time`
) `surf_times`
WHERE `SteamID` = "76561198065863390"
ORDER BY pos
LIMIT 1;

SQL Fiddle


Cursors are loops, but they can be more complex for the database engine to implement depending on the options you chose.

There are many options for cursors in TSQL that can have a significant impact on performance depending on the SELECT that feeds the cursor with information (see the TSQL docs for detail). The options will change what locks and other resources are needed, could cause parts of the query to be rerun, and so forth. Also your inner loop may be doing things that affect the behaviour of the cursor by updating base tables it reads from in a way that makes the database engine need to do more work.

If we could see the two bits of code you are talking about we could be more specific.

By running the initial query into a table variable and looping through that you are essentially emulating a STATIC FAST_FORWARD READ_ONLY cursor, those options remove quite a chunk of the complexity that the engine might otherwise have to worry about. Your main SELECT is definitely run once and in one go, and your loop is not competing with other activity on the base data while it interacts with the copy in the variable.

Of course you might also find that something within your two loops (the one using the cursor and the one without) has significantly different performance metrics if they are not written exactly the same.


MySQL: Does the order of the ON condition of a LEFT JOIN matter?

SQL is a declarative language; that is, it does not specify how something should be done, only what the result should be.

The computing languages I have used all respect the properties of equality, so employees.ID = unionMembers.ID is equivalent to unionMembers.ID = employees.ID

In this case, rather than an outer join, which can generate a large data-set, you would probably want to use a sub-query or the EXISTS operator, depending on your data structure. Something like:

SELECT   ID,
          wage,
          unionDues
FROM      employees
WHERE     ID in (
    SELECT    ID
    FROM      unionMembers
    WHERE     ID = 'ID001'
    );

There is an in-depth discussion here: https://dev.mysql.com/doc/refman/8.0/en/subquery-optimization-with-exists.html

HTH


how to import mysql system "mysql" database

mysqldump actually has an option to run FLUSH PRIVILEGES; after the dump is completed
[root@**** ~]# mysqldump --help | grep privileges
  --flush-privileges  Emit a FLUSH PRIVILEGES statement after dumping the mysql
flush-privileges                  FALSE

In light of this you would run
mysqldump --all-databases --flush-privileges > dumpfile

Running mysqldump against a MySQL Instance and reloading back into the same MySQL Instance should not annihilate user privileges requiring you to run FLUSH PRIVILEGES; by hand. This may be a problem with MariaDB. You should test this on a Dev Server. You should also see the open bug list of MariaDB to see if this is already a known issue.
I have recommended using --flush-privileges in my past posts

Feb 03, 2012 : How to resolve the master server shut down/unavailability in mysql with master - slave replication
Jan 19, 2012 : How do I replicate Mysql DB server to new server?
Dec 05, 2011 : Set up a MySQL slave using mysqldump to get the initial data
Jun 26, 2011 : How to enable all the possible backup options of MySQL

CAVEAT
Please keep in mind that grants are stored in RAM. Running FLUSH PRIVILEGES; simply reads
mysql.user and reloads the grants into RAM. The error message you mentioned earlier

ERROR 1133 (42000): Can't find any matching row in the user table

may be not referring to the physical mysql.user table, but the in-memory copy of it. Again, I emphasize that MariaDB may be mishandling user grants upon reload.
What worries me further is the fact that you restarted mysql (MariaDB) and the grants still did not work? At the risk of sounding redundant, this may be a problem with MariaDB. You should test this on a Dev Server. You should also see the open bug list of MariaDB to see if this is already a known issue.


How can I run a proc against all records in a temp table?

If you need to improve the performance of your code it probably makes sense to focus on converting the stored procedure into a set-based operation. For example, instead of issuing 4000 individual UPDATE queries you could issue one UPDATE that acts on 4000 rows. Changing from a cursor to some other row by row method benefit is very unlikely to speed up your code.
If you truly need to loop over all of the rows in a table just use a cursor. That's what they're designed for. This is especially true when you're doing with a small number of rows in the table like 4000. The cursor won't take a meaningful amount of time as long as you don't increase the number of rows or have many concurrent processes.
Test procedure and data:
CREATE OR ALTER PROCEDURE dbo.DO_NOTHING (@input_id INT)
AS
BEGIN
    DECLARE @i INT;
END;

GO

CREATE TABLE #t (ID INT);

INSERT INTO #t
SELECT TOP 2000 number
FROM master..spt_values

UNION ALL

SELECT TOP 2000 number
FROM master..spt_values;

Here's the code that calls the procedure for each row in the temp table:
DECLARE @id INTEGER;
DECLARE @rows_processed INTEGER = 0;
DECLARE cur CURSOR LOCAL FAST_FORWARD
FOR
SELECT id
from #t;

PRINT 'Start time = ' + CAST(SYSDATETIME() AS VARCHAR(30));
OPEN cur;

FETCH NEXT FROM cur INTO @id;

WHILE @@FETCH_STATUS = 0
BEGIN
    EXEC dbo.DO_NOTHING @id;

    FETCH NEXT FROM cur INTO @id;
    SET @rows_processed = @rows_processed + 1;
END;
CLOSE cur;
DEALLOCATE cur;
PRINT 'Rows processed = ' + CAST(@rows_processed AS VARCHAR(5));
PRINT 'End time = ' + CAST(SYSDATETIME() AS VARCHAR(30));

The output on my machine:

Start time = 2017-06-02 22:39:23.2709937
Rows processed = 4000
End time = 2017-06-02 22:39:23.3255551

The code ran in 0.05 seconds.


What is the benefit of using BOOLEAN over TINYINT(1)?

Those two are really synonyms, so you can use them interchangeably. You won't see any difference between them.

If you want to allow 0 and 1 only, you can still use the bit type.


Which are the minimum privileges required to get a backup of a MySQL database schema?

According to the MySQL Documentation

  mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions.

Thus, to run

mysqldump --no-data --routines --triggers ...

the privileges required are

SELECT
SHOW VIEW (If any database has Views)
TRIGGER (If any table has one or more triggers)
LOCK TABLES (If you use an explicit --lock-tables)

You will also need the SELECT privilege on mysql.proc in order to dump Stored Procedures.


DBCC checkDB stuck in killed/rollback state

Restarting a server while something is rolling back, is almost always a bad idea. You may make things worse.

You don't say if you are using one of the repair options with DBCC CHECKDB if you are, it can complicate things even more.

It has been a few hours since the original question was posted, so things have probably either self corrected or gotten worse.  Hopefully you have backups, that you will be able to use in the recovery, if things are not going well.

Some Related posts that may help

Why did a SQL Server Restart roll-back seemingly committed transactions?
SQL database stuck in recovery
KILLED/ROLLBACK query doesn't stop running
SQL Server 2008 R2 - Service shutdown - what with running stored procedures?
Query Execution Time


If you migrate to a new version of PostgreSQL use pg_dump from the latest version. Anyway, this is a simple way to dump and restore :

pg_dump -U username -h hostname source_database | psql -U username target_database


Basic transactional DDL script in PostgreSQL

if any errors, roll the whole thing back;

It's simpler than you seem to think. Any exception in a transaction (that is not trapped somehow) triggers a ROLLBACK for the whole transaction automatically. You don't have to do anything extra.

BEGIN;
CREATE TABLE IF NOT EXISTS blah ( ... );
-- much more DDL
COMMIT;

You cannot start, commit or roll back transactions inside plpgsql at all, since a plpgsql block is always run in the context of an outer transaction automatically.

Don't confuse SQL commands for transaction management with elements of a plpgsql code block. BEGIN is used as keyword in both, that's the only thing in common. That's never ambiguous, because the SQL command is not available inside plpgsql code and there are no plpgsql commands outside plpgsql code blocks.

An EXCEPTION clause in a plpgsql block is only used to trap errors and do something before or instead of the ROLLBACK. Of course, it only makes sense to do something that ROLLBACK isn't going to undo - like raising a message which is never rolled back.
You don't need any of this for your demonstrated code.


You forget about DELIMITER:

DELIMITER $$

CREATE TRIGGER set_update_flag BEFORE UPDATE ON `reputation_email_score`
FOR EACH ROW
    BEGIN
        IF NEW.`score` <> OLD.`score` THEN
          SET NEW.`updated` = "yes";
        END IF;
END $$
DELIMITER ;

also ; after last END - not necessary


"Count the disk hits."

Let's construct a simplistic table and query against it.  Then let's discuss how two database engines will perform that query.

We have a table with lastname and firstname, plus other stuff.  The table is quite big; so big that it cannot be cached in RAM.  The query needs to look up all the people with a given last name and some first initial:

SELECT * FROM tbl
    WHERE lastname = 'James'
      AND firstname LIKE 'R%';

Plan A:  The table is ordered by lastname+firstname using a B+Tree.  The operation work like this:

Locate the first (James,R) row.  This requires drilling down the layers of a B+Tree of depth perhaps 4.  It is very likely that all non-leaf nodes in such a tree will stay cached.  But the leaf node won't be (because of the size of the atable).  Cost (so far):  0 disk hits for the non-leaf nodes, plus 1 disk hit for the leaf node.
Scan forward through the B+Tree.  Let's say the typical block in the tree holds 100 records.  Let's say there are 300 names that match.  Cost:  About 3 more disk hits.

Total cost:  ~4 disk hits.

Plan B:  There is an INDEX(lastname, firstname) to hash-pointer, which then leads to the data.

Traverse that index to find all the 300 matching names.  If the INDEX is something like a BTree, that is, again, ~4 disk hits.
For each of the 300 names, do a hash lookup in the data to get SELECT *.  Since the look up involves a hash, the records will be scattered around.  And, since I hypoticated that the table was too big to be cached, that is upwards of 300 disk hits to find the records.

Total cost:  Somewhere between 4 and 304; let's be generous and say 100.

Further, let's say there is 1ms of CPU for the entire query.  And let's say 10ms for HDD disk hit and 0.5ms for SDD disk hit.

Plan             HDD (ms)   SDD (ms)   RAM (ms, if the table were fully cached)
Plan A: B+Tree    41          3          1
Plan B: Hash    1001         51          1

(Side note: Indexing on UUID in any database engine is like throwing out BTree and using only Hash.)

Until SSDs become as fast as RAM, there will be a performance gap.

As your you Question 2 (Does it make sense to use SSDs):  If you are I/O-bound, then yes.  Otherwise, it may not be "cost-effective".  An RDBMS tries to cache everything, thereby eliminating I/O (for reads); if the data is small enough, the I/O speed does not matter.

On the other hand, writes are much more likely to be dependent on I/O speed.  But that is another discussion (with a similar conclusion).

Responding to question update:

Disclosure:  I am very familiar with MyISAM; Plan A is very close to MySQL's InnoDB.  I am very unfamiliar with Postgres; I do not know if Plan B relates to it.
Yes.
Yes.
It depends. A SELECT where everything it needs is cached in ram: no I/O.  A write in a transaction with setting at maximum security: always I/O.  And there is lots of "in between".


RMAN-03009: failure of backup command due to ORA-27038 and OSD-04010:

You must add unicity variable in the backup format name:
 backup incremental level 0 format 'F:\DATABASE_BACKUP\%d\%Y-%M-%D\FULL_%d_%T_%t_%U' database ;
As your backupset is composed of multiple backup pieces, they must have different names.


Postgresql - archive_cleanup_command with multiple standby servers

The built-in way to do this is to use replication slots and to do away with a WAL archive altogether.  Replication slots will cause the WAL to be retained in pg_xlog until all standbys have received it, rather than stored in the WAL archive directory.  So make sure you have enough space available for this purpose, i.e. assign the space currently set aside for WAL archive to be added to the available space for pg_xlog.

An alternative is to use "wal_keep_segments" rather than replication slots, and define it at a little less than how much space you have reserved for pg_xlog.  Any standbys which fall behind by more than this amount will be sacrificed, rather than being allowed to bring down the master.

For your other question, archive_cleanup_command cannot run on the master (it is set in recovery.conf, which the master doesn't have), nor would it make any sense for it to do so.


How do I perform a UNION query on a dynamic list of tables?

Yes you can. There are several ways to do this.

1> If you are using the database interactively, then just type in the query.
2> If the database is being accessed via a application or script, write a method in the application/script to create the query required and then run it. You would have a database call to get the list of table names, then create a string with the union query then execute that query.
3> In a similar vein to option 2, most stored procedure languages allow you to dynamically create a query as a string and then execute that string - Check out your DB's documentation to find out how to do this.

A Mysql example https://stackoverflow.com/questions/190776/how-to-have-dynamic-sql-in-mysql-stored-procedure. You may like to use a cursor ( http://dev.mysql.com/doc/refman/5.0/en/cursors.html ) to iterate though the list of tables names to Concat ( http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_concat ) together the query string.


Why can't I use variables in T-SQL like I imagine I can?

Per the Books online page for variables

  Variables can be used only in expressions, not in place of object
  names or keywords. To construct dynamic SQL statements, use EXECUTE.

It would work the way you were expecting if, for example, you used your variable in a where clause. As for why, I would think it has something to do with the parser not able to evaluate the variable and thus check for existence. When executing, the query is parsed first for syntax and objects and then, if parsing successful, the query executes at which point the variable would be set.

DECLARE @name varchar(20);
SET @name = 'test';

CREATE TABLE [#tmp]([val] varchar(10));

insert into #tmp
values('test')

SELECT *
FROM [#tmp]
WHERE [val] = @name;


How to update two tables using subquery in postgresql?

For example

UPDATE second_table
SET Currentstatus = 1
WHERE EXISTS ( SELECT 1
               FROM first_table
               WHERE first_table.Complaintid = second_table.Parent_complaint_id
                 AND first_table.templateid is not null )

Or

UPDATE second_table
SET Currentstatus = 1
FROM first_table
WHERE first_table.Complaintid = second_table.Parent_complaint_id
  AND first_table.templateid is not null

Or ... too many variants.


Why can't a committed transaction be undone

You can achieve that result in Oracle using flashback (https://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#g1026131).
IBM DB2 has a somewhat similar feature.
You could use database snapshots in older versions of SQL Server (https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server?view=sql-server-ver15) and newer versions have temporal tables (https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15).


You need to use Credentials:

  A credential is a record that contains the authentication information
  (credentials) required to connect to a resource outside SQL Server.
  This information is used internally by SQL Server. Most credentials
  contain a Windows user name and password.

  The information stored in a credential enables a user who has
  connected to SQL Server by way of SQL Server Authentication to access
  resources outside the server instance. When the external resource is
  Windows, the user is authenticated as the Windows user specified in
  the credential.

This way you can associate your SQL login that sends the mail with an NT credential that has file access to the attachment.


Use insert trigger to copy a modified NEW record to another table, with unknown columns

Looks like the underlying issue is that you're trying to pass the row the trigger is acting on as ANYELEMENT, which is for types; what you want here is RECORD.
Assuming you're using PostgreSQL 11 or later (example contains CALL syntax), this should work:
CREATE OR REPLACE FUNCTION increase_trigger()
  RETURNS TRIGGER
  LANGUAGE plpgsql
AS $$
DECLARE
    tbl_out VARCHAR;
BEGIN
    tbl_out := TG_TABLE_NAME || '_increase';
    CALL calc_increase(new, tbl_out);
    RETURN NEW;
END;
$$;

CREATE OR REPLACE PROCEDURE calc_increase(
   sample RECORD,
   tbl_out REGCLASS
)
  LANGUAGE plpgsql
AS $$
BEGIN
    sample.value = sample.value + 1.0;
    EXECUTE FORMAT('INSERT INTO %s VALUES ($1.*)', tbl_out) USING sample;
END;
$$;

Not tested for potential side-effects.


PostgreSQL (10) show all NOTICE messages

You can change log_min_messages  to notice which will then write all notices to the server's log file.

Note that a client can overwrite that setting any time, so it's not 100% reliable.


Good tool for simulating many threads doing INSERTS UPDATES SELECTS and DELETES on an ODBC database?

The IT shop I work for "bleeds blue" (ie, sticks mostly to IBM tooling), so the performance software we use is IBM's Rational Performance Tester (RPT). IBM's stuff isn't (usually) free or cheap, but RPT has helped us to populate our databases through our applications (mostly because the relationships among the tables is too complex to do nicely via script).

That being said, we are also considering using a tool like the following data-generator, in order to better be able to mass insert data into our database for performance testing.


Error when using WITH (DROP_EXISTING = ON)

This can happen when the current primary key is nonclustered, and there is also a clustered index:

CREATE TABLE dbo.MyTable
(
    MyTableID integer NOT NULL,
    RowStatus tinyint NOT NULL,
    SomeOtherKey integer,

    CONSTRAINT [PK_MyTable]
        PRIMARY KEY NONCLUSTERED (MyTableID, RowStatus),

    CONSTRAINT [CUQ_MyTable]
        UNIQUE CLUSTERED (SomeOtherKey)
);

CREATE Unique CLUSTERED INDEX PK_MyTable
    ON dbo.MyTable (MyTableID, RowStatus)
    WITH (DROP_EXISTING = ON);

  Msg 1902, Level 16, State 3, Line 14
  Cannot create more than one clustered index on table 'dbo.MyTable'. Drop the existing clustered index 'CUQ_MyTable' before creating another.

It is valid to change a primary key from nonclustered to clustered using DROP_EXISTING syntax:

CREATE TABLE dbo.MyTable
(
    MyTableID integer NOT NULL,
    RowStatus tinyint NOT NULL,
    SomeOtherKey integer,

    CONSTRAINT [PK_MyTable]
        PRIMARY KEY NONCLUSTERED (MyTableID, RowStatus),
);

CREATE Unique CLUSTERED INDEX PK_MyTable
    ON dbo.MyTable (MyTableID, RowStatus)
    WITH (DROP_EXISTING = ON);


Using surrogate key after merging the person's data?

A surrogate key is a system assigned unique value to identify an entity occurrence.  A natural key is what the business uses to identify an entity occurrence.  The source systems, as well as your BI/Data Integration database, can use either type to identify the entity occurrence - such as Jim Brown in your example.  In the source system we call what the source system uses to identify the entity occurrence a source key.  So if you can have 3 different source systems each of which contain Jim Brown, each will have a different source key in addition to the natural key - which you have identified as the SSN + birthdate.  The BI staging environment, which the ETL uses, will include a key map table which will map each source key to the assigned surrogate key for the BI database.  So for example:

Person  Table
Surrogate Key   Name            Birthday     SSN          Other fields
1                   Jim Brown   9-15-1988    123456789

Person Key Map
Surrogate Key   Source Key  Source System
1                   12345           System A
1                   230383          System B
1                   294829          System C

When a transaction against Jim Brown is to be processed, it will come from the source using the source key.  Say its system A with source key 12345 with an update to one of the other fields.  The ETL looks up the source key in the key map and finds it, and knows to apply that transaction to the row on Person with a surrogate key of 1.

If you only had 1 source system life would be easy.  But you have 3.  This is where the natural key comes in as you know you can identify Jim in any system by using these 2 characteristics that never change and are true about him.  Now, say you integrate System D.  The first transaction comes in for Jim Brown in system D, and system D's source key is AB2945.  Now when the ETL looks in the key map, it doesn't find it.  But since you know you have multiple systems that may contain a person, the ETL also does a lookup on the Person table for the natural key and voila - a match.  Now the ETL inserts a new row to the key map for System D also for Jim Brown.

So in summary you always want to use a surrogate key as the PK in the BI database table when doing data integration of the same logical entity among many sources.  You'll map it to the source key using a key map table, and prevent duplicates by looking up on the natural key in the BI table (along with an alternate unique key on the natural key to ensure no duplicates).  Now there are many other details to work out - like is your natural key really unique, do you want to store it in your key map to prevent look ups on Person, do you have to handle composite source keys, do you keep a history of source key changes, do you track a system of record and systems of reference, how do you handle source key reuse (a potentially very sticky issue in its own right), etc.  But this short summary gives you and idea of the differences between natural, surrogate, and source keys and where to use each in the BI scenario.  I hope this helps...


Mysql trigger to update if date match and insert if no match all BEFORE INSERT

"if the incoming date matches with existing date, it should update the table and add incoming value to existing value else it should just insert" -- You have just specified what INSERT ... ON DUPLICATE KEY UPDATE ... does.  Use that.

this trigger works but how to include the ON DUPLICATE KEY UPDATE
giving me tough time and dosent work out

DELIMITER $$
CREATE TRIGGER inc_trig
    BEFORE INSERT ON income_
FOR EACH ROW
BEGIN
    DECLARE fees,bus,uniform,c_uniform,sp_wear,pullover,car INT;

    SET fees=1; SET bus=2; SET uniform=3; SET c_uniform=4;
    SET sp_wear=5; SET pullover=6; SET car=7;

    IF NEW.income_name_ = fees THEN
    INSERT INTO income_summary_monthly
            (date_, fees_, month_, term_, schid_)
        VALUES
            (new.date_, new.amount_, new.month_, new.term_, new.schid_)
        ON DUPLICATE KEY UPDATE
            amount_ = VALUES(amount_);

// Or do you want
//          amount_ = amount_ + VALUES(amount_) ??

    ELSEIF NEW.income_name_ = bus THEN
    ...
    END IF;

END  $$
DELIMITER ;

I am a bit lost since the original code does not look much like this version.


You have mixed the REGEXP syntax for alternatives (first|second) and for symbol classes [abcd]. Your query should look like that:

SELECT *
  FROM tbl_json
 WHERE json_data REGEXP '"14":"(1|2)"'
   AND json_data REGEXP '"16":"(39|7)"'
 LIMIT 3;


How can I Estimate Table Sizes within Schema (Oracle)

Look at the "dba_segments" view (or user_segments if you don't have dba rights). The following query should give you what you're looking for:

select
  owner as "Schema"
  , segment_name as "Object Name"
  , segment_type as "Object Type"
  , round(bytes/1024/1024,2) as "Object Size (Mb)"
  , tablespace_name as "Tablespace"
from dba_segments
order by owner;


There are a couple of ways to pivot the data to get the output you want. There's the PIVOT operator, demonstrated here: https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server

The syntax for PIVOT can be difficult to remember. A simpler method that usually performs just as well is to nest CASE expressions inside SUM:

SELECT userid
, SUM(CASE WHEN YEAR(d1) = 2015 THEN amt ELSE 0 END) AS [2015 TA]
, SUM(CASE WHEN YEAR(d1) = 2015 THEN inflation ELSE 0 END) AS [2015 TI]
, SUM(CASE WHEN YEAR(d1) = 2016 THEN amt ELSE 0 END) AS [2016 TA]
, SUM(CASE WHEN YEAR(d1) = 2016 THEN inflation ELSE 0 END) AS [2016 TI]
FROM @information
WHERE d1 BETWEEN '01/01/2015' AND '12/31/2016'
GROUP BY userid
ORDER BY userid

It accomplishes the same pivot without the complexity of the PIVOT operator.


You can use the data dictionary to check whether the column exists before trying to drop it

DECLARE
  l_cnt integer;
BEGIN
  SELECT COUNT(*)
    INTO l_cnt
    FROM dba_tab_columns
   WHERE owner = <<table owner>>
     AND table_name = 'MY_PROPERTY_LOCK'
     AND column_name = 'PROP';

  IF( l_cnt = 1 )
  THEN
    EXECUTE IMMEDIATE 'ALTER TABLE my_property_lock DROP COLUMN prop';
  END IF;
END;

My bias, though, would be to record somewhere what version of the schema is installed and to determine what DDL to execute based on that.  For example, if you have a VERSION table that tells you that the current installed version is 1.4 and you want to drop the column when you move to version 1.5, you can just execute the DROP without worrying that it might not exist.