Postgresql - Sybase concepts correspondence

SAP/Sybase ASE uses a slightly different conceptual model than PostgreSQL, Oracle & SQLServer, which all have the concept of a schema.

PostgreSQL  |   SAP ASE
-----------------------
Cluster     |   Database Server or Database Server Cluster
Catalog     |   Database
Schema      |   User
-----------------------

In ASE, a cluster is multiple physical Database Servers working as a single logical Database Server.  There is no separate designation if a Database Server (single or cluster) is running multiple databases, since that is the expected operation.

Database is the collection of related objects (Tables, views, triggers, users, etc).

Schemas: In ASE a user can own objects, and set permissions on those objects independently, which satisfies some of the same use cases for schemas BUT a user can not be dropped from the database if they own any objects.  So for practical purposes, most sites don't use user level objects.

The lowest granularity storage object in ASE is a page (other databases call this a block) the size of which is set when the server is installed, but can be 2k, 4k, 8k or 16k.  This means that the database allocates pages in chunks equivalent to the page size setting.

Referential integrity can cross database (schema) boundaries.

I highly recommend checking out sypron.nl.  Rob Verschoor is an SAP VP & Engineer and his site is packed with good information, tips, tricks and scripts.  His quick reference books are also VERY good, and I would recommend them to anyone needing to work with ASE, Replication Server or IQ.


String parsing: Extracting the filename from a URL

I used this code to extract the FileName with Extension.

UPDATE TableName set ColumnName= right(Image_Link, CHARINDEX('/', REVERSE(ColumnName) + '/') - 1)

And this code to extract and update only FileName

UPDATE TableName  SET ColumnName= REVERSE(SUBSTRING(REVERSE(ColumnName),CHARINDEX('.', REVERSE(ColumnName)) + 1, 999))


What about adding the where clause to the join itself?  Something like

left outer JOIN
retail_append.nxr nxr
ON (items.code = nxr.code AND nxr.categoryCode = 'wine')) and similar for the other with the item group code?


Sql Server fails to use index on simple bijection

Am I simply expecting too much?

Yes. At least in current versions of the product.

SQL Server will not pick apart the CASE statement and reverse engineer it to discover that if the result of the computed column is 'one' then [Extent1].[Number] must be 0.

You need to make sure that you write your predicates to be sargable. Which almost always involves it being in the form. basetable_column_name comparison_operator expression.

Even minor deviations break sargability.

WHERE P.Number + 0 = 0;

would not use an index seek either even though it is even more straightforward to simplify than the CASE expression.

If you want to search on a string name and get a seek on number you would need a mapping table with the names and numbers and join onto it in the query, then the plan might have a seek on the mapping table followed by a correlated seek on [dbo].[MyEntities] with the number returned from the first seek.


Availability Strategies in MS SQL Server 2008

Database Mirroring isn't a database recovery strategy.  It's a high availability strategy.  If you can afford to loose up to 15 minutes worth of data then you take transaction log backups every 15 minutes.

If you need to look into high availability that's a totally different conversation, but it doesn't remove the need to be taking transaction log backups.

As for your self-hosted customers, they are responsible for setting up their own backup solution unless your contract states that you'll work with them to set one up for them.


SSMS 2014 (and 2012) CTRL+TAB requires pressing enter

To fix this issue: Tools \ Options \ Keyboard.  Bring up the command in the "Show commands containing:" list.  Then in the "Press shortcut keys:" text box press CTRL+Tab and click Assign.  This will re-assign the command back to itself, but fixed the "sticky" window.  Just had the same issue. Hope this helps.


We converted a database table to be in-memory and the corresponding memory-optimized filegroup takes 1GB on the disk

I would like to close the loop on my own question.  We worked with Microsoft support on this and the bottom line is : "it's by design".  I strongly believe that this is a flawed design and should be changed.  The Microsoft support engineer opened a feature change request for this.  In addition I created a "MS Connect" item about this.  Please up-vote it if you agree with the request:
https://connect.microsoft.com/SQLServer/feedback/details/3116296/enabling-in-memory-feature-for-databases-takes-up-huge-amount-of-disk-space-for-checkpoint-file-pairs-cfps-in-cases-when-there-are-many-databases-on-a-sql-server-instance


How do I copy a DB dump file to a remote computer?

If you are using Windows and are using RDP to connect to the server you can just select the dump file, press CTRL-C, switch to the server connection and browse to a folder where you would like to copy the file to and press CTRL-V.

If your laptop and the server are on the same network, you can create a file share on either your laptop or on the server (or use an existing one) and use Windows Explorer to copy-paste the files over.

There is also a copy command if you want to do it from a DOS box or batch file.

If you are using Linux, again, it depends on how you can connect to your server. There are options to mount a remote file share using NFS or Samba and copy files over, or you can use scp to copy files over an SSH connection.

This way you can have a "copy" of the dump file on the remote server, and use pg_restore to restore that copy of the dump file.


In SQL Server, how do I relate each date in a set to the subsequent date from the same set?

Unless you're on a very old version of SQL Server, the LEAD function should work.
SELECT
    MyDate AS CurrentDate,
    LEAD(MyDate, 1, NULL) OVER
    (
        ORDER BY
            MyDate
    ) AS NextDate
FROM #Dates
ORDER BY CurrentDate;


Unusual behaviour of a query when using case expression in group by, some records are being missed

This behaviour is directly related to your group by - it is behaving as intended, by 'uniquing' your customer_nums (of which there are 4 in your example data).
Without further information on what you are looking to derive with this query it's hard to determine what results you wish for from this query. If you do need any further help, happy to give any guidance that I can.
See the full explanation of GROUP BY on https://www.w3schools.com/sql/sql_groupby.asp.


Advices for modeling facts, time hierarchies based on two conditions

You need a date dimension table, and it should look something like this:

create table calendar (
  date_id int primary key,
  name text not null unique,
  date_iso date unique,
  year smallint,
  quarter_of_year smallint,
  month_of_year smallint,
  day_of_year smallint,
  day_of_month smallint,
  fiscal_year smallint,
  quarter_of_fiscal_year smallint,
  month_of_fiscal_year smallint,
  ...

);

and insert data like this:

insert into calendar values
(0, 'N/A', null, ...),
(20130703, '2013-07-03', '2013-07-03', 2013, 3, 7, 184, 3, ...),
(20130704, '2013-07-04', '2013-07-04', 2013, 3, 7, 185, 4, ...);

Notes:

use a smart key (with the date as the key, like 20130703) as the primary key for your date dimension, for easier fact table partitioning. this is the only time you should use a smart key in your warehouse. use meaningless surrogates otherwise.
use a text 'name' field to allow for 'N/A' dates
you should have ONE date dimension table and your fact table date keys should all point to it
you should not include times in your date dimension table. Consider a time dimension table if you need to track times, with say minute granularity.


Unfortunately, mysql_config contains quite a bit of black magic and quite a bit of blatant sketchiness.  It's notoriously untrustworthy if you have more than one copy of it or the version doesn't match your server build.

Unfortunately, too, if you're using one from a distro (not official binaries) then that utility may not even be in the same package as the server... it's bundled with the client, which could easily be 32 bit and work just fine with a 64-bit server.

$ mysql_config
The program 'mysql_config' can be found in the following packages:
 * libmysqlclient-dev
 * libmariadbclient-dev
Try: sudo apt-get install <selected package>

Not confidence-inspiring.

$ mysql_config --version
5.5.40

Neat, I don't even have server on this particular system, and never have.

So, that gives you a sense of how much hocus pocus this little utility can sometimes offer up.

Fundamentally, though, there's this: wrong ELF class: ELFCLASS32 means you compiled the plugin for 32 bit, but your server is indeed 64 bit.

