Mongo DB Upgrade 3.0 to 3.2 Verification of Upgrade

Can I programatically verify that the upgrade was successful by looking at any metadata from the DB itself?

Yes, you can use shell scripts to redirect input/output of a mongo shell.

Beware that scripted mongo shell behaves differently than the interactive mongo shell: https://docs.mongodb.com/manual/tutorial/write-scripts-for-the-mongo-shell/#differences-between-interactive-and-scripted-mongo

a tangential question, YAML is current preferred config file format, as it is supported even with 4.2 any issues continuing with it?

From https://docs.mongodb.com/manual/reference/configuration-options/ :

  Changed in version 2.6: MongoDB 2.6 introduced a YAML-based
  configuration file format. The 2.4 configuration file format remains
  for backward compatibility.

  MongoDB configuration files use the YAML format.

With YAML being the official format, there is no issue. The old format was deprecated, it should still work, but I wouldn't bet that this will never be removed in the future.

About the upgrade procedure (as described here), I imagine you should evaluate the result of rs.status(), but as you are running standalone, I see why you use asserts from db.serverStatus(). In your case, I would be more worried when restarting the mongod process, an exit code was returned.


This could be to resolve a data model issue known as the FAN TRAP. There is quite a good youtube video describing it at https://www.youtube.com/watch?v=YKqJ_FjfyVM. The video also goes on to describe another common modeling issue; the CHASM TRAP.


Is the NOLOCK hint completely safe to use on a table that's guaranteed not to change?

If I have a table where the data is guaranteed to never change, is it completely safe to add the NOLOCK hint to my SELECT queries against that table?

In practice, yes (although not documented as such), but also useless.  If your data never changes, then using dirty reads won't cause any observable behavior or material performance difference.

  If I have a table where some of the rows will be updated, but I'm only selecting rows that are guaranteed not to change, is this also a completely safe scenario to use the NOLOCK hint?

No.  Absolutely not.  The query plan to read the target rows may read changing data structures without proper concurrency controls.  So you are back to hoping that your queries don't return bizarre, incorrect results or simply fail.


Rebuilding an index online doesn't allow you to completely avoid locking - when the process completes the table is locked for a brief period and the new index replaces the old one.

Online index operations need to take two very short-term table locks. Shared)table lock at the start of the operation to force all write plans that could touch the index to recompile, and a SCH-M (Schema-Modification) table lock at the end of operation to force all read and write plans that could touch the index to recompile.

And the article you provided has some info on this as well:


I wen through your post and here is my response (one-by-one) on the basis of what I got.

It's not really a problem if these processes run slow or get delayed

That's good if it suits you requirement.

But on the other hand this data should be available via a website and then the queries on the data should be quick

and

I was thinking about replicating parts of the processed data to a second server and running the website on that database.

In transactoinal replication SQL server allow to create index on the tables in subscriber database. So you want to use subscriber to generate reports, you can optimize you query and create indexes (on subscriber DB) that boosts your query performance to get quick response.

But if you want to make transaction at both side INSERT,UPDATE, DELETE then you should go for MERGE Replication.

I didn't understood rest part of you post. It would be good to write proper suggestion if you clarify it.

Thanks!


You would need to add the folder containing bcp.exe to the environment variables search path.


Can I monitor the progress of importing a large .sql file in sqlite3 using zenity --progress?

The sqlite3 tool does not output such numbers.

You would have to write some script that feeds the lines of the import file into sqlite3, and at the same time feeds the percentage numbers into zenity. Try something like this:

#!/bin/bash
maxlines=$(wc -l < import_file.sql)
exec 3> >(zenity --progress --auto-close)
counter=0
while IFS= read -r line; do
    echo "$line"
    counter=$(($counter+1))
    echo $(($counter * 100 / $maxlines)) >&3
done < import_file.sql

Call it with thescript | sqlite3 dbfile.


Most efficient way to set up a database handling millions of links?

(Not an answer, but some pitfalls that make it difficult to design this for efficiency.)

More than half the users will have only one link.
Some users will each have over 100K links.
More than half the sites will have only one page.
Some sites will each have over 100K pages.

What does it mean?  It means that any form of indexing, compression, etc, needs to be flexible enough to handle such extremes.

(I experimented with a similar experiment, quitting after a million or so links.  Those are extrapolations of what I encountered.)


SQL Server 2017 Express edition Bulk load

After creating the table you want to import the data to (TextFileImportTable) with a nullable column (FileName) on the end to store the file name, here's an example on how to import to it:
BULK
INSERT TextFileImportTable
FROM 'c:\textfile-1.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

UPDATE TextFileImportTable
SET FileName = 'textfile-1'
WHERE FileName IS NULL;

BULK
INSERT TextFileImportTable
FROM 'c:\textfile-2.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)

UPDATE TextFileImportTable
SET FileName = 'textfile-2'
WHERE FileName IS NULL;

Rinse and repeat a new batch for each text file.
Note this answer assumes the columns are comma delimited and each row is new line delimited.  You can modify those attributes by changing the characters used in the FIELDTERMINATOR and ROWTERMINATOR properties, per this Microsoft Docs page.


The problem you're likely facing is around SARGability, namely using the LEFT function in your WHERE clause:

LEFT(s2.RecordKey, 2) = a.Prefix

With that in there, you're stuck running the function for every row and then comparing it. You can't index for that, as-is. Putting the transformation into a CTE, view, or derived table wouldn't help, nor would writing a function to perform the manipulation.

One way around that is to create and index a computed column:

ALTER TABLE dbo.SlowCrossApply ADD LeftTwoPrefix AS LEFT(RecordKey, 2);

Which can be indexed. I'm also changing your index definition a bit:

CREATE NONCLUSTERED INDEX TodayRecords_Filtered ON
dbo.SlowCrossApply(LeftTwoPrefix, FromToday, Score6)
INCLUDE (Score1, Score2, Score3, Score4, Score5)
WHERE FromToday = 1

Another alternative would be to dump the results of your CROSS JOIN into a temp table:

SELECT     LEFT(s.RecordKey, 2) AS Prefix, CAST(ROUND(sm.ScoreGoal * COUNT(*), 0) AS INT) AS Quant
INTO       #yourmom
FROM       dbo.SlowCrossApply AS s
CROSS JOIN dbo.SmallTable AS sm
WHERE      s.FromToday = 1
AND        sm.ScoreMonth = MONTH(GETDATE())
GROUP BY   LEFT(s.RecordKey, 2), sm.ScoreGoal;

You followed up with:

  ... do you agree with the FromToday [column] being the index key in
  i02TodayRecords instead of RecordKey, ignoring the specifics of the
  query I used here?

In this case, it doesn't matter much. Non-unique nonclustered indexes store clustered index key columns in all levels of the nonclustered index. See my post here: Where Clustered Index Keys Dare.

And:

  Furthermore, since RecordKey is "random", there would likely be a
  great deal of fragmentation that would occur if it were the index key
  as opposed to FromToday which will always be 1. Do those points sound
  valid?

I generally don't worry about index fragmentation, so no. I aim to create indexes to help queries. A fragmented index is much more helpful than a non-existent index, and in many circumstances you'll never notice the fragmentation.

Hope this helps!


Best approach for populating date dimension table

UPDATE: for a more generic example of creating and populating a calendar or dimension table, see this tip:

Creating a date dimension or calendar table in SQL Server

For the specific question at hand, here's my attempt. I will update this with the magic you use to determine things like Fiscal_MonthNumber and Fiscal_MonthName, because right now they're the only non-intuitive part of your question, and it's the only tangible information you actually didn't include.

The "best" (read:most efficient) way to populate a calendar table, IMHO, is to use a set, rather than a loop. And you can generate this set without burying logic into user-defined functions, which really don't gain you anything but encapsulation - otherwise it's just another object to maintain. I talk about this in a lot more detail in this blog series:

http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2
http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3

If you want to keep using your function, make sure it's not a multi-statement table-valued function; that's not going to be efficient at all. You want to make sure that it is inline (e.g. has a single RETURN statement and no explicit @table declaration), has WITH SCHEMABINDING, and doesn't use recursive CTEs. Outside of a function, here is how I would do it:

CREATE TABLE dbo.DateDimension
(
  [Date]                      DATE PRIMARY KEY,
  [DayOfWeek_Number]          TINYINT,
  [DayOfWeek_Name]            VARCHAR(9),
  [DayOfWeek_ShortName]       VARCHAR(3),
  [Week_Number]               TINYINT,
  [Fiscal_DayOfMonth]         TINYINT,
  [Fiscal_Month_Number]       TINYINT,
  [Fiscal_Month_Name]         VARCHAR(12),
  [Fiscal_Month_ShortName]    VARCHAR(3),
  [Fiscal_Quarter]            TINYINT,
  [Fiscal_Year]               SMALLINT,
  [Calendar_DayOfMonth]       TINYINT,
  [Calendar_Month Number]     TINYINT,
  [Calendar_Month_Name]       VARCHAR(9),
  [Calendar_Month_ShortName]  VARCHAR(3),
  [Calendar_Quarter]          TINYINT,
  [Calendar_Year]             SMALLINT,
  [IsLeapYear]                BIT,
  [IsWeekDay]                 BIT,
  [IsWeekend]                 BIT,
  [IsWorkday]                 BIT,
  [IsHoliday]                 BIT,
  [HolidayName]               VARCHAR(255)
);
-- add indexes, constraints, etc.

With the table in place, you can perform a single, set-based insert of as many years of data as you want from whatever start date you choose. Just specify the start date and the number of years. I use a "stacked CTE" technique to avoid redundancy and only perform a whole slew of calculations once; the output columns from the earlier CTEs are then subsequently used in further calculations later on.

-- these are important:
SET LANGUAGE US_ENGLISH;
SET DATEFIRST 7;

DECLARE @start DATE = '20100101', @years TINYINT = 20;

