What are you using to measure the speed of your query? Without a baseline, how will we know if the way that we do it will be faster?

It could also be useful to add in maybe a snippet of your query, even if it's just to check that we are not doing the same that you are.

Creating and populating a test table with the information that you have given us, on SQL Server 2012:

if object_id('dbo.TwoIds', 'U') is not null
begin
    drop table dbo.TwoIds;
end;

create table TwoIds
(
    Tid int
    , Bid char(1)
    , [Action] varchar(25)
    , [Status] varchar(25)
    , [Value] int
    , [Time] time
);

insert TwoIds
(Tid, Bid, [Action], [Status], [Value], [Time])
select
    *
from
(
    VALUES
    (1, 'T', 'Insert', NULL, 50, '10:11')
    , (1, 'T', 'Update', 'Executed', 50, '10:12')
    , (1, 'T', 'Remove', 'Executed', 50, '10:50')
    , (1, 'S', 'Insert', NULL, 10, '10:10')
    , (1, 'S', 'Update', 'Executed', 10, '10:11')
    , (2, 'T', 'Insert', 'Executed', 22, '12:20')
    , (2, 'T', 'Remove', 'Executed', 22, '12:44')
    , (3, 'B', 'Insert', 'Executed', 44, '15:21')
    , (3, 'B', 'Update', 'Executed', 48, '15:25')
) as data(Tid, Bid, [Action], [Status], Value, [Time]);

That should give us your table named TwoIds that we can test against.

The following query is what I would have used to get the results that you want.

with CTE_LatestWithExecuted as
(
    select
        *
        , [Ordering] = ROW_NUMBER() over (partition by Tid, Bid order by [Time] asc)
    from TwoIds
),
 CTE_Order as
(
    select
        LastId = max(Ordering)
        , Tid
        , Bid
    from CTE_LatestWithExecuted as le
    where [Status] = 'Executed'
    group by
        Tid
        , Bid
)
select
    cle.*
from CTE_Order as ceo
inner join CTE_LatestWithExecuted as cle on ceo.Tid = cle.Tid and ceo.Bid = cle.Bid and ceo.LastId = cle.Ordering
where not exists
(
    select 1
    from CTE_LatestWithExecuted as ne
    where ne.Tid = cle.Tid
    and ne.Bid = cle.bid
    and ne.[Action] = 'Remove'
);

From checking the statistics and time on the query, I'm getting the following results:

SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 13 ms.
Table 'TwoIds'. Scan count 3, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 0 ms.

It's just one possible way that could be done but hopefully it will give you another option on aa way to do this. With testing you could see if that is performs better for you.


Removing InnoDB indexes causes database table almost not accessible and slow

5.1 ALTERs work faster if you perform them simultaneously:

ALTER TABLE mainData
    DROP INDEX foo,
    ADD INDEX (col1, col2);

You can perform that in the UI window that lets you perform arbitrary SQL.  It will be twice as fast (in older versions) than doing the DROP and ADD separately.  This is because each ALTER (not each clause in it) involves a complete copy of the data, plus a complete rebuild of the resulting set of indexes.


Mysql Daemon cannot start after upgrade from 5.1 to Mysql 8

That is a gynormous upgrade.

Plan A:  Oracle recommends doing it in 4 steps 5.1 -> 5.5 -> 5.6 -> 5.7 -> 8.0.  And, at each step, run mysql_upgrade.

Plan B:  Alternatively, you could dump the data and hope that the reload doesn't find some syntax errors.

I fear that you may have already hosed the files too much to have a successful upgrade.


SQL server unable to create user with dash in domain name

You have a misconception in your thoughts. A Windows login which is used to create a SQL Server login does not require a password.

Solution

When you create a SQL Login using a Windows account you have to use a different syntax.

CREATE LOGIN [ABC-SEN-DEA\proxyssisuser]
FROM WINDOWS
WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO

Reference: CREATE LOGIN (Transact-SQL) (Microsoft)

Explanation

When you link a Windows login with SQL Server, the SQL Server will trust the Windows login which has already been validated by the account logging in to the Windows system.

The chapter Connecting Through Windows Authentication in the article Choosing an Authentication Mode explains how Windows Accounts login to a SQL Server instance:

  When a user connects through a Windows user account, SQL Server validates the account name and password using the Windows principal token in the operating system. This means that the user identity is confirmed by Windows. SQL Server does not ask for the password, and does not perform the identity validation. Windows Authentication is the default authentication mode, and is much more secure than SQL Server Authentication. Windows Authentication uses Kerberos security protocol, provides password policy enforcement with regard to complexity validation for strong passwords, provides support for account lockout, and supports password expiration. A connection made using Windows Authentication is sometimes called a trusted connection, because SQL Server trusts the credentials provided by Windows.

Good luck.


Unexpected very slow user count in WordPress

Add this composite index to wpe_posts:  INDEX(post_status, post_type, post_author).
The answer to "why":  In 5.5 (and before), IN ( SELECT ... ) was a terribly slow construct.
Amd see if this runs faster:
SELECT  SQL_CALC_FOUND_ROWS wpe_users.*
    FROM  ( SELECT  DISTINCT post_author
            FROM  wpe_posts
            WHERE  post_status = 'publish'
              AND  post_type IN ( 'post', 'page', 'attachment',
                        'wp_block', 'products' )
          ) AS x
    JOIN wpe_users USING wpe_users.ID = x.post_author
    ORDER BY  display_name ASC
    LIMIT  0, 10;

For further discussion, please provide EXPLAIN SELECT ... and SHOW CREATE TABLE.
Be aware that a future version is likely to remove the SQL_CALC_FOUND_ROWS feature, forcing you to make two SELECTs.


Booleans, CONSTANTS or mapping table for 'status'-like fields?

In MySQL, you could use ENUM or SET, depending on whether the states are mutually exclusive.  That is, can it be both 'active' and 'detained'?

ENUM('active', 'detained', 'deactivated', 'suspended')

or

SET('active', 'detained', 'deactivated', 'suspended')


ERROR: requested character too large for encoding

Because it didn't raise an error, doesn't mean it worked. None of these work, but only one of them is so absurd as to make chr() explode.

100000 (186A0) maps to the Supplementary Multilingual Plane, but there is nothing there (yet).
55296 (U+D800) maps to the  Basic Multilingual Plane, but nothing is there (yet).
1199111 (U+124C07) is not even valid, it maps to a plane so high that Unicode doesn't even support it.

Unicode only supports 1,111,998: 17 planes × 65,536 characters per plane - 2048 surrogates - 66 noncharacters. See this answer for more information. 1.11 Million code points, is far less than 1.19 Million code points.


Concatenate with NULL values ( Should not replace NULL )

Wrap your strings in single quotes so they will be input as strings.  The NULL does not need quotes.

SELECT 'INSERT INTO nulos (nome,cidade) VALUES (' +
    CASE WHEN nome is null then 'NULL' ELSE ''''+
        REPLACE(nome,'''','''''')+'''' END +
    ',' +
    CASE WHEN cidade is null then 'NULL' ELSE ''''+
        REPLACE(cidade,'''','''''')+'''' END +
    ')'
FROM nulos

This will give you:

INSERT INTO nulos (nome,cidade) VALUES ('rafael','são paulo')
INSERT INTO nulos (nome,cidade) VALUES ('Juliana',NULL)


You could assign the current context to a cookie then revert to that cookie when you desire to.


Windows Authentication fails with "Cannot generate SSPI context"

You can find a more information on Kerberos from the CSS team here. As well Microsoft released a tool that can help you identify Kerberos issues, download that here.

To troubleshoot, check your error log that shows the startup of your instance after you changed the service account to that new domain. In a perfect world you will see a message like this:

  The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) MSSQLSvc/<servername>

If it fails it will show a similar message just with a failure and should provide an error code. If not check your Windows Event log for similar events around startup time of SQL Server.

To fix the issue you need to follow the KB article you provided. It is not old and is still current, check the "Applies to" section where it list all the editions of SQL Server 2012.


Where can I get a proper dataset for performing Normalization techniques?

Check out Percona's tpcc-mysql. Here you can generate a dataset of any size. It should do what you ask.


Conversion failed when converting date and/or time from character string

You are storing dates as strings - why? Opening_Date and Date should be date or datetime, not varchar. But before you can fix that, you need to identify the rows that are causing the conversion problem:

SELECT cod_store, [Date]
FROM dbo.FactTransactions
WHERE ISDATE([Date]) = 0;

SELECT cod_storekey, Opening_Date
FROM dbo.DimStore
WHERE ISDATE(Opening_Date) = 0;

And now that you've fixed the question and I know you're using 2012 and not 2008 R2, it might be cleaner to use TRY_CONVERT(), especially since it will allow you to identify any rows where the month and day are transposed incorrectly. For example, assuming you expect dates to be stored as mm/dd/yyyy strings:

SELECT cod_store, [Date]
FROM dbo.FactTransactions
WHERE TRY_CONVERT(datetime, [Date], 101) = 0;

SELECT cod_storekey, Opening_Date
FROM dbo.DimStore
WHERE TRY_CONVERT(datetime, Opening_Date, 101) = 0;

In addition to identifying garbage rows where users have stored nonsense like floob and 9992-13-36 as "dates," this will also identify rows where users have stored 13/07/1999 instead of 07/13/1999 (but there is no way to know if 05/06/2000 is meant to be May 6 or June 5).

Now you need to fix those rows before you can correct the tables.

ALTER TABLE dbo.DimStore ALTER COLUMN Opening_Date date; -- or datetime;
ALTER TABLE dbo.FactTransactions ALTER COLUMN [Date] date; -- or datetime;

You might also consider renaming the Date column to be (a) less vague and (b) not a reserved word.

If you can't fix the tables, then you need to change your query:

UPDATE T1
SET ORDINAL = DATEDIFF(DAY,
  CASE WHEN ISDATE(T2.Opening_Date) = 1 THEN T2.OpeningDate END,
  CASE WHEN ISDATE(T1.[Date]) = 1 THEN T1.Date END)
FROM dbo.FactTransactions AS T1
INNER JOIN dbo.DimStore AS T2
  ON T1.cod_store = T2.cod_storeKey
WHERE ISDATE(T2.Opening_Date) = 1
  AND ISDATE(T1.[Date]) = 1;

And @RLF brought up a great point, too; if you can't fix the table, then the date columns could contain data that represent a specific date (say, September 7) but be entered in the wrong format (e.g. on a US English system, entered as a string in British format, 7/9/2015). So really, you need to fix the table and stop storing these things as strings.

Some other useful material:

Bad habits to kick : mis-handling date / range queries
Bad habits to kick : avoiding the schema prefix


Can I install SQL Server 2012 Express edition on a same machine with 2012 SQL Server Standard version

Suppose that as the Running SQL Server 2012 Standard SP1 instance name
  is ABC. Can I install the same instance name (ABC) of SQL Server 2012
  Express SP1 or it will be different.