The missing piece from your compiler flags is -m64.  If you add that and remove the -m32 (I'm not sure where that's coming from... mysql_config doesn't include either one on my test system) then you should be able to get that plugin to compile and install.

You have the plugin in the correct directory, or the system wouldn't have found the file to complain about the architecture it was compiled for.


Improve performance with the WHERE NOT IN sub-select clause

With queries like this is it often more efficient to perform a LEFT OUTER JOIN instead of the NOT EXISTS style check, it often implies a full index scan (or table scan without the right indexes in place) but with many rows in the main table(s) this is less expensive than the large number of index seeks (one on the reference table for each row returned from the main table) that would otherwise result. Some query planners are quite bright about spotting this equivalence and using the alternate plan where it is the better choice, but it doesn't sound like this has happened in your case.

Try something like:

SELECT t1.CUSTID, COUNT(*)
FROM   CUST_TRX t1
LEFT OUTER JOIN
       CUST_TRX t2
ON     t2.CUSTID=t1.CUSTID
AND    t2.DATED<CURRENT_DATE-365
WHERE  t2.CUSTID IS NULL
GROUP BY t1.CUSTID

(note: I'm not familiar with firebird, so the above syntax may need tweaks but should illustrate the point)

Without the WHERE  t2.CUSTID IS NULL every row from t1 with matches in t2 will be output once for every match found in t2 and those with no matches in t2 will be output once but with any columns selected from that object set to NULL. The WHERE clause then screens out the matches.

Depending on the DB engine's abilities, especially if the amount of data in the reference object (CUST_TRX with a filter applied here) is huge, this may be significantly less efficient than the WHERE <something> NOT IN or WHERE NOT EXISTS options, so benchmark over realistic data sets first before using the method. It often works out much more efficient with MS SQL Server in cases where the query planner doesn't notice that the WHERE NOT IN arrangement can be performed this way more efficiently.

Also if you do it this way around leave a comment in the code (and/or supporting documentation) to say that you are doing this as an equivalent to WHERE <something> NOT IN or WHERE NOT EXISTS which you expect to be more efficient. You'll remember it and an experienced SQL person will recognise the pattern, but other people looking at the code might not immediately understand the intent/reason and flip it back to using WHERE NOT EXISTS for clarity as that reads better as on English sentence.


Stored procedure runs forever, but inner query runs fine

It is difficult to tell just from examining the code if it is running forever because of an infinite loop or blocking - my guess is the latter. But here is a slightly different approach that avoids some of the things that could be problematic:

CREATE PROCEDURE dbo.pr_BuscaLog
  @qtdDias INT
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @cmd NVARCHAR(MAX) = N'', @dataLimite DATETIME = CONVERT(SMALLDATETIME,
      DATEADD(DAY, @qtdDias, GETDATE()));

  SELECT name INTO #tmp FROM sys.databases WHERE database_id > 4;

  -- delete the ones that don't have that view
  SELECT @cmd += N'DELETE #tmp WHERE name = N''' + name
      + ''' AND NOT EXISTS (SELECT 1 FROM ' + QUOTENAME(name)
      + '.sys.views WHERE name = N''vwLogProjetos'');' FROM #tmp

  EXEC sys.sp_executesql @cmd;
  SET @cmd = N'';

  SELECT @cmd += N'INSERT PainelControle..tblLogProjetos(...columns...)
    select ...columns... FROM ' + QUOTENAME(name)
      + N'.dbo.vwLogProjetos as T1 with (nolock)
          where DtTrabalho >= @dt and not exists
          (
            select 1 from PainelControle.dbo.tblLogProjetos T2
             Where t2.BancoDados collate SQL_Latin1_General_CP1_CI_AS
             = t1.BancoDados collate SQL_Latin1_General_CP1_CI_AS
             and t2.codlog = t1.codlog
          );' FROM #tmp;

  BEGIN TRY
    EXEC sys.sp_executesql @cmd, N'@dt DATETIME', @dataLimite;
  END TRY
  BEGIN CATCH
    insert into dbo.tblLogProjetosErro (rotina, comando,Data)
      values ('pr_BuscaLog', @cmd + ERROR_MESSAGE(), GETDATE());
  END CATCH
END

Some things I've changed (or that you should):

No explicit loops. Contrary to popular belief, a while loop is not faster than a cursor (see this post), but neither is needed here.
Don't count when you don't have to - see this post.
I parameterized the date value instead of dealing with messy string concatenation (see this post and this post). This is harder to avoid with an entity, but should always be done with variables.
Please stop using lazy shorthand; e.g. use DATEDIFF(SECOND instead of DATEDIFF(s - see this post.
Use the schema prefix. Always.


Why no rows in sys.dm_os_cluster_* on Availability Group?

This dmv is for Failover Cluster Instance also known as FCI and not for Windows Server Failover Cluster (WSFC).

  These properties are used to set the values that affect failure
  detection, failure response time, and the logging for monitoring the
  health status of the SQL Server failover cluster instance.

Details here:

https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/sys-dm-os-cluster-properties-transact-sql


How to know the Database user Login History from begining?

No, SQL Server does not track successful logins by default. And even if you turn that option, it'll only keep it back through the current log + 6 previous (again, by default), and those logs will be bloated and full of noise. You may wish to implement a logon trigger going forward, but going back in time, this information is simply not available.


MySQL speed up aggregation query on table

I think the best thing to do is create a generated column and use that as your index. Consider this:

 CREATE TABLE `meterreadings` (
 `Id` bigint(20) NOT NULL AUTO_INCREMENT,
 `meterid` varchar(16) DEFAULT NULL,
 `metervalue` int(11) DEFAULT NULL,
 `date_time` timestamp NULL DEFAULT NULL,
 `fifteen_mins_date` datetime AS
    (concat(DATE_FORMAT(date_time, 'Y-m-d H:'),
            15*(minute(date_time) div 15),
            ':00')) STORED,
 PRIMARY KEY (`Id`),
 KEY `meterid` (`meterid`),
 KEY `fifteen_mins_date` (`fifteen_mins_date`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

Then, your query could look like this:

SELECT AVG(metervalue) as value, DATE_FORMAT(min(date_time), "%d %b %Y %H:%i") as label
FROM meterreadings WHERE meterid IN (SELECT meterid from devices)
   AND fifteen_mins_date BETWEEN '2018-06-23' AND '2018-06-24'
GROUP BY `fifteen_mins_date`
ORDER BY min(date_time) ASC;


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

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

ALTER SCHEMA dbo TRANSFER dummy_schema.object_or_synonym;

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

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


Repair table field: Import/append records from specific backup field if they are not null

There are two solutions I can immediately think of for your current situation:

Write a tool to parse the MySQL dump file, extract the records you're interested in, and insert them.
This sounds like a lot of work, and a lot of time (especially if your dump is large).
Do as you suggest in the question: Reimport the data somewhere and use a SQL query to get the fields you want.

My vote would be for (2) because you can be more precise about the selection of the rows (and the SQL will probably be faster than writing a script to parse the dump file. You'll need a machine running MySQL, with enough room to import your data, but you might be able to do that on your main server by restoring to an alternate database.


how to update a column from another database?

If you want to write an SQL statement that affects tables from different databases, the best option is to use a postgres_fdw foreign table.

But perhaps it is simpler to extract the data from one database to a file using COPY and to load the file into the other database.


Is it possible to drop tablespace CASCADE in MySQL?

According to the documentation it is not possible:

https://dev.mysql.com/doc/refman/5.6/en/drop-tablespace.html

Important
The tablespace to be dropped must not contain any data files; in other
words, before you can drop a tablespace, you must first drop each of its
data files using ALTER TABLESPACE ... DROP DATAFILE (see Section 13.1.8,
“ALTER TABLESPACE Syntax”).

You can however locate the files that has to be dropped first, via INFORMATION_SCHEMA.FILES:

select ...
from INFORMATION_SCHEMA.FILES
where TABLESPACE_NAME = ?

I'm not able to test this since I don't use any of the engines that support tablespaces.


Select from a table where column row has Zero (0) or Zeros (000000)

Something like, if your acctno has empty strings you will need to elaborate this query a bit
create table #t (name varchar(20), acctno varchar(30))

insert #t values ('Tim', '000')
insert #t values ('Jim', '00750')
insert #t values ('Bim', '0000000')

select * from #t where replace(acctno, '0','') = ''


pg_replication_slots: how to determine how far behind a consumer is?

The view you are looking for is pg_stat_replication.  In 9.6 you can only get this info expressed in LSN, not clock time.  Starting in v10, you can get it in clock time.


How to bulk convert ISO8859 text in Text columns to UTF8 in nvarchar(max) columns?

SQL Server 2008r2 dopes not support UTF8 (such support does not arrive until the upcoming 2019 release) it only supports UCS2 (essentially a subset of UTF16). Because of this there is unlikely to be a practical solution in SQL Server itself.

You may have to code up a tool to pull the data out, convert it, and update the DB with the newly converted values.

On rereading the question, I notice you mention mysql as the target database, though your tags do not mention that. The types you mention (NVARCHAR etc.) are not mysql types. You may need to clarify the question and update the tags.


[!!] Total fragmented tables: 551  -- bogus, ignore it
[0.071 qps],  -- very lightly loaded
[!!] Maximum possible memory usage: 126.3G (100% of installed RAM)  -- bogus, ignore it
[OK] Highest usage of available connections: 0% (2/600)  -- consider lowering max_connections
[OK] Key buffer size / total MyISAM indexes: 1.0G/153.6M  -- lower key_buffer_size
[!!] Query cache efficiency: 0.0% (0 cached / 3K selects)  -- Turn the QC off
[OK] Sorts requiring temporary tables: 5% (7 temp sorts / 125 sorts)  -- Let's see some slow queries and help you optimize them
[!!] Joins performed without indexes: 252  -- ditto
[OK] Temporary tables created on disk: 21% (344 on disk / 1K total)  -- ditto
[OK] Open file limit used: 0% (48/20K)  -- 20K?
[!!] InnoDB data size / buffer pool: 108.0G/107.0G  -- bogus; it's a cache!

General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance  -- NO!
    MySQL started within last 24 hours - recommendations may be inaccurate  -- true
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***  -- bogus
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_limit (> 1M, or use smaller result sets)  -- no, set to 0
    innodb_buffer_pool_size (>= 107G)  -- No, it is dangerously high now (assuming you have 128GB of RAM)

For more analysis, provide SHOW VARIABLES and SHOW GLOBAL STATUS, plus RAM size.  (Due to limitations here, you may need a postit site.)

The biggest problem seems to be poorly designed/indexed queries.  Those cannot be fixed by tuning.


SQL Server mirroring / failover alternative

I'm not sure I understand, you state you are going to use mirroring, in standard it is still available so why can't you use that?  Or is the question that is there anything else available?

You can also use Failover clustering in standard edition, the limit is you can only use 2 nodes but I don't see that as an issue for your request.

Also, if you are using any virtualization technology (VMware, Hyper-V etc) there may be options that you can speak to your virtualization about which are edition (not to mention software) agnostic.

Mat


Oracle Enterprise Manager Jobs Failing Initialization

Oracle support has been no help.  After about a week of the jobs sometimes running they stopped running completely.  Every single job got an initialization error every time it ran and every time it was retried.  We are in the process of re-creating the repository and migrating to dbms_scheduler jobs.


SQL Server 2016 (SP2-GDR) - correct upgrading path

Yes, it's that simple. Applying a cumulative update applies all the fixes up to that point. More info here.


The -S portion of the osql command tells osql where to find the SQL instance that you want to interact with. It typically consists of two parts -- the server name and the instance name.
You'll need to replace "myserver" with the name of the server that your SQL installation is running on. And, if your SQL installation is using a named instance, you'll need to replace "sqlexpress" with the name of that instance. If, on the other hand, you installed SQL as a default instance, you can leave out the part after the backslash.
Examples
I've got SQL running on a machine named DougSQL01. As a default instance. So my osql command would look like this:
osql -S DougSQL01 -E -i "c:\test.sql" -o "c:\output.txt"

I've got another instance of SQL running on another server, DougSQL02. This one is a named instance, with an instance name of "Testing". In this case, my osql command would look like this:
osql -S DougSQL02\Testing -E -i "c:\test.sql" -o "c:\output.txt"

There are some other subtleties about how to specify a server and an instance, like specifying a port instead of an instance name, using an IP address instead of a hostname,  specifying "mssqlserver" for a default instance, and the ability to leave out the name of the instance if a named instance is running on port 1433, but none of them are likely to apply in your case.


Possible to view historical login and database information from backup of master database

While this is possible to do with the Dedicated Admin Connection and a modified version of sys.server_principals, it is unsupported.

I would rather spend this time to restore master properly on a new instance (on Developer Edition under a VM makes the most sense), and then access it from there.


Does an index with multiple columns make a similar index redundant?

In this case I consider the first index redundant (and less useful). Note that dropping the index will invalidate any plan that references it, so the next time you run any of those queries, you may see a blip in performance due to the required recompile to access the second index instead. While both indexes existed, any query that only needed to act on ActivityName would probably have chosen the first index since it is skinnier.

If the second column in the second index was something wider, then I might be a little less cavalier about my advice to drop the first index. If that column were a wide varchar, for example, then the additional I/O required to use the index when necessary (and when the varchar column were not needed for output or filtering) would actually make the first index (even though it is technically redundant) more desirable to satisfy that type of query.

So, just a long-winded way to say: it depends. :-)


Yes, modifying all 11 NC indexes to be filtered indexes (CREATE INDEX ... ON ... WHERE DeletedDate IS NULL) would help. This way you get two advantages:

The Query Optimizer will know that any row coming from these indexes already does satisfy your query filters on DeletedDate, so it won't have to lookup the clustered index to double check the DeletedDate
All NC indexes will be 10-15% smaller in size, requiring less memory and fewer IO operations to lookup.

The trade off is that any query that looks after soft deleted rows (and there have to be some query, otherwise why do the rows exist?) will be unable to use these NC indexes.


Table with two customer IDs that I want to relate to the entity

You want to get names matched to two separate fields.

Fields are indepentent.

So the tables which are used to find relative names must be independent too.

So you need 2 separate copies of names table:

SELECT tr.*,
       n1.name AS buyer_name,
       n2.name AS seller_name
FROM transactions AS tr
JOIN names AS n1 ON tr.buyer = n1.id
JOIN names AS n2 ON tr.seller = n2.id


Visual Studio database project and Enity Framework code-first

Here is and article explaining a good way to do this will little extra effort. The article describes comparing the SQL Database Project with the EF generated LocalDB to persist schema changes.

Use EF to make schema/entity updates like you would normally in you development workflow. The use the EF database as your comparison DB to update your SQL Database project.

Going the other way means updating objects like stored procedures, logins, additional indexes, and other more administrative/fine tuning objects.

There is a caveat though... EF does not have the tooling to detect these changes automatically, so be disciplined in using the right tools for the right jobs. EF for models and SQL DB Project for... well, everything not controlled with EF code migrations :-)

SQL Database Project and EF


What to do with those invalid JAVA CLASS object type objects in SYS prior to the upgrade the oracle db from 10g to 11g?

From this blog you can try this on development first...

SQL> spool resolve.sql;

SQL> select ‘alter java class “‘||object_name||’” resolve;’
  2  from user_objects
  3  where object_type like ‘%JAVA%’;

SQL> spool off;

SQL> @resolve

The clean sweep approach would be to remove and reinstall the java JVM

The Java VM is created and populated with system classes during CREATE OR REPLACE JAVA SYSTEM command.  Run rmjvm.sql to remove the JVM first, then bounce the database then initjvm.sql

I repeat, these should be tried on your development stack first!


Environment Varible PATH exceeding recommended length solution?

I found the exact solution: and your exactly correct Max Vernon.

"Problem causes problems when installing an Oracle 11g database on a machine which already has a lot of different software installed,
On win 7 64bit, go to PATH variable(Right click on my computer>properties>Advanced system Settings>Advanced Tab>click on Envirnment variable>in system variable - look for Path>

When the failure occurs during installation of Oracle 11g, click on cancel, you will need to restart the installation from scratch
On win 7 64bit, go to PATH variable(Right click on my computer>properties>Advanced system Settings>Advanced Tab>click on Envirnment variable>in system variable - look for Path>
Find the Path environment in the System Variables window and edit it, click on it and select all the values within it,
paste these into a word document or something and save it. You might wish to see whats needed and what isnt but thats a different problem !
Clear the Path environment and simply add a single directory such as c:\Seer
Start the installation of Oracle 11g again and sit there and wait while it chunders through the processing.
Return to the Environment Variables and paste the originals back after the new ones put in by the installation "


Postgresql cannot restore partial pg_dump "relation "public.my_table_seq" does not exist"

I had also a similar case and I solved it via doing theese steps:

First of all I made an initial psql_dump with the tables I wanted to dump:

 pg_dump -t my_table1 -t mytable2 > my_dump.sql

Then I listed any sequence existing by filtering them, from the existing dump I made. In case of larger dumps use tools offered in bash or in powershell in order to filter them out. In other words you need to search any lines having DEFAULT nextval( and keep only the sequence name.
Once found the all the sequences do an another pg_dump as the one follows:

     pg_dump -O -x -t my_table_seq -t another_table_seq -t my_table1 -t mytable2 > my_dump.sql

In the command above the my_table_seq and the another_table_seq are sequences used. Also with the -x and -O parameters I ewxclude dumping any permission/role related sql so in the new database will contain only the tables and their respective data.


Very weird result of CTEs and subqueries

In the last, external query you cross join foo with t1 and t2, while you only need (and use) columns from foo and t2.

So remove the t1 join:

WITH
    ...
SELECT
FROM (
     ...
     ) foo, t2 ;

The whole external query could be simplified further, as you don't really need the t2 used four times, in the three subqueries plus the main query. You can use it once and still join it to t1 three times:

WITH t1 AS ...,
     t2 AS ...
SELECT std.id   AS std_id,   t2.max_std_avg,
       pos.id   AS pos_id,   t2.max_pos_avg,
       gloss.id AS gloss_id, t2.max_gloss_avg
FROM t2
     JOIN t1 AS std   ON std  .std_avg   = t2.max_std_avg
     JOIN t1 AS pos   ON pos  .pos_avg   = t2.max_pos_avg
     JOIN t1 AS gloss ON gloss.gloss_avg = t2.max_gloss_avg
;

Another way to get the same results but in a slightly different format (3 rows instead of 1), would be to not use joins at all but more window functions. You could also:

combine the two CTEs into one.
remove the accounts from the first CTE. If there is a FOREIGN KEY from orders to accounts, the results will be the same.

The new query:

WITH t1 AS (SELECT account_id,
                   AVG(standard_qty) AS std_avg,
                   AVG(poster_qty)   AS pos_avg,
                   AVG(glossy_qty)   AS gloss_avg,

                   MAX(AVG(standard_qty)) OVER () AS max_std_avg,
                   MAX(AVG(poster_qty))   OVER () AS max_pos_avg ,
                   MAX(AVG(glossy_qty))   OVER () AS max_gloss_avg
            FROM orders o
            GROUP BY account_id)

SELECT t1.*,
       CASE WHEN std_avg   = max_std_avg
           THEN 'max_std_avg'   ELSE NULL
       END AS std_result,
       CASE WHEN pos_avg   = max_pos_avg
           THEN 'max_pos_avg'   ELSE NULL
       END AS pos_result,
       CASE WHEN gloss_avg = max_gloss_avg
           THEN 'max_gloss_avg' ELSE NULL
       END AS gloss_result
FROM t1
WHERE std_avg   = max_std_avg
   OR pos_avg   = max_pos_avg
   OR gloss_avg = max_gloss_avg ;


Create sequence starting with maximum id from a table

Create the sequence, then set the value afterwards:

create sequence some_seq;
select setval('some_seq', (select max(id) from some_table));

You probably also want to "link" the sequence to the table's column as well:

alter sequence some_seq
  owned by some_table.id;

For modern Postgres version (>= 10), it is recommended to use an identity column instead:

alter table some_table
   alter id add generated always as identity;

select setval(pg_get_serial_sequence('some_table', 'id'), (select max(id) from some_table));


Why can't I double quote built-in type names in Postgres?

You can only quote identifiers, but not keywords. Data types prescribed by the SQL standard, such as int, integer, boolean, bigint etc. are keywords and, when put in quotes, are not recognised as such, producing errors. bool and int8, on the contrary, are, as you say, Postgres-specific aliases and are therefore identifiers, which allows them to be quoted.

Note that aliases follow the rules for quoted identifiers:

SELECT CAST (1 as BOOL); -- succeeds
SELECT CAST (1 as "BOOL"); -- fails

A little explanation of how the parser works.

  A token can be a key word, an identifier, a quoted identifier, a literal (or constant)

The order of precedence in the quote above is significant. When seeing an unquoted sequence of characters, the parser converts it to lowercase and first tries to match it against the known keywords. If unsuccessful, it will then try to find an identifier with that name. When seeing a quoted character sequence the parser knows that this must be an identifiers (since keywords are never quoted).

So,

BOOLEAN --> to lowercase --> boolean --> is keyword? yes --> done.
BOOL --> to lowercase --> bool --> is keyword? no --> is identifier? yes --> alias for boolean --> done.
"BOOL" --> quoted identifier, don't change case --> does not exist --> done.


(MySQL) SET column with values from a foreign key constrant

Schematically.

CREATE TABLE users ( id PRIMARY KEY, ... );
CREATE TABLE groups ( leader_id REFERENCES users (id),
                      member_id REFERENCES users (id) );

The keys (including primary) and constraints in groups are dependent by:

does the user may be a member of one group only, or of a lot of groups;
does the group leader may be a member of another group (rings possible);
does a leader must (or may) be a member of his own group.


Explanation to this problem was that replication halted on one database in Availability group. Transaction log backup was successful but log file could not be emptied because  replication to secondary server was paused. I did not notice this looking at the databases initially. And didn't notice that log_reuse_wait_desc was showing AVAILABILITY_REPLICA on the primary server (looking at the wrong line in result). But when checking again after Shankys reply I discovered that and Availability group Dashboard showed that replication had halted. Resumed replication, backed up log again and then I was able to shrink logfile to a more accurate size.


Well, having occupation as part of Person isn't very well normalized.  One person can have multiple occupations, so that should go in a different table or set of tables, depending on how normalized your schema is.

I am confused - how is a "bank name" an occupation?  Or even related to a person?

I would note that while the theory of a Person table is sound, in practice it can get fairly interesting; for example, perhaps you start with U.S. SSNs.  Then you get some Canadians with TINs (also U.S. 9 digit federally issued identifiers).  And companies with TINs.  Then you get some people with Mexican CURP values (18 character alphanumerics).  Now you have a person with a CURP and a TIN both.  Now you have a person with a Chinese identification number as well.

Let's not even get into truly international addresses :).

You may want to consider normalizing further, but definitely work out how a single human being with multiple roles (employee, vendor, customer) and multiple locations (different addresses or cities or regions or countries) and multiple ID's (TIN, SSN, etc.) is going to work.

Likewise with organizations - perhaps your food service vendor is also a customer, and then comes to work for you part-time.

EDITED TO ADD: I would also note that the "password" field horrifies me.  Please read How to securely hash passwords? and replace the password field with fields for the plaintext random per-person salt, the PBKDF2/Bcrypt/Scrypt password hash, and ideally the # of iterations (so it's easy to increase).


Benefit of filegroups and setting filegroups to read-only

Your question is a bit wide-ranging, however some of the main points include the following.

Multiple file groups, in general:

can be a performance improvement, assuming they are physically isolated.  Typically, you might create one filegroup for clustered indexes, one filegroup for non-clustered indexes, and one filegroup for heaps.  Or you might create multiple filegroups in a partitioning situation.
can improve recoverability by enabling piece-meal restore.  Piece-meal restore can be used to bring critical production data online quickly while allowing the restore of archival data to not impact online activities.  Be aware online piece-meal restore is an "Enterprise-only" option.  I've written a blog post showing how to do piece-meal restores over at SQLServerScience.com
do not need to be backed-up individually, however they can be backed up individually, which might be necessary for VLDBs.  Note, when backing up individual filegroups you must also backup the primary filegroup.

Read-only file groups:

Can be used to reduce contention for reporting-only data.
Can be used, obviously, to prevent modifications to data.
Do not need to have the recovery process ran when the database is brought online.  This can substantially improve recovery time for VLDBs.
Also, while read-write filegroups cannot be used with NTFS filesystem-based compression, read-only filegroups can.  The implication here is that for a very limited set of systems you may be able to see a slight performance improvement for VLDB data that resides on slower disks by compressing the readonly filegroup files on disk.  This would of course need to be evaluated on a case-by-case basis vs the CPU overhead necessary to decompress the NTFS clusters as they are read off the disk.

Myths surrounding read-only filegroups:

"Statistics will never be updated for tables stored on a read-only filegroup."  This is totally incorrect, and can be proven by creating a test-bed as shown in my blog post on SQLServerScience.com.  Thanks are due to @PaulWhite for pointing this out to me.
"No locking occurs in read-only databases. This can cause faster query performance". This is incorrect unless the entire database is read only.  A read-only filegroup alone does not allow shared locks to be skipped.


Is it possible to re-initialize sequence

This thread over on Stack Overflow has the information you are looking for. There are two main options:

Drop the sequence and re-create it, or
Use a trick, courtesy of Tom Kyte:

either as a one-off:

alter sequence seq increment by {minus_the_current_value} minvalue 0;

or you can create a procedure if you need to do this regularly:

create or replace
procedure reset_seq( p_seq_name in varchar2 )
is
    l_val number;
begin
    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by -' || l_val ||
                                                      ' minvalue 0';

    execute immediate
    'select ' || p_seq_name || '.nextval from dual' INTO l_val;

    execute immediate
    'alter sequence ' || p_seq_name || ' increment by 1 minvalue 0';
end;
/


You must create a temp table with Audit.ErrorLog Columns and then get output records.

Declare @temp Table (ErrorLogID Int, ErrorReported Int, Error NVarChar(100))

UPDATE Audit.ErrorLog
SET ErrorReported = 0
OUTPUT inserted.*
INTO @temp

SELECT *
FROM @temp
FOR XML PATH('Error'), ROOT ('Errors')


Adapted from here; see that for rationale.

For separated tables, each should look similar to this:

CREATE TABLE XtoY (
    # No surrogate id for this table
    x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
    y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
    # Include other fields specific to the 'relation'
    PRIMARY KEY(x_id, y_id),            -- When starting with X
    INDEX      (y_id, x_id)             -- When starting with Y
) ENGINE=InnoDB;

For the single table:

CREATE TABLE XtoY (
    # No surrogate id for this table
    x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
    y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
    type ENUM('foo', 'bar') NOT NULL,  -- or TINYINT UNSIGNED  ***
    # Include other fields specific to the 'relation'
    PRIMARY KEY(x_id, y_id),            -- When starting with X  ***
    INDEX      (y_id, type, x_id)       -- When starting with Y
) ENGINE=InnoDB;

A third option is to put both types in a single table:

CREATE TABLE XtoY (
    # No surrogate id for this table
    x_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to one table
    y_id MEDIUMINT UNSIGNED NOT NULL,   -- For JOINing to the other table
    type ENUM('foo', 'bar') NOT NULL,  -- or TINYINT UNSIGNED
    # Include other fields specific to the 'relation'
    PRIMARY KEY(x_id, type, y_id),      -- When starting with X  ***
    INDEX      (y_id, type, x_id)       -- When starting with Y
) ENGINE=InnoDB;

(*** indicates the only deviation(s) from previous version.)

Which is "best"?  I don't know.  And if you add FOREIGN KEYs, things get messier.


Is It Okay to Leave the Connection Open and Set CommandTimeout for Long Durations?

I solved this by creating a list of tasks and then starting all the long running tasks at the end of the processing and did not wait for them to finish.  This way, the program is able to exit.


insert results from store procedure with parameters into table

Here is an example of inserting the results of a store procedure call into a table variable (of course,you can use a real table). The stored procedure takes 2 parameters and returns their values as a result set.

--demo setup
drop procedure if exists [dbo].[sp1]
go
CREATE PROCEDURE [dbo].[sp1] @P1 INT
    ,@P2 INT
AS
BEGIN
    SELECT @p1 AS p1
        ,@p2 AS p2
END
go

--create table variable to hold results of SP call
Declare @T table (P1 int, P2 int)

--insert into the table variable the results of the SP call
Declare @P1 int = 1, @P2 int = 2
insert into @T
exec [dbo].[sp1] @P1, @P2

--select from the table variable to verify
select * from @T

P1  P2
1   2


How to create a role that cannot create or alter a table?

The privilege to create tables is granted to new roles automatically. You need to REVOKE the role's CREATE privilege on the schema (not the database):

REVOKE CREATE ON SCHEMA myschema FROM manager;

Per documentation on GRANT:

  CREATE

  For databases, allows new schemas to be created within the database.

  For schemas, allows new objects to be created within the schema. To rename an existing object, you must own the object and have this
  privilege for the containing schema.

  For tablespaces, allows tables, indexes, and temporary files to be created within the tablespace, and allows databases to be created that
  have the tablespace as their default tablespace. (Note that revoking
  this privilege will not alter the placement of existing objects.)

And REVOKE all direct privileges any role might have in respective schemas. By default, plain roles only have privileges for the schema public and schemas they create themselves.


There is no good reason to keep autovacuum disabled in a production database, and you will run into problems if you do that.

It may make sense to disable autovacuum during exceptional operations, where you run a manual vacuum anyway and don't want your performance sapped by a background job, but more often than not such trickery is a premature optimization.

I would run VACUUM (FULL) on the database, followed by VACUUM (ANALYZE). That shoud take care of any damage done.

Then enable autovacuum.


Recover MySQL database from data folder without ibdata1 from ibd files

MyISAM

For a MyISAM table mydb.mytable, you should have three files

\bin\mysql\mysql5.6.12\data\mydb\mytable.frm
\bin\mysql\mysql5.6.12\data\mydb\mytable.MYD
\bin\mysql\mysql5.6.12\data\mydb\mytable.MYI

They should already be accessible as a table since each file contains needed data, metadata, and index info. Collectively, they form the table. There are no external storage engine mecahnisms to access.

InnoDB

Take a look at this Pictorial Representation of InnoDB

The only thing that attaches ibdata1 to the .ibd files is the data dictionary.

Your mission, should you decide to accept it, is to create each table and swap in the .ibd

Before you do anything, make a full copy of "\bin\mysql\mysql5.6.12\data" to another

Here is a sample

Suppose you have a database mydb with the table mytable. This means

You have the folder \bin\mysql\mysql5.6.12\data\mydb
Inside that folder, you have

mytable.frm
mytable.ibd

You need the .frm. If you look at my post How can extract the table schema from just the .frm file?, you can download a MySQL utility that can generate the SQL needed to create the table.

You should now do the following

Move mytable.ibd to \bin\mysql\mysql5.6.12\data
Run the SQL to create the InnoDB table
Login to mysql and run ALTER TABLE mydb.mytable DISCARD TABLESPACE; (This will delete \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd)
Copy \bin\mysql\mysql5.6.12\data\mytable.ibd into \bin\mysql\mysql5.6.12\data\mydb
Login to mysql and run ALTER TABLE mydb.mytable IMPORT TABLESPACE; (This will register \bin\mysql\mysql5.6.12\data\mydb\mytable.ibd into the data dictionary)

After this, the table mydb.mytable should be fully accessible. You can test that accessibility by simply running:

SELECT * FROM mydb.mytable LIMIT 10;

Give it a Try !!!

DRINK (Data Recovery Incorporates Necessary Knowledge) Responsibly


If in mysql I have string `xxx yyy` - how can I SELECT this if user input `xxxyyy`?

Replace space and compare:
... where replace('xxx yyy',' ','') = 'xxxyyy'

or generally it can be: ... where replace(column,' ','') = 'xxxyyy'

But not that this may not like your indexes on the columns.


Create the first table, then load each table into the merged_table:

CREATE TABLE merged_table LIKE `1`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `1`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `2`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `3`;
...
...
;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `999`;
INSERT INTO merged_table (url,title,content) SELECT url,title,content FROM `1000`;

Here is a way to script it (if the database is mydb)

DB="mydb"
MYSQL_CONN="-uroot -ppassword"
echo "CREATE TABLE merged_table LIKE \`1\`;" > MergeData.sql
SQL="SELECT CONCAT('INSERT INTO merged_table (url,title,content)"
SQL="${SQL} SELECT url,title,content FROM \`998\`')"
SQL="${SQL} FROM information_schema.tables"
SQL="${SQL} WHERE table_name REGEXP "$[0-9]"
mysql ${MYSQL_CONN} -ANe"${SQL}" >> MergeData.sql

Look at the output of the file

less MergeData.sql

If it looks like the output I gave at the top of my answer, then run

mysql ${MYSQL_CONN} -D${DB} < MergeData.sql

Give it a Try !!!


SQL Server creates different plans when OR condition is re-arranged

But why doesn't SQL server sees both queries as one? After all, a AND
  (b OR c) = (a AND b) OR (a AND c)?

Logically it is the same, and it will get the same results.

Assumptions

My assumptions are that for the 'faster' plan, the optimizer is not considering some filter statements at the top of the OR to be the same as some filter statements at the bottom.  I might be entirely off base here.

the reasoning for getting these assumptions are based on this filter predicate:

This filter predicate uses the result of the join between the Main table and manymany table.

Notice that EXPR1021 and EXPR1022 in this filter are expressions  created from the scalar operator on the manymany table.

This filter  consists of two parts, the first one with (.. AND .. OR .. AND ..)
and the second one plain AND filtering

(getdate()>=[Expr1021]
AND getdate()<=[Expr1022]
AND getdate()>=[DB1].[dbo].[main].[FromDate]
AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL
OR getdate()>=[Expr1021]
AND getdate()<=[Expr1022]
 AND getdate()>=[DB1].[dbo].[main].[FromDate]
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
 AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL)

 AND (getdate()>=[DB1].[dbo].[main].[FromDate]
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
 AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL OR getdate()>=[DB1].[dbo].[main].[FromDate]
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
 AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL)

As you can see, the only difference above and below the OR in the first part of this filter is

AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL

VS

AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL

And the second part needs to be true no matter what, as they are AND predicates without any OR's.

Resulting in extra calculations of the same functions, that in my opinion are not needed.
Again, my guess here is that the reason that sql server does these calculations is that it does not know that they are the same.

For some other parts of the where clause, it does know that these are the same, e.g. In the main table, the statusid = 1 is only evaluated once:

And in the manymany table, the same statement is evaluated twice:

In the 'slow' plan, the statements are not added together with OR clauses, and that is why the optimizer is generating a different plan, applying filter predicates on the tables separately (and no duplicate filters).

End of assumptions

Comparison of the two plans

I think that you got lucky with the performance of the 'fast' plan, but that the 'fast' plan might turn ugly when the matching data increases. It can depend on where and when you are applying your filters (and other factors).

The fast plan filtering

In the 'fast' plan: sql server applies some of the filters after the join of
  the main table with the manymany table as a result of different combinations with the two OR's  + (AND ... AND ... AND...) blocks.
The columns from the maintable are filtered after finding all the possible combinations with the manymany table.

As a result, the same predicate is executed twice on the manymany table:

For the predicates above and below the OR.

But this is not the case for some of the seek predicates on the main table

After this the join happens, and an even bigger filter predicate on the results of the join between main and manymany happens, again for all the possible combinations

Notice that EXPR1021 and EXPR1022 in this filter are expressions  created from the scalar operator on the manymany table.

This filter  consists of two parts, the first one with (.. AND .. OR .. AND ..)
and the second one plain AND filtering

(getdate()>=[Expr1021]
AND getdate()<=[Expr1022]
AND getdate()>=[DB1].[dbo].[main].[FromDate]
AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL
OR getdate()>=[Expr1021]
AND getdate()<=[Expr1022]
 AND getdate()>=[DB1].[dbo].[main].[FromDate]
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
 AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL)

 AND (getdate()>=[DB1].[dbo].[main].[FromDate]
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
 AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL OR getdate()>=[DB1].[dbo].[main].[FromDate]
 AND getdate()<=isnull([DB1].[dbo].[main].[UptoDate],'2050-01-01 00:00:00.000')
 AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL)

As you can see, the only difference above and below the OR in the first part of this filter is

AND [DB1].[dbo].[main].[TextCol1] IS NOT NULL

VS

AND [DB1].[dbo].[main].[TextCol2] IS NOT NULL

And the second part needs to be true no matter what, as they are AND predicates without any OR's.

Resulting in extra calculations that in my opinion are not needed.

The slow plan filtering

In the 'slow' plan: sql server applies the filter directly to the
Main table as a result of the AND (TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL) part and then joins with the manymany table to filter out the rest to get to 15 rows.

Main table filters

manymany table filters

Some other, sometimes overlapping information:

The slower plan

When we look at the slower plan, the clustered index PK_main is used, into a compute scalar, filter  and nested loops operator:

When we compare this with the estimated rows to be returned, we see a difference:

It is estimating 93 rows to be returned by the predicate on the scan:

Which is actually about 20x less than what was expected, which is 1947 rows.

Afterwards, the Compute scalar or this statement:

 , CASE WHEN TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL THEN -1 ELSE 0 END AS MoreFlag
 , CASE WHEN Stars BETWEEN 1 AND 5 THEN Stars END AS Rating

is evaluated on these 1947 rows.

Then the filter operator (main.TextCol1 IS NOT NULL OR main.TextCol2 IS NOT NULL) to reduce it to 1374 rows.

After this, join these 1374 rows to the dbo.manymany table to get 15 rows returned.

The faster plan

The faster plan is using the NC index: CVR_main_4 on the dbo.Main table,

It is filtering with a seek predicate, returning 27 rows to the nested loops Join operator, again joining with the dbo.manymany table.

And the actual rows returned are even lower than the estimated rows:

27 actual rows for an estimate of 152 rows

Filtering

A big difference is where the filtering happens, where on the 'slower' plan this is being done directly on the dbo.Main table:

With the predicate: TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL

And is applying this filter to 1943 rows.

With the other filtering happening directly on the dbo.manymany table

(seek) predicates on dbo.manymany

While the other OR, on the 'faster' plan, is being filtered after the join from dbo.Main to dbo.manymany, and results in a much bigger filter, on the 27 rows.

Much bigger filter with multiple OR's on 27 rows.

Another difference is the Key lookup operator:

which gets 10 additional columns from the clustered index, but only has to do this for 27 rows.

Another reason that the optimizer chooses the 'slower' plan might be because the optimizer  thinks not looking up the other columns would be better.

Is the fast plan even faster, or always going to be 'faster'?

I do think that, if the data passing through the filter increases, the 'slow' plan will be better. Not only due to the key lookup, but also due to the bigger filter operator further down in the plan.

If that happens, next  to indexing. You could improve the filtering by splitting the query into multiple parts using a UNION statement.

Like so:

SELECT main.MainID, Title, Column1, Column2, Column7, Column4, Column6, Column3, Column5
     , CASE WHEN TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL THEN -1 ELSE 0 END AS MoreFlag
     , CASE WHEN Stars BETWEEN 1 AND 5 THEN Stars END AS Rating
FROM manymany
INNER JOIN main ON manymany.MainID = main.MainID
LEFT JOIN aux ON manymany.AuxID = aux.AuxID
WHERE manymany.WebsiteID = @P1
  AND manymany.Check1 = -1
  AND manymany.Active = -1
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND TextCol1 IS NOT NULL

 UNION

 SELECT main.MainID, Title, Column1, Column2, Column7, Column4, Column6, Column3, Column5
     , CASE WHEN TextCol1 IS NOT NULL OR TextCol2 IS NOT NULL THEN -1 ELSE 0 END AS MoreFlag
     , CASE WHEN Stars BETWEEN 1 AND 5 THEN Stars END AS Rating
FROM manymany
INNER JOIN main ON manymany.MainID = main.MainID
LEFT JOIN aux ON manymany.AuxID = aux.AuxID
WHERE manymany.WebsiteID = @P1
  AND manymany.Check1 = -1
  AND manymany.Active = -1
  AND CURRENT_TIMESTAMP BETWEEN ISNULL(manymany.FromDate, '1950-01-01') AND ISNULL(manymany.UptoDate, '2050-01-01')
  AND main.Active = -1
  AND main.StatusID = 1
  AND CURRENT_TIMESTAMP BETWEEN main.FromDate AND ISNULL(main.UptoDate, '2050-01-01')
  AND TextCol2 IS NOT NULL
ORDER BY SortCode;


Phpmyadmin feed a timestamp column with "0000-00-00 00:00:00"?

If you need to calculate with the timestamp field, you simply may use SELECT UNIX_TIMESTAMP(yourtimestampfield);. I decided to use this instead of the int(11) because of the built in function on update CURRENT_TIMESTAMP which shows this format 0000-00-00 00:00:00.

Also inside of mysql queries yo may use it the reverse way like UPDATE user SET logouttime = FROM_UNIXTIME(UNIX_TIMESTAMP(logintime)+3600) WHERE user_id = 12345;

Hope this helps.


Check the log file, where the error was written on disk or check in the event viewer log


How to get Time in milliseconds & How to add 5 minutes to the milliseconds in mySql

MySQL 5.6.4 has introduced fuller support for fractional seconds in time values. The CURTIME function can now return the current time with up to microseconds. To return it with milliseconds, you call it like this:

CURTIME(3)

However, that will give you a time value, not an integer number of milliseconds that I am assuming you are after. To get the number of milliseconds, you can apply the UNIX_TIMESTAMP function to CURTIME(3) and multiply the result by 1000:

UNIX_TIMESTAMP(CURTIME(3)) * 1000

The result will be a decimal with the scale of 3. You can further cast it to unsigned if you need the result to be an actual integer type:

CAST(UNIX_TIMESTAMP(CURTIME(3)) * 1000 AS unsigned)

So much for the current time in milliseconds. As for adding 5 minutes to it, you can add 300,000 to the last result:

CAST(UNIX_TIMESTAMP(CURTIME(3)) * 1000 AS unsigned) + 300000

Or you can add just 300 (seconds) before the multiplication (similar to Arnel Aves's suggestion):

CAST((UNIX_TIMESTAMP(CURTIME(3)) + 300) * 1000 AS unsigned)

That way the intent might be clearer. But it will probably be even clearer if you add the interval directly to CURTIME:

CAST(UNIX_TIMESTAMP(CURTIME(3) + INTERVAL 5 MINUTE) * 1000 AS unsigned)

Additional information:

Date and Time Functions
Cast Functions and Operators


What is wrong with this syntax of a multi-table DELETE...ORDER BY?

(Serge self-Answered the title question; this Answer tries to look beyond that.)

If the deadlock is caused by the same DELETE being run from multiple threads, then simply ignore the deadlock; the other thread will do the work.

If there are other concerns, there are ways to use subqueries instead of a JOIN.  There is even a kludge where an extra level of subquery is a workaround for another error you may encounter.

If you have massive deletes, then see this for tips: http://mysql.rjweb.org/doc.php/deletebig


SQL - Spilt timestamp into multiple rows


SQL Server database is too large because available space allocated 70% mdf space

The "unused space" is reserved by extents of pages which are maintained to be allocated to your data as your Tables and Indexes grow. The reason it's as big as it is currently is because you had a growth event at some point in the database that caused the MDF to grow.
General recommendation is to not shrink the file back down because it's likely that unallocated (and even unused) space will become allocated and used, and growth operations are heavy and affect performance. (There's also potential risk of data corruption as a result, when doing it to the MDF.)
It is not uncommon to have about 50% of your drive space in Unallocated. You can read more about Unallocated Space here. When SSMS calls it Space Available, it's already reserved by SQL Server but available to become allocated as needed.


ORA-01502: index or partition of such index is in usable state problem

Faced a similar issue.
If you just need to get rid of the error, do:
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;'
FROM DBA_INDEXES
WHERE STATUS = 'UNUSABLE';

This will output ALTER INDEX ... REBUILD; statements for all "unusable" indexes. Run them, so that the indexes can be "usable" again.


Inserting unrelated data while a transaction is occuring

If you can handle all the writes in a single connection, then Plan A.  Note:  Moving from one "page" to another in a browser necessarily leads to another connection, hence Plan B.

Plan A:  BEGIN; write...; COMMIT;  And check for errors.  ROLLBACK on errors and retry the entire transaction.  If not successful, tell the user that his seat on the plane was taken and he must start over.

Plan B:  You must set up another table to keep track of "transactions" and be ready to rollback (etc) things outside of what InnoDB (much less MyISAM) can do for you.

Note:  If Plan A takes more than a few seconds, you are asking for trouble and for failures.  If it takes longer, switch to Plan B.


ODBC Microsoft Access Driver: Not enough space on temporary disk

This is due to the fact that MS Access tables have a limit of 2GB - this might be the issue ! The temp table fills up to 2 gb and then throws this error.
 As a suggestion - brake your query into some sub-query with their own where predicates and finally join just the results


viewing a column in mysql database using php script

I have checked your code and i found out that you have issue with your query execution on php function mysqli_query

mysqli_query requires two parameter first one is connection object and second one is query variable to execute a query.

Below is exact code:-

$conn = mysqli_connect($servername, $username, $password, $db);
if (!$conn) {
   die("Connection failed: " . mysqli_connect_error());
}

else{

echo "Connected successfully";

}

$any = "show columns from transaction";
$chk = mysqli_query($conn, $any) or die("Bad Query: $sql");
echo"
";
echo"
Type
";

while($row = mysqli_fetch_assoc($chk))

{

echo"
{$row['Type']}
";

}

echo"
";


Database size twice as large after mysql dump and re-import

Run the following command so that free space is compressed, and indexes checked and optimized:

mysqlcheck -o -A

After that, try to execute the following query:

SELECT table_schema "Database Name",
sum( data_length) / 1024 "data size in MB",
sum( index_length) / 1024 "index size in MB",
sum( data_free )/ 1024 / 1024 "free space in MB"
FROM information_schema.TABLES GROUP BY table_schema;

Especially check if the index size in MB column is different between the 2 databases, and show us the results.


sp_primary_keys_rowset is consuming a lot of CPU

sys.sp_primary_keys_rowset returns details about the primary key columns present in the table name that is passed-in via the @table_name and @table_schema parameters.

The stored procedure has the following definition:

create procedure sys.sp_primary_keys_rowset
(
    @table_name         sysname,
    @table_schema       sysname = null
)
as
    select
        TABLE_CATALOG   = s_pkv.TABLE_CATALOG,
        TABLE_SCHEMA    = s_pkv.TABLE_SCHEMA,
        TABLE_NAME      = s_pkv.TABLE_NAME,
        COLUMN_NAME     = s_pkv.COLUMN_NAME,
        COLUMN_GUID     = s_pkv.COLUMN_GUID,
        COLUMN_PROPID   = s_pkv.COLUMN_PROPID,
        ORDINAL         = s_pkv.ORDINAL,
        PK_NAME         = s_pkv.PK_NAME
    from
        sys.spt_primary_keys_view s_pkv
    where
        (@table_schema is null and s_pkv.TABLE_NAME = @table_name) or
        s_pkv.object_id = object_id(quotename(@table_schema) + '.' + quotename(@table_name))
    order by 2, 3

Although we can't directly see the source of the sys.spt_primary_keys_view view referenced in the above code, we can indirectly see that the view gathers data from various system objects, including:

syssingleobjrefs
sysidxstats
sysobjvalues
sysschobjs
syscolpars

This is what part of an "actual" execution plan looks like when I run the store procedure on my test SQL Server 2016 instance:

As you can see, it's fairly involved.  Having said that, I wouldn't expect this stored procedure to be negatively affecting performance unless it is running against a database containing a very large number of objects.  Glenn's query is returning the top 50 rows ordered by total_worker_time, which is the total amount of CPU time, in microseconds, consumed by executions of this plan since it was compiled.  It's quite possible this procedure is showing up at the top of the list as a red-herring; if the number of executions is extremely high it may be consuming a lot of CPU time without actually having a seriously negative impact on performance.   For example, if the procedure runs for 100ms at the start of a batch that takes 3 seconds, it's responsible for 1/30th of the run time.  If you change the ORDER BY clause to return results in order of average run time, or perhaps total execution count, you may see something more meaningful.

I wrote about how to look at wait stats to see what, if anything, is slowing SQL Server down.


Why isn't Postgres doing an index-only scan?

processing_date is not in the index, so it cannot use an index only scan.

Create the index on (presentation_code, pct_id, processing_date, actual_cost) so that it can use an index only scan, and in that order so that it can efficiently use the sort order of the index to do the grouping.


Non-Admin access to SQL Server Configuration Manager

There is but it would be a bit painful to implement and might be granting more access than you think.

It will depend on what level of access you want (read only, or ability to manage services). This is mostly a guess for SSCM but in Window Server 2003 you used to use "SUBINACL" command line to grant access. I believe this is still availble in higher OS but you would need to check.

Outside of that just specific to SSCM, this uses WMI calls in the background so at most you are going to need permissions to execute WMI calls. Understand that this is not just specific to SSCM so granting access to this may give more permissions that is needed.

I am also aware that the old "sc" command was used to grant permissions to services so that might also be used as well. This was required when WMI calls did not work properly and services did not show up in SSCM like they should.


Disabled SSL on SQL Server, now connection fails

You are using SQL Authentication. This requires the use of a self signed SSL certificate which is provided by the SQL Server so that the client can encrypt the authentication packets of the connection process.

My guess, no SSL breaks this process.


high 'total_worker_time' for stored proc using OPENQUERY in SQL Server 2005

In sys.dm_exec_query_stats total_worker_time is:

  Total amount of CPU time, in microseconds, that was consumed by
  executions of this plan since it was compiled.

So it is the total CPU time for all executions of the cached plan.  The execution_count column gives the number of times the complete plan has executed, and there is also information about the lowest and highest values recorded.  The figures from the DMV are in microseconds, whereas STATISTICS TIME output shows milliseconds.  Finally, the MAXDOP hint only applies to the portion of the query that runs locally.

I noticed you are running SQL Server 2005 SP2.  There were all sorts of timing issues with that (now unsupported) release, for some detail, see:

SQL Server 2005 SP2 will introduce new messages to the error log related to timing activities.

Consider moving to 2005 SP4.


ssl = on only enables the possibility of using SSL.

To ensure that all clients are using SSL, add hostssl lines in pg_hba.conf, e.g.,

hostssl  all  all  0.0.0.0/0  md5

and remove all host lines.  (Well, maybe keep the ones for localhost.)

If the desire is to force the client to send a certificate, then md5 has to be changed to cert. e.g.,

hostssl  all  all  0.0.0.0/0  cert


Extended Stored Procedures (XPs) have been deprecated as of the release of SQL Server 2005 and new projects should not be using that API.

This can be done easily via SQLCLR, and yes, you can return a string (or most datatypes) from a C# method. The only construct restricted to returning an INT is a Stored Procedure, and that holds true for both T-SQL and SQLCLR Stored Procedures.

You would use something along the lines of:

using System;
using System.IO;

public class Stuff
{
  [SqlFunction(IsPrecise=true)]
  public static SqlString DeleteFile ([SqlFacet(MaxSize=500)] SqlString FilePath)
  {
    string _ReturnMessage = String.Empty;

    try
    {
      File.Delete(FilePath.Value);

      _ReturnMessage = "some text here";
    }
    catch (Exception _Error)
    {
      _ReturnMessage = _Error.Message;
    }
  }

  return new SqlString(_ReturnMessage);
}

When you create the T-SQL wrapper object, be sure to specify the RETURNS NULL ON NULL INPUT option so that it won't even both executing if a NULL is passed in (you should probably add additional validation to the method, such as returning if an empty string is passed in, etc):

CREATE FUNCTION dbo.DeleteFile
(
  @FilePath NVARCHAR(500)
)
WITH RETURNS NULL ON NULL INPUT
EXTERNAL NAME.... ;

Since the Assembly will need to be marked as PERMISSION_SET = EXTERNAL_ACCESS, please do not set the Database to TRUSTWORTHY ON. Instead you should sign the Assembly, create the Asymmetric Key or Certificate in [master], create a Login from that Asymmetric Key or Certificate, and then grant that Login the EXTERNAL ACCESS ASSEMBLY permission (if using SQL Server 2005 - 2016) or the UNSAFE ASSEMBLY permission (starting with SQL Server 2017).

For detailed instructions for doing this via a build process, you can try either of the two techniques I describe in the following blog posts (both work fully with SSDT or independently):

SQLCLR vs. SQL Server 2017, Part 2: “CLR strict security” – Solution 1
SQLCLR vs. SQL Server 2017, Part 3: “CLR strict security” – Solution 2

Also consider supporting my Asymmetric Key suggestion which would eliminate most of the hassle of publishing signed Assemblies:

Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE

OR, if you would rather not deal with any of this, there is a File_Delete function in the SQL# SQLCLR library (that I wrote) that does this. It even returns a string, which is the error message if an error occurs, else is just an empty string. No way to have it pass back a custom string on success, though. Also, please note that while there is a Free version, the File System functions are only available in the Full version. But the security aspect would all be handled cleanly and properly, and there is even a T-SQL Stored Procedure, that exists in the same DB as the SQLCLR code, that will set up the Asymmetric Key and associated Login in [master] (which could help with deployments to new systems by not requiring a full install, as long as this Stored Procedure was deployed and executed prior to deploying any Assemblies (assuming an environment of SQL Server 2017)).

UPDATE

SQL Server 2019 (which was not available at the time I posted this answer) introduces a new, undocumented system stored procedure, xp_delete_files, that is a bit more flexible than xp_delete_file. For more info, please see my answer here (also on DBA.SE):

How is the new system stored procedure sys.xp_delete_files different from sys.xp_delete_file?


Hello I create a temporary table variable type to not create the table

DECLARE @tbl_provider_available_schedule TABLE
    (
    [id] [int] NOT NULL IDENTITY(1, 1),
    [practiceid] [int] NULL,
    [providerid] [int] NULL,
    [providerlocationid] [int] NULL,
    [fromDate] [datetime] NULL,
    [toDate] [datetime] NULL,
    [appointMode] [int] NULL
    );
    INSERT INTO @tbl_provider_available_schedule
    (
        practiceid,
        providerid,
        providerlocationid,
        fromDate,
        toDate,
        appointMode
    )
    VALUES
    (1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T11:45:00', 0),
    (1, 1, 2, N'2019-09-19T11:00:00', N'2019-09-19T12:00:00', 2),
    (1, 1, 2, N'2019-09-19T11:30:00', N'2019-09-19T11:45:00', 1);

    WITH CTE
    AS (SELECT fromDate AS [Time],
               1 AS RLevel,
               id,
               toDate,
               CASE appointMode
                   WHEN 0 THEN
                       'office'
                   WHEN 1 THEN
                       'home'
                   ELSE
                       'tele'
               END AS mode
        FROM @tbl_provider_available_schedule
        UNION ALL
        SELECT DATEADD(MINUTE, 15, [Time]),
               RLevel + 1,
               id,
               toDate,
               CTE.mode
        FROM CTE
        WHERE DATEADD(MINUTE, 15, [Time]) <toDate--We tell you not to miss the end date
        )
    SELECT id,
           [Time] AS StartDate,
           DATEADD(MINUTE, 15, [Time])  as EndDate,--add 15 minutes more
    CASE WHEN mode='office' THEN 1 ELSE 0 END AS Office,
    CASE WHEN mode='tele' THEN 1 ELSE 0 END AS tele,
    CASE WHEN mode='home' THEN 1 ELSE 0 END AS home
    FROM CTE
    ORDER BY id;

analyze it and you commit me.

PD:The final result is different because in your insert there is more data, notice that by the ID it looks like it was.

Demostration:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=b02953ffbc0d70fe1e863a43e63c551f


Host 'host_name' is blocked because of many connection errors. Unblock with 'mysqladmin flush-hosts'

I was able to resolve my issue with following:

# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 216305
Server version: 5.1.73-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET GLOBAL max_connect_errors=10000;
Query OK, 0 rows affected (0.00 sec)

mysql> Bye
#

I'm not entirely sure, if that actually what addressed my issue, however after above SQL statement, my app started to work (without being blocked anymore).


Error statement is not logged, only *password---- with many dashes

It is confirmed that this is caused by a column called password or an argument @password.

A column called abc_password does not cause this behaviour.


Create WAL at interval, but only if data changed

The archive_timeout and the checkpoint_timeout feed off each other in such a way that you will get a WAL switch once per whichever of those two settings is greater, even on a system that is otherwise idle.  So you can increase checkpoint_timeout to the maximum value (1 hour) to cut down on the idle log generation by a factor of 12.  The problem is that a checkpoint creates a record that needs to be archived, and the archival creates traffic that needs to be checkpointed (or at least, that PostgreSQL can't prove to itself doesn't need to be checkpointed)

There are a variety of ways around this, but most of them only work if you are running something less ancient than version 8.4.


Postgresql can I access inline array items returned from function?

Can I access its items inline inside a select query?

Yes, but you need an extra pair of parentheses (as documented in the manual)

select foo, bar,
       (function(foo,bar))[1] as array_item_1,
       (function(foo,bar))[2] as array_item_2
from table...

However, unless the function is defined as stable or immutable, each expression will call the function. So in the above, if the function is defined as volatile (the default!) then it would be called twice. In that case using a derived table is a better approach:

select foo, bar,
       result[1] as array_item_1,
       result[2] as array_item_2
from (
  select foo, bar, function(foo,bar) as result
  from table...
) as t


How to explain/introspect what locks a DDL operation will need (Postgres)

What I do is BEGIN the transaction, run the DDL, then look at the pg_locks table to see what locks are held (not blocked, but held).  I do this on the test server, so there is nothing to block it or be blocked by it. But the knowledge generally transfers to production.  Some DDL can't be run in transactions, those generally require special handling.


Can't create table or database in sql server as permission denied in database master . (Logged in with windows authentication )

In order to create a database, you need to either be a part of the dbcreator fixed server role, or have either of the following permissions on the server: CREATE DATABASE, CREATE ANY DATABASE, or ALTER ANY DATABASE.

The fact that you are using Windows Auth is a neutral point, as within SQL Server it is a principal that still needs the required permissions to perform the necessary actions.


PostgreSQL - Create view with autoincremental column

You can use row_number, and the easiest way is to just add a

row_number() OVER (PARTITION BY true)

field into the view. You need the PARTITION BY and using the "true" expression is the most performant way (no need for sorting like in Fabrizio Mazzoni's answer).


MySQL Replication issues on server placed in different continents

MySQL's replication is based on binary log shipping.

Binary log are flushed whenever a transaction commits. Master server sends logs to slave servers, then slaves replays received logs. Moreover, logs are basically replayed in order, in 5.6 or earlier.
So, when there are a huge bunch  of logs, the lags  may occur because the slave has to replay those logs, as well as those logs have to be send to the slave.

Simply put:

Master                   Slave
begin;
queries;
commit;
flushing binary log
                lag1:sending binary log

                         lag2:replaying binary log

Since MySQL 5.7's parallel replication feature has improved, to use the 5.7 might be the answer you want. See document: slave-parallel-workers and slave-parallel-type.

In addition, if slave's workload is very heavy, lags may also occur since slave is prevented to replay logs.

These are just my experiences. I don't know whether they can be applied to your environment.

Addition: compressing binary log, which other people suggests, is also a good solution, i think.


Yes you can hide those messages in the log.

In the calling session, before running the INSERT statement, issue this statement:

SET log_min_messages TO log;

If you want to still see the ERROR: part, and hide only the STATEMENT: part, use another parameter:

SET log_min_error_statement TO log;

See docs.

Use only session parameters, your application must know which statements will be silenced. Technically, you could make the setting permanent (in postgresql.conf) but it's not a good idea. In some cases this will be a footgun.


You should invest in both.

As for MySQL, if you are using InnoDB as your default storage engine, you must upgrade to MySQL 5.5 in order to use more cores. Here are some of my past posts on this subject

Multi cores and MySQL Performance
Possible to make MySQL use more than one core?
About single threaded versus multithreaded databases performance
16 Cores 12 GB RAM server MySql Configuration - my.cnf

As a point of reference, I have a client at my employer's web hosting company that has three DB Servers in Circular Replication. Each DB server has the following:

MySQL 5.5.9
192 GB RAM
162 GB InnoDB Buffer Pool
dual hexacore (that's right, 12 CPUs)
1.8TB Disk Volume
978 multitenant databases
892 GB of InnoDB Data
innodb_file_per_table enabled

They have had this setup for a whole year. I personally configured my.cnf for multicore engagement. All 12 CPUs are in use. The InnoDB Buffer Pool is full to capacity. There have been no complaints from the client on the DB Performance since this layout was setup for them.

Tuning InnoDB is very important because if you don't, MySQL 5.5 will perform worse than MySQL 5.1. In some cases, it can be worse than MySQL 4.1.

Query runs a long time in some newer MySQL versions
Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)

As for RAM, tuning connection usage, the number of connections and InnoDB caches is the most important thing

Cache MySQL database in memory
What are the main differences between InnoDB and MyISAM?
How do I properly perform a MySQL bake-off?


You need to use an outer join, otherwise you won't get those students back that did not borrow a book.

Then you need to count() on the students table, not "the group" (which is done when you use (*))

SELECT
    LEERLINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    LEERLINGEN.KLAS,
    COUNT(LEERLINGEN.LLNR) AS AANTAL
FROM
    LEERLINGEN
    LEFT OUTER JOIN UITLENINGEN ON UITLENINGEN.LLNR = LEERLINGEN.LLNR
GROUP BY
    LEERLINGEN.LLNR,
    LEERLINGEN.VOORNAAM,
    LEERLINGEN.TUSSENVOEGSEL,
    LEERLINGEN.ACHTERNAAM,
    LEERLINGEN.KLAS
HAVING
    COUNT(UITLENINGEN.LLNR) = 0;

I dont' know MS-Access, but I assume it supports outer joins using standard SQL.


MySQL cluster replication: Slave cluster should not store any data except data replicated from master cluster?

I do not see any problem (if you configure it properly: bin_log on, log_slave_updates, row replication, different server ids for all servers, same exact versions on all servers), but only if you use one of the clusters as read-only at any time.


PostrgeSQL 9.1 Certificate Authentication: how to get certificate common name of client?

Add the extension sslinfo: http://www.postgresql.org/docs/9.1/static/sslinfo.html

This will give you the ssl_client_dn function for the full subject name, or I believe you can do ssl_client_dn_field('CN') to extract just the common name.


DB mail sending attachment of size 64 KB only even after increasing maximum file size limit

You need to apply Cumulative update package 4 for SQL Server 2014. There was a bug which was fixed in CU4.

Symptoms

  Assume that you use Database Mail and sp_send_dbmail stored procedure
  with the @query and @attach_query_result_as_file option in Microsoft
  SQL Server 2014. The stored procedure run a query and set the
  result-set as an attachment of the email. When the query has a large
  result-set, the attachment is truncated to 64 kilobyte (KB) maximum.

Resolution

  The issue was first fixed in the following cumulative update of SQL
  Server.

Details in below link:

FIX: "sp_send_dbmail" stored procedure truncates email attachments to 64 KB in SQL Server 2014


Full-text search does not work if query begins from 0

Are you using MyISAM or InnoDB?

What is the min-word-length?

In InnoDB, with innodb_ft_min_token_size > 2, the +xx leads to finding nothing.  Here's the logic:  + says that it must find the the word xx.  However the token size says that the "word" xx is not indexed.

Note:  I think MyISAM was more lenient.

One workaround:  When building the AGAINST string, don't prepend + to any 'word' shorter than min-token-size.  (I actually have an app that uses this trick; I am happy with the results.)

*06 does not work in FULLTEXT

Under no circumstances can you get FULLTEXT to find pxx by searching for xx.

The 0 is irrelevant.  (If it is relevant, please rephrase the Question with a suitable example.)


Partitions and Indexes, in which cases should be used

Easy: you should always use indexes.

Partitioning for performance is probably the most misunderstood myth out there. When you partition, the best you can hope for is on-par performance with a non-partitioned table. And yes, that is including partition elimination enhancements. Reducing table scans to partition scans because of missing indexes is simply not the answer. Replacing table scans with index seeks or index range scans it is a much better the answer.

Partitioning is a great feature for data maintenance and administration and for efficient ETL switch-in and switch-out operations. For a good discussion of pros and cons of partitioning, see How To Decide if You Should Use Table Partitioning.


How secure is an Azure database within Microsoft?

The only way to be secure, is to control your data. If someone else is running it on its own infrastructure, they are able to read, change or use them. Period.
For example: if your database is confidential one, you should not use someone else infrastructure.
Saying that ( which is completely obvious), you may assume, that as long as you not breaking the law, and USA is not in political conflict with your country, Microsoft is not interested in peeking at your data, exactly as any other company

So if you have something to hide, use your own server. If you have nothing special ( and putting it on the web is in compliance with local law) go on.


The new Oracle 12c In-memory database feature is not the same as TimesTen. It implements a column-store based storage in memory in addition to the traditional row-based format on disk. So your data is held in BOTH formats simultaneously. You choose which tables you want to store in memory. This can allow immense improvement in mixed-workload databases.

It is an extra cost option, not included free even with Enterprise edition. The rationale is that if you are maintaining a separate datamart for your analytics, you could instead do the analytics in real-time right on your OLTP system with the In-memory feature and eliminate the need for a datamart environment and its cost (not to mention all the ETL work you have to do to maintain a datamart).

See: http://oracle-base.com/articles/12c/in-memory-column-store-12cr1.php

A short video on it: http://www.youtube.com/watch?v=8Yf1ghec0gU

A longer video on it: http://www.youtube.com/watch?v=CjkPUg1m6PA


Order by numbers followed by alphabet and then null values

select
*
from
t
order by
case
when col regexp '^[0-9]' then 1
when col regexp '^[a-zA-Z]' then 2
when col = '' or col is null then 3
end
, col * 1 /*this converts to number, so that 100 is not sorted before 2*/
, col /*finally sort strings correctly*/


Toad for Oracle 64-bit : 1603 - Fatal error during installation

I get this error all the time with the installation or updgrade of the sql optimizer tool, For me this error is always due to the required VBSCRIPT.DLL and JSCRIPT.DLL files not being correctly registered.  Following the step listed here:
http://social.msdn.microsoft.com/Forums/en-US/jscript/thread/b7d1a35f-3759-4217-91ba-e4416ac19d78
works for me.


If you want to show total as your data, then you need to do the following:

select FullName,
['1-Urgent'], ['2-High'], ['3-Medium (3000)']
from (select FullName,AltBusinessSeverity, total from Mdata) AS ST
PIVOT (
min(total) FOR  AltBusinessSeverity in (['1-Urgent'], ['2-High'], ['3-Medium (3000)'])
) AS PT


It's not blocking itself, this is just an odd artifact of how the waits are being reported. (And no, this isn't an invalid state, it happens all the time and is not indicative of any bug or deeper problem.) This has been reported a few times with table variables, and I think TVPs is a relatively newer one where there might still be some unresolved issues.

Some minor thoughts on optimization. First of all, why is the Guid column in the table type nullable? Why isn't it a primary key? The optimizer will be able to do a lot more with this if it understands the cardinality better, and your C# code is certainly capable of ensuring that it doesn't populate the parameter with duplicates or null values.

CREATE TYPE [dbo].[TblGuid] AS TABLE(
    [Guid] [uniqueidentifier] PRIMARY KEY
);

Now, the function has a lot of joins that I think are unnecessary, and if you can perform the grouping outside the function that may be better. I don't have sample data to verify that this returns the same results as your existing function, but if it's off it shouldn't be off by much. When you get it returning the same results, please do compare the actual execution plans and I am confident you will find this version is better.

CREATE FUNCTION [dbo].[GetUnits_By_IDsBattleTargets_As_CurrentUnitModel]
(
    @IDsBattleTargets [dbo].[TblGuid] READONLY,
    @IDCurrentPlayer  UNIQUEIDENTIFIER
)
RETURNS TABLE
WITH SCHEMABINDING -- added this
AS
RETURN
(
    SELECT
        U.IDUnitClass,
        U.IDBattleTarget,
        U.Amount
    FROM dbo.tblUnit AS U
    WHERE U.Amount > 0
    AND EXISTS
    (
      SELECT 1 FROM dbo.tblBattleTarget AS BT
        WHERE BT.BattleTargetID IN (SELECT Guid FROM @IDsBattleTargets
          WHERE Guid = U.IDBattleTarget))
    )
    AND EXISTS
    (
      SELECT 1 FROM cache.[tblHeroSharingWithPlayerClan]
        WHERE IDHero IN (SELECT Guid FROM @IDsBattleTargets)
        AND IDPlayer = @IDCurrentPlayer
      UNION ALL
      SELECT 1 FROM cache.[tblHeroSharingWithAlliance]
        WHERE IDHero IN (SELECT Guid FROM @IDsBattleTargets)
        AND IDPlayer = @IDCurrentPlayer
    )
    -- only if necessary:
    -- GROUP BY U.IDUnitCass, U.IDBattleTarget, U.Amount
);


SSRS Azure SQL Database Active Directory - Universal with MFA

The MFA auth flow is interactive.  For me, for instance, it opens a browser window, displays an code, and I get a notification on my phone to approve the login.
So you can't use it in background services like SSRS.


Finding out what permissions are required by the SQL Server Agent Jobs

The only way you'll be able to do this consistently and correctly is by looking at what each job does, and cross-referencing that with the msdn articles for each command on your given version.

For example, DBCC SHOW_STATISTICS has a section:

  Permissions

  In order to view the statistics object, the user must own the table or
  the user must be a member of the sysadmin fixed server role, the
  db_owner fixed database role, or the db_ddladmin fixed database role.

  > SQL Server 2012 SP1 modifies the permission restrictions and allows
  users with SELECT permission to use this command.

  Note that the following requirements exist for SELECT permissions to
  be sufficient to run the command:   Users must have permissions on all
  columns in the statistics object   Users must have permission on all
  columns in a filter condition (if one exists)   The table cannot have
  a row-level security policy.
  To disable this behavior, use traceflag 9485.

As each command has a different subset of required permissions, and this is at times version dependent, there is no quick and dirty way to determine it that I know of.
(Maybe some tracing could give you the same information faster, but it would also imply monitoring the tasks as they run)


Will changing server_id on master break replication?

It should not break replication provided you do not choose a bad server_id. Here is why:

Given this server_id setup

Master with server_id 1
Slave with server_id 2

Here is what happens with Replication

Master completes and SQL command
Master records its server_id along with SQL
Slave reads Binary Logs of Master and stores them in Relay Logs
Slave reads reads Relay Logs and sees server_id 1
Since the Master server_id and Slave server_id do not match, Slaves run the SQL

What happens if you set the Master's server_id to 2 ?

Master completes and SQL command
Master records its server_id along with SQL
Slave reads Binary Logs of Master and stores them in Relay Logs
Slave reads reads Relay Logs and sees server_id 1
Since the Master server_id and Slave server_id match, Slaves does not run the SQL

The only bad server_id to assign is the same server_id as the Slave. Other than that, Replication should not break.


Composing the "ultimate database maintenance script"

I'm afraid your script is littered with misconceptions about how databases work, at least in SQL Server.

First, you shrink the database as much as possible, using the generic DBCC SHRINKDATABASE command. Why? If the database is going to grow again, what have you gained by shrinking the file? Are you going to temporarily lease out that space in the meantime, until the database needs it again? This will cause fragmentation and make your reindex work harder. More on that in a minute.
Next, you reindex all tables in full, whether they need it or not. It is usually much better to analyze the tables first, and perform conditional actions on specific indexes (e.g. reorganize or rebuild) when necessary.
Since you're rebuilding all of the indexes, and an index rebuild requires - on average - 1.5x the space occupied by the index, where do you think it's going to get that space? You've just shrunk the database, so there is no free space. You shrunk the database for nothing because now it's going to have to grow to provide some space to accommodate the index rebuilds.
Then you update statistics with fullscan. You just rebuilt all of the indexes. Do you think updating statistics is necessary or helpful at this point? I don't, since rebuilding an index automatically updates its statistics. So this extra step is just wasteful.
Finally, clearing out the entire proc cache is unlikely to ever lead to overall performance improvement. It may be the case that you get an improvement in a few queries, but it will be much more effective IMHO to sniff those out and clear them out individually (you can manually clear plans in modern versions of SQL Server without throwing the baby out with the bathwater).

I would have to say that what you've got so far is far from optimal and there are much better solutions out there that will help you optimize your database in much more efficient and less wasteful ways. Check out Ola's solution, SQLFool's solution, and SQL Sentry. These don't shrink your database but they do optimize your indexes in an intelligent way and you shouldn't be shrinking your databases anyway.


Dealing with identity ranges for transactional replication

Assuming your Publisher is using an int identity that begins at 1, you could issue  DBCC CHECKIDENT('dbo.mytable', RESEED, -2147483648)
at the subscriber.  You can then use the range from -2147483648 to 0 to hold your "temporary deltas".


If MySQL had implemented FULL joins, this would be fairly simple:

select
    id,
    a.price as price_1701,
    b.price as price_1702,
    c.price as price_1703
from
    t_1701 as a
    full join t_1702 as b using (id)
    full join t_1703 as c using (id)
order by
    id ;

Unfortunately it hasn't (and neither the sister implementation MariaDB), so you have to restore using a UNION and LEFT joins:

select
    id,
    a.price as price_1701,
    b.price as price_1702,
    c.price as price_1703
from
    ( select id from t_1701
      union
      select id from t_1702
      union
      select id from t_1703
    ) as t
    left join t_1701 as a using (id)
    left join t_1702 as b using (id)
    left join t_1703 as c using (id)
order by
    id ;


Default Constraints with Merge Replication

When data is replicated from one server to another using SQL Replication all the values which were inserted into the row are sent along.  Default constraints only apply when no value has been specified for the column.  If the user specifies a value of 5 then 5 will be replicated and inserted into the table.


Run a query in loop, with the results of another query

If you want to assemble the statement at runtime, you're locked into Dynamic SQL as far as I know.  I don't like sp_MSforeachdb as well because it's undocumented and I've ran into bugs with it, so here's an alternative that doesn't require its use:

DECLARE @cmd nvarchar(max) = N'';

SELECT @cmd = @cmd + N'
UNION ALL

SELECT ' + QUOTENAME(QUOTENAME(name),N'''') + N' AS Database_Name
, o.name AS Table_Name
, i.name AS Index_Name
, i.type_desc AS Index_Type
FROM sys.indexes i
INNER JOIN ' + QUOTENAME(name) + '.sys.objects o ON i.object_id = o.object_id
INNER JOIN ' + QUOTENAME(name) + '.sys.schemas sc ON o.schema_id =     sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = ''U'''
FROM sys.databases
WHERE database_id > 4  -- comment out if you want to include system databses

SELECT @cmd = STUFF(@cmd, 1, 13, N'') + N' ORDER BY 1, 2'

PRINT @cmd

EXEC sp_executesql @cmd;


In a data warehouse, should a measure be based on a fact or a dimension?

The problem with contrived examples like the one you provide is that whatever answer you find, it won't be helpful in a real life situation. "A number of distinct customers" is useless as a measure, because it doesn't represent anything a business would care about.

Now, there could be more useful measures, such as "a number of distinct active customers per month", or "a number of distinct customers per geo", or "monthly customer churn", and from their very definitions you immediately see what information is required to compute them.


Exclusion constraint on recursive foreign key (on the example of a Tree)

Use a composite foreign key:

(tree_id, parent_id) REFERENCES (tree_id, id)

You will need to first change the PK or add a UNIQUE constraint on (tree_id, id)


Does mysql configuration options accepts - instead of _?

https://dev.mysql.com/doc/refman/8.0/en/command-line-options.html says:

  Within option names, dash (-) and underscore (_) may be used
  interchangeably. For example, --skip-grant-tables and
  --skip_grant_tables are equivalent. (However, the leading dashes cannot be given as underscores.)

This applies to options when you use them in option files, as well as command-line flags.

However, when using option variables in SQL queries, like in SET GLOBAL read_only=ON, you must use underscores only. Even putting the variable in back-ticks doesn't allow you to use hyphens.

mysql> set global `read_only`=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> set global `read-only`=ON;
ERROR 1193 (HY000): Unknown system variable 'read-only'

mysql> select @@`read_only`;
+---------------+
| @@`read_only` |
+---------------+
|             1 |
+---------------+
1 row in set (0.01 sec)

mysql> select @@`read-only`;
ERROR 1193 (HY000): Unknown system variable 'read-only'