;WITH src AS
(
  -- you don't need a function for this...
  SELECT TOP (DATEDIFF(DAY, @start, DATEADD(YEAR, @years, @start)))
    d = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY s1.number)-1, @start)
   FROM master.dbo.spt_values AS s1
   CROSS JOIN master.dbo.spt_values AS s2
   -- your own numbers table works much better here, but this'll do
),
w AS
(
  SELECT d,
    wd      = DATEPART(WEEKDAY,d),
    wdname  = DATENAME(WEEKDAY,d),
    wnum    = DATEPART(ISO_WEEK,d),
    qnum    = DATEPART(QUARTER, d),
    y       = YEAR(d),
    m       = MONTH(d),
    mname   = DATENAME(MONTH,d),
    md      = DAY(d)
  FROM src
),
q AS
(
  SELECT *,
    wdsname   = LEFT(wdname,3),
    msname    = LEFT(mname,3),
    IsWeekday = CASE WHEN wd IN (1,7) THEN 0 ELSE 1 END,
    fq1 = DATEADD(DAY,25,DATEADD(MONTH,2,DATEADD(YEAR,YEAR(d)-1900,0)))
  FROM w
),
q1 AS
(
  SELECT *,
    -- useless, just inverse of IsWeekday, but okay:
    IsWeekend = CASE WHEN IsWeekday = 1 THEN 0 ELSE 1 END,
    fq = COALESCE(NULLIF(DATEDIFF(QUARTER,DATEADD(DAY,6,fq1),d)
         + CASE WHEN md >= 26 AND m%3 = 0 THEN 2 ELSE 1 END,0),4)
    FROM q
)
--INSERT dbo.DimWithDateAllPersisted(Date)
SELECT
  DateKey = d,
  DayOfWeek_Number = wd,
  DayOfWeek_Name = wdname,
  DayOfWeek_ShortName = wdsname,
  Week_Number = wnum,
  -- I'll update these four lines when I have usable info
  Fiscal_DayOfMonth      = 0,--'?magic?',
  Fiscal_Month_Number    = 0,--'?magic?',
  Fiscal_Month_Name      = 0,--'?magic?',
  Fiscal_Month_ShortName = 0,--'?magic?',
  Fiscal_Quarter = fq,
  Fiscal_Year = CASE WHEN fq = 4 AND m < 3 THEN y-1 ELSE y END,
  Calendar_DayOfMonth = md,
  Calendar_Month_Number = m,
  Calendar_Month_Name = mname,
  Calendar_Month_ShortName = msname,
  Calendar_Quarter = qnum,
  Calendar_Year = y,
  IsLeapYear = CASE
    WHEN (y%4 = 0 AND y%100 != 0) OR (y%400 = 0) THEN 1 ELSE 0 END,
  IsWeekday,
  IsWeekend,
  IsWorkday = CASE WHEN IsWeekday = 1 THEN 1 ELSE 0 END,
  IsHoliday = 0,
  HolidayName = ''
FROM q1;

Now, you still have these "holiday" and "workday" columns left to deal with - this gets a little more cumbersome, but you need to update those three columns with any holidays that appear in your date range. Things like Christmas Day are really easy:

UPDATE dbo.DateDimension
  SET IsWorkday = 0, IsHoliday = 1, HolidayName = 'Christmas'
  WHERE Calendar_Month_Number = 12 AND Calendar_DayOfMonth = 25;

Things like Easter get a lot trickier - I have blogged some ideas here many years ago.

And of course your company non-workdays that have absolutely nothing to do with public holidays etc. must be updated directly by you - SQL Server isn't going to have some built-in way to know your company's calendar.

Now, I purposely stayed away from computing any of these columns, because you said something like the end users have previously preferred fields they can drag and drop - I'm not sure if the end users really know or care if the source of a column is a real column, a computed column, or comes from a view, query or function...

Assuming you do want to look into computing some of these columns to ease on your maintenance (and persist them to pay storage for query speed), you can look into that. However, just as a warning, some of these columns can't be defined as computed and persisted because they are non-deterministic. Here's one example, and how to get around it.

CREATE TABLE dbo.Test
(
  [date] DATE PRIMARY KEY,
  DayOfWeek_Number AS DATEPART(WEEKDAY, [date]) PERSISTED
);

Results:

  Msg 4936, Level 16, State 1, Line 130
  Computed column 'DayOfWeek_Number' in table 'Test' cannot be persisted because the column is non-deterministic.