You cannot have two named instances of SQL Server with the same name on the same pc.

So your options are

first uninstall your first instance and then install the second and
use the old name for newly installed second instance
give the second instance different name, but you can then alias it
with the first server's name once you uninstalled the first server

Here you can find information about aliases


I just deleted 19k hi-res images from a table column and have a few questions about shrinking

If the goal is to make backups smaller, shrinking the data file(s) will not help accomplish that goal in this case, because all of those pages that were holding that max data were LOB pages that should be deallocated (potentially requiring a rebuild).

So unless your drive is literally out of space, I don't see any reason to shrink the file from 8GB, unless you are 100% confident it will never grow that size again.

Note that when you shrink a data file you might actually increase fragmentation which could decrease read performance, impact memory usage, and reduce concurrency. The knee-jerk reaction there is to rebuild indexes, which fixes the fragmentation but increases the file size to make room for the new copies of the index. When the rebuild is done, those pages are freed up, but the data file doesn't magically shrink again, so you're back where you started, and have gained nothing.

If you delete 95% of the data, the backup size will go down, whether you shrink the MDF file(s) or not. An MDF file is just a container; its size does not affect the backup. The actual data affects the backup. More precisely, the number of pages allocated with any data affects the backup (which is why a table rebuild after a big delete might yield a difference in backup size).

This is easy to test for yourself, but keep in mind the impact you observe will depend on a lot of things, including data types, fragmentation, free space, and a host of other variables. Just look at the size of a full backup after each of these steps:

before you set the values to NULL.
after you set the values to NULL (and after dropping the column, maybe).
after rebuilding the table.
after shrinking the database.

My theory is that, in almost all cases I can think of, there will be no tangible difference between the backup size after 3. and after 4.

A different variable you might care about is the time it takes to restore that backup on another system, which will be dependent on the size of the MDF file(s) only in the following cases:

Instant file initialization is not enabled
The destination drive can not accommodate the current size

The first one is easy to fix, this is just a right you can grant to the service account. The second one should be easy to fix too: add disk space. There's no reason to sacrifice your primary system because your secondary system is inadequate.


I have faced the similar issue. The reason, why it has happened was the invalid entry in the /etc/oratab.

As dbstart script suggests, to configure, update /etc/oratab with instances that need to be started up. The entries are of the form:

$ORACLE_SID:$ORACLE_HOME:<N|Y|W>:

An example entry:

main:/usr/lib/oracle/emagent_10g:Y


Property PopulationStatus is not available for FullTextCatalog... How to fix?

I found out that even if it is not installed it is still available on SQL Server Management Studio and you can create the indexes. It shouldn't allow...

Then I found this: SQL Server 2008 R2 Express - Installation Options, to allow FTS I need the 800MB version...

To check the status of FTS I found these queries:

SELECT
    FULLTEXTCATALOGPROPERTY(cat.name,'ItemCount'),
    FULLTEXTCATALOGPROPERTY(cat.name,'MergeStatus'),
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateCompletionAge'),
    FULLTEXTCATALOGPROPERTY(cat.name,'PopulateStatus'),
    FULLTEXTCATALOGPROPERTY(cat.name,'ImportStatus')
FROM sys.fulltext_catalogs AS cat

SELECT fulltextserviceproperty('IsFulltextInstalled')


Using a string with single quotes in a table

You need to double-up apostrophes to embed them within a string, since they also serve as string delimiters:

