SQLAgentReaderRole and SQL AD ID login case affecting ability to edit jobs

Is there anyway to create users with the correct Windows AD ID cases
  so that I'm not having to figure it out then modify the logins for
  each AD ID user?

Built in, you already found it -- use the directory searching interface in the GUI. Alternatively, you could use SQL CLR (as mentioned), or write a PowerShell script. You could certainly write a routine to clean up an instance using either method.

Note that the GUI rewrites the domain name, even if it's incorrectly cased when entered, but not the login name itself. (You can see this by manually typing something in, then hitting the Script button.)

  But it was returning all lowercase for me, yet all uppercase when I
  asked the user to run SELECT SUSER_SNAME() and give me the result. The
  correct is all uppercase (...).

Under impersonation, SUSER_SNAME() returns the principal name as entered. If you try the test again with a different casing, it will return the exact name you typed in. The only reason the impersonation works using an incorrectly-cased name is because the Windows collation says the comparison of the two is a match.

Not under impersonation, there are two cases: (a) group login: the principal name is returned from what Windows supplies, which is always correctly-cased, and (b) individual login: the principal name is returned from what is entered for the name in SQL Server (it doesn't go out to Windows to double-check).

Now, having said all that, try as I might, I cannot replicate this problem using 2008 R2 management tools. If you're still running 2008 (non-R2) management tools, one option would be to try upgrading the tools, because this appears to be a bug in Management Studio itself.


postgresql function index not being used by query

Your WHERE condition is not selective enough. It returns 1631 rows which is 1/5 of the whole table, while it should be not more than 1/20 for an index to be used.


Moving to Production database for first time

Our IT team is saying that if we move to production... [users] can access the database only via API or Tableau or reporting services. is that so?

This very much sounds like a policy. From technical point of view, there is no reason why direct connections would not be possible. That being said, production admins are responsible for the production environment not going haywire, so this kind of restrictions are quite common in the industry. The current buzzword DevOps is pushing this limit a bit, but on more traditionally oriented houses there is little if any change.

The reason why production staff are quite strict about the access limits is simple. If something breaks in the production, it's their fault. Even if the real culprit was a developer running a cross-join-o-matic, dropping indexes and generally being a clown. Production admins are supposed to have the environments running at top performance, and letting those down for any reason is a sign for problems.

For a solution, talk with your team lead and boss. Write a memo that outlines the pros and cons the new method would introduce. Don't just list the obstacles, tell what's good too and propose solutions. The memo should give an impression that you are a problem solver, not a whiny complainer. After that, it's your boss' call what to do.

  Can I connect to the production database via SSMS?

In theory, yes. There might be firewalling or company policy that prevents such an action. Do not try to work around policies or technical barriers on your own! You might get into serious trouble.

  If I can't connect to the production server, how to develop ssis packages ?

Develop the packages on development environment. Push those to the test and let production admins move packages into the production.


Can we decompose a schema into BCNF when it is not even in 1NF

Always hit it at first shot? It is not guaranteed. You apply normalization rules to remove anomalies from relations. Relations rise to a more normal form. You observe for anomalies again and if there are anomalies apply rules again. It is possible to reach most normal form possible for the relation at first move (as you know there is a limit for normalization of any relation. Ron Fagin showed that it is often impossible to achieve DKNF form) but not always. Ron Fagin's paper may help.


I would say that an Edition is an entity that is related to a Book.

You then have a one-to-many relationship between Books and Editions.  (One book can have many editions.)  and a many-to-one relationship between Editions and Publishers  (many editions are published by one publisher).

When I was building actual databases, back in the day, I always had access to one or more "subject matter experts",  people who had become familiar with the data through constant use.  Most subject matter experts were neither computer experts nor database experts.  Your situation is somewhat different.

I would try to find some kind of on line subject matter expert, like the Library of Congress.  They might have some kind of model of the publishing industry that would provide answers to this and other questions you have regarding how the field of publishing really works.  And this model might be in the public domain.

PS:  Congratulations on using the ER model as an analysis tool,  and not diving into database design prematurely.


Answers to your questions, in order:

First point: I'm not sure what you're getting at here.  A measure can only be something that will display in a single cell.  You can aggregate stuff up to create a measure.  You can also have multiple fact tables (called measure groups) in a cube.  If you slice by common attributes they measure groups will slice by those attributes.  If you slice by something that one of the measure groups does not have in common, the values will be repeated for each slice of the other group.
Yes.  You can order by attributes or values of measures.
Probably OK.  I can't see any harm in that.  Can't see a lot of point in it either, but if you really only have one drill down path there's no reason that why that can't be implemented in a cube.
Yes, you can do distributed partitions.  Never built a system with remote paritions personally, but there's a technet article here..
SQL Server will do distributed partitioned views over partitions on multiple separate instances with Enterprise Edition.  There is a shared-nothing version as well, but you can only buy that in a hardware bundle from an OEM.  It doesn't seem to be getting a lot of traction in the market.


How to copy wt files from another mongo instance?

The WiredTiger.wt file is a crucial metadata file that keeps track of the state of the whole database and all the *.wt files. Hence, the content of this file is deployment specific, and not transferable to other deployments. It is not possible to recreate the content of this file.

If you have a known good backup of the whole dbpath, you may be able to restore to a valid state of the database, as long as you restore the dbpath as a whole.

Please see MongoDB Backup Methods for instructions and information on supported backup and restore methods.

For further your ref here, here and here


How do I copy a SQL Server Database Schema and Objects between servers using VS2019

not exactly database project solution, but might be able to help you out with the filegroups if you don't find any other way.
dbatools offer this function - Export-DbaScript.
With that, you could try to export any object you want using SMO.
# set the option to not include Filegroup information
$options = New-DbaScriptingOption
$options.NoFileGroup = $true

# get all the tables from MyDatabase on the localhost
# generate script using $options defined above
Get-DbaDbTable -SqlInstance localhost -Database MyDatabase | Export-DbaScript -ScriptingOptionsObject $options


Have you tried using CTEs? I've found several times that reducing the size of my data sets before attempting the joins can lead to a big improvement in query times.

with
    __d as(
        select
            "Selling_date",
            "Value_in_EUR",
            "Value_in_currency",
            "Site",
            "Internal_reference",
            "Customer_code"
        from
            report.get_sa001('2016-01-01'::date, '2017-03-31'::date, 32)
    ),
    __a as(
        select
            "Selling_currency",
            "Site",
            "Reference_internal" as "Internal_reference",
            "Customer_code"
        from
            report."Axis_RefCustomer"
        where
            ("Site", "Reference_internal", "Customer_code") in(
                select
                    "Site",
                    "Internal_reference",
                    "Customer_code"
                from
                    __d
            )
    )
select
    __d."Selling_date",
    __a."Selling_currency",
    __d."Value_in_EUR",
    __d."Value_in_currency",
    __d."Site"
from
    __d
    left join __a using("Site", "Internal_reference", "Customer_code")


1871183871663 is bigger than 1871183322646. You do not have the necessary archivelog in the backups for recovering the database beyond SCN 1871183322646.
You simply specified a point in time recovery target that is not included in the backups.


VLF count is only going to be a problem for recovery times if the combination of the count of VLFs and their size means the recovery process will require longer duration than your recovery-time-objective.

There is no single number of VLFs that is good or bad.  Very generally, I keep each VLF under 512MB, and keep the number of VLFs well under 1,000.

In order to know the actual recovery time for a given log file, you'll need to have identical hardware setup in a test environment, and test recovery of a full log where the database has been shutdown while active transactions are running.  If you have a database with hundreds of thousands of VLFs, be very scared about recovery time.  Ask me how I know.

You can view current info about VLFs by looking at DBCC LOGINFO in the context of the desired database.  The output from that command looks like:

╔════════════════╦════════╦═══════════╦═════════════╦════════╦════════╦════════╦═══════════╗
║ RecoveryUnitId ║ FileId ║ FileSize  ║ StartOffset ║ FSeqNo ║ Status ║ Parity ║ CreateLSN ║
╠════════════════╬════════╬═══════════╬═════════════╬════════╬════════╬════════╬═══════════╣
║ 0              ║ 2      ║ 268369920 ║ 8192        ║ 34     ║ 2      ║ 64     ║ 0         ║
║ 0              ║ 2      ║ 268369920 ║ 268378112   ║ 0      ║ 0      ║ 0      ║ 0         ║
║ 0              ║ 2      ║ 268369920 ║ 536748032   ║ 0      ║ 0      ║ 0      ║ 0         ║
║ 0              ║ 2      ║ 268369920 ║ 805117952   ║ 0      ║ 0      ║ 0      ║ 0         ║
╚════════════════╩════════╩═══════════╩═════════════╩════════╩════════╩════════╩═══════════╝

The Status column indicates the status of the given VLF, where 2 indicates the VLF has active log records and cannot be re-used until those records are backed-up/truncated (or in the case of mirroring, written to the mirror's log file).  Shrinking a log file (which is not typically recommended unless you have a problem) cannot reduce the size of the file below the point occupied by the last VLF with a Status of 2.  In my example above, shrinking the log file will allow me to get rid of the last 3 VLFs, however, I cannot shrink the 1st VLF since it is status 2.  If the 4th VLF had a status of 2, I would not be able to shrink the logfile at all until the log had been backed-up (in full recovery) or truncated (in simple recovery).

You can see file size and growth settings using this query:

SELECT [DB Name] = d.name
    , [File Name] = mf.name
    , mf.physical_name
    , Size = mf.size * 8192E0 / 1048576
    , MaxSize = mf.max_size * 8192E0 / 1048576
    , Growth = mf.growth * 8192E0 / 1048576
    , [Recovery Model] = CASE WHEN mf.type = 1 THEN d.recovery_model_desc ELSE '' END
    , [Log Reuse Wait Reason] = CASE WHEN mf.type = 1 THEN d.log_reuse_wait_desc ELSE '' END
FROM master.sys.databases d
    INNER JOIN master.sys.master_files mf ON d.database_id = mf.database_id
WHERE d.name = DB_NAME()
ORDER BY d.name
    , mf.type
    , mf.name;

Sample output for the query above looks like:

╔══════╦══════════╦═════════════════════════╦══════╦═════════╦════════╦══════════╦═════════════╗
║ DB   ║ File     ║ physical_name           ║ Size ║ MaxSize ║ Growth ║ Recovery ║ Log Reuse   ║
║ Name ║ Name     ║                         ║      ║         ║        ║ Model    ║ Wait Reason ║
╠══════╬══════════╬═════════════════════════╬══════╬═════════╬════════╬══════════╬═════════════╣
║ Test ║ MyFile   ║ D:\Data\Test_MyFile.bak ║ 125  ║ 1000    ║ 100    ║          ║             ║
║ Test ║ Test_DB  ║ D:\Data\Test_DB.mdf     ║ 384  ║ 2048    ║ 128    ║          ║             ║
║ Test ║ Test_Log ║ D:\Logs\Test_DB.ldf     ║ 256  ║ 2048    ║ 128    ║ SIMPLE   ║ NOTHING     ║
╚══════╩══════════╩═════════════════════════╩══════╩═════════╩════════╩══════════╩═════════════╝

You can see log space usage for SQL Server 2012+ using this query:

SELECT su.database_id
    , LogSizeMB = su.total_log_size_in_bytes / 1048576E0
    , LogUsedMB = su.used_log_space_in_bytes / 1048576E0
    , LogUsedPercent = su.used_log_space_in_percent
FROM sys.dm_db_log_space_usage su;

The sys.dm_db_log_space_usage output looks like:

╔═════════════╦═════════════╦═══════════╦════════════════╗
║ database_id ║ LogSizeMB   ║ LogUsedMB ║ LogUsedPercent ║
╠═════════════╬═════════════╬═══════════╬════════════════╣
║ 7           ║ 255.9921875 ║ 6.171875  ║ 2.410962       ║
╚═════════════╩═════════════╩═══════════╩════════════════╝

SQL Server 2016+ includes a Dynamic Management Function named sys.dm_db_log_info, which provides details about VLFs.

An example of how to use it:

DECLARE @DatabaseId tinyint = DB_ID('msdb');
SELECT DatabaseName = d.name
    , FileName = mf.physical_name
    , ddli.vlf_begin_offset
    , ddli.vlf_size_mb
    , ddli.vlf_active
    , ddli.vlf_status
FROM sys.dm_db_log_info (@DatabaseId) ddli
    INNER JOIN sys.databases d ON ddli.database_id = d.database_id
    INNER JOIN sys.master_files mf ON ddli.file_id = mf.file_id AND ddli.database_id = mf.database_id
ORDER BY d.name
    , mf.file_guid;

The output looks like:

╔══════════════╦══════════════════════════════╦══════════════════╦═════════════╦════════════╦════════════╗
║ DatabaseName ║           FileName           ║ vlf_begin_offset ║ vlf_size_mb ║ vlf_active ║ vlf_status ║
╠══════════════╬══════════════════════════════╬══════════════════╬═════════════╬════════════╬════════════╣
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         31064064 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         41549824 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         52035584 ║          10 ║          1 ║          2 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         62521344 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         73007104 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         83492864 ║          10 ║          0 ║          0 ║
║ msdb         ║ F:\Data\msdb\log\msdblog.ldf ║         93978624 ║       10.37 ║          0 ║          0 ║
╚══════════════╩══════════════════════════════╩══════════════════╩═════════════╩════════════╩════════════╝

For aggregate details about the log, sys.dm_db_log_stats provides great info:

SELECT *
FROM sys.dm_db_log_stats(@DatabaseId)

Output includes the following columns:

database_id
recovery_model
log_min_lsn
log_end_lsn
current_vlf_sequence_number
current_vlf_size_mb
total_vlf_count
total_log_size_mb
active_vlf_count
active_log_size_mb
log_truncation_holdup_reason
log_backup_time
log_backup_lsn
log_since_last_log_backup_mb
log_checkpoint_lsn
log_since_last_checkpoint_mb
log_recovery_lsn
log_recovery_size_mb
recovery_vlf_count

For further details, I wrote a blog post on SQLServerScience.com about sizing your log file for the optimal number of VLFs.


Does a NULL DATE use as much storage as a value?

If you are using MyISAM, don't.
If you found that DATETIME is 8 bytes, you are using an old version of MySQL; it takes only 5 bytes now.  Upgrade.
"Fixed" is slightly important for MyISAM; rarely important for InnoDB.  So don't worry.  In particular, don't worry unless you have a billion rows.


The first primer which will save you research is by Martin Fowler.
NoSQL Distilled.  Available on Kindle from Amazon.

Reviews the types of NoSQL databases.

Key Value Pair {Redis, Raik}, Uses {Web Sessions, Shopping Cart, User Profiles}
Document {MongoDB, CouchDB...}, Uses {EventLogging, CMS, Blog}
Column {Cassandra, Hbase...}, Uses {EventLogging, CMS, Blog}
Graph {Neo4j,FlockDB...},Uses {Connected data, location based}

Each of the databases have specific use cases.  Document and Column will store more of Domain models like you would see in your code.  (Customer, Orders, OrderDetails)

You can get Mongo Up and running in short order.
View at docs.mongodb
- Windows
- OSX
- Linux

Once you code up a few command line apps to push data and query it out you can start to build your RESTful services against the mongodB.

To move to the intermediate and advanced you can visit 10gen for Videos and books for monogdB.

mongly.com - Has some free items for you to read as well.

Start with one NoSQL and then you can move to a few others.  Document and Column would be a good starting point, we choose mongo and then Redis.  That's the path we took when moving off of SQL Server.


Connecting to an SQL Azure Database from SSMS - IP Restriction

You don't want to connect to Windows Azure SQL Database by using an IP address, you will want to use the logical servername you are assigned (something like fooxxxbar.database.windows.net).

Also, WASD only uses port 1433, you can't configure that (not today, anyway).

The firewall rules for WASD are for the client connections. When you first create your SQL database you will be prompted to create firewall rules and your current IP address will be listed as an example of an IP to allow.

HTH


sql server replication - how to find and drop subscriptions?

You need to look at the actual catalog/metadata table(s) and clean them up. The GUI and stored procedures often don't cleanup the underlying tables properly. So, after you have run the sp_dropsubscription SP run a select from MSsubscriptions and MSsubscriptions to see if there is any data referring to your unwanted subscriptions. See: msdn.microsoft.com/en-us/library/ms179855(v=sql.105).aspx.
Delete any row(s) representing subscriptions that shouldn't be there and it should resolve the problem. You may need to set sp_configure 'Allow Updates' = 1 to make changes to the table(s).


The right configuration is:

port = 52000

under a [server] or [mysqld] section.

Your most likely problem is that either you are modifying a configuration file that is not being included, or it is being included, but other file is overriding your config (last parsed option wins). Look for other files containing the port definition and delete them, or make sure they are read in the right order.

To check which mysqld config files are applied, you can run mysqld --verbose --help to verify which config files are read. More suggestions at https://stackoverflow.com/questions/580331/determine-which-configuration-file-is-being-used


How to get the size of an index created in a plpgsql block

My idea is to use execute and rollback then. I would suggest you to read it "what are the difference between perform and execute on PL/pgSQL?"

DO
$$
declare
  s int;
begin
  execute 'create index ix on t(n)';
  select pg_relation_size('ix') into s;
  raise notice 'size is %', s;
  select 1/0; -- This is for ROLLBACK
END
$$;

NOTICE:  size is 8192
ERROR:  division by zero
CONTEXT:  SQL statement "select 1/0"
PL/pgSQL function inline_code_block line 8 at SQL statement


MySQL: Rank by Department in an employees table

PROPOSED SOLUTION

SET @currhash = MD5('dummy');
SELECT A.id,B.deptname,B.hours,A.rank
FROM (SELECT
    (@prevhash := @currhash),
    (@currhash := MD5(depttype)),
    (@rnk:=IF(@prevhash=@currhash,@rnk+1,1)) rank,
    id,depttype FROM
    (SELECT id,LEFT(deptname,LEAST(
    IF(LOCATE('0',deptname)>0,LOCATE('0',deptname),99),
    IF(LOCATE('1',deptname)>0,LOCATE('1',deptname),99),
    IF(LOCATE('2',deptname)>0,LOCATE('2',deptname),99),
    IF(LOCATE('3',deptname)>0,LOCATE('3',deptname),99),
    IF(LOCATE('4',deptname)>0,LOCATE('4',deptname),99),
    IF(LOCATE('5',deptname)>0,LOCATE('5',deptname),99),
    IF(LOCATE('6',deptname)>0,LOCATE('6',deptname),99),
    IF(LOCATE('7',deptname)>0,LOCATE('7',deptname),99),
    IF(LOCATE('8',deptname)>0,LOCATE('8',deptname),99),
    IF(LOCATE('9',deptname)>0,LOCATE('9',deptname),99))-1) depttype
FROM dept ORDER BY depttype,hours DESC) AA) A
LEFT JOIN dept B USING (id)
ORDER BY id;

YOUR SAMPLE DATA

use test
DROP TABLE IF EXISTS dept;
CREATE TABLE dept
(
    id int not null,
    deptname varchar(32),
    hours INT NOT NULL,
    PRIMARY KEY (id)
);
INSERT INTO dept VALUES
(1003,'sales1'     ,  2),(1004,'sales2'     ,  6),(1005,'sales3     ',  4),
(1006,'sales4'     ,  3),(1007,'operations1',  5),(1008,'operations2', 10),
(1009,'operations3',  7),(1010,'operations4',  8),(1011,'operations5',  4);

YOUR SAMPLE DATA LOADED

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS dept;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE dept
    -> (
    ->     id int not null,
    ->     deptname varchar(32),
    ->     hours INT NOT NULL,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO dept VALUES
    -> (1003,'sales1'     ,  2),(1004,'sales2'     ,  6),(1005,'sales3     ',  4),
    -> (1006,'sales4'     ,  3),(1007,'operations1',  5),(1008,'operations2', 10),
    -> (1009,'operations3',  7),(1010,'operations4',  8),(1011,'operations5',  4);
Query OK, 9 rows affected (0.01 sec)
Records: 9  Duplicates: 0  Warnings: 0

mysql>

PROPOSED SOLUTION EXECUTED

mysql> SET @currhash = MD5('dummy');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT A.id,B.deptname,B.hours,A.rank
    -> FROM (SELECT
    ->     (@prevhash := @currhash),
    ->     (@currhash := MD5(depttype)),
    ->     (@rnk:=IF(@prevhash=@currhash,@rnk+1,1)) rank,
    ->     id,depttype FROM
    ->     (SELECT id,LEFT(deptname,LEAST(
    ->     IF(LOCATE('0',deptname)>0,LOCATE('0',deptname),99),
    ->     IF(LOCATE('1',deptname)>0,LOCATE('1',deptname),99),
    ->     IF(LOCATE('2',deptname)>0,LOCATE('2',deptname),99),
    ->     IF(LOCATE('3',deptname)>0,LOCATE('3',deptname),99),
    ->     IF(LOCATE('4',deptname)>0,LOCATE('4',deptname),99),
    ->     IF(LOCATE('5',deptname)>0,LOCATE('5',deptname),99),
    ->     IF(LOCATE('6',deptname)>0,LOCATE('6',deptname),99),
    ->     IF(LOCATE('7',deptname)>0,LOCATE('7',deptname),99),
    ->     IF(LOCATE('8',deptname)>0,LOCATE('8',deptname),99),
    ->     IF(LOCATE('9',deptname)>0,LOCATE('9',deptname),99))-1) depttype
    -> FROM dept ORDER BY depttype,hours DESC) AA) A
    -> LEFT JOIN dept B USING (id)
    -> ORDER BY id;
+------+-------------+-------+------+
| id   | deptname    | hours | rank |
+------+-------------+-------+------+
| 1003 | sales1      |     2 |    4 |
| 1004 | sales2      |     6 |    1 |
| 1005 | sales3      |     4 |    2 |
| 1006 | sales4      |     3 |    3 |
| 1007 | operations1 |     5 |    4 |
| 1008 | operations2 |    10 |    1 |
| 1009 | operations3 |     7 |    3 |
| 1010 | operations4 |     8 |    2 |
| 1011 | operations5 |     4 |    5 |
+------+-------------+-------+------+
9 rows in set (0.00 sec)

mysql>

PROPOSED SOLUTION FOR TIES (BONUS)

SET @currhash = MD5('dummy');
SET @curhours = -1;
SET @rank = 0;
SET @rnk = 0;
SELECT A.id,B.deptname,B.hours,A.rank
FROM (SELECT
    (@prevhash := @currhash),
    (@currhash := MD5(depttype)),
    (@curhours := IF(@prevhash=@currhash,@curhours,-1)),
    (@rnk      := IF(@prevhash=@currhash,@rnk+1,1)),
    (@rank     := IF(@curhours=hours,@rank,@rnk)) rank,
    (@curhours := hours),
    id,depttype FROM
    (SELECT id,LEFT(deptname,LEAST(
    IF(LOCATE('0',deptname)>0,LOCATE('0',deptname),99),
    IF(LOCATE('1',deptname)>0,LOCATE('1',deptname),99),
    IF(LOCATE('2',deptname)>0,LOCATE('2',deptname),99),
    IF(LOCATE('3',deptname)>0,LOCATE('3',deptname),99),
    IF(LOCATE('4',deptname)>0,LOCATE('4',deptname),99),
    IF(LOCATE('5',deptname)>0,LOCATE('5',deptname),99),
    IF(LOCATE('6',deptname)>0,LOCATE('6',deptname),99),
    IF(LOCATE('7',deptname)>0,LOCATE('7',deptname),99),
    IF(LOCATE('8',deptname)>0,LOCATE('8',deptname),99),
    IF(LOCATE('9',deptname)>0,LOCATE('9',deptname),99))-1) depttype,hours
FROM dept ORDER BY depttype,hours DESC) AA) A
LEFT JOIN dept B USING (id)
ORDER BY id;

GIVE IT A TRY !!!

NOTE : Why this works

All the department names are unique. I needed to strip off the digits on the far right.

I did that with the innermost subquery

SELECT id,LEFT(deptname,LEAST(
    IF(LOCATE('0',deptname)>0,LOCATE('0',deptname),99),
    IF(LOCATE('1',deptname)>0,LOCATE('1',deptname),99),
    IF(LOCATE('2',deptname)>0,LOCATE('2',deptname),99),
    IF(LOCATE('3',deptname)>0,LOCATE('3',deptname),99),
    IF(LOCATE('4',deptname)>0,LOCATE('4',deptname),99),
    IF(LOCATE('5',deptname)>0,LOCATE('5',deptname),99),
    IF(LOCATE('6',deptname)>0,LOCATE('6',deptname),99),
    IF(LOCATE('7',deptname)>0,LOCATE('7',deptname),99),
    IF(LOCATE('8',deptname)>0,LOCATE('8',deptname),99),
    IF(LOCATE('9',deptname)>0,LOCATE('9',deptname),99))-1) depttype
FROM dept ORDER BY depttype,hours DESC) AA

I designed it so that it will strip everything to the right of the first digit in the deptname. That will handle two-digit or three-digit numbers in deptname. After doing so, you end with two distinct values: sales and operations. I used this as the department type, which is aliased as depttype.

I could then iterate using local variables on the current depttype. I would also reset the ranking back to 1 when the depttype changes while iterating. The ranking technique I used is borrowed from the algorithm that I posted in Get the rank of a user in a score table. I implemented it to mechanically group by depttype and rank by hours.


How to change the mount point for a column in postgresql table?

Maybe I'm missing something, but it sounds as if that is a simple case of replacing the value:

update the_table
   set link = replace(link, '/ABC/ABC_DATA/Test/', '/CBF/CBF_DATA/Documents/Test_DATA/');


How can I model and enforce constraints on a categorical item contained in a collection?

Categories and Buckets.

create table categories
(
  category_id int primary key
);

create table buckets
(
  bucket_id int primary key
);

Depending on how to interpret "an item may belong to at most 1 bucket" you need to do some different things with Items.

An item has to belong to one bucket.

create table items
(
  item_id int primary key,
  category_id int not null references categories(category_id),
  bucket_id int not null references buckets(bucket_id),
  unique (category_id, bucket_id)
);

If an item may belong to one bucket but does not have to you need to allow for null values in bucket_id and use a filtered index as the unique constraint.

create table items
(
  item_id int primary key,
  category_id int not null references categories(category_id),
  bucket_id int references buckets(bucket_id)
);

create unique index uq_items on items(category_id, bucket_id) where bucket_id is not null;

  There is a collection of items where each item belongs to 1 category.

category_id as a foreign key in items.

  an item may belong to at most 1 bucket.

bucket_id as a foreign key in items.

  There is a collection of buckets which may contain at most 1 item from
  each category.

A unique constraint on bucket_id and category_id in items.


In this particular example the temp table is created in schema pg_temp_6. create_reference_table tries to create the exact same schema in the worker, and fails.

Could you just create a regular reference table, and drop it when you are done instead of creating a temporary table ?


High delay lag between master/slave in Postgres 9.3

In other servers with a lot of traffic the delay is small.

By this statement, I am assuming that this server does not have a lot
of traffic.

If there are no transactions to replay, then the value of pg_last_xact_replay_timestamp() will not advance merely due to the passage of time.  Also, archive_timeout will only kick in when there is something to archive.  If there is no new WAL data since the last archive switch, then the existing WAL file (which is empty except for the initial header) will not be archived just because 5 minutes have passed.

A possible work around is to set checkpoint_timeout to 300 seconds as well.  In 9.3 with this setting, the checkpoint will create records to be archived, and the archival will create work to be checkpointed, so you will generate a nearly empty file every 5 minutes on an idle server.  Most people consider this to be a bug, but perhaps it is a feature for you.


What Microsoft SQL Server 2008 R2 features do you need to install to get OLEDB Provider Linked Table Connectivity?

The link below has the download you need:

Microsoft OLE DB Provider for Visual FoxPro 9.0

  The Visual FoxPro OLE DB Provider (VfpOleDB.dll) exposes OLE DB interfaces that you can use to access Visual FoxPro databases and tables from other programming languages and applications.


How do I write a query that gives me 2 results from the same table?

Answer provided by mustaccio in a comment:
COUNT(uniqueid) Total_Dialed_Calls,
COUNT(CASE disposition WHEN 'ANSWERED' THEN 1 END) Total_Connected_Calls...


How to resolve MDF and LDF files remaining in use?

The Restart-Service cmdlet sends a stop message and then a start message to the Windows Service Controller for a specified service. If you specify -force parameter, it means override restrictions that prevent the command from succeeding,  e.g. stop and restart a service that has dependent services.

However, Windows Server manager launched via services.msc you mentioned is not running with force parameter when you click restart button there.

Thus if you want to do that, either stop the dependent services first, then retry or use the PowerShell command line to restart.


Create Oracle DB LINK to a database with no DB_DOMAIN

Can you show us the content of your sqlnet.ora file ?
The domain name should be listed there :

NAMES.DEFAULT_DOMAIN = domain.com.xxx

In case you don't have one , create a new sqlnet.ora file using the netca tool.
- after you create that file edit it and add the entry with you domain name!


SchemaID like '%[^a-zA-Z0-9-_]%' is going to be problematic no matter what you do.

Having said that, if you added an index like this:

CREATE INDEX IX_FormSchema
ON dbo.FormSchema(SchemaID)
INCLUDE (AccountID, SchemaID, Name, Description, CurrentVersion);

And change the query to:

WHERE SchemaID like '[^a-zA-Z0-9-_]%'

you might get an index seek.

Having said that, an index scan is not inherently a bad thing.  In fact, SQL Server is choosing to do an index scan because it believes that is the fastest way to get your results.


selecting complete rows grouped by one column based on the maximum of another

general answer

SELECT t1.* from [table] t1
JOIN (SELECT column1, MAX(column3) as max_c3 from [table] group by column1) t2
ON t1.column1 = t2.column1 AND t1.column3 = t2.max_c3

how is it work -

SELECT column1, MAX(column3) as max_c3 from [table] group by column1) t2

prepare all rows with correct column1 and MAX(column3)

JOIN ON t1.column1 = t2.column1 AND t1.column3 = t2.max_c3

correctly select other columns corrected for result,

if table have more than 1 row with same MAX(column3) - query return 1 row for each MAX(column3) result.

Example data:

id; column1; column2; column3;column4
1   10  50  1   1
2   10  60  2   2
3   12  50  3   3
4   12  60  4   4
5   11  70  5   5
6   10  99  6   6
7   11  55  7   7
8   10  60  6   8

result:

id; column1; column2; column3;column4
8   10  60  6   8   10  6
6   10  99  6   6   10  6
7   11  55  7   7   11  7
4   12  60  4   4   12  4

if delete from table row with id=8 (remove duplicates for max(column3)), result will be:

id; column1; column2; column3;column4
6   10  99  6   6   10  6
7   11  55  7   7   11  7
4   12  60  4   4   12  4


Where do I set the I/O size for DB2 9.7 LUW driver (AIX 6.1)?

The communication buffer size for remote clients is determined by the RQRIOBLK database configuration parameter (more info in the manual).

However, the statement you quote is overly simplistic and does not make much sense for DB2 configuration. Firstly, DB2 will perform direct disk I/O, so the OS read-ahead setting, if such thing even exists, will not have any effect on DB2. Secondly, DB2 will determine prefetch size, which is what you might call the DB2's answer to read-ahead, dynamically during each query compilation, so the number of pages read with each I/O will be different for different tables and even different queries against the same table. Thirdly, if your query has a WHERE clause, not all that is read from disk will be returned to the client, obviously, so the prefetch size will hardly matter at all.

You'll be better off, instead of trying to follow some very vague and generic advice, determine the actual bottleneck in your system and address that.


Send data from SQL to a serial port or IP address

No SQL Server will not be able to write directly to a serial port or TCP socket.  You'll need to either write an application that pulls the data from the SQL Server, or a SQL CLR procedure that calls out to the TCP IP address and socket.

I'd recommend using an app to query the SQL Server database then write that data to the serial port or TCP socket.


Cursor to insert .pdf files into a table

I created a directory called c:\TestPdf and created two txt files with some text in them (you would use your pdf files)

123456789.txt
987654321.txt

From your example, my assumption is you want file 123456789.txt inserted as ID 123 and you want file 987654321.txt inserted as ID 987.

This dynamic SQL worked for me - you would need to test against your data

set nocount on
IF OBJECT_ID('dbo.PDFTest', 'U') IS NOT NULL
  DROP TABLE dbo.PDFTest;
Create table PDFTest (NumExample integer,image_data VARBINARY(MAX))
DECLARE @FileTable TABLE (FileName VARCHAR(max),DepthColumn INT,FileColumn INT)

INSERT INTO @FileTable
EXEC master.sys.Xp_dirtree 'c:\testpdf',0,1

DECLARE @cmd VARCHAR(max)
DECLARE @FileName VARCHAR(max)

DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT FileName
FROM @FileTable

OPEN _CURSOR

FETCH NEXT
FROM _CURSOR
INTO @FileName

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @cmd = '
INSERT INTO PDFTest(NumExample ,image_data)
SELECT ' + substring(@FileName, 1, 3) + ',image_data
FROM OPENROWSET(BULK N''c:\testpdf\' + @FileName + ''',SINGLE_BLOB) AS ImageSource(image_data)
'

    PRINT @cmd

    EXEC (@cmd)

    FETCH NEXT
    FROM _CURSOR
    INTO @FileName
END --End While

CLOSE _CURSOR

DEALLOCATE _CURSOR

SELECT *
FROM pdftest


Are #tables and #procedures 'not best practice' or 'bad practice'?

I doubt that the example you provide is a valid use case for temporary procedures (I don't see any benefit here to using #temp procedures over permanent procedures), but for #temp tables, which have a much wider set of use cases, the only way to fight these arguments with policy-setters is to run the code - using a full load and during typical workload activity - without using the #temp tables. If you can demonstrate to them that even one of these processes works worse without the #temp table, then that should be a valid counter. If they still won't listen, then this is not a question for this site - maybe workplace.


Try using the FIELD function's return value and make sure it is not zero:

SELECT * FROM
(SELECT *,field(foo,'this','that','bar','baz') fieldfoo FROM t) A
WHERE fieldfoo > 0;


The default fill factor is zero, which is identical to 100. As per the product documentation,

  The fill-factor value is a percentage from 1 to 100, and the
  server-wide default is 0 which means that the leaf-level pages are
  filled to capacity.

Trying to find an opitmal value for fill factor is a well-researched problem. In the described scenario, there is not much to be done unless the table doesn't use default values.


Will Derby, H2, or SQLite give faster load time and/or smaller file size than HSQL?

You need a larger Java memory allocation and a larger HSQLDB memory cache size for faster loading of data into large tables.

Add hsqldb.cache_rows=4000000 and hsqldb.cache_size=1000000 to the startup configuration for a new database.

http://hsqldb.org/doc/2.0/guide/dbproperties-chapt.html#dpc_db_file_mem


Is it possible to replicate wide table in MS SQL?

To make a table into a wide table, you must create sparse columns and also add a column set. If you don't create a column set your table will still be limited to 1024 columns. This is why you can't use replication for wide tables.


Problem when using gtid with MySQL replication

I've been having this issue on a new install of 5.6.26. I found a bug report on this and it suggests that the fix is available on 5.6.27 or 5.7.9.

https://bugs.mysql.com/bug.php?id=68525

I've upgraded to 5.6.27 and just watching to see if it happens again.

Hope this helps.


MySQL restrict specific login to single ip address

Thanks for the contributions - although i have discovered a work around. Instead of using windows firewall to do port translation, i can just change my.ini and set my desired port. and instead of creating a user i just create the grant :

grant all on *.* to 'testuser'@'82.2.2.2' identified by 'testpassword';


MySQL does not allow to define those kind of constraints, the only ones that are allowed are those related to indexes, such as UNIQUE or FOREIGN KEYs, or invalid data types. CHECK is not supported.

There are several options, depending on what you want to achieve.

First, there is obviously the option of doing application-only checks.
The recommended way on server side is to revoke INSERT and UPDATE access to the table from the user editing the information and enforce the usage of a couple of stored procedures to check the data before actually inserting it, so that the user has to CALL insert_my_table(colums, of, my, table).
You can also do some "hacks" like using a BEFORE INSERT/UPDATE TRIGGER that will force the transaction to fail if it detects a "wrong" value, as it was suggested on the previous link. This is easier to implement but a bit more ugly.


Inability to query underlying table of a schema-bound view

From the documentation:

A query does not have to explicitly reference an indexed view in the FROM clause for the Query Optimizer to use the indexed view. If the query contains references to columns in the base tables that are also present in the indexed view, and the Query Optimizer estimates that using the indexed view provides the lowest cost access mechanism, the Query Optimizer chooses the indexed view, similar to the way it chooses base table indexes when they are not directly referenced in a query.

When you have an invalid indexed view in your database, this Enterprise Edition feature can produce errors when the optimizer considers using the indexed view instead of base table access.
This automatic indexed-view matching feature can be disabled with the EXPAND VIEWS query hint:

EXPAND VIEWS
Specifies the indexed views are expanded. Also specifies the Query Optimizer won't consider any indexed view as a replacement for any query part. A view is expanded when the view definition replaces the view name in the query text.
This query hint virtually disallows direct use of indexed views and indexes on indexed views in the query plan.

When you upgraded your database, a warning was emitted:

Warning: The object "ZZZZ" could not be bound and was ignored during upgrade. Consider reviewing and correcting its definition.

This is easy to miss, and may not even be visible to the user in some scenarios. Still, the lesson is clear: A database with invalid objects may produce unexpected errors.
DBCC CHECKDB WITH EXTENDED_LOGICAL_CHECKS will also detect the problem.
If you need to run the database on SQL Server 2019, use database compatibility level 100 until you are able to resolve the upgrade issues. This will allow datetimes to be added to times using the addition operator.


Handling of Topic Drill Downs in Databases

It looks like you're wanting to store a tree structure. This is a bit awkward with databases, but it's definitely possible. Here's a link which explains how: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ The "Nested Set Model" part is what you're looking for.

Applying that model to your scenario, you would get a table that looks something like this:

And a conceptual model for the first 3 levels that looks something like this (please excuse my MS Paint drawing skills):
Obviously you would have more nodes, but it's the basic idea.


Is query executed when mysql server has gone away?

Don't run the SQL statement :-)

If you want the mysql client to stop you running it, there is a --safe-updates aka --i-am-a-dummy option.

If you want the server to rollback, use innodb tables that provide transactional support.

For the error see gone away.


Does Oracle cache queries when using a database link

There are some restrictions with database links. You can't execute DDL remotely for example.

Queries run against remote tables are not processed in exactly the same way as regular tables:

  The local Oracle Database server breaks the distributed query into a corresponding number of remote queries, which it then sends to the remote nodes for execution. The remote nodes execute the queries and send the results back to the local node. The local node then performs any necessary post-processing and returns the results to the user or application.

In most cases, the breaking process is adequate and you will just get the expected overhead of the database link (network delay between the databases, two query analyses/processing instead of one, etc..). The remote subqueries will be treated as normal queries by the remote database and the data will get into the remote cache as with regular queries.

You might get into performance problems if you attempt to join a local table to a remote table (or tables from two distinct db links) since the data has to be carried to a single database to perform the join. In that case, you might want to change the breaking up process (for example performing a complex join in a remote database instead of the local database).

The documentation details a number of ways you can tune distributed queries.


Log Shipping - Secondary Database - Can CDC be enabled?

For log shipping, yes you can put it in read only between restores with a standby file.

However you won’t be able alter the database regardless of whether you are thinking of CDC or triggers. Also if you think about how CDC works it was created based off of replication technology. It has a log reader that reads out of the transaction log, that’s different to simply restoring a log backup which is what log shipping is doing.

You could potentially use transactional replication instead then have CDC fire on the subscriber - I have seen this a couple of times. The caveat with this approach is that if you have to re-sync your subscriber from the publisher you will either end up with duplicate data in the CDC tables or missing data - depending if you initialise from a snapshot or backup. So worth exploring your requirements and setting up some tests to go through the different scenarios.


I would use a view. Just prepare a select in the form of:

SELECT Field1, Field2, Field3, Field4, null as Field5, null as Field6
  FROM Table1

UNION

SELECT Field1, null, Field3, Field4, Field5, Field6
  FROM Table2;

You can refine the select to filter or extend, as you like. When you're satisfied, just define a view as:

CREATE VIEW MergedTable AS
SELECT Field1, Field2, Field3, Field4, null as Field5, null as Field6
  FROM Table1

UNION

SELECT Field1, null, Field3, Field4, Field5, Field6
  FROM Table2;

Then you can always run your selects against the view, nearly as it was a table.

SELECT * FROM MergedTable;


Why can't I log in to SYSTEM without AS SYSDBA

Because you are not logged in as the user system. When you execute conn system/password as sysdba, Oracle won't even check the user and password (--> from the local systeem where the db is running on). If you enter the system/password as sysdba and then execute 'show user', you'll notice you are logged in as user 'SYS'. So you are actually entering the wrong password when you execute the conn system/password, Oracle doest not lie.
Check http://docs.oracle.com/cd/B28359_01/server.111/b28310/dba006.htm#ADMIN11046 for more information. Because what I told you is not the whole story.


Np, PostgreSQL doesn't have any built-in facilities for automatic failover.
You have to use third-party software like Patroni.
Client failover can be managed by using a connection string that looks like
host=host1,host2 port=5432,5432 target_session_attrs=read-write


Is it good idea to invoke Web Service from the database function or trigger?

You could write a trigger that calls a web service in PL/Python, but I don't think it is a good idea to do something like that from a trigger:

Even though you think that your HTTP communication is one-way, it isn't, since HTTP is implemented on top of the connection-oriented TCP protocol. There is some back-and-forth involved in establishing a TCP connection and sending a request. Now if for example the destination host were unreachable, you would have to wait for a surprisingly long timeout.

Database transactions should be as short as possible, so that locks are not held longer than necessary and connection pooling works well (not to mention VACUUM). So any database-external activity of uncertain duration should be avoided during a database transaction. But a trigger always runs in the same transaction as the statement that triggers its execution.

I think the idea with LISTEN/NOTIFY or an approach with a queuing table would be better: the trigger just creates a task, and a worker process picks up the tasks and client-side code sends the HTTP request.


Making sense of INNODB buffer pool stats

The Buffer pool size   393215 This is in pages not bytes.

To see the Buffer Pool size in GB run this:

SELECT FORMAT(BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM
(SELECT variable_value BufferPoolPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

Database pages     360515 This is the number of pages with data inside the Buffer Pool

To see the amount of data in the Buffer Pool size in GB run this:

SELECT FORMAT(BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM
(SELECT variable_value BufferPoolPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

To see the percentage of the Buffer Pool in use, run this:

SELECT CONCAT(FORMAT(DataPages*100.0/TotalPages,2),' %') BufferPoolDataPercentage FROM
(SELECT variable_value DataPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value TotalPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

Modified db pages  300 This is the number of pages in the Buffer Pool that have to be written back to the database. They are also referred to as dirty pages.

To see the Space Taken Up by Dirty Pages, run this:

SELECT FORMAT(DirtyPages*PageSize/POWER(1024,3),2) BufferPoolDirtyGB FROM
(SELECT variable_value DirtyPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_dirty') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

To see the Percentage of Dirty Pages, run this:

SELECT CONCAT(FORMAT(DirtyPages*100.0/TotalPages,2),' %') BufferPoolDirtyPercentage FROM
(SELECT variable_value DirtyPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_dirty') A,
(SELECT variable_value TotalPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

As for the other things in the display, run this:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

You'll see all the status variables for the Buffer Pool. ou can apply the same queries against whatever you need to examine.


Here is my reading of that section.  I will admit it is confusing.

Suppose I have two tables:

CREATE TABLE batch (
   id serial not null unique,
   control_code text primary key,
   date_posted date not null default now()
);

CREATE TABLE details (
   batch_id int not null references batch(id),
   description text,
   primary key(batch_id, description)
);

Now, suppose we insert batch and details records in different transactions.  Session 1 inserts a batch and starts to insert details but before it finishes, session 2 starts up.  Session 2 gets to see the batch heading info, but does not wait on the commit on details to inform the user that no records are found.  Now if your batch and details are entirely in the same transaction then this never is a problem.

this would differ from serializable where you would expect to wait for the previous insert to complete and commit or rollback before determining whether to notify the user that no rows were found.


SQL Server 2012 - Disable Job On Linked Server

I added the linked server user to the SQL Agent Operator role and the issue has been resolved.

EXECUTE sys.sp_addrolemember @rolename = 'SQLAgentOperatorRole',
                             @membername = 'linked_server_user'


You can aggregate all uns/value pairs into a JSON object, then use that to access the UNS values by name. This requires some casting as the values can only be extracted as text from the JSON object, but the formula looks very similar to your description then:

with vals(postcode, v) as (
  select postcode, json_object_agg(uns, value)
  from x
  group by postcode
), factors (postcode, denominator, divisor) as (
  select postcode,
         (v->>'53')::decimal * (v->>'56')::decimal + (v->>'54')::decimal * (v->>'57')::decimal + (v->>'55')::decimal * (v->>'58')::decimal,
         (v->>'56')::decimal + (v->>'57')::decimal + (v->>'58')::decimal
  from vals
)
select postcode,
       denominator / nullif(divisor, 0)
from factors;

I have divided the aggregation, the evaluation of the denominator and divisor and the final division into three steps to make it more readable.

Online example: http://rextester.com/IZYT54566

You can simplify the formula by creating a function:

create function val(p_vals json, p_uns text)
  returns decimal
as $$
  select (p_vals ->> p_uns)::decimal;
$$
language sql;

with vals (postcode, v) as (
  select postcode, json_object_agg(uns, value)
  from x
  group by postcode
), factors (postcode, denominator, divisor) as (
  select postcode,
         val(v, '53') * val(v, '56') + val(v, '54') * val(v, '57') + val(v, '55') * val(v, '58'),
         val(v, '56') + val(v, '57') + val(v, '58')
  from vals
)
select postcode,
       denominator / nullif(divisor, 0)
from factors;


SQL Azure large multi-tenant table / lock escalation issue / partitioning / full text index

I have found a solution.

For the tables that need full text indexing, I create an additional column, computed/persisted from the concatenation of the ProjectKey and an int identity column. This new primary key column is partitioned with PartitionFunction/Scheme A and is used as the single column unique index for the full text index.
All non-full text indexed tables are partitioned with the ProjectKey, using PartitionFunction/Scheme B.

The learning point was: Multiple different partition functions / schemes can be created in SQL Server. If the functions produce equal results, the respective tables will be "splitted" to the same partition. Using (lock_escalation = auto), every project will now be locked separately from the others.


Here is your solution

SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
INTO @mass_delete_table_command
FROM
(
    SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    GROUP_CONCAT(table_schema,'.',table_name) table_list
    FROM
    (
        SELECT table_schema,table_name
        FROM information_schema.tables
        WHERE table_schema=database() AND
        table_name IN
        ('jos_bet_details','jos_bet_1x2','jos_bet_1x2_best','jos_bet_1x2_prev',
         'jos_bet_1x3','jos_bet_1x3_best','jos_bet_1x3_prev','jos_bet_hcp',
         'jos_bet_hcp_best','jos_bet_hcp_prev','jos_bet_ou','jos_bet_ou_best',
         'jos_bet_ou_prev','jos_bet_debug','jos_bet_deleted')
    ) ListOfTables
) DeleteParameters;
PREPARE s1 FROM @mass_delete_table_command;
EXECUTE s1;
DEALLOCATE PREPARE s1;

Here is the principle behind this: You want to use information_schema.tables to build the DELETE query dynamically for you.

First collect the names of the tables in a variable called 'mass_delete_table_command' as a delete list and a table list. Then, execute it as a prepared statement.

Here is sample code from creating 4 tables, adding records them, and zapping the records:

use test
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
DROP TABLE IF EXISTS t4;
CREATE TABLE t1 (n int NOT NULL);
CREATE TABLE t2 like t1;
CREATE TABLE t2 like t1;
CREATE TABLE t3 like t1;
CREATE TABLE t4 like t1;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t3 SELECT * FROM t1;
INSERT INTO t4 SELECT * FROM t1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;
SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
INTO @mass_delete_table_command
FROM
(
    SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    GROUP_CONCAT(table_schema,'.',table_name) table_list
    FROM
    (
        SELECT table_schema,table_name
        FROM information_schema.tables
        WHERE table_schema=database() AND
        table_name IN ('t1','t2','t3','t4')
    ) ListOfTables
) DeleteParameters;
SELECT @mass_delete_table_command;
PREPARE s1 FROM @mass_delete_table_command;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;

I ran this in MySQL 5.5.12 on my PC. Here is the output:

mysql> use test
DROP TABLE IF EXISTS t1;
DROP TABLE IF EXISTS t2;
DROP TABLE IF EXISTS t3;
Database changed
mysql> DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (n int NOT NULL);
CREATE TABLE t2 like t1;
CREATE TABLE t2 like t1;
CREATE TABLE t3 like t1;
CREATE TABLE t4 like t1;
INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
INSERT INTO t2 SELECT * FROM t1;
INSERT INTO t3 SELECT * FROM t1;
INSERT INTO t4 SELECT * FROM t1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;
SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
INTO @mass_delete_table_command
FROM
(
    SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    GROUP_CONCAT(table_schema,'.',table_name) table_list
    FROM
    (
        SELECT table_schema,table_name
        FROM information_schema.tables
        WHERE table_schema=database() AND
        table_name IN ('t1','t2','t3','t4')
    ) ListOfTables
) DeleteParameters;
SELECT @mass_delete_table_command;
PREPARE s1 FROM @mass_delete_table_command;
EXECUTE s1;
DEALLOCATE PREPARE s1;
SELECT A.*,B.*,C.*,D.* FROM
(SELECT COUNT(1) t1count FROM t1) A,
(SELECT COUNT(1) t2count FROM t2) B,
(SELECT COUNT(1) t3count FROM t3) C,
(SELECT COUNT(1) t4count FROM t4) D;
Query OK, 0 rows affected (0.04 sec)

mysql> DROP TABLE IF EXISTS t2;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS t3;
Query OK, 0 rows affected (0.03 sec)

mysql> DROP TABLE IF EXISTS t4;
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE TABLE t1 (n int NOT NULL);
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE t2 like t1;
Query OK, 0 rows affected (0.10 sec)

mysql> CREATE TABLE t2 like t1;
ERROR 1050 (42S01): Table 't2' already exists
mysql> CREATE TABLE t3 like t1;
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE t4 like t1;
Query OK, 0 rows affected (0.25 sec)

mysql> INSERT INTO t1 VALUES (1),(2),(3),(4),(5),(6),(7);
Query OK, 7 rows affected (0.08 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t2 SELECT * FROM t1;
Query OK, 7 rows affected (0.05 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t3 SELECT * FROM t1;
Query OK, 7 rows affected (0.07 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> INSERT INTO t4 SELECT * FROM t1;
Query OK, 7 rows affected (0.06 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT A.*,B.*,C.*,D.* FROM
    -> (SELECT COUNT(1) t1count FROM t1) A,
    -> (SELECT COUNT(1) t2count FROM t2) B,
    -> (SELECT COUNT(1) t3count FROM t3) C,
    -> (SELECT COUNT(1) t4count FROM t4) D;
+---------+---------+---------+---------+
| t1count | t2count | t3count | t4count |
+---------+---------+---------+---------+
|       7 |       7 |       7 |       7 |
+---------+---------+---------+---------+
1 row in set (0.00 sec)

mysql> SELECT CONCAT('DELETE ',delete_list,' FROM ',table_list)
    -> INTO @mass_delete_table_command
    -> FROM
    -> (
    ->     SELECT GROUP_CONCAT(table_name,'.*') delete_list,
    ->     GROUP_CONCAT(table_schema,'.',table_name) table_list
    ->     FROM
    ->     (
    ->         SELECT table_schema,table_name
    ->         FROM information_schema.tables
    ->         WHERE table_schema=database() AND
    ->         table_name IN ('t1','t2','t3','t4')
    ->     ) ListOfTables
    -> ) DeleteParameters;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT @mass_delete_table_command;
+-----------------------------------------------------------------+
| @mass_delete_table_command                                      |
+-----------------------------------------------------------------+
| DELETE t1.*,t2.*,t3.*,t4.* FROM test.t1,test.t2,test.t3,test.t4 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> PREPARE s1 FROM @mass_delete_table_command;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

mysql> EXECUTE s1;
Query OK, 28 rows affected (0.08 sec)

mysql> DEALLOCATE PREPARE s1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT A.*,B.*,C.*,D.* FROM
    -> (SELECT COUNT(1) t1count FROM t1) A,
    -> (SELECT COUNT(1) t2count FROM t2) B,
    -> (SELECT COUNT(1) t3count FROM t3) C,
    -> (SELECT COUNT(1) t4count FROM t4) D;
+---------+---------+---------+---------+
| t1count | t2count | t3count | t4count |
+---------+---------+---------+---------+
|       0 |       0 |       0 |       0 |
+---------+---------+---------+---------+
1 row in set (0.00 sec)

mysql>

This will definitely work for you as long as

you are standing in the correct database at the time
you suppply the list of tables you want dropped to the subqueries

Give it a Try !!!


New SQL Server from backup; application has successful login on 1 database, but fails on another: 0x80040e4d

Here is a summarization of our efforts to make this work:

We set up a new SQL Server using SQL Server Express 2014 and attached the .MDF files from the most recent backup.
We created a user and gave access to all the databases; by giving ownership and db_datareader and db_datawriter to the user for all the relevant databases, and by granting rights under User Mapping.
In the application we needed to get back online: The initial connection string, that worked fine with the old SQL server before it crashed, was: DSN=<SQL_Server>;UID=<User Id>;PWD=<Password>;Database=<Catalogue>;

Our initial attempt was to replace <SQL_Server> with the IP-address to the new SQL Server (with and without port).
This did not work; so we changed DNS=.. to Server= (with and without port); this did not work either.
All these attempts gave errors that the connection string was invalid. So we looked up different connection strings for IP-Addresses using OLE DB and found this: Provider=sqloledb;Data Source=<IP-address>;Network Library=DBMSSOCN;Initial Catalog=<Database>;User ID=<User>;Password=<Password>;. This worked, but only for one of the connections, for database A, while database B still failed.

The application successfully connects and reads data from database A, but fails with error: "Login failed for user 'goa'." on database B.

The SQL Server log files shows the following:
Login failed for user 'goa'. Reason: Password did not match that for the login provided. [CLIENT: <IP Address>] and Error: 18456, Severity: 14, State: 8.

We tried changing the passwords, and then neither connection worked. This gives me the impression that there were some encryption problem (because I recalled a mention of encryption in one of the forums I had browsed, looking for answers), and that the SQL Server tried to decrypt the password with the wrong key or something, and by chance uses the correct key in certain circumstances.

To solve this potential encryption problem; we added Integrated Security=SSPI; to the connection strings, this did not help. Nor did Integrated Security=yes; or Trusted_connection=yes;.

To make sure this was not a matter of the number of connections, or a limit in the number of open connections at a time: we changed the order of the connections so that database B went first, and A went second; and this time it was still the database B that failed.
We tried ressurecting the users from the original SQL server, by creating those users and executing: EXEC sp_change_users_login 'Auto_Fix', '<user>', NULL, '<Password>'. This gave a positive result: The number of orphaned users fixed by adding a new logins and then updating users was 1.. Thanks to @SQLPRODDBA and @Chad-Mattox

This is what worked for us:

Changing the connection string to:
Driver={SQL Server};Server=<IP-Address>;UID=<User Id>;PWD=<Password>;Database=<Catalogue>;That is going back to the initial format and adding Driver=.. to the string. For this connection we did not use a ressurected user from the old SQL server.


The LIMIT clause intended to do what you need:

SELECT *
  FROM food
 LIMIT 1,2  -- offset, count
;

Refer to the SELECT syntax manual for details: https://dev.mysql.com/doc/refman/8.0/en/select.html


How do you configure Postgres 9.4 for incremental backups and hot standby?

To address your first question, if you take a single basebackup of your database and just keep archiving away the WAL segments, the time to restore that database will grow longer and longer as the amount of WAL segments increases. That is not really an ideal situation to be in if you have downtime and need to bring a working database back up.

Most people figure out how much time they can spend restoring, and what recovery requirements they need to meet, and keep multiple basebackups and extra WAL segments as their storage budget and their recovery requirements allow.

Additionally, with very, very, very, very large WAL archives, sometimes things might happen to individual segments (which is yet another great reason to always test your backups!) and if a WAL segment fails to restore, no other segments following that failed segment will restore. At that point, you're out of luck.

One general rule I follow is to take a weekly basebackup, and keep 3 weeks of WAL segments and 2 basebackups, which allows me enough peace of mind to know I have one known-good backup, and a known-good set of WAL segments, and a reasonable restore time.

For your second question, you can have a hot standby server, and incremental backups, if you set it up that way. Your hot standby server only requires an initial basebackup from the primary, and a stream of WAL segments or WAL records (in the case of Streaming Replication, which 9.4 supports and is much nicer than simple WAL shipping).

I would recommend having a hot standby using Streaming Replication, and then taking your basebackups with pg_basebackup from the hot standby server, which will reduce the resource load on your primary considerably.

For an incremental (PITR) backup, you just need to have your primary server send the WAL segments it generates somewhere else (it's best that it's different hardware, of course) using an archive_command or pg_receivexlog.

Pushing the weekly basebackup you created with pg_basebackup there as well will make keeping track of all the necessary components for incremental restoration much easier. Plus, having everything together makes testing those backups trivial.

As for the third question, I don't know of a PITR manager that runs on Windows. In theory, since it's just Python, it could run on Windows, but as the FAQ says, it hasn't been tested.

Magnus Hagander has a great talk on backups from PgCon 2013 that you might find helpful: PostgreSQL Backup Strategies


A lot of this depends on what else your query is doing. If that predicate is one of many, and isn't particularly useful for an index, then you may be seeing that predicate in a Predicate property (being a residual predicate) in your plan.

Alternatively, if you have an index on CategoryID, then are you wanting it to do dozens of Seeks into Table?

I suspect that the cost of the sub-query, especially if it's run frequently so that it's in memory already, would be negligible if you're doing dozens of Seeks. But without an index on CategoryID, you might be better off with just a residual, by using your IN clause. If you have a maximum number of values that could be used, you could parameterise it, like: (@p1, @p2, ...). To get the same number of parameters each time would be important. If you always have between 10 and 15, then maybe code up for 15, and use -1 as the value for any extras (ie, if you have 11 values, then 12-15 could be all -1 to avoid recompilation).


Pessimistic locking is a good thing used well.

The big thing though is that your issues are all going to be concurrency issues and things which interfere with concurrency will interfere with performance.  For example if your network switch starts dropping 10% of packets randomly, this will make a network connection take a lot longer, and your locks are going to suffer.  These will not be deadlocks per se and will only make a problem that would be painful under the best of scenarios a good deal worse.

System crashes should not cause deadlocks.  If it is the client, the network connection will time out so you may have a sudden issue with locks that goes away right away.  If the client is middleware however, this is likely to be a non-issue because all the other connections will be broken too.  If it is the server, the transactions will abort.

The big issues are developer short-sightedness.  For example, suppose you write a stored procedure that calls a web service and the network connections there are timing out.  Or suppose you make a connection back to the client computer to request input on whether to continue a stored procedure and the user never responds.  In general when you pause a transaction to wait for something outside the database, bad things happen.


Indexing strategy for VARCHAR2 LIKE search

No, forget LIKE operator for this, conditions as first_name LIKE '%name%' will never be effective. A regular index can not be used for range scans for conditions as first_name LIKE '%name%'. If first_name is indexed, index full scan is the best the database can use. And you still need to handle case-sensitity.

(index range scan can be used for conditions as first_name like 'name%', where the beginning of the value is fixed)

This is a task for Oracle Text.

No, forget virtual columns, you can not create text indexes on virtual columns, this is what you get if you try it:

DRG-11304: function-based indexes are not supported by this indextype

I have answered a similar question: Oracle “matches” multiple columns

But here it is again, with an example:

create table t1
(
  id number primary key,
  code number not null,
  first_name varchar2(100 char) not null,
  last_name varchar2(100 char) not null,
  email varchar2(50 char) not null
);

insert into t1 values (1, 111, 'Balazs', 'Papp', 'dba@stackexchange.com');
insert into t1 values (2, 222, 'linux', 'unil', 'index@stackexchange.com');
commit;

begin
ctx_ddl.create_preference('t1_multi_column_datastore', 'multi_column_datastore');
ctx_ddl.set_attribute('t1_multi_column_datastore', 'columns', 'code, first_name, last_name, email');
ctx_ddl.create_preference('t1_lexer', 'basic_lexer');
ctx_ddl.set_attribute('t1_lexer', 'mixed_case','no');
end;
/

create index i1 on t1 (first_name) indextype is ctxsys.context
  parameters ('lexer t1_lexer datastore t1_multi_column_datastore');

Then the query:

SQL> select * from t1 where contains (first_name, '%STACK%') > 0;

        ID       CODE FIRST_NAME LAST_NAME  EMAIL
---------- ---------- ---------- ---------- -------------------------
         1        111 Balazs     Papp       dba@stackexchange.com
         2        222 linux      unil       index@stackexchange.com

SQL> select * from t1 where contains (first_name, '%11%') > 0;

        ID       CODE FIRST_NAME LAST_NAME  EMAIL
---------- ---------- ---------- ---------- -------------------------
         1        111 Balazs     Papp       dba@stackexchange.com

SQL> select * from t1 where contains (first_name, '%Uni%') > 0;

        ID       CODE FIRST_NAME LAST_NAME  EMAIL
---------- ---------- ---------- ---------- -------------------------
         2        222 linux      unil       index@stackexchange.com

Notice how I did not specify all the columns, still the query searched in all of them (enumerated in the multi_column_datastore). I specified first_name in the index and the query, yet the query returned records that matched on the email, code or last_name column.

Notice how I did not specify anything related to case-sensitivity in the query. It was specified with the mixed_case=no property (Oracle Text word searches are not case-insensitive by default: Case-Sensitive Searching).

And the query uses the index:

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  a4vw67psmh4x3, child number 0
-------------------------------------
select * from t1 where contains (first_name, '%Uni%') > 0

Plan hash value: 1218792127

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     4 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1   |     1 |   544 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | I1   |       |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("CTXSYS"."CONTAINS"("FIRST_NAME",'%Uni%')>0)


SQL Server Nested views, how deep is too much?

It's not the number of levels of views that is the problem, it is the complexity of the resulting query.  Having a lot of nested views may contribute to the complexity of the query but a great deal depends on what those views are actually doing.
Having several layers of nested views often creates problems when it means that you are doing unnecessary or duplicative work.  If an underlying view, for example, joins tables A, B, and C when you only need information from A and B, the join to C means that the database may have to do unnecessary work (depending on the database engine and the exact definition, the optimizer may be able to work out that the join to C is unnecessary and eliminate it but this doesn't usually happen).  If a first level view joins A and B and then a second level view joins to B again because it wants to add some additional columns from B, you've now forced the database to do duplicate work and join to B twice.  The same thing happens when you have predicates in different levels of views that force the database to do work that it doesn't have to do.
Broadly, if you have a well-designed series of views where each level has some defined responsibility, you're much more likely to have reasonable performance than if you have an incoherent set of views that have grown up organically over time with different developers adding things to different views to meet that day's requirements.  Imagine you have a bug tracking system with the following objects

A bug table with all issues,
A vw_bug view on top of that that acts as an abstraction layer in case someone wants to refactor the bug table
A vw_current_bug view on top of vw_bug that that filters out soft-deleted rows
A vw_open_bug view that filters down to open bugs
A vw_bug_overview view that joins vw_open_bug to all the lookup tables to present information for the overview page

That's four layers of views with each one providing a well-defined abstraction layer.  That's likely to be OK from a performance standpoint.
Having several levels of views can also create problems for the database when it makes it difficult or impossible for the optimizer to figure out the best place to apply a predicate (Google "predicate pushing" for tons of additional information on this).  If you   have a number of different levels of views but you always specify a where table_a_primary_key = :1 predicate when you query the outer view, the optimizer has to figure out when to apply that predicate.  If table_a is the driving table in the inner view, ideally the optimizer figures out that it wants to push that predicate down through all the layers of views and apply it to that driving table before it does the work of the joins.  Depending on the database engine, version, and the complexity of the code in your views, however, it may be difficult or impossible for the optimizer to figure out it wants to push the predicate or to push it there without theoretically changing the results.  Different database engines and different versions of those engines will have very different behaviors in this respect.
Broadly speaking, this is no different than the challenges you'd have when you have a single view that happens to implement relatively complex logic.  Different database engines have different ways to let you circumvent these sorts of issues-- Oracle hints can force the optimizer to push a predicate, Oracle lets you define a pipeline table function that acts like a view but lets you pass in a parameter that you apply where you want, SQL Server table functions act in much the same way, etc.
Those aren't the only two potential issues with having a bunch of levels of views but those are the two big ones in my experience.  As with any query, the more complex it gets, the more difficult it is for the optimizer to exhaustively search every possible query plan and the more likely it is that you're going to end up with a less optimal plan.  The more complex the query, the more likely you're going to hit a corner case where the optimizer can't correctly work out the cardinality of various steps because it doesn't know how to combine the statistics correctly.
On the other hand, a well-designed set of views can provide useful abstractions that make it easier to define queries correctly.  In the bug tracking system from earlier, vw_current_bug provides a useful abstraction layer so that you don't have to filter out soft-deleted rows in hundreds of queries.  There may be cases where this abstraction layer has a performance cost but that may be a small cost that you're happy to pay to avoid duplicating code.


Will Partitions and Indexes on the same table help in performace of Inserts and Selects?

May I suggest the following strategy:

Since there is a limit of 1024 partitions and sub partitions in single table have a Current
table (1-2 months of data) partitioned by day and browser. And another History table for archive partitioned by month/day.

If you need to run reports on browser split, then have several tables with day/browser key.

Although there will be a number of tables (you may keep e.g. a table per quarter and then have second line of archiving to a table with month/browser key) it will be manageable, because partitions are quickly switched from one table to another.

Non-clustered indexes always help your reports, so having an index on a often used column
is a good idea whatever partitioning strategy is implemented.

Depending on your reporting requirements you may also consider creating a summary table, which will have data already pre-aggregated for your reports. It can be populated overnight and speed up your reports dramatically (indexes is a must on this table too). You can even populate this summary table several times during the day without locking the table by using READ UNCOMMITTED - it is unlikely that you need precision to every visitor.


DELETE query against a linked server issues error

Just use the syntax that allows you to define an alias:

DELETE t1 FROM Server.Database.dbo.Table1 AS t1
WHERE NOT EXISTS
(
  SELECT 1 FROM Server.Database.dbo.Table2 AS t2
  WHERE t2.Code = t1.Code
);

Or you can use dynamic SQL to simplify:

DECLARE @sql NVARCHAR(MAX) = N'DELETE t1 FROM dbo.Table1 AS t1
  WHERE NOT EXISTS
  (
    SELECT 1 FROM dbo.Table2 AS t2
    WHERE t2.Code = t1.Code
  );';

EXEC Server.Database..sp_executesql @sql;

This forces the query to be run on the other side, which can provide other benefits too...


Detect with Left Join duplicates entries and return the first value, also return sum of duplicates entries

SELECT DISTINCT
       CASE WHEN t2.ID IS NULL
            THEN t1.Name
            ELSE t2.Name
            END Name,
       CASE WHEN t2.ID IS NULL
            THEN t1.ID
            ELSE t2.ID
            END ID,
       CASE WHEN t2.ID IS NULL
            THEN t1.Account
            ELSE t2.Account
            END Account,
       CASE WHEN t2.ID IS NULL
            THEN 'No'
            ELSE 'Yes'
            END Column_A,
       ( SELECT COUNT(t4.Account)
         FROM BankAccounts t4
         WHERE t1.Account = t4.Account ) NumberOfMatches,
       CASE WHEN t2.ID IS NULL
            THEN CONCAT(t1.Name, ' (OK)')
            ELSE CONCAT(t1.Name, ' (BAD)')
            END Column_B,
       CASE WHEN t2.ID IS NULL
            THEN NULL
            ELSE t1.ID
            END BadID
FROM BankAccounts t1
LEFT JOIN BankAccounts t2 ON t1.Account = t2.Account
                         AND t1.ID < t2.ID
LEFT JOIN BankAccounts t3 ON t1.Account = t3.Account
                         AND t1.ID > t3.ID
WHERE t3.ID IS NULL
ORDER BY Account, ID;

fiddle


This error indicates that Management Studio is running out of memory, not the SQL Server service. Even if you installed 64 bit SQL Server, the SQL Server Management Studio executable is a 32 bit application.

This is likely caused by the size of the result set that you are returning to Management Studio. Are you executing something like SELECT * FROM really_big_table? See http://support.microsoft.com/kb/2874903 for more.


Saas MySQL Multiple Databases or One Database

There is no performance difference between single base and number of bases, because "schema" is just the domain of authority. Grouped together or aparted, tables stored and processed in the same way. From the administrative point of view multiple bases are bit preferrable as far as you can dump the whole data for single client by DB name, not the pattern for the mangled tablenames.

Sure your experience may vary.


Linked Server only for Select Statements?

If you have linked your servers, you can query any server using [servername].databasename.schema.object from any one of the two servers.


Convincing my DBA there's a sporadic issue with the SQL Server

Start with Tara Kizer's post on Getting Help with a Slow SQL Server. (Disclaimer: it's on my company's blog.)

She walks you through:

Getting your server's wait stats since startup with sp_BlitzFirst @SinceStartup = 1
Getting server health data in Markdown format so you can copy/paste it into your Stack question with sp_Blitz @OutputType = 'markdown', @CheckServerInfo = 1


SQL Server: no search results caused by noise words

Simply deactivating the stop list fixed the problems. There may be better solutions but this worked for me.

Use database;
ALTER FULLTEXT INDEX ON table
   SET STOPLIST OFF;
GO


Getting value from a table based on one of the possible foreign keys

You're ganging the tables together in a very awkward manner. Say you have a table of all car types, a table of all car models, a table of all car versions, a table of all car parts and finally a table with problems (dent, etc). Then you can have one intersection table that relates all of these together with the price to fix the problem. Using simplified data, a row of this table might look like this:

S, TC, Z, FDP, D, $250

This row shows that the price to repair a dent in the front door panel of a Toyota Camry sedan, ZXI is $250.


Update rows in table with id from row before in PostgreSQL

You can get your desired result by using LAG() function.

select
    id,
    grp,
    time_added,
    lag(id) over (partition by grp order by grp, time_added) previous_id
from
    tbl;

You can update in this way:

with x as
(
    select
        id,
        lag(id) over (partition by grp order by grp, time_added) previous_id
    from
        tbl
)
update tbl
set
    previous_id = x.previous_id
from
    x
where
    x.id = tbl.id;

db<>fiddle here


I'd consider 3 tables to describe the items, and a table to describe the transactions against those items.  Something like:

CREATE TABLE dbo.Manufacturers
(
    ManufacturerName NVARCHAR(100) NOT NULL
        CONSTRAINT PK_Manufacturers
        PRIMARY KEY CLUSTERED
    , ManufacturerWebsite NVARCHAR(100) NULL
);

CREATE TABLE dbo.Models
(
    ManufacturerName NVARCHAR(100) NOT NULL
    , ModelName NVARCHAR(100) NOT NULL
    , ModelDescription NVARCHAR(1000) NOT NULL
    , CONSTRAINT PK_Models
        PRIMARY KEY CLUSTERED
        (ManufacturerName, ModelName)
    , CONSTRAINT FK_Models_Manufacturer
        FOREIGN KEY (ManufacturerName)
        REFERENCES dbo.Manufacturers(ManufacturerName)
);

CREATE TABLE dbo.Parts
(
    ManufacturerName NVARCHAR(100) NOT NULL
    , ModelName NVARCHAR(100) NOT NULL
    , PartNumber NVARCHAR(100) NOT NULL
    , ReorderLevel INT NOT NULL
    , ReorderQuantity INT NOT NULL
    , CONSTRAINT PK_PartNumbers
        PRIMARY KEY CLUSTERED
        (ManufacturerName, ModelName, PartNumber)
    , CONSTRAINT FK_PartNumbers_Model
        FOREIGN KEY (ManufacturerName, ModelName)
        REFERENCES dbo.Models(ManufacturerName, ModelName)
);

The above tables can contain whatever extra columns you need that make sense for each table.  Perhaps the manufacturer's phone number, or perhaps where you order parts from, etc.

Here is the table where you keep track of when items were consumed or received:

CREATE TABLE dbo.Transactions
(
    TransactionID INT NOT NULL
        CONSTRAINT PK_Transactions
        PRIMARY KEY CLUSTERED
        IDENTITY(1,1)
    , ManufacturerName NVARCHAR(100) NOT NULL
    , ModelName NVARCHAR(100) NOT NULL
    , PartNumber NVARCHAR(100) NOT NULL
    , TransactionDate DATETIME NOT NULL
    , TransactionDescription NVARCHAR(1000) NOT NULL
    , Quantity INT NOT NULL
    , CONSTRAINT FK_Transactions_Parts
        FOREIGN KEY (ManufacturerName, ModelName, PartNumber)
        REFERENCES dbo.Parts(ManufacturerName, ModelName, PartNumber)
);

To show this design, we'll insert some sample data:

INSERT INTO dbo.Manufacturers (ManufacturerName, ManufacturerWebsite)
VALUES ('HP', 'www.hp.com');

INSERT INTO dbo.Models (ManufacturerName, ModelName, ModelDescription)
VALUES ('HP', 'LaserJet II', 'Laser printer');

INSERT INTO dbo.Parts (ManufacturerName, ModelName, PartNumber, ReorderLevel, ReorderQuantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', 10, 5);

INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Received Paper', 5);

INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Received Paper', 15);

INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Consumed Paper', -6);

INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Consumed Paper', -8);

INSERT INTO dbo.Transactions (ManufacturerName, ModelName, PartNumber, TransactionDate, TransactionDescription, Quantity)
VALUES ('HP', 'LaserJet II', 'Letter Paper', GETDATE(), 'Consumed Paper', -5);

This shows how you can see a running total and when you need to reorder:

SELECT t.ManufacturerName, t.ModelName, t.PartNumber
    , t.TransactionDate
    , t.TransactionDescription
    , t.Quantity
    , Balance = SUM(t.Quantity) OVER (PARTITION BY t.ManufacturerName, t.ModelName, t.PartNumber ORDER BY t.TransactionDate ROWS UNBOUNDED PRECEDING)
    , NeedToReorder = CASE WHEN SUM(t.Quantity) OVER (PARTITION BY t.ManufacturerName, t.ModelName, t.PartNumber ORDER BY t.TransactionDate ROWS UNBOUNDED PRECEDING) < p.ReorderLevel THEN 'X' ELSE '' END
    , QuantityToOrder = CASE WHEN SUM(t.Quantity) OVER (PARTITION BY t.ManufacturerName, t.ModelName, t.PartNumber ORDER BY t.TransactionDate ROWS UNBOUNDED PRECEDING) < p.ReorderLevel THEN p.ReorderQuantity ELSE 0 END
FROM dbo.Transactions t
    INNER JOIN dbo.Parts p ON t.ManufacturerName = p.ManufacturerName
        AND t.ModelName = p.ModelName
        AND t.PartNumber = p.PartNumber
ORDER BY t.TransactionID;

The output from the above query:


How do I declare and use variables in Oracle?

Inside pl/sql block:

declare
 startdate number;
begin
  select 20110501 into startdate from dual;
end;
/

using a bind variable:

var startdate number;
begin
  select 20110501 into :startdate from dual;
end;
/

PL/SQL procedure successfully completed.

SQL> print startdate

 STARTDATE
----------
  20110501

in a query:

select object_name
from user_objects
where created > to_date (:startdate,'yyyymmdd');  /*prefix the bind variable wïth ":" */


Whether Cold backup method can be used to “migrate oracle DB”?

Should work when the directory structure is the same. Check you $ORACLE_HOME/dbs directory to see if you have other SID related files. If your database does not need recovery then no archivelogs are needed. Do not forget to change at least the DBID if you need to integrate the database in the RMAN backup process.

If you use RMAN for backups then you can also clone your database to another server. This way you can place the files in a different location and the DBID (and DBNAME) will be changed during the cloning.


Bulk insert and fast ordered search large data 3B+ records

It looks like this question will be closed soon, but you could consider using a nonclustered columnstore index. The business case that you are describing with this question seems to match Microsoft's vision for it:

  Real-time analytics uses an updateable columnstore index on a rowstore table. The columnstore index maintains a copy of the data, so the OLTP and analytics workloads run against separate copies of the data. This minimizes the performance impact of both workloads running at the same time. SQL Server automatically maintains index changes so OLTP changes are always up-to-date for analytics. With this design, it is possible and practical to run analytics in real-time on up-to-date data. This works for both disk-based and memory-optimized tables.

There's a lot of information contained in that link and it links out to a bunch of other blog posts, so it's difficult to summarize it all. You might experience the following benefits:

CCIs have a typical compression ratio of 10:1, so it might be smaller than the nonclustered index that you want to build. It'll certainly be smaller than a separate copy of the table.
You can use the compression delay option discussed here to possibly lighten the load on the server when inserting data.
Converting the table to a CCI will enable batch mode for your query. You may see performance benefits from this, especially because the window functions are eligible for batch mode as of SQL Server 2016.

However, it also might not be helpful for your workload. The only way to know is to test. If you really need to constantly run a query against a 3 billion row table without any filters perhaps a data model change is needed. The insert timeout that you saw also hints at a deeper problem, but I don't have the knowledge or additional context needed to to troubleshoot that.


SELECT * FROM runners WHERE id NOT IN (SELECT winner_id FROM races);

Is something like this (actual values from races):

SELECT * FROM runners WHERE
id <> 2 and
id <> 3 and
id <> null;

id <> null is unknown by definition, so the whole where clause evaluates to either false (for ids: 2, 3) or unknown (for ids: 1, 4, 5) because of the and operators, thus no result returned.

If you want to run the query disregarding the nulls in races, you can use:

SELECT * FROM runners
WHERE id NOT IN
      (SELECT winner_id FROM races WHERE winner_id IS NOT NULL);


How to automatically rename columns from same table join?

SQLite is designed as a small, embedded database to be accessed from a 'real' programming language, so it has no built-in mechanism for dynamic SQL.

You have to construct the SQL query from inside that other programming language; something like this:

cols = []
for tab in tables:
    for col in columns:
       cols.append("c_{}_{}".format(tab, col))
sql = "SELECT " + ", ".join(cols) + " ..."
execute(sql)


what does "if @@trancount > 0 commit tran" mean?

I think there are two concepts that you need to understand:

IF @@trancount > 0 COMMIT TRANSACTION
This checks whether there are still any open transactions from earlier in the
script, or that will be present if IMPLICIT_TRANSACTIONS are on. If you don't
check for open transaction before doing a COMMIT, you will receive the
following error:

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

IMPLICIT_TRANSACTIONS
To simplify this, when IMPLICIT_TRANSACTIONS is ON, there is an invisible BEGIN TRANSACTION before specific statements (see list here) if there aren't any transactions open already. And when IMPLICIT_TRANSACTIONS is OFF, your statement is in autocommit mode

If you provide us with a code example, we might be able to answer your question more specifically. You can play with this code to see how these commands affect a statement:
    /*
    SET IMPLICIT_TRANSACTIONS ON
    SET IMPLICIT_TRANSACTIONS OFF
    */

    --BEGIN TRANSACTION  --Uncomment if IMPLICIT_TRANSACTIONS is ON

    CREATE TABLE Test (col int)

    DROP TABLE Test

    SELECT @@TRANCOUNT

    BEGIN TRANSACTION

    SELECT @@TRANCOUNT

    COMMIT

    SELECT @@TRANCOUNT

    COMMIT

    SELECT @@TRANCOUNT

    --When IMPLICIT_TRANSACTIONS is ON, SQL will begin a new transaction count here,
    --You need to explicitly end the transaction at the end
    CREATE TABLE Test (col int)

    DROP TABLE Test

    SELECT @@TRANCOUNT

    IF @@TRANCOUNT > 0
        COMMIT

    SELECT @@TRANCOUNT


Why is @temp table performance some times worse than #temp table performance?

Table Variables don't have statisics in the same way as Temp Tables normally they're assumed to have only 1 row.  This incorrect estimate of rowcount will make a nested loop operation look like the best plan but when this is done for a larger amount of rows the cost can easier be greater than a table scan.


Connecting to a self signed mongodb server though mongo shell doesn't require `pem` file

As per MongoDB documentation here Before you can use TLS/SSL, you must have a .pem file containing a public key certificate and its associated private key.

  Note : For FIPS mode, ensure that the certificate is FIPS-compliant (i.e use of FIPS-compliant algorithms) and the private
  key meets the PKCS#8 standard. If you need to convert a private key to
  PKCS#8 format, various conversion tools exist, such as openssl pkcs8
  and others.

MongoDB can use any valid TLS/SSL certificate issued by a certificate authority, or a self-signed certificate. If you use a self-signed certificate, although the communications channel will be encrypted, there will be no validation of server identity. Although such a situation will prevent eavesdropping on the connection, it leaves you vulnerable to a man-in-the-middle attack. Using a certificate signed by a trusted certificate authority will permit MongoDB drivers to verify the server’s identity.

In general, avoid using self-signed certificates unless the network is trusted.

This operation generates a new, self-signed certificate with no passphrase that is valid for 365 days. Once you have the certificate, concatenate the certificate and private key to a .pem file, as in the following example:

cat mongodb-cert.key mongodb-cert.crt > mongodb.pem

For your further ref here


Environment Variable PATH exceeding recommended length

Thank you all for your answers.  I believe @John Eisbrener had the correct answer.  I placed the installation in a root "C:\Oracle" directory instead of accepting the default User Directory installation.  It installed without issue after that.

Thank you all for your responses.


how to improve performance by using or not using table variables

First thought...

If you have sufficient data to take this much % of the batch, use a #temptable.

When the table variable is used later, it is always assumed to have one row: there are no statistics on this table variable. So if you have several 1000, subsequent plans won't be optimal.

Temporary tables have statistics (and indexes if required etc) and can perform better for larger datasets


From BOL

If a package that is not configured to support transactions includes a Sequence container that uses the Required option, the Sequence container would start its own transaction. If the package were configured to use the Required option, the Sequence container would join the package transaction.
If I have a sequence container and it is marked as "transaction required" and I have tasks in that sequence container that are marked as "transaction supported", if all the tasks in the container are finished successfully does SSIS commit the transaction when it is done with the sequence container or does the transaction live past the end of the sequence container?

SSIS will commit the task with the sequence container.
IMHO, best is to use TSQL Transactions instead of SSIS Transactions.
Also refer to : Design Pattern: Avoiding Transactions in SSIS


MySQL: Updating large data set including time aggregation

If your event table is huge, what takes a while is reading it. Once the data is read, computing a few extra aggregates won't hurt a lot. And with one row per second in the event table and one row per hour in the aggregate table, you'll reduce data size substantially anyway. So you should basically store everything you'll need.
For example if you need avg(a-b) and avg(a+b), knowing that avg(a-b)=avg(a)-avg(b) and avg(a+b)=avg(a)+avg(b) the simplest option would be to just store avg(a) and avg(b) and you get the sum, difference, and any linear combination thereof for free when selecting from the aggregate table. Of course, that won't work if you use aggregates that cant' be combined this way. For example max(a-b) can't be calculated from max(a) and max(b).

As data is newly imported on a regular basis, how can I prevent the calculation_result table to be locked?

Use an engine that supports this, like InnoDB, for your aggregates table. If you don't want a long running update on your aggregation table, you could write the results of the aggregation in a temporary table, this takes no locks while the query runs. Then update the main table quickly.
If you have performance problems with your amount of data you could consider trying clickhouse, or another time series database. Clickhouse is easy to use if you know SQL, but it's not a general purpose database. If you use it for what it's good at, performance is just ridiculous, like 120 million rows/s for SELECT x,avg(y) GROUP BY x. It also has an AggregatingMerge table engine which automatically stores aggregates on larger time windows automatically, which is basically what you're asking. And since it compresses the tables, data files are tiny. It uses huge amounts of RAM though.


The instance's TNS name would be defined in the tnsnames.ora file on your system. When you bust that file open, you'll see something like...

MyOracleInstanceTNSName =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.0)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = ORA12)
 )
)

The instance name (defined in the file) is really arbitrary; meaning, you can name it whatever you want. So, in the example above, I named it MyOracleInstanceTNSName, but I could've named it something else.

In the professional world, we typically use the same name across hosts, but that's a convention. This way as we share scripts or talk about instances, we're using the same name. That's a while lot easier than remembering that Bob's "foobar" instance is the same as your "bigSexy" instance.

You can find this file in ${ORACLE_HOME}/network/admin on most systems. You should be able to find (or define) the tnsname for the instance you're using there.


SQL Server 2014 Buffer Pool Extension - There is insufficient memory available in the buffer pool

The best answer I can come up with is that Sql really doesn't have any room in the buffer pool to give up.  Meaning, we have too many queries running, or the queries running have plans that are taking up too much space.  This link was somewhat helpful No Free Buffers.  My solution at this point is to evaluate the queries that were running when the error occurs, so see if I can tune them.