The reason this can't be persisted is because many date-related functions rely on the session settings of the user, like DATEFIRST. SQL Server can't persist the above column because DATEPART(WEEKDAY should give different results - given the same data - for two different users who happen to have different DATEFIRST settings.

Then you might get clever, and say, well, I can set it to be the number of days, modulo 7, offset from some day I know to be a Saturday (say, '2000-01-01'). So you try:

CREATE TABLE dbo.Test
(
  [date] DATE PRIMARY KEY,
  DayOfWeek_Number AS
    COALESCE(NULLIF(DATEDIFF(DAY,'20000101',[date])%7,0),7) PERSISTED
);

But, same error.

Instead of using an implicit convert from a string literal that represents a date time in an unambiguous (to us, but not SQL Server) format, we can use the number of days between the "zero date" (1900-01-01) and that date we know is a Saturday (2000-01-01). If we use an integer here to represent the difference in days, SQL Server can't complain, because there is no way to misinterpret that number. So this works:

-- SELECT DATEDIFF(DAY, 0, '20000101');  -- 36524

CREATE TABLE dbo.Test
(
  [date] DATE PRIMARY KEY,
  DayOfWeek_Number AS
    COALESCE(NULLIF(DATEDIFF(DAY,36524,[date])%7,0),7) PERSISTED
    -----------------------------^^^^^  only change
);

Success!

If you're interested in pursuing computed columns for some of these calculations, let me know.

Oh, and one last thing: I don't know why you would ever scrub down this table and re-populate it from scratch. How many of these things are going to change? Are you going to alter your fiscal year constantly? Change how you want to spell March? Set your week to start on Monday one week and Thursday the next? This really should be a build-it-up-once table, and then you make minor tweaks (like updating individual rows with new/changed holiday information).


"ORA-01017: invalid username/password" while connecting to an instance remotely as sysdba

Seems simply a password issue. Different instances may have different passwords, the SYS password is not necessarily the same for all the instances on the same server. Make sure remote_login_passwordfile is configured properly in the instances, and the password file exists for instance SID1 on the server in ORACLE_HOME/dbs/orapwSID1 (Linux-Unix) or ORACLE_HOME/database/pwdSID1.ora (Windows).

If you do not know the correct SYS password, you can recreate the password file, for example:

orapwd file=$ORACLE_HOME/dbs/orapwsid1 password=xxxx force=y


Only allow access to a table via a function, not directly via queries

You need to create the function as a user that has (select) access to the underlying table. Then define the function with the option security defined which means when the function is called, it operates with the privileges of the function owner, not the caller:

create function list_t() returns setof t
  security definer
as $$
    select * from t;
$$ language sql stable;

Another way of doing this is to create a view, then grant SELECT privileges to the view (not the table):

create view v_t
as
select *
from t;

grant select on v_t to my_user;

Note that a set returning function should be used in the FROM clause like a table:

select *
from list_t();


SQL Server: Recursive dynamic SQL puzzle

I am curious to know how the column names getting concatenated.

Can't really answer that. Some internal workings of SQL Server creates a result like that, sometimes.

The technique has been around for a considerable amount of time. Microsoft does not support the functionality and advises not to use it.

From SET @local_variable (Transact-SQL)

  Do not use a variable in a SELECT statement to concatenate values
  (that is, to compute aggregate values). Unexpected query results may
  occur. This is because all expressions in the SELECT list (including
  assignments) are not guaranteed to be executed exactly once for each
  output row.

Instead you can use a cursor or the for xml trick to do the same.

declare @a as varchar(4000);
set @a = 'xxx ';

set @a = @a + (
              select quotename(COLUMN_NAME) as '*'
              from INFORMATION_SCHEMA.COLUMNS
              for xml path(''), type
              ).value('text()[1]', 'varchar(4000)');

Result: xxx [city][id][name]


Can I use SPARSE somehow on a non-nullable bit column with mostly false values?

A filtered index (WHERE IsMostRecentRun = 1) sounds like a better idea to me than using sparse. If you can make it so that false is instead represented by null, you may be able to do both, but while that will potentially save some space in the base table, I suspect the bigger gain would be in query performance from the filtered index - as long as it's covering. If you need too many columns to cover and/or need to also filter or join on other columns, then you may find a balancing act between the improvements you get from the seek or range scan on the filtered index and the costs of things like lookups to get at the rest of the columns.

That all said, it seems suspicious you would need an additional column to serve as a flag for this. Isn't this information redundant? Can't it be determined by other data? In which case I would focus on index tuning to optimize the use of the existing columns instead of storing and maintaining redundant data.

(Also I don't think Pinal is correct.)


The database owner account has been deleted. What impact?

Just change ownership for starters.

Database ownership (the owner_sid in sys.databases) not have really have much impact on day to day operations


Automate the patching of SQL Severs on Windows

While the rotating passwords are a hindrance, they don't negate any approaches available.  Kendra Little does a great job of explaining a general approach to patching servers in an automated fashion in this blog post, as well as some of the risks about when server patching goes bad.
Because your servers are not production, these would fall under her "cattle" classification which means that if you can tap into an existing automated toolset such as SCCM/WSUS (as denoted by Alen prior), you should do so.  Patches that don't go through may be annoying, but they hopefully won't cause outages that wake you in the night.  The rotating password complexity may require you customize this to a degree, and really the best approach there is to automate the process as best you can using PowerShell scripts.
Adam Bertram probably has the most robust toolset out there now that I'm aware of in this domain.  I would suggest you check it out and see if you can augment it to your needs.  Here's a direct link to his GitHub repository for the same toolset.


Send email when some users connect to SQL Server

Wouldn't it be better to store logins in a table and then provide a report for that table? Or perhaps, email a report that shows a list of logins for that day.

/* create test login */
CREATE LOGIN login_test WITH PASSWORD = '3KHJ6dhx(0xVYsdf'
GO
GRANT VIEW SERVER STATE TO login_test;
GO

USE AUDIT
GO

/* Creating audit table*/
CREATE TABLE audit.dbo.LogonAuditing
(
    SessionId int,
    LogonTime datetime,
    HostName varchar(50),
    ProgramName varchar(500),
    LoginName varchar(50),
    ClientHost varchar(50)
)

GO

/* Create Login Audit Trigger */
CREATE TRIGGER connection_audit_trigger
ON ALL SERVER
FOR LOGON
AS
BEGIN

       DECLARE @LogonTriggerData xml,
               @EventTime datetime,
               @LoginName varchar(50),
               @ClientHost varchar(50),
               @LoginType varchar(50),
               @HostName varchar(50),
               @AppName varchar(500)

       SET @LogonTriggerData = eventdata()

       SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
       SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
       SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
       SET @HostName = HOST_NAME()
       SET @AppName = APP_NAME()--,program_name()

    INSERT INTO audit.dbo.LogonAuditing
     (
        SessionId,
        LogonTime,
        HostName,
        ProgramName,
        LoginName,
        ClientHost
    )
    SELECT
        @@spid,
        @EventTime,
        @HostName,
        @AppName,
        @LoginName,
        @ClientHost

END;

/* in case you want to drop the trigger
drop trigger connection_audit_trigger on all server
    go
*/


Can't get update one prompt for same variable over multiple tables

3654   Corresponding select-list expressions are incompatible.

  Explanation:

  If a query contains a UNION, MINUS, or INTERSECT operator, then the
  data types for all select-list expressions must be compatible with the
  corresponding expression in the select-list for the first SELECT in
  the query. All numeric data types are considered compatible. Character
  data types are not compatible with numeric data types. Byte data types
  are only compatible with byte data types. Graphic data types are only
  compatible with graphic data types.

The datatypes of the columns in TABLE1 and TABLE2 do not match.


Change left outer join on SAP Universe Design Tools

The OUTER clause is optional, you'll find a good answer in this SO question:

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

CREATE TABLE T1 (id int, name varchar(10));
CREATE TABLE T2 (id int, name varchar(10));
INSERT INTO T1 VALUES (1, 'OneT1'),(2, 'TwoT1'),(3, 'ThreeT1');
INSERT INTO T2 VALUES (2, 'TwoT2'),(5, 'FiveT2'),(6, 'SixT2');

SELECT    *
FROM      T1
LEFT JOIN T2
ON        T1.id = T2.id;

SELECT     *
FROM       T1
LEFT OUTER JOIN T2
ON         T1.id = T2.id;
GO

id | name    |   id | name
-: | :------ | ---: | :----
 1 | OneT1   | null | null
 2 | TwoT1   |    2 | TwoT2
 3 | ThreeT1 | null | null

id | name    |   id | name
-: | :------ | ---: | :----
 1 | OneT1   | null | null
 2 | TwoT1   |    2 | TwoT2
 3 | ThreeT1 | null | null

dbfiddle here


Can I default to T-SQL only view in VS 2012?

I haven't found an option to turn off the design portion of the split view. Nor have I found a way to have the order of the windows remembered after closing a window, which is annoying!

However, if you want to ditch the SSDT editor completely you can change the default for .sql files in Tools/Options/Text Editor/File Extension, as shown below:


How can I batch a drop column operation?

I think the following thread might prove useful to you: MySQL Drop Column From Large Table

The suggestion is to copy the data to a new table MINUS the column you wish to lose, then drop the old table and rename the new one.  This is if you can't secure a maintenance window, say overnight/off-production hours, to do the work instead.

From my understanding - and others, please correct me if I'm wrong - although you could clear/NULL the data in this column in batches, you couldn't drop the column in batches since a column drop implies rewriting the row without that column - and you can't have a row in a table that doesn't have an attribute column that the other rows have.


Got the answer. Below command had to be executed before executing update.

add trandata gg01.*

Thanks!


Restoring MySQL 8.0 server from cold backup files

Looks like you're reading the wrong page. Restoring from a file system ("raw") backup doesn't require you to "set up identical table structures"; simply copy all MySQL files from the backup to their original location, then start MySQL.


How to add a server list into Registered Servers on Microsoft SQL Server Management Studio (SSMS)

Phil Factor wrote a great article at the Simple-talk website that includes this and much other useful information
https://www.red-gate.com/simple-talk/sql/sql-tools/registered-servers-and-central-management-server-stores/


What is this view being used in my query?

Views starting with VW_DTP showing up in the execution plan is the result of one of the cost-based query transformations available since 11.2, called distinct placement.

It happens with statements using the DISTINCT clause, just like yours. It is an absolutely acceptable an quite obvious execution plan to join the tables and filter the rows, and after that, find distinct rows. However, finding distinct rows at the time of accessing the tables, before joining them, may be more effective, and the database may choose to transform the query based on the estimated cost.


In your notes you say:

  4NF describes multivalued dependencies, wherein a key value has multiple attributes dependent upon it, but those attribute have, at best, only a trivial relationship to each other.

This is not the definition of 4NF, since multivalued dependencies are not present when “a key value has multiple attributes dependent upon it”, but when those attributes have multiple values depending from some other attribute.

A typical example is when you have an employee which has the attribute child with multiple values and, in the same relation, an attribute skill which has multiple values, so that you have two  Multivalued Functional Dependency (MVD), Employee →→ Child, Employee →→ Skill. Instead, in your relation you have only one MVD, Title, Developer, Publisher, ReleaseDate, Price, ESRB →→ Platform, and for this reason the MVD is called a trivial MVD.

So, the redundancy in your example is not due to the fact that the relation is not in 4NF, rather that it is not in BCNF, neither in 3NF. Assuming that there is the functional dependency Title → Developer, Publisher, ReleaseDate, Price, ESRB, your relation in neither in BCNF, nor in 3NF. So, a decomposition of your schema in 3NF would be:

R1(Title, Developer, Publisher, ReleaseDate, Price, ESRB)
R2(Title, Platform)

where the first relation has key Title and the only Functional Dependency Title → Developer, Publisher, ReleaseDate, Price, ESRB, and the second one has key Title, Platform.


Restore master database from SQL Server 2008 R2 on SQL Server 2012

You should never restore a system database from from one version of SQL Server to another.  The master database contains tables and other objects that support the inner workings of the server itself.  I wouldn't even restore system databases to another server of the same version.  I would instead determine what the information was I was trying to capture (user accounts, scheduled jobs, SSIS packages etc.) and have those extracted and imported to the new server.


How to see restore history of a deleted database?

Depending on how the database was dropped, your luck will vary considerably.

If the database was dropped simply by using the DROP DATABASE command, then the restore history will be available to you.

However, if the database was dropped via the SSMS GUI after the restore, then you may be out of luck. In SSMS, the default is to select the option for "Delete backup and restore history information for databases". When this option is selected, the backup & restore history for the dropped database(s) will be removed from msdb:

However, if you either uncheck that box, or do the drop from T-SQL, then your backup & restore history will be available.

CREATE DATABASE Floop;
BACKUP DATABASE Floop TO DISK = 'C:\temp\Floop.bak' WITH INIT;
RESTORE DATABASE Floop FROM DISK = 'C:\temp\Floop.bak' WITH REPLACE;
DROP DATABASE Floop;

To see the restore history, simply query msdb.dbo.restorehistory:

SELECT *
FROM msdb.dbo.restorehistory
WHERE destination_database_name = 'Floop';

The details of the restorehistory table are available in the docs. Essentially, it contains the database that was restored, information about who restored it, the type of restore, and the restore point.

If you want information about the backup that was restored, you can join to other backup tables. When a database from a different instance is restore, SQL Server copies the backup information from the backup header in the file into the backup tables.

SELECT
        RestoreDate = rh.restore_date,
        RestoredDb  = rh.destination_database_name,
        RestoreUser = rh.[user_name],
        RestoreType = rh.restore_type,
        ReplaceDb   = rh.[replace],
        RecoverDb   = rh.[recovery],
        BackupSourceServer  = bh.machine_name,
        BackupSourceDb      = bh.database_name,
        BackupFinishDate    = bh.backup_finish_date,
        BackupFile          = bmf.physical_device_name
FROM msdb.dbo.restorehistory    AS rh
JOIN msdb.dbo.backupset         AS bh  ON bh.backup_set_id = rh.backup_set_id
JOIN msdb.dbo.backupmediafamily AS bmf ON bmf.media_set_id = bh.media_set_id
WHERE rh.destination_database_name = 'Floop';


These errors mean you had a connection timeout.  The subsequent messages are a result of that.   In a multi-subnet environment, each AG will have an IP address in each subnet and be registered in DNS.  The DNS server returns multiple IP addresses when queried. Only one will be online. SQL tries all the ip address, serially, until connection is established.  The TCP timeout is 21 seconds.  If the first ip is not online, it will wait 21 seconds before it tries the next one. The default connection timeout period for .NET client libraries is 15 seconds.  You can see where that could be a problem. You need to read this article http://blogs.msdn.com/b/alwaysonpro/archive/2014/06/03/connection-timeouts-in-multi-subnet-availability-group.aspx

If you don't have a multi-subnet environment, then I would look at the network logs again and see how long it was taking for your tcp connections.


Well I have some ideas which could cause this behavior.

Do you monitor your memory pressure? Maybe your queries raise a certain limit which will cause the flush of the plan cache. I don't know your application, but does this correspondent with your logs from your frontend servers? Is there pressure too during this time?
Do you have a dedicated SQL Server or does the server share it's hardware with other processes/services? If not, try to consider to outsource your SQL Server to a dedicated machine instead. This will reduce side effects from other services.
You may want to use optimize for ad hoc workloads, which will just save a plan stub and compile it if it's needed. This will reduce the load of your plancache, which will lower the chance of an plancache flushing. You can enable it using sp_configure 'optimize for ad hoc workloads',1; reconfigure. This can be done if you have enabled the advanced options using sp_configure 'show advanced options',1; reconfigure.
Another idea can be backups. Just simple backups. If they are aggressively, it may occur that your machine gets under pressure too. The time your mentioning just sounds like a good timespan for planning a backup.
Maybe it's quite simple a bug in your maintenance script. Have you checked if there is a logical issue which causes your script to rebuild all indices instead of just those who matches the criteria. This maybe can cause it too.

Just beside all of this possibilities, it may be useful to check the log files for some changes to the options affinity mask, affinity I/O mask and their x64 partners. Another thing can be a change of the MAXDOP option of your instance. Please check the logs for them too. They will need to flush the plancache too.

Last but not least, you can still run a serverside trace (just setting it up using the profiler, start it, stop it and using sql command to start it again on the serverside). Beside that perfmon is your friend. It can watch and monitor your performance values for a time. Maybe you can see parallels in the pressure with certain actions on your server which may cause those flush.

Hopefully this will help you, even if the answer come a bit later.


Postgres server is not responding to a nodejs request

The problem is that I have Nodejs 12 in my Mac, but I have NodeJS 14 in my Windows, so it's a compatibility problem.

Since I updated my postgres (pg) to 8.0.3 or superior (i.e. npm install pg), now it works!

One guy helped me to realise that issue in https://stackoverflow.com/questions/61611039/postgres-server-is-not-responding-to-a-nodejs-request?noredirect=1#comment108987882_61611039.


How to disable annoying MySql update console

I may be running a more recent version of MySQL, but what I had to do to disable this was:

Run the MySQL Installer (located at C:\Program Files (x86)\MySQL\MySQL Installer for Windows)
Click the wrench icon
Un-check the box to check for updates


What does N'[Uncategorized (Local)] mean?

The @name parameter is a parameter of msdb.dbo.sp_add_category and not EXEC.

See the sp_add_category documentation:

  [ @name = ] 'name'

  The name of the category to be added. The name must be unique within the specified class. name is sysname, with no default.

[Uncategorized (Local)] just indicates a default category when you didn't specify a category on the SQL Agent Job. The leading N just indicates the string is nvarchar and not varchar.

Please read through Organize Jobs to learn more about job categories

The code you are looking at is most likely just a scripted out job. For example when I right-click a random job on the SQL Server I'm looking at and script job as > create > new query editor window this is the result:

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Warmup_SSRS_EP',
        @enabled=1,
        @notify_level_eventlog=0,
        @notify_level_email=3,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'Warmup_SSRS_EP',
        @category_name=N'[Uncategorized (Local)]',
        @owner_login_name=N'domain\user',
        @notify_email_operator_name=N'monitor operator', @job_id = @jobId OUTPUT

Where the creation of the category is just included in the script to make sure it exists because otherwise the following procedures would error out.


The "best" index for this query seems to be (cat_id, visible, primary, updated). Thanks to jkavalik


Spatial Index ignored when using additional non-spatial conditions

It would appear bitwise not in p1.isMale = ~p2.isMale causes the optimiser to pick wrong.
Try p1.isMale <> p2.isMale instead


Find the violated index on ORA-00001 (dup_val_on_index)

AskTom has already discussed this.

This could be achieved in following manner:

BEGIN
   -- code containing INSERT operation
EXCEPTION
   WHEN dup_val_on_index THEN
      Trace_SYS.Message(substr(sqlerrm, instr(sqlerrm, '(')+1,
                                        instr( sqlerrm, ')') - instr( sqlerrm,'(') -1 ));
END;


Find tables with multiple indexes on same column

This query retrieves a complete list of indexes build on the same (list of) table column(s in the same order):
SELECT indrelid::regclass::text AS table
     , idx_columns
     , indexrelid::regclass::text AS index
FROM   pg_index i
     , LATERAL (
   SELECT string_agg(attname, ', ') AS idx_columns
   FROM   pg_attribute
   WHERE  attrelid = i.indrelid
   AND    attnum  = ANY(i.indkey)  -- 0 excluded by: indexprs IS NULL
   ) a
WHERE  EXISTS (
   SELECT FROM pg_index
   WHERE  indrelid = i.indrelid
   AND    indkey = i.indkey
   AND    indexrelid <> i.indexrelid  -- exclude self
   )
AND    indexprs IS NULL -- exclude expression indexes
ORDER  BY 1, 2, 3;

pg_index.indkey is data type int2vector and contains, quoting the manual:

an array of indnatts values that indicate which table columns this
index indexes. For example a value of 1 3 would mean that the first
and the third table columns make up the index key.

So the match on this column asserts the same columns in the same order.
Sort order, operator class, collation or any other decorators can differ.
Constraints:

Multicolumn indexes only qualify if all index columns match in the same order.
Indexes including any expressions other than plain table columns are excluded.

Aside: index ialtreproc3 in your example is dead freight, you should remove it. Postgres can scan indexes in both directions with practically the same speed. Exception: multicolumn indexes with mixed sort order. See:

Optimizing queries on a range of timestamps (two columns)

While the only difference is sort order, it makes no sense to maintain multiple btree indexes on the same single column.


Is it safe or possible to change SQL Server AutoGrowth when in a SQL Server AlwaysOn Cluster

You have to have AutoGrow enabled and do it on the primary replica. You can also do it in t-sql using the ALTER DATABASE command.


Identical query slower when searching for `T` rather than `F`

As already stated in the responses below your request it is most certainly due to the unbalanced nature of F and T causing SQL Server to prefer scan over seek.
Though this is quite some simplification as you are querying a view containing quite a lot of tables and WHERE as well as JOIN conditions.

In case you query "[...] and MOVIsResolved = 'F'" the SQL Optimizer most likely prefers a SCAN over a SEEK, as the given result is that large.
with "[...] ='T'" it prefers a SEEK as the result set is small enough.
And finally with "[...] <>'F'" the optimizer has no other choice but to SCAN and check each entry, whether the column contains not 'F'. In the end for you it is quite clear "<> F" equals "= T" as you know there can only be these 2. SQL Server does not know this.
Therefore the resulting execution plans for "<> 'F'" and "= 'F'" are the same.

As an alternative you might wanna look into OPTION( OPTIMIZE FOR UNKNOWN ), thought I do not know, whether this actually works with hard coded queries as yours.


Join tables based on variable value contained in column

With the way you've written that join condition, SQL server will search for exactly "table1.audience", not the column value. Column names within single quotes are just treated as string literals, not the underlying column value.

Try this instead:

SELECT *
FROM table1
LEFT JOIN table2
ON ((table2.Channel like '%' + table1.audience + '%')
AND (table2.date = table1.date))

This will concatenate the value contained within table1.audience and wrap it in wildcards.

I'd recommend dropping the leading wildcard if it's not necessary, as this will significantly hinder performance if you're dealing with a large data set (as you will force a scan).


SSIS hangs during For Each File enumeration

This appears to be an issue with the Visual Studio / BIDS client hitting memory boundaries, not with SSIS itself. While the package is running in debug mode in BIDS, the devenv.exe process can be observed to continually occupy additional memory. While the number of files processed before hanging is inconsistent, the package did, on one occasion, process nearly 9000 files before Visual Studio threw an out of memory exception.

Running the package with dtexec or through SQL Agent appears to resolve the issue.


PostgreSQL (big data): search by tags and order by timestamp

There are two approaches:

create an index on the array:

CREATE INDEX ON items USING gin (tags);

That allows the database to quickly find the matching rows, but then it has to perform a top-n sort.
create a B-tree index on created_at:

CREATE INDEX ON items (created_at);

That will allow the database to avoid the sort, but it has to scan and discard the rows that don't match the condition.

Unfortunately, the two strategies are mutually exclusive, and which is best depends on the data. You'll have to experiment.


What privileges are needed to run EXPLAIN PLAN FOR CREATE INDEX?

The problem is most likely in you not having sufficient privileges to create the index in question -- nothing to do with EXPLAIN PLAN itself.

To quote the manual, "To create an index in another schema, you must have the CREATE ANY INDEX system privilege".


Update field with new string made from characters in existing value

Assuming that meta_value is always dd/mm/yyyy and you are looking for yyyymmdd (which isn't clear), try this:

UPDATE wp_postmeta
SET meta_value = CONCAT(SUBSTRING(meta_value FROM 7),
     SUBSTRING(meta_value, 4, 2),
     SUBSTRING(meta_value, 1, 2));

Run that as a SELECT first to verifying the output before update and make sure it's what you're really looking for.


Oracle DB unix/linux autostart - oracle user no login shell

The init.d script has to be modified by adding "-s /bin/bash" to the start and stop case. This should allow the oracle user to be login without the password.

#!/bin/bash
# chkconfig: 35 99 10
# description: Starts and stops Oracle processes

ORA_HOME=/opt/oracle/product/12cR1/db
ORA_OWNER=oracle

PATH=${PATH}:$ORACLE_HOME/bin
HOST=`hostname`
PLATFORM=`uname`
export ORACLE_HOME PATH
case $1 in
'start')
        su - $ORA_OWNER -s /bin/bash -c "$ORA_HOME/bin/lsnrctl start"
        su - $ORA_OWNER -s /bin/bash -c "$ORA_HOME/bin/dbstart"
        ;;
'stop')
        su - $ORA_OWNER -s /bin/bash -c "$ORA_HOME/bin/lsnrctl stop"
        su - $ORA_OWNER -s /bin/bash -c "$ORA_HOME/bin/dbshut"
        ;;
*)
        echo "usage: $0 {start|stop}"
        exit 1