INSERT dbo.table(column) VALUES('''affinity mask'',0');


Connection pooling - NodeJS or Database products?

You can use connection pooling on any level, but I believe that it is best done with a connection pool inside the application server code for these reasons:

You don't need an extra process like pgBouncer that you have start and monitor.
You don't have any more TCP connections than necessary, thus conserving resources and being faster.

This holds if you have a single application server. If several of them access the same database, connecting pooling in the application server isn't enough. It is still a goid idea if you want to limit the DB resources each one can use, but you will still need another connection pool to limit the total number of database connections.

Such a connection pool can be implemented in the database if the RDBMS supports it (PostgreSQL doesn't), or it can be an external tool like pgBouncer.


Does Postgres preserve insertion order of records?

The answer for this simple case is Yes. Rows are inserted in the provided order in the VALUES expression. And if your id column is a serial type, values from the underlying sequence will be fetched in that order.

But this is an implementation detail and there are no guarantees. In particular, the order is not necessarily maintained in more complex queries with WHERE conditions or joins.

You might also get gaps or other rows mixed in if you have concurrent transactions writing to the same table at the same time. Unlikely, but possible.

There is no "natural" order in a database table. While the physical order of rows (which is reflected in the system column ctid) will correspond to their inserted order initially, that may change any time. UPDATE, DELETE, VACUUM and other commands can change the physical order of rows. But the generated values for id are stable and not in any way connected to that, of course.


Force using an Index in Inner Join SELECT

The goto solution to improve queries should not be query or table hints.

You should try other solutions like changing the query, adding or changing indexes first.

If the optimizer is still making mistakes after trying other workarounds then you might try adding a query / table hint.

Table variable estimates

The table variables have low estimates (one , two rows) while 850 rows or even 16.284 rows can be returned.
This is impacting the rest of the query.

Estimated plan on the inner join query:

Actual query plan on the inner join query:

Consider changing the table variables to temporary tables:

CREATE TABLE #lTemas(TemaId int);
INSERT INTO #lTemas (TemaId)
SELECT t.TemaId FROM Temas t
INNER JOIN @lClientes lc ON lc.n = t.ClienteId
AND nEstado > 0;

or adding OPTION(RECOMPILE) to the main query.

Indexing

Next to these awful estimates, the query gets filtered from 127.689 rows to 4.071 rows at this point:

This could be due to the predicate on dNota

** possible fix**

Consider adding an index on dNota first

CREATE INDEX  IX_dNota_ArtId
ON dbo.NotasHist(dNota,ArtId)
INCLUDE(bTipo,idDominio,iFuente);

and removing the with (index(Buscador)) hint to see if the optimizer will use the index with the better estimates.

The main difference with the left join

The filtering is happening much sooner on the left join:

The estimates are higher

And due to the higher estimates the query is going parallel

Whereas on the inner join you are not using parallelism.

This and other considerations make the difference in execution time happen, fixing the estimates by adding option recompile or changing the table variables to temp tables would be the first thing to look into.


SSRS: http://localhost/ReportServer accessible but no report listed

The solution was slightly easy: right click on the project > deploy.

Strange there isn't enough info on internet about a so simple glitch. :)


How do I execute an Oracle SQL script without sqlplus hanging on me?

You can do this under Linux/Unix when you embed the SQL commands in a shell script and pipe them into a SQL*Plus instance.


postgres: archive_command can't write to an nfs share

You are probably a victim of selinux or something like it, in which the same system OS account can have different permissions depending whether it is running as a deamon or from a command line (and, it often seems, depending on the barometric pressure, the phase of the moon, and what you had for breakfast).

You can turn it off, or put it into permissive mode where it allows the access and just logs a gripe.


Can a Table Lock eliminate a deadlock or make it worse Oracle

In addition to someone doing full table locks, there are two other things that I can think of. If you have a table where there are several columns that have a parent child relationship to the same column in a child table and if the child column is not indexed, then indexing it could help. I had a case with an online content management system that had unindexed foreign keys that caused cascading locks. I was able to resolve that by using the Tom Kyte query to find the missing indexes and add them in.

http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html

Another possibility is that you have cases where two triggers are firing on different tables and they are each trying to update the other table. You can tell this by looking at the trace log that gets produced from a dead lock and figuring out what objects and rows are locked, then check your triggers to see if any of your triggers are running that SQL.

It would be helpful to know how long this has been happening and what changed around the time that this became an issue.


Does querying the sys entities and DMVs lock the underlying tables and what happens when SQL Server tries to write to them?

To start with, sys.tables is a system catalog view, not a table. And DMVs / DMFs (especially those that report stats) often query memory, which is why they get cleared out when you restart or execute DBCC FREESYSTEMCACHE(N'ALL');.

Still, since sys.tables does eventually lead to actual system tables, I figured I should test this, especially because I have noticed in the past that sometimes adding WITH (NOLOCK) does help when querying some of the system catalog views.

In order to test this, I used a SQLCLR function to pause for a few seconds after returning each row. This keeps the auto-commit transaction open long enough to be able to see what locking is actually happening during the operation (without coming up with several cross joins in an attempt to return tens of millions of rows which also takes a while but takes up a lot of memory as well). The SQLCLR function, DB_WaitForDelay, is available in the free version of SQL#, a library of SQLCLR procs and functions that I wrote.

USE [tempdb];

SELECT *, SQL#_Full.SQL#.DB_WaitForDelay(5000, NEWID())
FROM   sys.tables  -- WITH (NOLOCK)

While that was executing, the following resources were being locked by that session:

LockResource                             Mode   Type  Status
[sys].[sysschobjs] (SYSTEM_TABLE)        Sch-S  LOCK  GRANT
[sys].[sysidxstats] (SYSTEM_TABLE)       Sch-S  LOCK  GRANT
[sys].[sysobjvalues] (SYSTEM_TABLE)      Sch-S  LOCK  GRANT
[sys].[syssingleobjrefs] (SYSTEM_TABLE)  Sch-S  LOCK  GRANT
[sys].[sysmultiobjrefs] (SYSTEM_TABLE)   Sch-S  LOCK  GRANT

[sys].[sysschobjs]                       S      LOCK  GRANT {not if WITH (NOLOCK) is used}

That last item, sysschobjs does not show up if I uncomment the WITH (NOLOCK) when selecting from sys.tables. Interesting.

While that was running (without the WITH (NOLOCK)), I was able to execute the following in [tempdb]:

CREATE TABLE dbo.IsLocked (Col2 INT);
GO
DROP TABLE dbo.IsLocked;
GO

So, even with some locks being taken, it seems like you should be fine. In fact, I even tested again, but this time I first executed the following before the SELECT:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN;

And again there was no interference with the CREATE TABLE / DROP TABLE operations.

When selecting from the sys.dm_exec_query_stats DMV and using DB_WaitForDelay I did not see any locks being taken by that session.

Just to have this caveat stated: I only tested the two objects mentioned in the question. It is possible that other system catalog views and/or DMFs/DMVs do some additional locking that might have a more noticeable impact.


PostgreSQL full text search on many columns

I suggest this expression for query and index:
SELECT * FROM tbl
WHERE  to_tsvector('simple', f_concat_ws(' ', country, city, street, house_nr, postcode))
    @@ plainto_tsquery('simple', '22 Kärntner Wien');

Note the custom function f_concat_ws() above. That's because concat_ws() is only STABLE not IMMUTABLE. You need to create it first:
CREATE OR REPLACE FUNCTION f_concat_ws(text, VARIADIC text[])
  RETURNS text LANGUAGE sql IMMUTABLE AS 'SELECT array_to_string($2, $1)';

It can be used as drop-in replacement for concat_ws(), except that it only accepts actual text data as input (which allows us to make it IMMUTABLE without cheating, effectively). Detailed explanation (read it!):

Combine two columns and add into one new column

About VARIADIC:

Passing multiple values in single parameter

For many columns, this is shorter and faster. You could do without it but then the syntax gets rather verbose (see joanolo's answer).
The matching index to go with this:
CREATE INDEX tbl_adr_fts_idx ON tbl USING GIN (
       to_tsvector('simple', f_concat_ws(' ', country, city, street, house_nr, postcode)));

You are dealing with international address data, so do not use the english text search configuration. Stemming makes little sense for names  and most of your example data is not even English to begin with. Use the simple configuration instead. You need the form with two parameters - see below.
Concatenate the strings and call the more expensive function to_tsvector() once. Use concat_ws() to deal with possible NULL values elegantly. Cheaper overall, and also shorter.

String replace using concatenated strings from various columns

Like I commented, Full Text Search has limited support for fuzzy matching, but there is the often overlooked feature of prefix matching:

Get partial match from GIN indexed TSVECTOR column

So, if you are not sure whether it's 'Kärntner' or 'Kärnten', and whether it's 'Straße', 'strasse' or 'Strabe' (like in your buggy example data) but you know that the second word follows the first, you could:
... @@ to_tsquery('simple', '22 & Kärnt:* <-> Stra:* & Wien')
<-> is the phrase search operator and requires Postgres 9.6.

How to store short stories for access to individual sentences?

And if you want to ignore diacritical signs as well ('ä' <> 'a'), add unaccent() to the mix. You can use it as separate function or you can add it as dictionary to your text search configuration. You need to install the extension first ...

Does PostgreSQL support “accent insensitive” collations?

Overview over pattern matching option in typical Postgres installations:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Joanolo already provided some basic information about FTS and the link to the manual for more.
Adressing your comment

I am trying add this index it but gives me an error:
ERROR: functions in index expression must be marked IMMUTABLE

There are two variants of the function to_tsvector() - see "function overloading". The 1st takes only text, the 2nd takes regconfig and text. See for yourself:
SELECT proname, provolatile, proargtypes[0]::regtype, proargtypes[1]::regtype
FROM   pg_proc
WHERE  proname = 'to_tsvector';

Only the second is IMMUTABLE and can be used in an index expression directly. 'simple' in the above example is a text search configuration (regconfig).
More importantly, my oversight: concat_ws() (which I had in my first version) is only STABLE, not IMMUTABLE. I added necessary steps above.
Related:

How do IMMUTABLE, STABLE and VOLATILE keywords effect behaviour of function?

Combine two columns and add into one new column


There is some Technet information on the intended behaviour of the export: https://technet.microsoft.com/en-us/library/dd255234(v=sql.105).aspx

  Images, charts, sparklines, data bars, maps, gauges, indicators, and
  lines are positioned within one Excel cell but they sit on top of the
  cell grid. Lines are rendered as cell borders.


How to correct Failed global > initialization: FileNotOpen

Run as sudo. That worked for me.

  trevor@ubuntu-s-4vcpu-8gb-lon1-01:~/opt$ sudo ~/opt/mongodb/bin/mongod
  -f ~/opt/mongodb/mongod.conf & [1] 23948
   trevor@ubuntu-s-4vcpu-8gb-lon1-01:~/opt$


Can I speed up this slow query which should be limited by MIN/MAX values?

This should work:

CREATE TEMPORARY TABLE user_retention_temp (first DATE, last DATE, amount INT);

INSERT INTO user_retention_temp
(SELECT MIN(ymd) AS first, MAX(ymd) AS last, player_id
   FROM day_item
  GROUP BY player_id
  HAVING first >= ? AND last < ?);


Would like to know why this query is behaving this way?

Your query, given your data, is equivalent to this one:

SELECT emp.name, role.role_name, emp.emp_id
FROM emp
INNER JOIN role
    ON emp.role_id = 1; -- because this is the role_id for 'tech'

This means that you don't define a condition about how to join the two tables.  In turn, this results in a Cartesian product of role (without any restriction about which rows to return) and emp (where role_id is 1).

Again rewriting the query, it is effectively a CROSS JOIN:

SELECT emp.name, role.role_name, emp.emp_id
FROM emp
CROSS JOIN role
WHERE emp.role_id = 1;

See them on SQLFiddle.

What you possibly want is

SELECT emp.name, role.role_name, emp.emp_id
FROM emp
INNER JOIN role
    ON emp.role_id = role.role_id
WHERE emp.role_id = 1;


I discussed How is INFORMATION_SCHEMA implemented in MySQL? about 4 years ago.
SQL and PostgreSQL have ObjectID enumeration (oid) of object types. MySQL made the INFORMATION_SCHEMA database a more simplified representation of table, column, and index metadata. The metadata tables are temporary MEMORY tables with no indexes.
Last year in Adding new tables -- memory usage increases I discussed what happens to memory with the INFORMATION_SCHEMA does when you increase the number of tables.
Believe me, adding an oid for data types to the MySQL INFORMATION_SCHEMA would just bloat the INFORMATION_SCHEMA. In this particular instance, I greatly appreciate MySQL not having oid values exposed.
If you really want to learn about how mysql represents data types under the hood I refer you to pages 48-53 of "Understanding MySQL Internals" (ISBN 0-596-00957-7).

Those pages refer to sql/table.h and sql/field.h in the source code. You can download the source code and look it over. Then, judge for yourself how and why oid is not included.
SUGGESTION
Create your own. For example, to make oids for columns do this
DROP DATABASE IF EXISTS my_metadata;
CREATE DATABASE my_metadata;
USE metadata
CREATE TABLE objects ENGINE=MEMORY
SELECT data_type FROM information_schema.columns WHERE 1=2;
ALTER TABLE objects ADD COLUMN oid INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE objects ADD UNIQUE INDEX (data_type);
INSERT INTO objects (data_type)
SELECT DISTINCT data_type FROM information_schema.columns;


shiphome is nothing but a specified location in Oracle grid  installation environment , Which identify that your runInstaller meets all Oracle installation  requirements. If your installing the grid file , which meet the prerequisite installation required privilege like ( Oracle Home, Central inventory,Meta-data files ,operating system certification & location of file ) then installation  will success. If during installation Verifying environment and performing prerequisite checks if any such specified path not meet then shiphome Error comes.

If you are getting the shiphome error during installation.

Please be sure about that  some points like

1) oracle grid installation has stop before oracle grid patch installtion
2) Before installation of Oracle grid service, sure about the specified node-list path is correct or not.

for your ref https://docs.oracle.com/cd/E11882_01/relnotes.112/e23557/toc.htm  and http://www.snapdba.com/2014/01/oracle-database-11gr2-11-2-0-4-installation-on-oracle-linux-6-4/


Large number of row deletes fails with 'out of log space'

If there is a FULL recovery model - you need to schedule the regular transaction log backups
If not (your case) - try to run your statements in smaller bulks, instead of one huge transaction
and finally the most like the your problem - place your begin tran/commit inside the loop


Optimized MySQL "optional" FULLTEXT search on multiple tables

Wrong approach.  Instead, "construct" the SELECT based on which fields the user provides.

Why?  OR does a poor job of optimizing.  If, instead, you remove the clause from WHERE, you don't need the test for 0, thereby avoiding the OR.

Worse yet is an OR spanning multiple tables, as you need to to with the FULLTEXT checks.  The only way for the query, as it stands, to be run is to evaluate all combinations of all rows in all tables, then start filtering.  (OK, I exaggerate, but not by much.)

A common way to avoid OR is to turn it into UNION:

( SELECT id FROM a WHERE MATCH(a...) ... )
UNION DISTINCT
( SELECT id FROM a JOIN b ON ... WHERE MATCH(b...) ... )
...

That will get all the possible values for a.id.

Why LEFT?  That will slow down the 2nd and 3rd parts of the subquery for no good reason.  Remove LEFT.  If there is no b row, that's equivalent to the MATCH failing.

Then, put that in a subquery to get the rest of the desired data:

SELECT a.id, a.title, b.txt AS b_txt, c.txt AS c_txt
FROM ( the above "union" query ) AS tmp
JOIN a
LEFT JOIN b ON a.b_id=b.id
LEFT JOIN c ON a.c_id=c.id;

In this case you might need LEFT; but remove it if you don't.

Other notes

While you are 'constructing' the query, you may as well plug in the @ values.  @variables inhibit the optimizer in some cases; actual literals are more easily processed.  Be sure to escape them to help prevent "sql injection".

In case it was not obvious, 0=@bid OR goes away.


Slow query when adding additional where clause

@Joishi already provided an explanation for what you saw.

Here is a solution to make it fast.

Your query (unchanged) after trimming some noise:

SELECT m.*
FROM   posts    p
JOIN   messages m ON m.post_id = p.id
WHERE  p.project_id = '418fdd03-ab90-4efd-b04d-5d5563d58972'
AND    m.kind = ANY ('{10,11,12}')
ORDER  BY m.updated_at DESC
LIMIT  100
OFFSET 0;

1. Multicolumn index on posts

This is only a minor improvement and not strictly necessary. Should give you an Index Only Scan instead of an Index Scan. Saves the lookup on the heap (the table)

 CREATE UNIQUE INDEX index_posts ON posts (project_id, id);

UNIQUE is also not necessary, but it better documents that the index is, in fact, unique.

2. Partial multicolumn index on messages done right

This is the major part.

Use the same predicate in the partial index and in the query. Postgres only applies very rudimentary tests to determine whether an partial index is applicable. Else, the cost would get out of hand quickly.
Since kind IN (10, 11, 12) is rewritten to kind = ANY ('{10,11,12}'::integer[]) internally, it should still work in this case, but it's better to be clear.
It would be useless to make kind the indexed column here. It's only relevant in the predicate, not as column to retrieve from the index. You need an equality check on post_id (so it comes first) and a range check on  updated_at (so it comes after that). Details:

Multicolumn index and performance

DESC is not strictly necessary, but still better here.

-- DROP INDEX index_messages_on_kind;
CREATE INDEX index_messages_on_kind ON messages (post_id, updated_at DESC)
WHERE kind = ANY ('{10,11,12}');

Then run ANALYZE on both tables and try EXPLAIN ANALYZE again. Are we fast, yet?


Two rows having null values merge into one row without null values

You can use grouping to get only one row per Name. If more than one row for the same Name and column have values you have to decide what to do with those values: do you want to see minimum, maximum, sum, average etc? So for example:

SELECT Name, min(Opp), min(Bid), min(Pro) FROM your_table GROUP BY Name


PostgreSQL CREATE TABLE creates with incorrect owner

That the owner of your new table turns out to be postgres is very odd.

Either way, you can make Postgres grant or revoke any privileges to / from any role by default with ALTER DEFAULT PRIVILEGES:

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT INSERT, UPDATE, DELETE ON TABLES TO test1;


Yes. Just remove the set rowcount and use

DELETE TOP (1000) FlightAudit
WHERE  CreatedUTC < @short_time;

The rest of the code can remain unchanged.

This deletes an arbitrary 1000 rows matching the WHERE clause (same semantics as the original query).

If you wanted to define a particular ORDER BY for the TOP then you could use

WITH T
     AS (SELECT TOP (1000) *
         FROM   FlightAudit
         WHERE  CreatedUTC < @short_time
         ORDER  BY CreatedUTC)
DELETE FROM T;


Storing variable length arrays of doubles in MySQL without loss of information

Plan A:  Use NULL to indicate NaN.

Plan B:  Use JSON now.  Build whatever structure makes sense in you app, encode it in JSON, store it in a TEXT field.  Note:  JSON is expecting utf8 string inside.  Keep in mind that you need to fetch the entire JSON field and manipulate it in your app, not in SQL.


Primary node becomes secondary before application is deployed and moved into AAG

You can only add a database to an AG from the Primary node.  Your options are to back and restore the database to the new primary, or fail back over to SQL06 to make it the primary, then add it to the AG.


How to append some letters or numbers to already existing data

You can use the CONCAT() function:

select concat(255, 720967537), concat('my ', 'test')

Results:

255720967537, my test

SQL Fiddle with Demo

If you want to UPDATE the values in your table, you could do the following:

update t
set col1 = concat(255, col1)
  , col2 = concat('my ', col2);

See SQL Fiddle with Demo


How can I speed up a DateTime comparison query in MySQL?

Take the 100ms query and LEFT JOIN it back to the locations table:

SELECT B.id, B.company_id, B.raw_address FROM
(
    SELECT id FROM locations
    WHERE last_updated > last_geocoded LIMIT 25
) A LEFT JOIN locations B USING (id)
WHERE B.raw_address IS NOT NULL;

Give it a Try !!!


Converting a varchar date into a date (dd/mm/yy)

I understand your problem to be how to successfully convert the string into a DATE value. An undelimited string of integers is assumed to be Year-Month-Day order.  (And, of course, I agree with the comments that dates should be stored in DATE data types in the database.)

Reviewing the MSDN CONVERT documentation does not show a built-in conversion for your string, but it is easy to work around.

http://msdn.microsoft.com/en-us/library/ms187928.aspx -- CAST and CONVERT

I changed the month to 8 to make it easier to double check.  Using the CONVERT style option 3, you can do the following:

DECLARE @String VARCHAR(10);
DECLARE @DateValue DATE;
SET @String = '250809';

-- Convert your undelimited string DDMMYY into a DATE
-- First: Add / between the string parts.
SET @STRING = SUBSTRING(@String,1,2)+'/'+
     SUBSTRING(@String,3,2)+'/'+SUBSTRING(@String,5,2);
-- Second: Convert using STYLE 3 to get DD/MM/YY interpretation
SELECT @DateValue = CONVERT(Date, @String, 3);

-- Using the DATE
-- Select the value in default Year-Month-Day
SELECT @DateValue AS DefaultFormat;
-- Select the value formatted as dd/mm/yy
SELECT CONVERT(VARCHAR(20),@DateValue,3) AS [DD/MM/YY];

The results of the last two selects are:

DefaultFormat
-------------
2009-08-25

DD/MM/YY
--------
25/08/09

To get your DATE formatted in the way you want it, you have to insert the '/' delimiters then use the STYLE 3 in converting from the string to the DATE. (I am sure that there are other workarounds and conversion styles that would work as well.)

Likewise when displaying the DATE as you desire, you need to use STYLE 3


It is feasible (you can chain-replicate more than 2 servers), but unnecessary: you can forward access using a VPN or, if you do not want to set that up, with simple SSH port forwarding.

This applies even if you have a Windows server -you can install OpenSSH or any other SSH software or application that allows you to forward ports. SSH is the standard secure tool on unix.


"tempdb metadata memory-optimized" configuration option

As far as I can tell the feature has not been officially announced yet.

There are a couple of resources available on this feature from members of the community:

Youtube video from Erik Darling

Blog post from Ned Otter

This would appear to be the configuration option that will allow you to put some TempDB metadata tables into memory, preventing many types of resource wait and hopefully speeding use of that database up on busy systems. The value of the configuration option can be found with this query:

SELECT * FROM sys.configurations
WHERE configuration_id = 1589


Change rowguidcol property to a different column

First of all, the Table Designer is not the wisest of all software and probably does not choose the approach to change the table that you would expect.  The designer usually creates a new table, moves the data, then drops the original table.  However, based on your error the designer also may not be FILESTREAM savvy.

But if you try it yourself through ALTER commands, you will see that SQL Server cannot alter a column to remove the rowguidcol setting.   See what happens:

ALTER TABLE TABLEName ALTER COLUMN Col1 uniqueidentifier DROP ROWGUIDCOL;

Gives the error: Cannot alter column 'Col1' because it is 'ROWGUIDCOL'.

Acknowledgements to Martin Smith.  I overlooked the ROWGUIDCOL operators, which are:

ALTER TABLE MyTable ALTER COLUMN Col1 DROP ROWGUIDCOL;
ALTER TABLE MyTable ALTER COLUMN Col2 ADD ROWGUIDCOL;

However, because of the involvement of the FILESTREAM the DROP ROWGUIDCOL command cannot be processed.

So, yes, you need to create a new table and move the data.


Are SQL unit tests supposed to be so long?

Am I wrong about this reasoning? I am new to unit testing, so perhaps I am just writing my tests wrong? Is SQL longwinded language and thus it's unit tests are longer as well?

I think this is a great (though somewhat opinion-based) question. The past few years I've attempted to implement tSQLt unit testing in a personal OSS project (https://dba-multitool.org/) to attempt to answer it via real world application. Unit testing for any type of SQL definitely breaks from classic norms and patterns. My tests definitely don't feel like unit tests and are usually more complex/longer than I'd prefer, but they have saved me a bunch of times from introducing breaking changes.
The stored procedures in my example are quite complex and long, so I came to a similar impasse as you: should I suck it up and deal with very complex "unit" tests, or fundamentally modify how the stored procedure works so that it is more amicable to unit testing in general? As with many database questions, the ultimate answer, I believe, is it depends. Performance and maintainability take a front seat here, in my experience.
I found that I wasn't able to justify largely changing the complex stored procedures to benefit from unit testing - adding more objects, as you noted, can increase complexity and in this case I was wary of asking end users to house a bunch of single-use objects in their database instead of offering an all-in-one utility stored procedure.
One technique that I did come up with was using optional parameters to induce test cases better. For example, I have parameters @IsExpress, @SqlMajorVersion, and @SqlMinorVersion that end users should not use (they are fetched at runtime if not passed) but that my tests utilize to make sure I'm catching errors I want around unsupported versions. Adding a lot of errors will increase the overall reliability of the code without having to necessarily explicitly test the more complex logic areas that may be hard/not reasonable to do. This marginally affects the stored procedure's length and complexity, but lets me easily isolate functionality and reliably test for it. You could take this further and use parameters to trigger select portions of the stored procedure that need testing the most, or to achieve better code coverage percentages by avoiding a messy area that isn't worth covering or is out of scope.
With this approach I was able to get a fairly high code coverage percentage (98%+) and while I definitely don't test all functionality, I know that almost all of the code gets executed, errors when I need it to, and will parse on execution.
To close with a quote, Wikipedia has unit testing defined as (emphasis mine):

In computer programming, unit testing is a software testing method by which individual units of source code—sets of one or more computer program modules together with associated control data, usage procedures, and operating procedures—are tested to determine whether they are fit for use.

I won't say that Wikipedia (or the book being quoted by Wikipedia) is the end-all for technical definitions, but I do like this definition since it focuses on the end goal of unit testing, which I think tSQLt can reasonably achieve.
Besides, some unit tests are better than none and having lines of code that aren't covered by unit testing defined may be very valuable in the future. To expand on the latter:
In 3 years, you've left the company and Employee #948 is trying to address a bug in the stored procedure. They look and see that only lines 50-65 out of 300 aren't covered by a unit test, otherwise all other functionality has been tested. They now have a pretty good shot at spending time on a likely problematic area rather than reverse engineering the entire 300 lines. Or, the next intern/new hire can be asked to fill in missing unit test coverage gaps as a learning exercise. Knowing what you aren't testing yet can help prioritize future work and diagnose problems faster.


The key is actually AE. The proof is simple, a key for definition is a minimal set of attributes whose closure contains all the attributes of the table. If you calculate the closure of A with respect to the given functional dependencies you will find:

A+ = {ABCD}

that does not contain the attribute E. So A is not a key, and E must be present in any key of R. And since:

AE+ = {ABCDE}

then AE is a key, and it is minimal (you cannot remove any attribute from it without losing the property of determining all the other attributes of the relation).


Query to filter out data based on two logic

So we're grouping by the combination of the sample_id and wheither timestamp IS NULL. The max is returned the group. MAX(NULL) is NULL so that case is covered.

SELECT sample_id, MAX(timestamp)
FROM table
GROUP BY sample_id, timestamp IS NULL


If the DBAs restored a single table from backup, would there be evidence anywhere in the data dictionary?

Depends on how the DBA restored it; if flashback is enabled for the database, there could be evidence that it was restored using the

flashback table scott.mytab to before drop;

command.  Here are stats on a personal table, before dropping, then after restoring it via the above command; I imagine other recovery techniques might also fill in the same data:

SYS@dev01> select CREATED, LAST_DDL_TIME, TIMESTAMP from dba_objects where object_name='MYTAB';

CREATED             LAST_DDL_TIME       TIMESTAMP
------------------- ------------------- -------------------
2018-07-09 19:18:29 2018-07-09 19:18:29 2018-07-09:19:18:29

SYS@dev01> drop table "MARK.STEWART".mytab;

Table dropped.

AtlasDev:SYS@dev01> flashback table "MARK.STEWART".mytab to before drop;

Flashback complete.

SYS@dev01> select CREATED, LAST_DDL_TIME, TIMESTAMP from dba_objects where object_name='MYTAB';

CREATED             LAST_DDL_TIME       TIMESTAMP
------------------- ------------------- -------------------
2018-07-09 19:18:29 2018-12-15 03:09:31 2018-12-15:03:09:31

SYS@dev01>

Now the DBA could have defined triggers to monitor table creation and deletion, and auditing could capture such activity if enabled.


Is there any side effect if select statements are wrapped in BEGIN TRAN ... COMMIT?

The answer depends on what isolation level your .NET project is setting when it accesses SQL Server.
If it's READ COMMITTED (the default), then there's not really any additional overhead (other than the additional BEGIN TRAN and END TRAN statements being executed).  You might think additional information would be written to the transaction log, but transactions don't really "begin" until something happens that needs to be logged.  I've written about this here: Transactions Don't Start At BEGIN TRAN
If you're using the SERIALIZABLE isolation level, you could get burned by the fact that those SELECTqueries will hold the locks they take for the duration of the transaction.  This could be happening without you realizing it if you're using TransactionScope in your .NET project, since that uses SERIALIZABLE by default (see my blog post for a demo: TransactionScope Considered Annoying).
I'd say all in all, if the default isolation level is being used, there's not much downside to leaving things the way you've described.  You'll want to be careful about the isolation levels though.


POSTGRESQL error Could not create shared memory segment: Cannot allocate memory

The HINT seems pretty clear,

  This error usually means that PostgreSQL's request for a shared memory segment exceeded available memory or swap space, or exceeded your kernel's SHMALL parameter. You can either reduce the request size or reconfigure the kernel with larger SHMALL. To reduce the request size (currently 5616156672 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.

Redhat has really good docs on SHMALL. The sysctl method is the new method.

sysctl -w kernel.shmall=5616156672

See man sysctl. These changes do not persist through reboot. Pay attention to the bottom, to make the change permanent,

echo "kernel.shmall=5616156672" >> /etc/sysctl.conf

Also note the top which is great generic advice This parameter sets the total amount of shared memory pages that can be used system wide. Hence, SHMALL should always be at least ceil(shmmax/PAGE_SIZE).


Database size reduced after backup on PostgreSQL 8.3 and restore in PostgreSQL 9.4

When you restore a database you have all the information on it packed, with no empty space between rows (or in indices), unless some specific settings are in place (basically: FILLFACTOR for tables and FILLFACTOR for indices).

On the other hand, when your database has been in use for some time, and you've had your share of inserts, updates and deletes, free unused space will appear. This is because of the way PostgreSQL and Multiversion Concurrency Control, a.k.a. MVCC work. MVCC allows for less lockings, which basically means you save  time. But you pay a price in terms of space:

Every UPDATE is equivalent to an INSERT together with a DELETE, with the overhead (at least in terms of space used) associated to both.
When you have several transactions running, and every one is INSERTing, UPDATEing or DELETEing, you have simultaneously several copies of every row involved.
The space allocated to these row versions won't be freed immediately after commit, and for a while, will be unused space within the files where your table data (and indexes) is being stored.

Autovacuum takes care of this space being made reusable by default, or you could have some specific procedure for routine vacuuming.

This fact already can explain the size change.

Optimizations between versions probably also took place; and can explain further improvements. Optimizations could also have been made for speed and not for size, and the actual size could actually grow from one version to the next. I really don't know the specifics to be able to tell; although the comment from @Erwin states that both changes making your tables shrink and changes making your tables bloat (grow) have taken place since version 8.3.

To distinguish between the two effects, if you're curious, you could just, as @Jack Douglas suggests, restore your database on 8.3. It will most probably shrink in size. If it shrinks to less than 151 MB (a size smaller that what you get with 9.4 version), then the removal of unused space made your DB shrink, and the version change actually made your DB grow.

For a better understanding of MVCC, look at Bruce Momjian's presentation.


You need to rebuild the clustered index after making the columns sparse. The dropped columns still exist in the data page until you do this as can be seen with a query against sys.system_internals_partition_columns or using DBCC PAGE

SET NOCOUNT ON;
CREATE TABLE Thing
(
ThingId int IDENTITY CONSTRAINT PK PRIMARY KEY,
USER_CHAR1 nvarchar(150) null,
USER_CHAR2 nvarchar(150) null,
USER_CHAR3 nvarchar(150) null,
USER_CHAR4 nvarchar(150) null,
USER_CHAR5 nvarchar(150) null
)
INSERT INTO Thing
SELECT REPLICATE('A',150),
       CASE WHEN number % 5 = 1 THEN REPLICATE('A',150) END,
       CASE WHEN number % 5 = 2 THEN REPLICATE('A',150) END,
       CASE WHEN number % 5 = 3 THEN REPLICATE('A',150) END,
       CASE WHEN number % 5 = 4 THEN REPLICATE('A',150) END
FROM master..spt_values

EXEC sp_spaceused 'Thing'

ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR2 ADD SPARSE
ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR3 ADD SPARSE
ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR4 ADD SPARSE
ALTER TABLE dbo.Thing ALTER COLUMN USER_CHAR5 ADD SPARSE

DECLARE @DynSQL NVARCHAR(MAX);

SELECT @DynSQL =  'DBCC TRACEON (3604);
                   DBCC PAGE(0, ' + LEFT(file_id,10) + ', ' + LEFT(page_id,10) + ', 3);
                   DBCC TRACEOFF(3604); '
FROM Thing
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
WHERE ThingId=76

EXEC(@DynSQL)

SELECT pc.*
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p on p.partition_id=pc.partition_id
WHERE p.object_id = object_id('Thing')
AND pc.is_dropped=1

 EXEC sp_spaceused 'Thing'

ALTER INDEX PK ON Thing REBUILD;

SELECT @DynSQL =  'DBCC TRACEON (3604);
                   DBCC PAGE(0, ' + LEFT(file_id,10) + ', ' + LEFT(page_id,10) + ', 3);
                   DBCC TRACEOFF(3604); '
FROM Thing
CROSS APPLY sys.fn_PhysLocCracker(%%physloc%%)
WHERE ThingId=76

EXEC(@DynSQL)

SELECT pc.*
FROM sys.system_internals_partition_columns pc
JOIN sys.partitions p on p.partition_id=pc.partition_id
WHERE p.object_id = object_id('Thing')
AND pc.is_dropped=1

EXEC sp_spaceused 'Thing'

DROP TABLE Thing


How to find the sum and overpayment of three table values

Probably easiest to do with sub-selects:

select clientno
     , coalesce(totalamount,0) as totalamount
     , coalesce(totalpaid,0) as totalpaid
     , least(coalesce(totalamount,0)-coalesce(totalpaid,0),0) as overpaid
from (
    select c.clientno
        , (select sum(i.totalamount)
           from invoice i
           where c.clientno = i.clientno) as totalamount
        , (select sum(p.totalpaid)
           from payment p
           where c.clientno = p.clientno) as totalpaid
    from client c
)

If least is not availible in your DBMS something like:

case when totalamount-totalpaid > 0
     then 0
     else totalamount-totalpaid
end as overpaid

should do

A slightly different approach is to use LATERAL. I think this is called CROSS APPLY in sql-server. I cannot verify this at the moment so you will probably have to modify this:

select clientno
    , coalesce(totalamount,0) as totalamount
    , coalesce(totalpaid,0) as totalpaid
    , case when coalesce(totalamount,0)-coalesce(totalpaid,0) > 0
           then 0
           else coalesce(totalamount,0)-coalesce(totalpaid,0)
      end as overpaid
from (
    select c.clientno, i.totalamount, p.totalpaid
    from client c
    outer apply (
        select sum(totalamount) as totalamount
        from invoice
        where clientno = c.clientno
    ) as i
    outer apply (
        select sum(totalpaid) as totalpaid
        from payment
        where clientno = c.clientno
    ) as p
) as x;


SSAS queries time out when Windows File System Cache drops

I believe you seeing the symptoms of an issue with Windows 2003 requiring contiguous memory and this causes the running processes including Analysis Service to trim their Working Set memory.  With large amounts of memory allocated this trim process can take a significant amount of time to complete, while this is running new allocations will be blocked, causing the process to stall.
This issue is fixed in Windows 2008 onwards this support article is for SQL Server but has all the links to the relevant pages http://support.microsoft.com/kb/918483 in particular this quote

  In Windows Server 2008, the allocation of physically contiguous memory
  is greatly enhanced. Requests to allocate contiguous memory are much
  more likely to succeed because the memory manager now dynamically
  replaces pages, typically without trimming the working set or
  performing I/O operations. In addition, many more types of pages—such
  as kernel stacks and file system metadata pages, among others—are now
  candidates for replacement. Consequently, more contiguous memory is
  generally available at any given time. In addition, the cost to obtain
  such allocations is greatly reduced.

I believe that with this issue you will either have to upgrade to Windows 2008 onwards or test granting Lock Pages In Memory to the Analysis Services account.  I have also had issues with memory allocation on Windows 2003 which requires a reboot to clear.

This SQLCAT blog compares the changes between Windows Server 2003 & 2008.

This blog mentions some of the issues of LPIM with Analysis Services (near the end)

Also this knowledge base article on working set trimming causing performance problems and this article shows how to Identifying Drivers That Allocate Contiguous Memory


Locations, single primary plus multi-secondary

Sounds pretty straightforward, so perhaps there is something I don't understand?

CREATE TABLE LOCATIONS
( location_key ... primary key not null
, location attribute_1 ... not null
, ...
, location attribute_n ... not null );

CREATE TABLE ACCESS_POINTS
( access_point_key ... not null
, location_key ... not null
, access_point attribute_1 ... not null
, ...
, access_point attribute_m ... not null
,    constraint ... primary key (access_point_key, location_key)
,    constraint ... foreign key (location_key)
                    references LOCATIONS (location_key)
);

A assume here that several locations can share the same access_point.


ALTER TABLE ALTER COLUMN cannot run inside a transaction block;

From AWS Documentation Check if you are violating any conditions..

  ALTER COLUMN column_name TYPE new_data_type A clause that changes the
  size of a column defined as a VARCHAR data type. Consider the
  following limitations:

  You can't alter a column with compression encodings BYTEDICT,
  RUNLENGTH, TEXT255, or TEXT32K.

  You can't decrease the size less than maximum size of existing data.

  You can't alter columns with default values.

  You can't alter columns with UNIQUE, PRIMARY KEY, or FOREIGN KEY.

  You can't alter columns within a transaction block (BEGIN ... END).
  For more information about transactions, see Serializable isolation.


MySQL is not cutting it at 233. The problem is likely in your save method which cuts it to 233 before the data even reaches MySQL.

Also, don't forget that 233 limit is not character limit, and as some character might need more han 1 byte to be stored, you might see less than 233 characters.

Also please make sure that data in MySQL server is really stored as latin1, this can be accomplished with:

show variables like 'char%';


The root nodes in the "Object browser" are actually connections, not servers. And a connection requires a user name. There is a bit of mis-labelling floating around, I have raised this issue on the developer mailing list before.

However, you can just edit the properties of a connection before you open it. If you don't want to store your password for automatic login, don't check the box at login time. Once you change the username in the properties you have to enter a new password automatically.

You can also save multiple connections to the same server with different usernames.


Indexing a date column in MySQL doesn't work if a date function is used

This is a common issue with functional transformations of any type not only dates. To avoid this issue just move the transformation to the constant side of comparison. Instead of
WHERE CONVERT_TZ(TBL.completed_date, timezone1, timezone2)
      BETWEEN date1
          AND date2

use the next syntax: (notice the reverse transformation of the TZs)
WHERE TBL.completed_date
      BETWEEN CONVERT_TZ( date1, timezone2 , timezone1 )
          AND CONVERT_TZ( date2, timezone2 , timezone1 )

The reason of that behaviour is that plain index contains the plain values ordered by itself. Functional indexes implemented in mysql-8.0.13+ can store the values ordered by some function. And only that function can be indexed on lookups. Sometimes it's impossible to move the fuctional transformation to the constant side, so the functional index is the only option.


Stored procedure with dynamic sql. Is it isolation safe by default?

In your current setup, each of those queries is a separate transaction. Individually they are reading only committed data. But nothing is binding them together. If you want to guarantee the same underlying data for that view across both statements, you need to wrap them in a transaction, and add a Table Hint to the first query specifying to lock the table: WITH (TABLOCK, HOLDLOCK). I just tested this on a View and it does block access until the transaction completes, unless a query uses the WITH (NOLOCK) hint.

Regardless, you can do an easy test with your code to see how your current code works and how any changes might do what you are wanting:

Right before the EXEC sp_executesql @DynamicPivotQuery line, add the following two lines:

RAISERROR('Make a change now...', 10, 1) WITH NOWAIT;
WAITFOR DELAY '00:01:00.000' -- 1 minute pause

Then, run the code you have posted above. When you see "Make a change now" in the Messages tab, make a change to the underlying table. Once the WAITFOR finishes it will run the dynamic SQL and you should see the effects of your changes.

So the following should work:

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX);
DECLARE @ColumnName AS NVARCHAR(MAX);

BEGIN TRAN;
--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
       + QUOTENAME(Course)
FROM (SELECT DISTINCT Course
      FROM CourseSalesView WITH (TABLOCK, HOLDLOCK)) AS Courses;

--Prepare the PIVOT query using the dynamic
SET @DynamicPivotQuery =
  N'SELECT Year, ' + @ColumnName + '
    FROM CourseSalesView
    PIVOT(SUM(Earning)
          FOR Course IN (' + @ColumnName + ')) AS PVTTable';

--Execute the Dynamic Pivot Query
EXEC sp_executesql @DynamicPivotQuery;
COMMIT TRAN;

Beyond that basic interaction, there are two other options:

Storing the results of the View into a local Temp Table (i.e. #TableName). This has the benefit of not locking the underlying table(s) of the View, but it will have to write the entire results of the view into tempdb and that does not sound like a scalable solution if the View returns 10k rows or more.
Enable SNAPSHOT ISOLATION (introduced in SQL Server 2005) which handles this for you without needing to explicitly lock the table:

Snapshot Isolation in SQL Server
ALTER DATABASE SET Options
You will still need to wrap the two queries in an explicit Transaction, but no need for the Table Hint
If you don't enable READ_COMMITTED_SNAPSHOT then you will need to issue SET TRANSACTION ISOLATION LEVEL SNAPSHOT before the BEGIN TRAN


MySQL/Percona Server is taking long and I'd prefer to skip all this waiting even if it means data loss

Unfortunately, you have to let InnoDB purge all its moving parts (Redo Logs, Undo Logs, Dirty Pages, Index Changes, Log Buffer Contents, etc)
Going forward, I recommend the following : Set innodb_max_dirty_pages_pct = 0
By default, innodb_max_dirty_pages_pct is 75. If you set it to 0, it forces the InnoDB Storage Engine to flush every dirty page and its grandmother out of the InnoDB Buffer Pool without any pausing. It will either flush every page or flush it low enough so that new dirty pages enter the Buffer Pool as fast as old dirty pages are flushed out.
Flushing dirty pages and cleaning up transaction is what consumes the most time in a shutdown. Minimizing the number of pages shortens shutdown time.
You can monitor the following status variables

Innodb_buffer_pool_bytes_dirty
Innodb_buffer_pool_pages_dirty

Watch them decrease until it hits zero or levels off. Then, you can issue a shutdown.
Here are additional settings you could use

Set innodb_flush_log_at_trx_commit = 2
Set innodb_write_io_threads = 64

Give it a Try !!!
Afterthoughts
Don't worry about dumping the buffer pool map and reloading. The output file is very small.
SATA/RAID0 sound scary to me. You should be using SAS/RAID10 for InnoDB.
At the very least you think about splitting up InnoDB data, logs, and system tablespaces into separate devices. See my earlier post MySQL on SSD - what are the disadvantages?
You should also think about getting away from Barracuda. See my old post innodb_file_format Barracuda
UPDATE 2014-12-07 18:14 EST
If you want to interrupt an InnoDB purge of everything and you don't want to preserve the data being fixed up, your only option is to set InnoDB Recovery Options. See my post The InnoDB log sequence number is in the future. If you use the innodb_force_recovery options, it may not let you do any more writes of InnoDB. If you a moderate amount of data, you could mysqldump whatever data can come out, shutdown mysqld, delete all data folders (except mysql schema), ib_logfile0, ib_logfile1, and ibdata1, start up mysql, reload data.
I cannot really help you if you have 1TB in a forced recovered state.


Oracle - User to not see data from other users

This is the default behaviour in Oracle. Users do not have access to other user's objects unless explicitly granted.

The DBA role includes the "GRANT ANY OBJECT" privilege, allowing the user to see all database objects:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/dba005.htm
https://docs.oracle.com/cd/A97630_01/server.920/a96521/privs.htm

You should revoke the DBA role from every app user and stop granting it to new users.


The publication 'My_publication' doesn't exist?

You need to run that procedure in the context of the publication database (the database that is the source of the data you wish to replicate).  You can do that with a USE statement:

USE [YourDatabaseName];
GO

EXEC sys.sp_publication_validation
    @publication=ReplicationFaouzi,
    @rowcount_only=0,
    @full_or_fast=0,
    @shutdown_agent=0

If you're unsure of which database is the publication database, you can run this query to find which databases are configured as publishers:

SELECT [name] FROM sys.databases WHERE is_published = 1;

Once you have the database name from that query, you can run this to see the name of the actual publications that should be passed to sp_publication_validation.  In your case, the publisher is named Guichet, so you'd run:

USE [Guichet];
GO
EXEC sp_helppublication;

This will give you the value that needs to be passed to the @publication parameter.

Note that all of the above is written in the context of transaction replication (which is what the original question was about).  Different steps are required for different types of replication.

There is a great "information gathering" script on the Microsoft Docs website that walks through getting info on distributors and publishers when using different types of replication:

Distributor and Publisher Information Script


Changed stored procedure causes VB runtime error

So many problems with this code.  The error is right here:

.PrintString(MNth(J), 9, DosPrint.Align.Left)

Since J is a number between 2 and 13 when it returns from sql and then in the set 0,2..11,13 after the if statement, when it is 13 MNth(13) will throw an array out of bounds.

The fix is in - add the following line (if statement for context.)

 J = J - 1  ' now J goes from 1-12
 If J = 12
      Then J = 0
 End If

Another note (I can't resist pointing this one out but there are many...)

This part of your query:

Case
When Month(P.FromDate) in(4,5,6,7,8,9,10,11,12) then  Month(P.FromDate)
when Month(P.FromDate) in(1,2,3) then  Month(P.FromDate)
end as Mnth

could be written as:

Month(P.FromDate) as Mnth

Since Month() is defined to return a value from 1..12


Why null is getting compared with null in the join condition in SQL Server 2016?

Maybe the column col1 for these rows has a literal string value of ‘NULL’ and not a missing value null indicator? - sqlraptor

You can test by adding the conditions and comparing the output of the queries:

WHERE a.col1 = 'NULL'

WHERE a.col1 IS NULL


How to determine the best index on a table with 3 nullable columns?

When looking up contact info, an index with username, email, mobile_phone_number would be great except for three small problems

uniqueness of username
uniqueness of email
uniqueness of mobile_phone_number

If username is supposed to be unique, then username, email, mobile_phone_number would just be overkill with index bloating.

Since email and mobile_phone_number are nullable, they need unique indexes as well.

If you create a unique index on username, email or mobile_phone_number, MySQL will disallow the insert for you on any individual uniqueness.

You could still create an index username, email, mobile_phone_number as a good covering index to retrieve all three columns when looking up by username with

SELECT username, email, mobile_phone_number
FROM users WHERE username = '...';

SUGGESTION

Create the indexes to have the following

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `password` varchar(81) DEFAULT NULL,
  `mobile_phone_number` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `mobile_phone_number` (`mobile_phone_number`),
  UNIQUE KEY `contact_info_ndx` (`username`,`email`,`mobile_phone_number`)
) ENGINE=InnoDB;

by running

ALTER TABLE users
   ADD UNIQUE KEY (`username`),
   ADD UNIQUE KEY `email` (`email`),
   ADD UNIQUE KEY `mobile_phone_number` (`mobile_phone_number`),
   ADD UNIQUE KEY `contact_info_ndx` (`username`,`email`,`mobile_phone_number`)
;


How do I add a directory, or bulk, of users to Microsoft SQL Server permissions

SOLUTION UPDATE:

To finish the set up you must type the name of the directory into the check names box and click check names to confirm it. This seems intuitive but it is an easy step to miss. Please reference the pictures below, also my folder was named users, yours may be something else:

The first picture is before clicking the check names button

This picture is after clicking the check names button


How can I get distinct counts with different where clauses on the same field?

It seems like you really want a PIVOT.

First, we'll create your DeviceLicense table, and populate some sample data:

CREATE TABLE dbo.DeviceLicense
(
    SINo int NOT NULL
    , DeviceFilter nvarchar(50) NULL
    , OrganizationID int NOT NULL
);

INSERT INTO dbo.DeviceLicense (SINo, DeviceFilter, OrganizationID)
VALUES (1, NULL, 1001)
    , (2, NULL, 1001)
    , (3, 'iPad', 1000)
    , (4, 'iPad', 1001)
    , (5, 'MACommon', 1000)
    , (6, 'MACommon', 1001);

This is the PIVOT statement.  As you can see below, the statement adds a PIVOT clause to a typical SELECT statement to allow rows to be transformed via an aggregate into columns.

SELECT *
FROM (
    SELECT *
    FROM dbo.DeviceLicense
    ) src
PIVOT (
    COUNT(SINo)
    FOR OrganizationID IN (
        [1000]
        , [1001]
    )
) pvt;

The results of the select statement above:

+--------------+------+------+
| DeviceFilter | 1000 | 1001 |
+--------------+------+------+
| NULL         |    0 |    2 |
| iPad         |    1 |    1 |
| MACommon     |    1 |    1 |
+--------------+------+------+

I haven't added all your sample data, so the output is limited, but you can see there are columns for each OrganizationID, and rows for each DeviceFilter.  In future, know that it helps us give you a better answer if you provide the scripted DDL for the objects involved (the tables, etc), along with a table of data, not a screenshot.

Here, I'll attempt to show the PIVOT clause syntax in an understandable way:

PIVOT (
    COUNT(SINo)
    FOR OrganizationID IN (
        [1000]
        , [1001]
    )
) pvt;

The first obvious bit is the presence of the PIVOT keyword.  This tells SQL Server we're going to provide the following items:

a column from the main "src" SELECT query, wrapped in an aggregate, such as MAX(), SUM(), or in our case, COUNT().
The column headings we want, in the form of a list of the values present in a column from the "src" SELECT query.  In this case, the OrganizationID column contains the values 1000 and 1001, which we'd like displayed as headings.  The OrganizationID column might contain other values, such as 1002, but the way the PIVOT clause is written now, values from those rows will not be displayed.  In order to have 1002 values shown as another column, we'd need to define the pivot like this:

PIVOT (
    COUNT(SINo)
    FOR OrganizationID IN (
        [1000]
        , [1001]
        , [1002]  --note this creates an additional column in the output!
    )
) pvt;


How does the transaction log work in SQL Server when i run a DML

Yes, SELECT statements can in fact use space in the tempdb transaction log, as temporary working space to complete the query:

The Transaction Log (SQL Server):

  Long-running transactions might also fill up tempdb's transaction log. Tempdb is used implicitly by user transactions for internal objects such as work tables for sorting, work files for hashing, cursor work tables, and row versioning. Even if the user transaction includes only reading data (SELECT queries), internal objects may be created and used under user transactions. Then the tempdb transaction log can be filled.


Verify that the port is open. From a remote host with the default port:
telnet <ip> 3306

Can you show us the my.cnf file, [mysqld] section with the bind address parameter.

Can you ping that ip address at all? Do you use a hostname or an ip address to connect? If it works with ip and not hostname you may have a dns configuration issue.


avg_page_space_used_in_percent and fill factor

avg_page_space_used_in_percent does not care about fill factor.

Therefore if you set an index to have fill factor of 40, your avg_page_space_used_in_percent will straight away be 40.


use DBCC SHRINKDATABASE after rebuild / reindex

Because your database will grow again. You are not fixing anything by shrinking, because as you stated, it will grow out after the next rebuild operation anyway.

What are you going to do when you run out of free space before your next rebuild operation? Or you cannot run your rebuild / reindex anymore because of the limited disk space?

Shrinking use cases are very limited, and is considered a bad practice, not only because your db will grow out again.

Take a look at this Question for more info why you should not do this.

I suggest adding space to your disk / adding a new disk and moving your data to that disk if you can't add more space.

Also, there are some valid reasons as to not defrag your indexes:

https://groupby.org/conference-session-abstracts/why-defragmenting-your-indexes-isnt-helping/


Oracle 11g r2 standard edition installation - set environment variable PATH and create tnsnames.ora

Normally you use the oraenv script to set you environment correctly for a given ORACLE_SID. The ORACLE_SID and ORACLE_HOME combinations are maintained in /etc/oratab or /var/opt/oracle/oratab which is read by the oraenv script that should be sourced (called with a .)

What oraenv by default sets does is make sure that PATH contains at least $ORACLE_HOME/bin

If you want to have some control over where your SQL*net files are located it is smart to create a TNS_ADMIN environment variable that points to the directory where amongst others tnsnames.ora is located. The tnsnames.ora file is most important to be able to point to a - local or remote - database using SQL*net.

If you only access your database from the same machine as where the database is running and you do not access an other database, a tnsnames.ora is not needed, just convenient. Normally we have a tnsnames.ora that contains lots of database on the site.


PostgreSQL does not have time-based triggers.

If you want to store as much of your logic as possible in the database, you could create an ordinary function (not a trigger function) and then invoke it from your favorite job scheduler (cron for me).  If you want to use the database for storing data, not logic, then you could put the queries you want to run in a .sql file and use your favorite job schedule to invoke that file using psql -f for example.  Or, if you want all of your application logic in, for example, a Perl module, you could write a function in your Perl module which connects to the database (reusing whatever connection logic your module already has) and does what you want, then use your favorite job scheduler to invoke that function.

There are many ways to do it, the key is to pick a method which fits in with the your overall design.

It would probably be possible to put the logic into a trigger on a dummy table, then have your favorite job scheduler do a dummy operation on the dummy table to get the trigger to fire.  Pointless, but possible.


In the "Events Selection" tab of the trace definition, make sure that the TextData column is checked for RPC:Completed events.

(This is unchecked in the default trace definition)


SQL Server error log entry : Error: 17806, Severity: 20, State: 14

Any time I have seen this error in my environment (0x8009030c and State 14), it has been because the SQL Server engine account was running with a Domain service account, and that account was locked at the time a Kerberos connection was attempted.  The connection would work, but it would be kicked down to NTLM.

Method 1:

Go to register  start –> run … Regedit
Go to:  HKLM\System\CurrentControlSet\Control\LSA
Add a DWORD value called “DisableLoopbackCheck”
Set this value to 1
Rebooted after making this change.

After this registry change was effected.

Method 2. This somehow worked for me: Restarting the sql browser services:

Also, for more info on the related issue, refer to below article:

http://www.allenkinsel.com/archive/2010/06/sql-server-and-sspi-handshake-failed-error-hell/


If the correct auditing is turned on, and the security event log hasn't rolled over it'll be in there.  Failing that it may not be.

You could look at NTFS file permissions and ownership, and what accounts have sysadmin rights and possibly figure it out.


Plain and simple, think of developing a database without an ERD as building a house without a building plan. It might be doable because you think that simply laying a brick one over another is enough to build something, however the moment somebody else takes responsibility over the project there is disaster potential.

In my experience you will not benefit much from ERDs unless you use them in conjunction with CASE tools (ERWin, MySQL Workbench etc.) which will additionally allow you to perform some really helpful operations such as forward and reverse engineering. Even without these functions having a centralized schematic of the complete database is useful because sometimes the constraints implemented in the database itself are not enough to tell the full story about the relationships between particular database entities.

Here is an example involving MySQL which, as you might know, implements several internal storage engines, most notably MyISAM and InnoDB. There are significant differences between them one of the most important one being that MyISAM doesn't support constraints. Despite that fact MyISAM is heavily used for web applications which means that the relational logic needs to be implemented either through business logic (application code) or in another way. The problem is when you forward engineer an ERD with MyISAM tables (entities) MySQL will silently ignore the constraints set by the ERD and you will end up with a database which doesn't clearly identify the nature of the relationships between entities. In other words after you develop the database layout there is no way for the code developers to implement proper business logic without an ERD.


Can sst_state_transfer be set to rsync with innodb as storage engine in Galera cluster?

Is xtrabackup the only wsrep-sst-method to be used with innodb storage engine?

All Galera clusters are InnoDB, MyISAM (and derivates) not supported (expect mysql database with wrappers for managing users/permissions).

No, nothing wrong with your configuration. You can use Logical State Snapshot mysqldump or the Physical State Snapshot rsync and xtrabackup. See State Snapshot Transfers for a comparison.

A physical restore always needs a restart. The same concept like backup/restore.

In normal operations only IST is needed. SST is only needed when something goes wrong (maybe infrastructure issue) and the node is out-of-sync. Mostly the DBA cares about such cases.

State Snapshot Transfers (SST) Where a snapshot of the entire node
state transfers.
Incremental State Transfers (IST) Where only the missing transactions transfer.


Changes to main Database during Transactional Replication

By default, replication will push out the following DDL changes to subscribers:

ALTER TABLE
ALTER VIEW
ALTER PROCEDURE
ALTER FUNCTION
ALTER TRIGGER

And by default, I mean unless you have specified otherwise when you made a call to sp_addpublication when setting up the publication (even if done through the GUI, this is the underlying stored procedure that is called).  There is a parameter for that stored procedure, @replicate_ddl, that is set to 1 by default, meaning that the above DDL commands will replicate to subscribers for the articles in the publication.

When you made your initial call to sp_addarticle, you would have specified (again, even if through the GUI where all the article options are set) which indexes (clustered and nonclustered) were generated for subscribers to create for initialization as well.

Reference :: How to: Replicate Schema Changes


difference between having many non clustered index with single columns and with combination of many columns

Having many indices requires SQL Server to maintain and update many indices for each insert, delete or update statement. So having less indices is generally better.

However, when you have a compound index (made up of multiple columns), that only helps if you use/specify the n left-most columns in your query.

So if you have an index on (City, LastName, FirstName) for an address table, that index might be used if

you have all three columns in your WHERE clause
you specify WHERE City = 'London' in your query (using the 1 left-most column)
you specify WHERE City = 'London' and LastName = 'Smith' in your query (using the 2 left-most columns)

However, such an index cannot ever be used for:

finding all rows with a WHERE FirstName = 'Joe' clause - you're not using the n left-most columns in that index
finding all rows with a WHERE LastName = 'Brown' AND FirstName = 'Charlie' clause - again: you're not using the n left-most columns in that index

A compound index cannot (in many cases) replace all one-column indices. Be very careful when designing and creating compound indices to make sure you design them in a way they'll really be useful!

So while compound indices do have their reason to be, and they're generally preferable - you also need to make sure they'll really be able to be used. An index that is just being maintained (and causes effort on SQL Server's side), but can never be used in any queries, is the worst index ever - no gain, only maintenance overhead.

You should also read Kimberly The Queen of Indexing Tripp's excellent blog posts on SQL Server indexing - most notably Indexes: just because you can, doesn't mean you should!


I have written a stored routines called month_weeks. It accepts two parameters, the month and the year and output a list of weeks. It uses a temporary table to calculate the start and the end of weeks. For example:

call month_weeks(1,2015);

returns:

# WeekFrom, WeekTo
2014-12-29, 2015-01-04
2015-01-05, 2015-01-11
2015-01-12, 2015-01-18
2015-01-19, 2015-01-25
2015-01-26, 2015-02-01

The text of routine is:

DELIMITER $$
CREATE PROCEDURE month_weeks(
  IN month INT,
  IN year INT)
BEGIN
  drop table if exists tmp;
  create temporary table tmp(WeekFrom date, WeekTo date) engine=memory;
  set @s_month:=STR_TO_DATE( concat('01-', convert(month, char), '-', convert(year, char)),'%d-%m-%Y');
  set @e_month= last_day(@s_month);

  set @s_week:=date_add(@s_month, interval (-dayofweek(@s_month)+2) DAY);
  set @e_week:=date_add(@s_week, interval 6 DAY);
  while datediff(@e_month, @s_week) >= 0 do
      insert into tmp values(@s_week, @e_week);
      set @s_week:=date_add(@s_week, interval 7 DAY);
      set @e_week:=date_add(@e_week, interval 7 DAY);
  end while;
  select * from tmp;
END $$
delimiter ;


Check if value exists in postgres text column

That is not an "array column". The contents of a real array (or even JSON) would be displayed in a different way. I guess that's just defined as text or varchar.

It's not a valid syntax for an array so you can't use any of Postgres' array functions and it's not valid JSON either so you can't use any of Postgres JSON functions. As this is plain text, the only thing you can use is LIKE or regex comparisons.

Something like:

where direct_selector LIKE '%"departure"%';
-- or
WHERE direct_selector LIKE '%"departure"%' AND direct_selector LIKE '%"transit"%'


Could be this.  Part of Security Patch that came out in Nov. & was updated in Dec.
This explains the problem:
https://support.microsoft.com/en-us/topic/managing-deployment-of-kerberos-s4u-changes-for-cve-2020-17049-569d60b7-3267-e2b0-7d9b-e46d770332ab
This contains the patch under Security Updates header:
https://msrc.microsoft.com/update-guide/vulnerability/CVE-2020-17049 (first 1 is for 2016 & up)
First, make sure all AD Servers are on the same patch.
Create "enforce modes" create reg key if not there. Set to 1.  Let sit 7 days to reset.
Change to reg key to 2. Restart Service or wait another 7 days.


However, I'm not sure what would be the best practice to use identifier as a foreign key. Should I use as reference the value of the foreign key like this

A couple of points before hand.

There is no need to mark a PRIMARY KEY NOT NULL, and DO NOT create a UNIQUE index on it. That'll just slow things down. It's already implemented with a ``UNIQUE` index.
Reconsider bigserial, do you really need it?
If you do not use id, but instead fund_id, or id_fund or the like, you can use the USING (fund_id) clause which is

easier to type
less error-prone

PostgreSQL provides for a shorter more terse syntax to refer to primary keys..

-- it'll guess primary key
fund_identifier  type  REFERENCES table;

That said, I think your questions is if your primary table uses bigserial how does your foreign table declare the column that refers to it.. Like this, using bigint

fund_identifier  bigint  REFERENCES table;

So your schema should look like

CREATE TABLE funds (
    id_fund    bigserial PRIMARY KEY,
    identifier text      UNIQUE NOT NULL
);

CREATE TABLE security_papers (
  id_security_paper    bigserial  NOT NULL
  id_fund              bigint     REFERENCES funds
  as_of_date           date,
);

Then

SELECT * FROM security_papers
JOIN funds
  USING (id_fund);


How to move a SQL Agent Job to another Server?

If you're talking about moving SQL Server Agent Job, I suggest you use the option "Script Job as -> Create" and than copy and paste the code on the new server. Once you have the code on the new server you can edit it (or execute it and than edit the job through Job Properties window) so it could work in a new enviroment.


kill all processes from Vertica :

ps -ef | grep vertica

This is avaialbe on all nodes.

Then  start your database without the force option

admintools  -t  restart_node  --hosts= --database=--password=

While starting the database make sure you tail the logs to capture the errors.

to get more solutions and Vertica fixes see this link


how to set a row's value from a certain row's value?

Assuming that the tstamp has a UNIQUE constraint:

UPDATE activities AS a
  JOIN
  ( SELECT cur.tstamp,
           SUM(prev.amount) AS balance
    FROM activities AS cur
      JOIN activities AS prev
        ON prev.tstamp <= cur.tstamp
    GROUP BY cur.tstamp
  ) AS p
  ON p.tstamp = a.tstamp
SET a.balance = p.balance ;

Tested: SQL-Fiddle

MySQL has also a feature to use ORDER BY with an UPDATE, which you can combine with the use of variables:

SET @b := 0 ;
UPDATE activities
SET balance = (@b := amount + @b)
ORDER BY tstamp ;

Tested: SQL-Fiddle


Archive Logs filling up Flash Recovery Area (FRA) space

Oracle will automatically delete obsolete files. In general, you should not have to manually delete anything.

  An important part of RMAN maintenance is deleting backups that are no longer needed. If you configure a fast recovery area, then the database automatically deletes unneeded files in this area automatically

From Overview of RMAN Backup and Repository Maintenance

First, determine if you are running out of space. For example using the following query (sample output included):

SELECT * FROM V$RECOVERY_FILE_DEST;

NAME            SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES
--------------  ----------- ---------- ----------------- ---------------
/mydisk/rcva     5368709120 109240320             256000              28

If you are running out of space you have to either decrease your retention policy or increase the size of your FRA.


You do not have circular references. All those arrows point in the same direction, "up" to the tenant.

You can insert data to populate the tables without having a dependency on data not yet inserted.

Tenant
Group
Task
App
Group App
User
Group User

This would only be a circular reference if, for example, tenant had a user_id reference that pointed back down to the user table, which references back up to the tenant.

The arrows in your diagram have multiple paths back up to the "top" of the hierarchy (tenant, which is drawn to the bottom left). There is a clear tail and head of every path.

But the arrows don't create a circular cycle. In a circular reference, there is no clear head or tail in the path of the arrows.

So great job on your schema design! You did better than you thought.


Change the Hosts files on both SQL nodes to show 127.0.0.1 LocalHost

The hosts file on a server is typically located at: C:\Windows\System32\drivers\etc. In this location you will see several files, one of which is the Hosts file. Edit with caution.


Can a Database request automatically remove itself from the queue after a delay?

From Oracle 11gR1 you can use the WAIT instruction in DDL:

lock table t in exclusive mode wait 10;

This will wait 10 seconds before giving up.

The WAIT instruction has been available for a long time (9i) for DML:

SELECT * FROM t FOR UPDATE WAIT 10;


Connecting pgAdmin3 to Postgres on Heroku

Thanks to @araqnid on Stack Overflow for the answer here.

Quoted Verbatim from there:

Open the "Properties" of the Heroku server in pgAdminIII and change the "Maintenance DB" value to be the name of the database you want to connect to.
The default setup is suitable for DBAs et al who can connect to any database on the server, but apparently that isn't true in your case.

This worked for me!


It's a WARNING and a NOTICE, not an ERROR. Neither of them are responsible for the shutdown. I assume however you're getting this message because in your /etc/hostname (or dns) you have a polluted entry.

As for why mysql cares what the name resolves to, here is a post on the mailing list.

  This warning seems to come from MySQL ACL code. The reason is that
  reverse DNS lookup returns hostname which resembles IPv4 address
  (hostname starts with '98.158.'). One way to get rid of this warning
  and DNS lookups is to put

  skip-name-resolve

  in my.cnf. However, in this case you can use only IP addresses in
  MySQL grant tables. For more information see http://dev.mysql.com/doc/refman/5.5/en/dns.html.

If this is by intent, you may want to read this bug


My understanding of this is that, yes, under a transaction your SP will hold locks for the duration of the transaction. (Note there are lots of types of locks - SQL Server could row lock, table lock, page lock etc)

One SP being called simultaneously (with no other activity on your DB) causing deadlocks suggests one thing to me: the SP is not locking the same things in the same order on each call. Do you have IF blocks that perform actions on a table if a condition is met? You may wish to rewrite those to make the IF condition appear in the WHERE condition instead. This will ensure the SP locks the same resources in the same order each time.


What steps to take to resize an existing MYSQL NDB Cluster?

Since your data nodes are up and running you can check the
size of the virtual memory allocated to the data node. If
this is bigger than 80-90% of your available memory you will
experience issues like you describe.


JOIN and concatenate between tables and UPDATE

Does this work?

UPDATE KeyInventory
SET KeyInventory.Productcode = concat(SFXVencode, partnumber)
FROM KeyInventory
   INNER JOIN Keybrands ON keybrands.vencode = keyinventory.vencode

Might need a little tweaking once you post your DDL...


How do you version your Oracle database changes?

At the sites I've worked at, any changes that need to be made to the production instance(s) must be scripted as change scripts that will run in SQL*Plus; in addition, scripts needed to recreate all schema objects from scratch must be kept up-to-date. All these scripts are checked into change control, and are migrated from there.

You can audit DDL changes or use DDL triggers to pick up changes, or even use diff software to compare two instances, but these methods are indiscriminate; often a developer will make and undo a number of changes to a schema (e.g. little test changes, creating dummy tables to test out concepts, etc) before working out what exactly needs to be changed.


Use Extended Events to capture actual execution plans, via the query_post_execution_showplan Event Library.  Be aware of the following warning, found in the New Session Wizard, when creating the session:

query_post_execution_showplan
Occurs after a SQL statement is executed. This event returns an XML representation of the actual query plan. Using this event can have a significant performance overhead so it should only be used when troubleshooting or monitoring specific problems for brief periods of time.

The following T-SQL will create the required Extended Events session.  Do not run this on a production SQL Server unless you want to slow it to a crawl.
CREATE EVENT SESSION [CapturePlans] ON SERVER
ADD EVENT sqlserver.query_post_execution_showplan(
    ACTION(sqlserver.client_app_name,sqlserver.database_id,sqlserver.sql_text))
ADD TARGET package0.ring_buffer(SET max_memory=(2048))
WITH (STARTUP_STATE=OFF)
GO

ALTER EVENT SESSION [CapturePlans] ON SERVER STATE = START;


How can I benchmark the performance of UDFs to figure out which one is faster?

The SQL Profiler, execution plan, IO/Cpu statistics are your best friend at checking the performance of your code. You will see if there are any differences in performance between the 2 versions of functions.

In this question - Tuning exercises - you can find some basic and advanced references about tuning.


Are you sure the index was analyzed - or was it just that DBMS_STATS updated the LAST_ANALYZED timestamp?  The best way to find out it to trace the process performing the analysis.


How to extract a whole database schema using only ODBC access?

Following on from chat in "The Heap"...

Visio can reverse engineer a database. However, I assume thus needs to read metadata or system tables to get everything (keys, DRI, datatypes the works).

This old MSDN article may help "Migrating Btrieve Applications to MS SQL Server 7.0" too


Grant privileges to tables in all (dynamic) schemas

Just omit the IN SCHEMA clause, and it applies to all schemas, so no wildcard capability for schemas is needed.

ALTER DEFAULT PRIVILEGES FOR ROLE my_role GRANT SELECT ON TABLES TO my_role;

Your real problem is likely to be the FOR ROLE clause.  The above is kind of useless, as it applies only to tables (in any schema) created by my_role.  But the creator of tables automatically has select privileges, so this doesn't do anything useful.  Your FOR ROLE clause needs to specify who will be creating the tables (if you omit it, it means the current user).  If you have multiple such creating roles, you have little option but to repeat the ALTER DEFAULT PRIVILEGES for each one.  This is where the wildcard capability is really needed, but sadly does not exist.


Bulk load data and provide row-by-row feedback

You have no solution. You can speed it up (bulk load into temporary tables, then execute the actual insert from there), but your requirement ot absolute row by row feedback basically throws and set operation out of the window. And if it has to be atomic, it has to be atomic. Time optimization is all you CAN do.

Now, going from a temporary taging table (that can be ordered) with one complex scipt that then inserts the feedback into the staging table - is about the best yo ucan do. What it does is eliminate the latency to the server and that is the best yo ucan hope for.

Standard practice is to avoid any row by row processing, but your requirement just is what it is.