esac
#
exit


OCIEnvCreate failed to create environment handle after installing oracle_fdw

If you install from an rpm then it's installed as a service.

If you start postgresql from a custom script as user postgres then the process will catch the environment variables but if you start postgresql as a service it won't.

You should look for how to pass environment variables to systemd (or sysv) services.

I used this example for systemd services. CentOS 7 uses systemd.

In /etc/systemd/system create postgresql.service.d directory and place inside a file orafdw.conf with your environment variables and restart (not sure about only reload). Make sure the directory name match your postgresql service name.

Something like...

[Service]
Environment=ORACLE_HOME=/path/to/home
Environment=TNS_ADMIN=/path/to/tnsadmin
...
(All the variables you want)

Regards,

Walter


Postgres revoke database access from user

The right to connect is by default granted to PUBLIC.  You would have to revoke it from PUBLIC (and then grant it individually to other users who need it) to accomplish what you want.

Or use pg_hba.conf to deny the connection.


T SQL Table Valued Function to Split varbinary(max) into 16byte GUIDs

This is easy if you have a numbers table. The following example is cut and pasted from Erland Sommarskog's site:

CREATE FUNCTION fixbinary_single(@str varbinary(MAX))
RETURNS TABLE AS
RETURN(SELECT listpos = n.Number,
              n = convert(int, substring(@str, 4 * (n.Number - 1) + 1, 4))
       FROM   Numbers n
       WHERE  n.Number <= datalength(@str) / 4 )


xp_cmdshell only sends first character of string

The problem is with your variable declaration varchar.

The command_string is   varchar(8000) or nvarchar(4000)

so define your variable along with proper length  to avoid string truncation

  DECLARE
      @inspection varchar

should be

  DECLARE
      @inspection varchar(8000)

couple of things as well (since I dont know Python).

Make sure you print or select @inspection and see that you can run the produced command successfully.
Use a variable for success or failure and raise an error message when the command fails.

e.g.

declare @result int
DECLARE @inspection varchar(8000)
exec @result = master..xp_cmdshell @inspection
if @result <> 0
begin
    print 'Python script errored out !!'
    RAISERROR ('Python script errored out', 16, 1)
end


Running SSIS package from SQL Agent job owned by a non-sysadmin domain user

The problem looks more complex than it is. Since you are using SQL 2014 you are probably being bitten by the new security features introduced in 2012.

The only thing that actually matters is:

Server: {server name}, Package path: \SSISDB\Foo\Bar\foobar.dtsx, Environment reference Id: NULL.
Description: Login failed for user '{domain user that runs SSIS ETL overnight}'.

Your Proxy user's login most likely doesn't have access to the SSISDB catalog (even though he might have access to SQL Server).
You need to map the login to an SSISDB user and configure access to the SSISDB folders/projects in Integration Services.

Please have a look at this MSDN blog post SSIS Catalog Access Control Tips and SQL 2012 SSIS Catalog Permissions

Once you have the package actually loading you might run into other security context issues but you should get better logging from integration services itself.


SSMS - Is it possible to pass additional connection parameters via Command Line?

Unfortunately not. If you type ssms.exe -? or look at the documentation you'll see the list of possible options:

  Ssms
      [scriptfile] [projectfile] [solutionfile]
      [-S servername] [-d databasename] [-U username] [-P password]
      [-E] [-nosplash] [-log [filename]?] [-?]

So there is no option to specify a connection string or additional connection properties.


How to properly increment an int identity in SQL Server if the Identity Increment isn't set?

The best option is to use the SEQUENCE object, introduced in 2012. Since it is an independent object, you don't run the risk of querying it at the same time and retrieving the same value - it'll always provide the next in the chain.

Set the object with a specific start and increment value, then call it to get the next value desired. One of the biggest benefits here is that you can also specify a number of values to be cached for faster retrieval if you're working with rapid inserts.

CREATE SEQUENCE seq_obj
    START WITH 1050
    INCREMENT BY 1
    CACHE 1000
GO

Then to get the next ID value, use:

SELECT NEXT VALUE FOR seq_obj

Which will provide 1050, then 1051, and so on and so forth.

MS Docs for full information on other properties.


create mongodb user to only read in replica set (not in master)

User authorizations are same automatically at primary and secondary nodes, so answer is no.


How to fix SQL Server Management Studio 2014 IntelliSense when intermittent

If possible, I'd suggest trying SSMS 2016 instead. It's still supported to use it against SQL Server 2014 (in fact, it's supported to use it against instances as old as SQL Server 2008). With 2016, SSMS is now free, and the installer for it is separate from the installer for the SQL Server server components (Database Engine, Integration Services, etc.). Plus, Microsoft is now releasing updates and improvements for it on a roughly monthly basis.

You can download SSMS 2016 from https://msdn.microsoft.com/en-us/library/mt238290.aspx.

I've personally found IntelliSense to work more reliably with SSMS 2016 than what I was used to with 2014, especially when working over slower or less reliable network connections.


How to check if staff member has end date with a Null value

SELECT employee_no
FROM source_table
GROUP BY employee_no
HAVING COUNT(*) = COUNT(finish)

Is there a way to include the whole table in that sequence? – Chris
select serverproperty('ProductVersion') as [version], serverproperty('Edition') as [edition] and got Version 13.0.5102.14 of edition Devleoper Edition (64-bit) – Chris
This is (Microsoft) SQL server 2016 SP2. – Akina

WITH cte AS ( SELECT Employee_No,
                     Task,
                     Start,
                     Finish,
                     MAX(CASE WHEN Finish IS NULL
                              THEN 1
                              ELSE 0
                              END) OVER (PARTITION BY employee_no) have_nulls
              FROM source_table
            )
SELECT Employee_No,
       Task,
       Start,
       Finish
FROM cte
WHERE have_nulls = 0
/* ORDER BY 1,2,3 */


postgresql 9.1 failed insert occupies increasing disk space

Error on inserting data due to FK constraint violation will leave dead tuple on the target table. That's the nature of Postgres. This dead tuple of course will occupy disk space. Things to prevent this filling up disk space :

A well tuned autovacuum can handle this wasted space.
Do a manual FK constraint check (on application side perhaps) prior to inserting data to the target tables.


How many deadlocks am i really getting? system health or deadlocks/sec?

From a reliability standpoint of deadlocks per second the perfmon counter will be more accurate.  The system_health monitor is based on ring buffers which can overflow over time or miss events if there is enough activity within a brief period in order to facilitate reliability.  There is a file that backs this which you can look at which will give you more history but if you are experiencing as many deadlocks as you are indicating there is a distinct possibility that you may not have full history within these.  This would explain your discrepancy between the two measurements, you aren't doing anything wrong from what I can see.

The files will be located in your SQL Server install directory under %Program Files%\Microsoft SQL Server\MSSQL11.[Instance Name\MSSQLSERVER]\MSSQL\Log\system_health_*.xel.


Three-sided many-to-many relationship design

I think the first solution is good when you know that some instances WOULD NOT participate in relation.
But what about second solution? I think it's good when you're sure that all of the instances WOULD participate in relation.
The first thing you can do, is analyzing your Problem.


How to simulate postgresql recovery mode?

If you want to force PostgreSQL into recovery, you can create a recovery.conf with no restore_command or streaming replication configuration set. Leave hot_standby set to off in postgresql.conf.

Personally I recommend taking a base backup (pg_basebackup -X stream) and using that, rather than doing it to the original DB.


I query a table "Select count(*) from sf_guard_user_group"

That's not true, according to the screenshot. I don't see the FROM clause there.


Numa Node configuration for SQL server 2016

When viewed via task manger/ssms it shows 2 Numa nodes. Is my above understanding wrong of 8 Numa nodes?

No, SQL Server 2016 started to ship with automatic numa node partitioning (soft numa) which is what you're seeing here.

  If those are 2 Numa nodes will i have any performance impact compared to current setup?

Impossible to say. Automatic Soft-Numa was added as a default to the product starting with 2016 as certain tasks and threads are on a per-numa basis. Thus, in many environments that had high core count and low numa node count there would be an overall performance improvement by using soft-numa.

Your mileage may and will vary. If you're unsure or don't want to test the new configuration, I would suggest you turn the automatic soft-numa off for now until you can test. This is based on absolutely no data but configuration stabilization between upgrades.

If you want to turn soft-numa off, you can run the following command:

ALTER SERVER CONFIGURATION SET SOFTNUMA OFF


Is there a way to have SQL Server format a number into ordinal form?

No, there is not any built in function that will do this. This is something that should be handled in the presentation layer and not in the data coming from the database.

EDIT: based on the presence of a "crummy presentation layer", IF this would have to be done in the database I suggest creating a UDF to convert the values to ordinal or words. IT definitely would not be pretty.

From a comment: For the second part of the question, Daniel used the script found here:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=86067


How to insert/combine values from multiple tables?

I wrote this for SQL 2012, but it should work in 2008R2 and up.

DECLARE @January AS TABLE
    (
      Code CHAR(4) NOT NULL
    , test_name VARCHAR(100) NOT NULL
    , value INT NOT NULL
    );

INSERT  INTO @January
        ( Code, test_name, value )
VALUES  ( '0001', 'name1', 17 )
    ,   ( '0002', 'name2', 45 )
    ,   ( '0005', 'name5', 12 );

DECLARE @February AS TABLE
    (
      Code CHAR(4) NOT NULL
    , test_name VARCHAR(100) NOT NULL
    , value INT NOT NULL
    );

INSERT  INTO @February
        ( Code, test_name, value )
VALUES  ( '0001', 'name1', 3 )
    ,   ( '0002', 'name2', 7 )
    ,   ( '0004', 'name4', 13 );

DECLARE @march AS TABLE
    (
      Code CHAR(4) NOT NULL
    , test_name VARCHAR(100) NOT NULL
    , value INT NOT NULL
    );

INSERT  INTO @march
        ( Code, test_name, value )
VALUES  ( '0001', 'name1', 6 )
    ,   ( '0006', 'name6', 32 )
    ,   ( '0007', 'name7', 41 );

WITH CTE_AllMonths AS
    (
    SELECT Code, test_name, value, 'January' AS SourceMonth
    FROM @January
    UNION ALL
    SELECT Code, test_name, value, 'February' AS SourceMonth
    FROM @February
    UNION ALL
    SELECT Code, test_name, value, 'March' AS SourceMonth
    FROM @March
    )
    SELECT Code, test_name, January, February, March
    FROM CTE_AllMonths
        PIVOT (SUM(value) FOR SourceMonth IN ([January], [February], [March])) AS PT


Only if you have applications, T-SQL code, synonyms etc. that reference this database by name. If this is not a production environment, you could test this on the existing copy using ALTER DATABASE ... MODIFY NAME and seeing what breaks, or just taking the current copy of the database OFFLINE to see what kind of things might be depending on it. If this is production, you better take the old copy of the database offline as soon as you restore the copy as a new name, because your apps etc. could still be connecting to the old database and you wouldn't know.
Close, but I think that the second MOVE command should be MOVE N'OldDatabaseName_log'. Of course this will leave the files named with the old database name; you can do more cleanup to modify those files individually if you want it to be squeaky clean (I think this can be overkill except in cases where I have built automation around the assumption that files are named for their container).
NOUNLOAD is not really relevant today, and you can probably leave it out of all RESTORE statements going forward. From the old SQL Server 2000 documentation (the last time it appeared fully documented, IIRC):

  Specifies that the tape is not unloaded automatically from the tape drive after a RESTORE. NOUNLOAD remains set until UNLOAD is specified. This option is used only for tape devices. If a non-tape device is being used for RESTORE, this option is ignored.


Why postgres is using an odd index with no relation to the query

none of the columns are mentioned

Yes they are, right here:

where pd.active and pd.fk_status = 1

that matches the condition on the index and thus the index can be used to support the counting of the rows. Reading through all the rows in the index should be faster than doing a seq scan on the process_data table.

Why it's not using the other index I cannot tell. Maybe because the difference in size doesn't really matter for expected number of rows (246076)

Btw: there is absolutely no difference in performance between count(1) and count(*)

A very good site to understand how indexes work is "Use the index, Luke"
http://use-the-index-luke.com/


ERROR 1067 (42000): Invalid default value for 'uploadedDate'

MariaDB (unlike MySQL) has special behavior if NULL is assigned to column that uses the TIMESTAMP data type.

If the column is assigned the NULL value in an INSERT or UPDATE query (including implicit assigning via DEFAULT), then MariaDB will automatically initialize the column's value with the current date and time. So DEFAULT NULL for TIMESTAMP column is incorrect.

Each time when you try to assign NULL to TIMESTAMP column (explicitly or implicitly) it will be assigned to CURRENT_TIMESTAMP. Disabling NO_ZERO_DATE and NO_ZERO_IN_DATE has no effect in this case.

So specify columns as:

`uploadedDate` timestamp DEFAULT CURRENT_TIMESTAMP

and

`lastAccessed` DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP


Join Tables of Varying Criteria in Order of Priority

This would be better accomplished through a series of inserts. Create a temporary table that contains all of the columns from table Appointments and table Events.

Insert into the temp table using each of your join predicates in the order of importance that you have listed above. For each insert after the first one (the one joined on appointment_id), use the following predicates:

appointment_id not in (select appointment_id from #yourTempTable) and
   event_id not in (select event_id from #yourTempTable)

Since your goal is to have all appointments listed, do a final insert for everything from Appointments that has:

appointment_id not in (select appointment_id from #yourTempTable)

Then you will want to run a report showing what events were not matched up to an appointment by searching all event_ids from Events that do not exist in #yourTempTable.


Can shrinking temporary TEMP tablespace in Oracle cause any harm?

all the three options would work, but would prefer to use first or the second one in production environment to avoid queries failing post activity due to not enough temp tablespace. Would recommend identifying the usage history pattern using queries similar to the one defined in this blog and then use that for your keep size.
https://zukus.blogspot.com/2016/06/temp-tablespace-usage-history.html


Delayed or Asynchronous DML Trigger Execution

As far as I'm aware this is not possible with the triggers feature of Azure / SQL Server. One reason likely being, as you mentioned, it breaks the Consistency property of the ACID principals of a database:

...any data written to the database must be valid according to all
defined rules, including constraints, cascades, triggers, and any
combination thereof. This prevents database corruption by an illegal
transaction...

Triggers are meant to fire transactionally in lockstep with the operation that caused them to fire. Imagine if the row of data that the trigger was dependent on was deleted before the trigger got to execute asynchronously. This could get you into a lot of trouble.
Alternatively, you can have a trigger that simply adds a row to a queue table, and a SQL Agent Job for example, that constantly runs checking that queue table and firing a stored procedure with the logic from your original trigger to emulate an asynchronous event. Perhaps that would improve your workflow?


Oracle sort varchar2 column with special characters last

If the sort order that you want to specify is already supported by Oracle, you can do this by ordering by the NLSSORT function - like so:

ORDER BY NLSSORT(sorted_column, 'NLS_SORT = XDanish') -- Replace XDanish as appropriate

You can find a list of supported sort orders here.


Why am I getting different results from a query vs. a view

You're using GROUP BY in a non-deterministic way. You GROUP BY tagepc, but what value for the other columns do you expect?

MySQL permits this kind of query, but MySQL chooses a row from the group arbitrarily. In practice, it tends to be the first row in the group, as they are stored physically.

But in the case when you query through the view, the view stores its results in a temporary table, probably in a different physical order than the data in the original table.

Note that http://dev.mysql.com/doc/refman/5.6/en/create-view.html says:

  ORDER BY is permitted in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY.

So the query run by the view to populate the temp table ignores your view's ORDER BY, and since you used GROUP BY it probably takes values from arbitrary rows in the underlying table.

How to fix this? Stop running ambiguous GROUP BY queries. Any column referenced in your select-list should either by (a) listed in the GROUP BY clause, or (b) inside a grouping function like MAX() or something.

I don't know what you expect this query to do, or why you're using GROUP BY in the first place. So I can't offer any more specific advice.

Re your comment:

Thanks for the clarification. This is a pretty common type of SQL question. I created the tag greatest-n-per-group on StackOverflow and now it has close to a thousand questions. The tag exists on dba.stackexchange.com, but with fewer questions so far.

Here's a solution:

SELECT i.*
FROM inventoryrecords AS i
JOIN (SELECT TagEPC, MAX(StateDateUTC) AS StateDateUTC
      FROM inventoryrecords
      GROUP BY TagEPC ORDER BY NULL) AS maxi USING (TagEPC, StateDateUTC);


PostgreSQL newbie - how to create objects in a database

I figured it out. I can create an empty table using the "new table.." menu item. Then I can edit the table once created by right clicking on it and choosing "scripts".


Full outer joins, coalescing, indexes and views

Try this

select a.Date, a.Foo, b.Bar
from TableA a LEFT JOIN TableB b ON a.Date = b.Date
UNION
select b.Date, a.Foo, b.Bar
from TableB b LEFT JOIN TableA a ON a.Date = b.Date

Strictly speaking, the query above does not quite have the same semantics as the original full join, with respect to duplicates. A correct transformation of full join is to a left join union all anti-semi-join:

SELECT
    TA.TheDate,
    TA.Foo,
    TB.Bar
FROM dbo.TableA AS TA
LEFT JOIN dbo.TableB AS TB
    ON TB.TheDate = TA.TheDate
UNION ALL
SELECT
    TB.TheDate,
    Foo = NULL,
    TB.Bar
FROM dbo.TableB AS TB
WHERE NOT EXISTS
(
    SELECT NULL
    FROM dbo.TableA AS TA
    WHERE TA.TheDate = TB.TheDate
);

This second rewrite will also facilitate the use of an index on TheDate columns.


MongoDB - Killed when database disk is full

As at MongoDB 4.0, the overall contents of the dbPath for a deployment are integral irrespective of the directoryPerDB setting. For example, a replica set member maintains a shared oplog (operation log) with a rolling record of write operations to all databases in a deployment. All members of a replica set must have a common data history: it is currently not safe for a replica set member to continue writing data (or ignore some writes) that will cause divergence from other members of the replica set.

If there is insufficient free space for updates to be committed to disk, the affected mongod process will shut down assuming administrative intervention is required to free up additional space.

  Is there some way to deal wiith it ?

Ideally you should have monitoring in place to proactively warn about low free disk space before this becomes a critical issue. Running out of disk space is generally not a great scenario for applications (or operating systems) to recover from automatically.

Although not specific to MongoDB, some admins create "bumper files" which can provide a buffer in case emergency space is needed. For example, creating a 5GB file on each volume which can be removed to provide free space as disk usage is approaching  full (assuming you have monitoring in place so this action can be taken before disk reaches 100% usage).

If you are less concerned about the availability of some databases, you could also manage them as separate MongoDB deployments so they do not impact write availability for more critical data. However, this may be be adding unnecessary complexity versus a single deployment with appropriate monitoring.


What is the effect of replacing indexes with filtered (non-null value) indexes?

Very interesting approach. My upvote for the creativity.

Since you reclaimed the space, I assume the original indexes are no longer in place? The downsides of filtered indexes then are:

Too many of them may cause the search space of the optimiser to grow too large, leading to poor query plans as the optimiser times out
There are several situations where a filtered index will not even be considered, even though the non-filtered equivalent would be. Notably, this can happen when you get a hash join on the indexed column or if you try to ORDER BY the column (without a filter)
Query parameterisation doesn't work with filtered indexes (see: http://www.sqlservercentral.com/blogs/practicalsqldba/2013/04/08/sql-server-part-9-filtered-index-a-new-way-for-performance-improvemnt/)

In practical terms, this means that you have to be extremely careful with filtered indexes as they will often result in horrible query plans. I would not go so far as to call them useless, but I view them as an addition to traditional indexes, not as a replacement (as you are trying to do).


Questions for 'Tips on Converting to Galera' on MariaDB KB

(I guess I better answer this.)

"colo" = "co-location" = "datacenter" -- meaning a non-trivial latency between nodes.

SELECT ... FOR UPDATE should be in a transaction.

An example of where you would like to do stuff on one node without bothering the others:  You need to compute a bunch of stuff to get the data to INSERT in a table.  But you are happy to do the computation on a single node and not worry if the node crashes.  For example, you might have some other mechanism to recover from a crash.  Think "idempotency".

"Transaction size" -- well that should probably say "how long the transaction takes to run".  At some level, lots of rows being modified makes it take longer, but that is not the only cause of "size".

Too big -- more chance of cross-node deadlocks.  Too small -- more effort and delay in doing the cross-node COMMIT.

Critical read:  User write a comment on a blog, then clicks submit.  The next page comes up but fails to show that comment.  Why?  The write went to one node; the read went to another node, but it had not gotten there yet.  (This is perhaps the only realistic example of why Galera does not claim to be fully synchronous.  The check in the gcache only checks for "will it successfully commit", not "has it already been written to disk".)

"Torn page" -- An innodb unit of data is a 16KB block.  The typical disk thinks in units of 512-byte blocks.  InnoDB can recover from a power failure if its blocks are entirely written or not written at all.  But if only part (N*512, but less than 16K) is written it is a "torn page".  So, InnoDB stands on its head with "doublewrite buffer" to be able to recover from such.  This involves a non-trivial amount of I/O.  But, since Galera can completely recover a dead server, you can allow torn pages if you are willing to rebuild the node after a power failure.

DROP USER involves MyISAM tables in the database mysql.  MyISAM tables are (or were) not replicated.  I suspect that Galera has made sure that DROP USER works.  However, since all the GRANT stuff involves MyISAM, I suspect there are cases where it can fail.  (MySQL 8.0 moves GRANT stuff into InnoDB tables, thereby making it crash-safe.)

Thanks for pointing out the flaws in the article.  I will try to make improvements to http://mysql.rjweb.org/doc.php/galera and the MariaDB link you gave.  (Some day)


How to setup vault and Postgres in Google cloud to have the correct permissions?

Short Summary

You have to run an REASSIGN OWNED BY "$VAULT_USERNAME" TO "cloudsqlsuperuser" after the table/sequence/function creation process to reassign back to a the common role.

Longer Answer

Postgres 9.6 CREATE TABLE will by default create the table under the user's role. Because the vault user is designed to be a temporary credential, we need to also make sure that the objects created under the temporary roles are reassigned back to a common role of some kind. In the specific case I outlined in Google Cloud, we are going to reassign back to cloudsqlsuperuser.

The one command I found to work is the REASSIGN OWNED BY current_user to "cloudsqlsuperuser" command that will then take all of the objects owned by that temporary user and assign back to that common role.

Bonus: liquibase config

I am using liquibase to do my migrations and this is in my change log to fix the permissions everytime we run a migration:

<databaseChangeLog>
    <changeSet author="elindblom" id="fix-permissions" context="" logicalFilePath="fix-permissions" runAlways="true" runOrder="last">
        <sqlFile path="baselines/sql/fix-permissions.sql" splitStatements="false" relativeToChangelogFile="true" stripComments="false"/>
    </changeSet>
   ... rest of your change logs after ...
</databaseChangeLog>

The SQL looks like this:

REASSIGN OWNED BY current_user TO "cloudsqlsuperuser";

The changeSet runs after everything comes to completion and runs every-time. The very important bits are the runAlways and runOrder elements.


(More like a Delphi problem to me)

In my case, it had to do with a recent reinstallation of Access.
Access MDAC not beeing compatible with sqlncli11.dll, this mean I had to rebuild the whole application.

If I get it right, Delphi compiled only the units that I modified, using (I don't even know why, as I don't use Access) the current MDAC and it collided with the old DCU that were compiled using the old MDAC.


It sounds like you are trying to delete everything in your table, is that correct? If so, I would check into a TRUNCATE syntax, this will cause the least amount of performance impact as it will not log the row by row delete. Additionally, since this is relatively low impact you shouldn't need to divide this up for only 1 million rows.

  In Oracle, truncating a table is a fast way to clear out records from
  a table if you don't need to worry about rolling back. One of the
  reasons is that when the table is truncated, it does not affect any of
  the table's indexes, triggers, or dependencies. Truncating a table is
  also a lot easier than dropping the table and recreating it.

Oracle / PLSQL: TRUNCATE TABLE Statement


2 questions:
- Where is the IO problem ? On the master or on the replicas ?
- If the IO contention is on a replica, can you create a TEST replica, replicating from the same master, but having innoDb engine ?

The thing with MyISAM storage is that an UPDATE / INSERT query will lock the whole table. And the replication thread on each replica is also a serial process, running just one query at a time (event) from the master binlog.

My suggestion would be change the storage engine for all tables, from MyISAM to InnoDb, then re-check the replication parameters.


fetching Remote table data and storing into local dump file in pgSQL?

Using C#, you could have a relatively simple program. For instance, if you want to write to a CSV file, you could use the following code:

using System.IO;  /* We want to Write to the FileSystem */
using Npgsql;     /* Connection to PostgreSQL database */
using CsvHelper;  /* CSV writer - https://joshclose.github.io/CsvHelper/ */

namespace ConnectToDB
{
    class Program
    {
        const string connectionString =
            "Server=localhost;" +
            "Database=joanolo;" +
            "User Id=user;" +
            "Password=password;";
        const string fileName = "Output.csv";
        const string SQLCommand = "SELECT * FROM t";

        static void Main(string[] args)
        {
            /* Connect to the database */
            NpgsqlConnection DB = new NpgsqlConnection(connectionString);
            DB.Open();

            /* Prepare to read data */
            NpgsqlCommand Cmd = new NpgsqlCommand(SQLCommand, DB);
            NpgsqlDataReader dataReader = Cmd.ExecuteReader();

            /* Create CSV writer */
            StreamWriter outputFile = new StreamWriter("Output.csv") ;
            var csv = new CsvWriter(outputFile);
            csv.Configuration.Delimiter = ",";
            csv.Configuration.Quote = '"';
            csv.Configuration.QuoteAllFields = true;

            /* Write Data Header */
            for (var i = 0; i < dataReader.FieldCount; i++)
            {
                csv.WriteField(dataReader.GetName(i));
            }
            csv.NextRecord();

            /* Read data from DB and write it to CSV */
            while (dataReader.Read())
            {
                for (var i = 0; i < dataReader.FieldCount; i++)
                {
                    var value = dataReader[i].ToString();
                    csv.WriteField(value);
                }
                csv.NextRecord();
            }

            /* Close everything */
            outputFile.Close();
            DB.Close();
        }
    }
}


If you look in SQL Server BOL, SQL Server Agent provides a set of "tokens" that it will substitute into both the job step command text and the output file (the later will prevent the GUI "view" button from working). These tokens seem to work for any type of step except T-SQL.

https://docs.microsoft.com/en-us/sql/ssms/agent/use-tokens-in-job-steps#sql-server-agent-tokens

So, if you have a SQL 2008 PowerShell step, you can start it with:

$sqlInstance = "$(ESCAPE_DQUOTE(SRVR))"

You may need to use MACH (machine name) and INST (just instance name) instead, because with the default instance SRVR == MACH, but with named instances SRVR == MACH\INST.


Based on the information you have provided, here is my suggestion on how to get good performance when querying the latest users that have cast a vote on a given sub item (performance is assumed to be your goal, given that you are asking about what indexes to add.)

Create the following table:

CREATE TABLE vote_sub_item_cast_uncast (
vote_sub_item_id INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
created_at TIMESTAMP NOT NULL,
vote_sub_item_action_id INT UNSIGNED NOT NULL,
PRIMARY KEY (`vote_sub_item_id`,`user_id`,`created_at`),
CONSTRAINT `VoteSubItemAction_VoteSubItemCastUncast_fk`
    FOREIGN KEY (`vote_sub_item_action_id`)
    REFERENCES `vote_sub_item_action` (`id`),
CONSTRAINT `VoteSubItem_VoteSubItemCastUncast_fk`
    FOREIGN KEY (`vote_sub_item_id`)
    REFERENCES `vote_sub_item` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

The foreign keys are not required, but are there largely to help describe what columns point to which parent tables.

Whenever you insert into the vote_sub_item_action table, you must also insert into this new vote_sub_item_cast_uncast table, IF and ONLY IF, the action type is action_uncast_vote or action_cast_vote.

Then, you will be able to run the following query:

SELECT
vsicu.*
FROM vote_sub_item_cast_uncast vsicu
LEFT OUTER JOIN vote_sub_item_cast_uncast later_vsicu
ON later_vsicu.vote_sub_item_id = vsicu.vote_sub_item_id
AND later_vsicu.user_id = vsicu.user_id
AND later_vsicu.created_at > vsicu.created_at
INNER JOIN vote_sub_item_action vsia
ON vsia.id = vsicu.vote_sub_item_action_id
WHERE vsicu.vote_sub_item_id IS NULL
AND vsia.action_type = 'action_cast_vote'
AND vsicu.vote_sub_item_id = 10

The strategy employed here is the (second) one describe in the manual, for finding the groupwise maximum of a given column.

The primary key of the new table will be all the index you need. From your example, you are supplying the vote_sub_item_id as a constant, which is why that column is listed first. Then each user_id can have as many entries as there are unique created_at values (I think you have sub-second resolution in 5.7, but if not, that means at most once cast/uncast per second - be aware of this, and that your current query has an issue when querying such cases.)

The LEFT OUTER JOIN vote_sub_item_cast_uncast allows us to do an anti-join, and will return at most one row for each distinct user_id for the given vote_sub_item_id. From there, the INNER JOIN vote_sub_item_action will bring us back to the that table, allowing you to select the columns you need.

Why not just an index

I don't believe an index would be able to help, because of your IN clause, which functions as an OR and generally destroys the ability to use an index effectively. Your query itself is valid, and but I do not believe it would be fast for large amounts of data, due to that IN statement.


Proper use of FULLTEXT index for a column storing unique hash value

No, I "can't imagine".  That is, unless you did not have INDEX(token), which is the index that would fast.  Don't use FULLTEXT in this situation.

To discuss further, please provide SHOW CREATE TABLE and EXPLAIN SELECT for the case of token = '...'.


Conversion of simple ERD to Table: is the solution below any good?

Looking at Relationship 1 as an example.  The primary key of this table is ID_PhoneNumber and ID_Company.  The CompanyName, however depends only on ID_Company alone.  The table is not in second normal form.  For the design to be normalised it would have to be:

Company (ID_Company, CompanyName)
Relationship1 (ID_Company, ID_PhoneNumber)

The same argument holds for Relationship 2 and 3.

Telephone has only two columns - the ID and the number.  I'd suggest quite strongly that it would be an error to have two rows in this table with the same number.  So a unique contraint on the number would be appropriate.  The ID column, which is the primary key, will have a unique constraint, too.  So we have a table where each row consists of a unique number (ID) paired with a unique number (the phone number).  It seems to me one of these is redundant.  You may choose to retain the ID as a surrogate key since an integer (the most likely data type) will be shorter than a full international telephone number, especially with formatting characters embedded.  Adopting this would give the table

Relationship1(ID_Company, PhoneNumber)

I would be inclined to use call it CompanyPhone at this point.

I know I'm mixing logical and physical arguments.  I'm assuming your intention is to implement a system and not simply derive an abstract model.

One thing I haven't covered, and isn't mentioned in your question, is whether a phone number can relate to a company or an employee or a supplier i.e. are the relationships mutually exclusive.


“using stale statistics instead of current ones because stats collector is not responding”

To temporarily alleviate the problem until you find a solution, have you tried sending a SIGHUP to the server when it occurs?

I have a similar problem in a fairly different situation: the database runs on a windows desktop PC together with Apache and proprietary machine control software. My case fits in the "slow or overloaded" category. However, I see this happening even at night while the machine is idle. I've been monitoring many system parameters with PRTG to try to find a link with a specific activity, but I can only see the consequences, not the cause.

I don't see this as a simple warning because, once it starts, queries that normally take 100ms will start taking 10 seconds (but still leave other queries unaffected). Also, it stays that way until I send a SIGHUP (pg_ctl.exe reload ...), which I think is weird since the process should resume once it is no longer starved.

P.S. please don't hail me about the windows desktop part: I know, and ... circumstances.


Can you enforce uniqueness in MySQL based on column in foreign table?

You cannot directly create a unique constraint across tables. I say directly because you could come up with some indirect scheme; what comes to my mind would be using triggers to insert into a third table that has the unique constraint. But that's a lot of extra work compared to the obvious solution, which is to keep the column "a" in the "my" table.


Server with mariadb sometimes dies under high load

I assume you hit the open_files_limit. This should be indicated somewhere further up of your warning about the instances in your MariaDB Error Log.
The problem here is, that MariaDB behaves differently than MySQL:
In MySQL the number of FD (open_files_limit) used is: table_open_cache x 2 + ...
In MariaDB the number of DD used is table_open_cache x table_open_cache_instances x 2 + ...
https://mariadb.com/kb/en/server-system-variables/#table_open_cache_instances
Why they made it different I do not know.
So in your case you have:
table_open_cache = 207000 x table_open_cache_instances = 8
= requiring 1.6M TOC entries but you only have defined 207k as open_files_limit = 207000
So either lower the table_open_cache by at least 8 times or increase the open_files_limit by at least 8 times.
3 additional thoughts:

Do you really need 200k or 1.6M FD??? That happens only very rarely when you have multi-tenant set-ups or 100ks of tables.

See also here: https://fromdual.com/mysql-shared-hosting-configuration

Files are configured today in SystemD: https://fromdual.com/node/1270#systemd

The effective number of FD (open_files_limit) you need is calculated in:

MariaDB: table_open_cache x table_open_cache_instances x 2 + max_connections + 40
MySQL: table_open_cache x 2 + max_connections + 10

Regards,
Oli


Are there disadvantages to contained databases?

The primary purpose of contained databases is to make it easier to port your database to a new server without a lot of scaffolding around it. With that in mind, I'll treat a few potential issues that will make this migration more difficult - and most revolve around the fact that contained databases are only partially contained in SQL Server 2012 (containment is not actually enforced).

Connection strings

Connection strings to a contained database must explicitly specify the database in the connection string. You can no longer rely on the login's default database to establish a connection; if you don't specify a database, SQL Server is not going to step through all the contained databases and try to find any database where your credentials may match.

Cross-db queries

Even if you create the same user with the same password in two different contained databases on the same server, your application will not be able to perform cross-database queries. The usernames and passwords may be the same, but they're not the same user. The reason for this? If you have contained databases on a hosted server, you shouldn't be prevented from having the same contained user as someone else who happens to be using the same hosted server. When full containment arrives (likely in the version after SQL Server 2012 never), cross-database queries will absolutely be prohibited anyway. I highly, highly, highly recommend that you don't create server-level logins with the same name as contained database users, and try to avoid creating the same contained user name across contained databases. If you need to run queries that hit multiple contained databases, do so using a server-level login that has appropriate privileges (you might think this is sysadmin, but for read-only queries, this is CONNECT ANY DATABASE and SELECT ALL USER SECURABLES).

Synonyms

Most 3- and 4-part names are easy to identify, and appear in a DMV. However if you create a synonym that points to a 3- or 4-part name, these do not show up in the DMV. So if you make heavy use of synonyms, it is possible that you will miss some external dependencies, and this can cause problems at the point where you migrate the database to a different server. I complained about this issue, but it was closed as "by design" and didn't survive the migration to the new feedback system. Note that the DMV will also miss 3- and 4-part names that are constructed via dynamic SQL.

Password policy

If you have created a contained database user on a system without a password policy in place, you may find it hard to create the same user on a different system that does have a password policy in place. This is because the CREATE USER syntax does not support bypassing the password policy. I filed a bug about this problem, and it remains open (and it also didn't survive the move when Connect was retired). And it seems strange to me that on a system with a password policy in place, you can create a server-level login that easily bypasses the policy, but you can't create a database user that does so - even though this user is inherently less of a security risk.

Collation

Since we can no longer rely on the collation of tempdb, you may need to change any code that currently uses explicit collation or DATABASE_DEFAULT to use CATALOG_DEFAULT instead. See this BOL article for some potential issues.

IntelliSense

If you connect to a contained database as a contained user, SSMS will not fully support IntelliSense. You'll get basic underlining for syntax errors, but no auto-complete lists or tooltips and all the fun stuff. I filed a bug about this issue, and it remains open - and one more that did not survive the move.

SQL Server Data Tools

If you are planning to use SSDT for database development, there currently isn't full support for contained databases. Which really just means that building the project won't fail if you use some feature or syntax that breaks containment, since SSDT currently doesn't know what containment is and what might break it.

ALTER DATABASE

When running an ALTER DATABASE command from within the context of a contained database, rRather than ALTER DATABASE foo you will need to use ALTER DATABASE CURRENT - this is so that if the database is moved, renamed, etc. these commands don't need to know anything about their external context or reference.

A few others

Some things you probably shouldn't still be using but nonetheless should be mentioned in the list of things that aren't supported or are deprecated and shouldn't be used in contained databases:

numbered procedures
temporary procedures
collation changes in bound objects
change data capture
change tracking
replication

That all said, these aren't necessarily disadvantages to using contained databases, they're just issues you should be aware of and aren't all explicitly disclosed in the official documentation.

You'll also need to be sure that if a contained database is going to be migrated, or is part of an availability group or is being mirrored, that all potential destination servers have the sp_configure option contained database authentication set to 1.

You may find this blog post useful, as well as this one, even though they pre-date RTM.


CREATE TABLE Table1_new LIKE Table1;
INSERT INTO Table1_new SELECT * FROM Table1;
ALERT TABLE Table1_new ADD COLUMN a;         --slow because of big table

is not optimal and has a problem.  Better:

CREATE TABLE Table1_new LIKE Table1;
ALERT TABLE Table1_new ADD COLUMN a;          -- fast because table empty
INSERT INTO Table1_new SELECT *, NULL FROM Table1; -- needs value for `a`

The problem is -- What if Table1 is being written to?  Such DML would be lost.

ALERT TABLE Table1 RENAME Table1_bu;
ALERT TABLE Table1_new RENAME Table1;

Should be done as a single statement (atomically):

RENAME TABLE Table1 TO Table1_bu,
             Table1_new TO Table1;

Plan A:  (Your code, after fixes):  Viable only if no modifications will happen.

Plan B:  pt-online-schema-change (assuming you have not Triggers on the table).

Plan C:  oak... -- No; it seems not tok be maintained

Plan D:  Upgrade to MySQL 8.0 (perhaps MariaDB 10.3) so you can get ALTER TABLE .. ADD COLUMN .. ALGORITHM=INSTANT


SELECT DISTINCT on one column, while returning the other columns?

One more approach not mentioned yet is to use window functions, for instance row_number:

   SELECT * FROM
   (
   SELECT acss_lookup.ID AS acss_lookupID,
   ROW_NUMBER() OVER
   (PARTITION BY your_distinct_column ORDER BY any_column_you_think_is_appropriate)
   as num,
   acss_lookup.product_lookupID AS acssproduct_lookupID,
   acss_lookup.region_lookupID AS acssregion_lookupID,
   acss_lookup.document_lookupID AS acssdocument_lookupID,
   product.ID AS product_ID,
   product.parent_productID AS productparent_product_ID,
   product.label AS product_label,
   product.displayheading AS product_displayheading,
   product.displayorder AS product_displayorder,
   product.display AS product_display,
   product.ignorenewupdate AS product_ignorenewupdate,
   product.directlink AS product_directlink,
   product.directlinkURL AS product_directlinkURL,
   product.shortdescription AS product_shortdescription,
   product.logo AS product_logo,
   product.thumbnail AS product_thumbnail,
   product.content AS product_content,
   product.pdf AS product_pdf,
   product.language_lookupID AS product_language_lookupID,
   document.ID AS document_ID,
   document.shortdescription AS document_shortdescription,
   document.language_lookupID AS document_language_lookupID,
   document.document_note AS document_document_note,
   document.displayheading AS document_displayheading
   FROM acss_lookup
     INNER JOIN product ON (acss_lookup.product_lookupID = product.ID)
     INNER JOIN document ON (acss_lookup.document_lookupID = document.ID)
   )a
   WHERE a.num = 1
   ORDER BY product_displayheading ASC;


Pgpool - semanage config files - which filetype should I choose?

Solution founded:

To repair this issue I must execute this comand:

  chcon -t httpd_user_rw_content_t pgpool.conf

and

  chcon -t httpd_user_rw_content_t pcp.conf

then pgpooladmin will show that those files are writable and will allow to click "Next" to install pgpoolAdmin.
Yay Cheers :D


MySQL Workbench shows running query but query not in process list

It may happen when a transaction is not committed or even roll-backed.
I had similar issues regarding incomplete queries in here when-a-table-is-locked-but-show-processlist-shows-nothing.
Hope that help you.


The Data Loading Performance Guide was written for SQL Server 2008 but as far as I can tell Microsoft hasn't made any improvements in this area for heaps. Here's a quote for your loading scenario:

  Bulk Loading an Empty, Nonpartitioned Table

  Loading data into a nonpartitioned table, while a simple operation,
  can be optimized in several ways.

  ...

  Multiple, concurrent insert operations for heaps are possible only
  when the chosen bulk method issues bulk update (BU) locks on the
  table. Two bulk update (BU) locks are compatible, and hence two bulk
  operations can run at the same time.

  In this scenario, both INSERT … SELECT and SELECT INTO have a
  drawback. Both of these operations take an exclusive (X), table level
  lock on the destination. This means that only one bulk load operation
  can run at a given time, limiting scalability. However, BCP, BULK
  INSERT, and Integration Services are all capable of taking bulk update
  (BU) locks – if you specify the TABLOCK hint.

The important part is that you don't get a BU lock with INSERT ... SELECT. You'll always get an exclusive lock on the table, so only one INSERT can run at a time.

In the comments you said that you'll insert 100k rows or less and that other processes won't be running on the tables during the inserts. When sending two INSERT queries to the database I would expect one of three things to happen:

One insert runs first and blocks the other insert. The second insert waits until the first insert is done.
One insert finishes before the second insert starts. There is no explicit blocking but they aren't run concurrently.
You get a deadlock and only one insert completes successfully.

In all cases you either benefit or aren't hurt by adding a TABLOCKX hint to the query, so that is my recommendation of working around the deadlock. If you want to know why the deadlock sometimes happens you'll need to look to another answer for that.

For in a different scenario in which you really need parallel insert, two ways of working around the BU issue are to partition your heap and to have each session insert into a separate partition or to load your data through BCP, BULK INSERT, or Integration Services.


Speed of Searching Text vs INT/BIGINT - SQL Server 2012

As other suggested, sometimes the best choice is to test it out yourself. I hate answering my own question, so if someone can provide a better answer then I might chose it if it better explains my results.

It turns out there is not UBIGINT64 in SQL, so I created the table as I described as follows and used decimal instead of BigInt:

CREATE TABLE [dbo].[KEYS](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [KEY] [nvarchar](max) NOT NULL,
    [BI1] [decimal](28, 0) NOT NULL,
    [BI2] [decimal](28, 0) NOT NULL,
    [i1] [int] NOT NULL,
    [i2] [int] NOT NULL,
    [i3] [int] NOT NULL,
    [i4] [int] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

I then wrote a script to populate the table with approximately 2.25 million rows of data and setup the following loop to see how long each one would take.

declare @x integer
set @x = 0
while @x < 100
begin
    --QUERIES WERE INSERTED HERE
    set @x = @x + 1
end

I then plugged the following three queries into the loop and recorded the results for 50 loops and 100 loops.

select * from IntTypes where i1 = 64003101 and i2 = 64003107 and i3 = 64003144 and i4 = 640031031

50 Loops: 9 seconds
100 Loops: 18 seconds

select * from IntTypes where BI1 = 15284527576400310788 and BI2 = 16825458760271544958

50 Loops: 11 seconds
100 Loops: 23 seconds

select * from IntTypes where [KEY] = 'a123f-5n12d1km9-3cmc32mc32-32cm0429c-243g4453-g43f43'

50 Loops: 54 seconds
100 Loops: 1 minute 48 seconds

Perhaps the results would be different if I did use a BIGINT instead of a DECIMAL, but comparing 4 integer columns seems to outperform comparing the two decimal columns. Both of those completely destroyed the text search results timing, but overall it looks like I will be using integer columns.

So to sum it up and answer my own question (sort of), it is faster to search 4 integer columns than searching two decimal columns.


litterally just reinstall. that's the only way. just redo that database.


PostgreSQL row as jsonb "array", not "object"

You could extract the values from the JSONB value and aggregate them back into a JSON array

select 'some_table' as table_name,
       id,
       (select jsonb_agg(x.v) from jsonb_each(to_jsonb(t) - 'id') as x(k,v)) as content
from some_table t;

But you can't rely that the order of the array elements corresponds to the order of the columns in the table.

Another alternative (not using JSON) would be to cast the whole row into a text value:

select 'some_table' as table_name,
       id,
       t::text
from some_table t;

Casting a record to text should preserve the order of the columns as they are defined in the table.


How to set constraints on UDT in SQL Server 2005 - 2012

Currently, the only way to truly centralize/encapsulate this kind of logic (which is what I assume you're trying to do) is to use a CLR type. The good news it that this solution works with 2005+.

One alternative is to centralize the validation logic into a UDF, and then create individual check constraints that reference the UDF. If you're not using CLR already, this may be the best solution.

CREATE TYPE is very limited: when referencing a system type, it's just a type alias and nothing more. It would be nice to, for example, declare a check constraint (or something similar) that's associated with the type itself, but this is currently not implemented.


Login to SQL Server 2k8 R2 SP1 via Active Directory Group

It appears that because the security group was literally created moments before I added it as a Windows Auth Login that it hadn't synced yet with all the domain controllers.  It now appears to be functioning properly.

I apologize for the errant question.

Thanks,

Jeremie


So I am wondering how this might have been broken (SQLServer update?).

SQL Server update wont change any login / user or role permissions as part of update (installing CUs or SPs).

The permissions might have changed due to a possible database upgrade (your application version upgrade). Check your upgrade scripts.

To find out who changed the permissions, you need to have a server side trace running and collecting relevant information.

  how can I see what permissions have been granted to a role?

You can use this TSQL to check permissions granted to a role.


"Test-SqlAvailabilityGroup" takes too much time

I guess if you only want to see if the Availability group exists, you could query the DMV:
select name from sys.availability_groups

That will return all the name of the AG(s) on that specific instance.
It could be done from PowerShell too with a "invoke-sqlcmd" pretty easilly.