Parse a string to find data between delimiters, multiple occurrences

This is a pretty boring answer, but if you are guaranteed to always have your data in that format then you can get your desired results with a bunch of CHARINDEX and SUBSTRING calls. The idea is that if you find all of the positions of the labels you can take advantage of their fixed lengths to use those positions in the arguments of the SUBSTRING calls.

By the way, it looks like you have a few typos in your sample data. I've attempted to fix them so pay special attention to my test data.

CREATE TABLE #DETAILS (TEST_STRING VARCHAR(1000));

INSERT INTO #DETAILS VALUES ('Item1: A100 ; Item2: B200; Item3:C300; Item4:D400; Item5:E500; Item6: F600600600; Item7:; Item8: H800');
INSERT INTO #DETAILS VALUES ('Item1:; Item2:; Item3:; Item4:; Item5:; Item6:; Item7:; Item8:');

SELECT
  SUBSTRING(TEST_STRING, 7, P2 - 7) ITEM1VALUE
, SUBSTRING(TEST_STRING, P2 + 8, P3 - P2 - 8) ITEM2VALUE
, SUBSTRING(TEST_STRING, P3 + 8, P4 - P3 - 8) ITEM3VALUE
, SUBSTRING(TEST_STRING, P4 + 8, P5 - P4 - 8) ITEM4VALUE
, SUBSTRING(TEST_STRING, P5 + 8, P6 - P5 - 8) ITEM5VALUE
, SUBSTRING(TEST_STRING, P6 + 8, P7 - P6 - 8) ITEM6VALUE
, SUBSTRING(TEST_STRING, P7 + 8, P8 - P7 - 8) ITEM7VALUE
, SUBSTRING(TEST_STRING, P8 + 8, 8000) ITEM8VALUE
FROM
(
    SELECT
      TEST_STRING
    , CHARINDEX('; Item2:', TEST_STRING) P2
    , CHARINDEX('; Item3:', TEST_STRING) P3
    , CHARINDEX('; Item4:', TEST_STRING) P4
    , CHARINDEX('; Item5:', TEST_STRING) P5
    , CHARINDEX('; Item6:', TEST_STRING) P6
    , CHARINDEX('; Item7:', TEST_STRING) P7
    , CHARINDEX('; Item8:', TEST_STRING) P8
    FROM #DETAILS
) t;

Results (bottom row is all nulls as expected):

╔════════╦═══════╦═══════╦═══════╦═══════╦═════════════╦═══════╦═══════╗
║ ITEM1  ║ ITEM2 ║ ITEM3 ║ ITEM4 ║ ITEM5 ║    ITEM6    ║ ITEM7 ║ ITEM8 ║
╠════════╬═══════╬═══════╬═══════╬═══════╬═════════════╬═══════╬═══════╣
║  A100  ║  B200 ║ C300  ║ D400  ║ E500  ║  F600600600 ║       ║  H800 ║
║        ║       ║       ║       ║       ║             ║       ║       ║
╚════════╩═══════╩═══════╩═══════╩═══════╩═════════════╩═══════╩═══════╝

The above code will not work if you don't always have exactly eight items and you don't always have a space between ; and the next item label (except for label 1 of course). Also note that your item values cannot contain certain values or the code will throw an error. You said that you just want to get the data between : and ; and generally delimiters should not show up in data, but here's an example of data that would cause an issue:

INSERT INTO #DETAILS VALUES ('Item1:; Item7:; Item2:; Item3:; Item4:; Item5:; Item6:; Item7:; Item8:');


Getting count(column) as well as a specific row in each group

For the (column2 where column2 contains 'page1') expression, try something like:

MAX(CASE WHEN Column2 LIKE '%page1%' THEN Column2 END)

...but I'm not sure what you expect if you have multiple rows with "page1" per column1.


How to hint many-to-many join in SQL Server?

Just to be clear, the optimizer already knows that it's a many-to-many join. If you force merge joins and look at an estimated plan you can see a property for the join operator which tells you if the join could be many-to-many. The problem that you need to solve here is bumping up the cardinality estimates, presumably so you get a more efficient query plan for the part of the query that you left out.

The first thing that I would try is putting the results of the join from Object3 and Object5 into a temp table. For the plan that you posted it's just a single column on 51393 rows, so it should hardly take up any space in tempdb. You can gather full stats on the temp table and that alone might be enough to get a sufficient accurate final cardinality estimate. Gathering full stats on Object1 may help as well. Cardinality estimates often get worse as you traverse from a plan from right to left.

If that doesn't work you can try the ENABLE_QUERY_OPTIMIZER_HOTFIXES query hint if you don't already have it enabled at the database or server level. Microsoft locks plan-affecting performance fixes for SQL Server 2016 behind that setting. Some of them relate to cardinality estimates, so perhaps you'll get lucky and one of the fixes will help with your query. You can also try using the legacy cardinality estimator with a FORCE_LEGACY_CARDINALITY_ESTIMATION query hint. Certain data sets may get better estimates with the legacy CE.

As a last resort you can manually increase the cardinality estimate by whatever factor you like using Adam Machanic's MANY() function. I talk about it in another answer but it looks like the link is dead. If you're interested I can try to dig something up.


Microsoft SQL Server 2016 Restore command many files

to restore a differential backup please tale a look to the official doc here:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-differential-database-backup-sql-server?view=sql-server-ver15#using-transact-sql
You have to restore your full backup with the NORECOVERY clause. Then take your last differential backup and restore it.
Something like that:
RESTORE DATABASE [Database]
   FROM Disk = N'Database_backup_full.bak'
WITH FILE = 1, REPLACE, NORECOVERY,
MOVE 'Database' TO 'somepath\Database.mdf',
.....
   ;
GO
RESTORE DATABASE [Database]
   FROM Disk = N'Database_backup_differential.bak'
   WITH RECOVERY;
GO

You do not need to specify MOVE clause again in restoring differential backup files if that's already done when restoring full backup file


Thanks to CyberDem0n.

Two issues in etcd1 start command:

API VERSION
Etcd v3.4 default API version is v3.
Patroni doesn't currently support the v3 API.
Etcd 3.4 turned API v2 off by default.

I added --enable-v2=true flag on the three etcd.service and this error was fixed.

advertise-client-urls FLAG

And for each etcd server I replaced 0.0.0.0 by the real etcd server ip on the --advertise-client-urls flag (like --advertise-client-urls http://10.B.B.99:2379 in the example).


Queries for unique names from a site table using multiple criteria

1 Select unique site names which have been referred to at some point.

Get rows from site where site_id exists in referrals. This assumes site name is unique in site. If not you can add a distinct.

select s.name
from dbo.site as s
where s.deleted = 0 and
      exists (
             select *
             from dbo.referrals as r
             where s.site_id = r.site_id
             )

  2 Select unique site names and how many times they have been referred
  to.

Join site to referrals and count the number of rows grouped by site name.

select s.name,
       count(*) as times_referred
from dbo.site as s
  inner join dbo.referrals as r
    on s.site_id = r.site_id
where s.deleted = 0
group by s.name

  3 Select unique site names and the last date they were referred to.

Add a join to calls and get the max call_date for each site name.

select s.name,
       count(*) as times_referred,
       max(c.call_date) as last_call_date
from dbo.site as s
  inner join dbo.referrals as r
    on s.site_id = r.site_id
  inner join dbo.calls as c
    on r.call_id = c.call_id
where s.deleted = 0 and
      c.deleted = 0
group by s.name

  4 Select unique site names which have never been referred to.

Same as first query only you get the sites that does not exist in referrals.

select s.name
from dbo.site as s
where s.deleted = 0 and
      not exists (
                 select *
                 from dbo.referrals as r
                 where s.site_id = r.site_id
                 )


Quite often you can use row_number() to select only certain rows of the data. so maybe something like this would work:

select * from (
  SELECT
    instance_name ,
    DU.database_name ,
    DU.collection_time ,
    DU.dbsize ,
    DU.logsize ,
    DU.reservedpages ,
    DU.usedpages,
    row_number() over (
      partition by instance_name, DU.database_name, convert(date, DU.collection_time)
      order by DU.collection_time desc) as RN
    ...
) X
WHERE RN = 1
ORDER BY instance_name ,
         database_name ,
         collection_time;


on deleting a row, the update logic is run also

Instead of diagnosing your update/delete logic issue, I think it's better to just re-write your trigger.  The biggest problem with your trigger is that it's not set based, so that any attempt to modify multiple records at a time will fail, or more accurately give you odd results.  I suspect that this is at least part of the problem you are experiencing with it.

Please take a look at the one below.  It should work for you, but I apologize if I got some column names incorrect.  The CTE_Details at the top fuses the INSERTED and DELETED tables together and will properly return the Action taken based on whether it's an insert, update or delete.  It then inserts that into your reporting table.

ALTER TRIGGER [niku].[TRIG_STATUSRPT]
ON [niku].[ODF_CA_COP_PRJ_STATUSRPT]
AFTER INSERT, UPDATE, DELETE
AS
BEGIN

    ;WITH CTE_Details AS
        (
        SELECT I.ID
            , I.ODF_PARENT_ID
            , I.COP_SCHEDULE_STATUS
            , I.COP_SCOPE_STATUS
            , I.COP_COST_EFT_STATUS
            , ActionTaken = CASE WHEN D.ID IS NULL THEN 'Insert' ELSE 'Update' END
        FROM INSERTED AS I
            LEFT OUTER JOIN DELTED AS D ON D.ID = I.ID
        UNION ALL
        SELECT D.ID
            , D.ODF_PARENT_ID
            , D.COP_SCHEDULE_STATUS
            , D.COP_SCOPE_STATUS
            , D.COP_COST_EFT_STATUS
            , ActionTaken = 'Delete'
        FROM DELETED AS D
        WHERE NOT EXISTS (SELECT TOP (1) 1 FROM INSERTED AS I WHERE I.ID = D.ID)
        )
    INSERT INTO TEST_STATUS_REPORT(STATUS_ID,PROJECT_ID,COP_SCHEDULE_STATUS,COP_SCOPE_STATUS,COP_COST_EFT_STATUS,ACTION_TYPE,CREATED_DATE,MAXSTATUSID)
    SELECT T.ID AS STATUSID
        , T.ODF_PARENT_ID AS PROJECTID
        , T.COP_SCHEDULE_STATUS AS SCHEDULE_STATUS
        , T.COP_SCOPE_STATUS AS SCOPE_STATUS
        , T.COP_COST_EFT_STATUS AS EFT_STATUS
        , T.ActionTaken
        , GETDATE() AS CREATED_DATE
        , OP.ID AS MAXSTATUSIDVAR
    FROM CTE_Details AS T
        OUTER APPLY (   SELECT TOP (1) M.ID
                        FROM ODF_CA_COP_PRJ_STATUSRPT AS M
                        WHERE M.ODF_PARENT_ID = T.ODF_PARENT-ID
                        ) AS OP

END

** EDIT ** - I fixed a bug in my example code for the Insert action path, it wouldn't simulate the error you are seeing.

I have an example here below that shows it working correctly.  I think you have something else going on.  If you run the example code below you should see four entries.  2 inserts, 1 update and 1 delete.

DROP TABLE IF EXISTS dbo.TestMe;
DROP TABLE IF EXISTS dbo.TestMeReport;

CREATE TABLE dbo.TestMe
    (
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , TestValue VARCHAR(100) NULL
    )

CREATE TABLE dbo.TestMeReport
    (
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , TestMeID INT NOT NULL
    , TestValue VARCHAR(100) NULL
    , ActionTaken VARCHAR(20) NULL
    )

GO

CREATE TRIGGER trgTestMe ON dbo.TestMe
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN

    ;WITH CTE_Details AS
        (
        SELECT I.ID, I.TestValue, ActionTaken = CASE WHEN D.ID IS NULL THEN 'Insert' ELSE 'Update' END
        FROM INSERTED AS I
            LEFT OUTER JOIN DELETED AS D ON D.ID = I.ID
        UNION ALL
        SELECT D.ID, D.TestValue, 'Delete' AS ActionTaken
        FROM DELETED AS D
        WHERE NOT EXISTS (SELECT TOP (1) 1 FROM INSERTED AS I WHERE I.ID = D.ID)
        )
    INSERT INTO dbo.TestMeReport
    (TestMeID, TestValue, ActionTaken)
    SELECT T.ID, T.TestValue, T.ActionTaken
    FROM CTE_Details AS T

END

GO

TRUNCATE TABLE dbo.TestMe
TRUNCATE TABLE dbo.TestMeReport

INSERT INTO dbo.TestMe (TestValue)
VALUES ('Test1')
    , ('Test2')

UPDATE dbo.TestMe
SET TestValue = 'Test3' WHERE TestValue = 'Test1'

DELETE FROM dbo.TestMe WHERE TestValue = 'Test2'

SELECT * FROM dbo.TestMeReport


SQL Server 2012, Network for: AlwaysOn Availability Group with AlwaysOn AlwaysOn Failover Cluster Instances

Yes AG support multiple subnets as described here. Also make sure that your data provider supports MultiSubnetFailover .. .NET Framework 4 supports it.

To answer your question ...

IF you use .NET framework 4 or 3.5 then the provider will support it as described here.

Also, a good reference to SQL Server Multi-Subnet Clustering is well documented.

With legacy client libraries or third party data providers, you cannot use the MultiSubnetFailover parameter in your connection string. To help ensure that your client application works optimally with multi-subnet FCI in SQL Server 2012, try to adjust the connection timeout in the client connection string by 21 seconds for each additional IP address. This ensures that the client’s reconnection attempt does not timeout before it is able to cycle through all IP addresses in your multi-subnet FCI.


Is that syntax correct? Yes. You could also create the PK afterward and with a few different syntax options, but this will build.

But there are a lot of answers to this question. And a lot of questions in this question. "Is this a good idea?"  Well it depends – and that is sort of opinion and situation based so the question could be closed if not narrowed down a little with a more specific question.

When you define something as the primary key, you are stating that that is the fact or set of facts which describes one row and only one row.

So for what this table looks like the combination of UserID and ComputerName seem appropriate enough from a database design perspective alone.

There are still other questions though. Questions not in the question but things like:

Why UserID as a varchar? Why not an INT that points back to a User(s) table? This would be a potentially more proper normal form.
Same for ComputerName.
What is the purpose of the table – seems like it describes a computer. Why have the user info in there at all? Why not have a User table and a Computer table and then a table which assigns a user to a computer? (Not saying one way is more right just a question.)
The default behavior for SQL Server when creating a primary key is to make the index on the table clustered when doing it through the GUI – this isn't bad, but I like narrower clustered indexes and clustered indexes which are monotonously increasing and less likely to have index fragmentation and page splits. For that reason, I tend to be more often than not in the "composite clustered index" camp. Sometimes that means I make up a PK and let that be clustered like ComputerUserID – and have that be an identity column. Sometimes I just use that as the clustered and still use the natural key as the primary key as you are trying.
If you created a surrogate key to be the PK for this table and be the clustered index, you could still create a unique index on the combination of ComputerName and UserID and enforce the uniqueness.

Just some food for thought. There are likely many answers to this question. Which is why I'm not 100% sure it will last, but here are some thoughts for now at least.


Does SQL Server 2014 Express, with Advanced Services, actually support full text search?

There is already an existing answer posted by Aaron Bertrand for SQL Server Express 2012.

First install SQL Server 2014 Express with Advanced Services as you did. Then read Aaron's instructions at:

https://stackoverflow.com/questions/10407337/express-with-advanced-services-cant-create-full-text-index

The short version is that the user interface does not behave properly when setting up full text.  But you can enable and set up Full Text Indexing and Search through T-SQL code.


Size is one consideration.  An int can hold up to -2,147,483,648 in four bytes.  A char will need 11 bytes to hold the same value.

There are built-in functions to manipulate the various data types.  DATEADD() and DATEDIFF() are two examples.  This will not be possible with date-stored-as-text.  Constantly CASTing back and forth will not make for efficient processing, or legible code.

Automatic validation is another foregone benefit with the all-text approach. You may think that a column contains dates but there will be nothing to stop someone entering the value '2014-13-97'.

Sorting is unlikely to give the intended result with columns which are "really" numbers. For example, if a column contained integers '1' through '100', and the query sorted by this column, one would expect the result to be

1, 2 .. 10, 11 .. 20, 21 .. 100

However, the actual result is more likely to be

1, 10, 100, 11, 12, 2, 20, 21 ...

There may be similar concerns for dates, depending on the chosen character representation. Again type casting can cure this with the costs mentioned previously.

Occasionally you will find strings which contain digits only.  Examples are national identity numbers, bank account numbers, phone numbers and such like.  The common characteristic of such values is that it does not make sense to perform calculations on them.  Other things being equal it may be OK to store these as char() columns, especially if they have optional embedded alpha characters, but the above considerations still apply.


what is the best configuration for innodb_flush_log_at_trx_commit?

innodb_flush_log_at_trx_commit  has default value of 1 - To disk on each transaction commit. is extremely expensive. Your applications can get incredible performance boost if you change the value to 2 - To OS cache on each transaction commit. To disk every second.

With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB's crash recovery is not affected and thus crash recovery does work regardless of the value.


I would suggest some reading on normalization and many-to-many relationships.

One problem you're probably running into is figuring out how many fields to include with your current ingredients and directions lists. Ingredients_list with ingredients_list_id, ingredient1, ingredient2, and ingredient3 will be insufficient for any recipe needing more than three ingredients. Bumping up the column count to N doesn't work either, when Forrest's Fancy Fruitcake needs N+1 ingredients.

Following the pattern shown in the many-to-many link, you could link ingredients back to the recipe id, where each row in ingredients_list contains one recipe_id and one ingredient_id. Then (simplifying to ignore joins) getting all ingredients for a recipe would look like SELECT [ingredient_id] FROM [ingredient_list] WHERE [recipe_id] = 1234.

Overall, I might change the schema to something like this:

users

user_id
user_email
user_password

recipes

recipe_id
user_id
recipe_name

ingredients_list

recipe_id
ingredient_id
ingredient_qty

directions_list

recipe_id
direction_text
direction_number

ingredients

ingredient_id
ingredient_name


Can I prevent deadlock using `WITH(NOLOCK)`?

A better idea would be to enable snapshot isolation: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server

This will enable each transaction to have its own snapshot of data and will not lock, or block other transactions.


Are updates the only disadvantage of denormalization?

There are different kinds of denormalization. Say we can have the main table readouts like that:

CREATE TABLE `readouts` (
  `sensorID` INT(10) UNSIGNED NOT NULL,
  `timestamp` TIMESTAMP NOT NULL,
  `value` FLOAT(9,5) NOT NULL,
  PRIMARY KEY (`sensorID`, `timestamp`)
);
+----------+-----------+-------+
| sensorID | timestamp | value |
+----------+-----------+-------+

Table stores the readouts from the lot of sensors. If we need the last readout for each sensor we have to run the query like that:

SELECT a.*
  FROM readouts AS a
  JOIN ( SELECT sensorID
              , MAX(timestamp) AS timestamp
          GROUP BY sensorID
       ) AS b  ON b.timestamp = a.timestamp
              AND b.sensorID  = a.sensorID
 ORDER BY sensorID ASC
;

That is quite heavy query even when the table readouts is properly indexed. We can denormalize the database by creating the table of the same structure but the UNIQUE constraint for sensorID:

CREATE TABLE `last_readouts` (
  `sensorID` INT(10) UNSIGNED NOT NULL,
  `timestamp` TIMESTAMP NOT NULL,
  `value` FLOAT(9,5) NOT NULL,
  PRIMARY KEY (`sensorID`)
);

Now we have to INSERT incoming readouts to the both tables. The second table should be inserted in the special way:

INSERT INTO last_readouts
     VALUES (sID, ts, val)
     ON DUPLICATE KEY UPDATE
        `timestamp` = ts
         value = val
;

This addtitional INSERT is the insert-related disadvantage of the denormalization. But the advantage is the minimal cost of the query

SELECT * FROM last_readouts;

The DELETE disadvantage is of the same sort - you need more operations of DELETE but you have some benefits instead.


How to use TO_NUMBER function to update columns of a table Using oracle pl/sql

As per your comment, requesting a regex solution:

update table1 set
  error_code = 1
, error_desc = 'Incorrect format'
where not regexp_like( cust_no, '^[0-9]+$' )

However, I would suggest that this is too little, too late.

You should not be allowing this defective data into the database in the first place!  (Unless this is part of a migration exercise and this is a transitional table).

Your application should be detecting this bad formatting and not even saving the record to the database.

Second Thought:

Alternatively, put the check into the table definition

[Air Code; untested]

create table Table1
( Cust_No Varchar2(50)
, CHECK( regexp_like( Cust_No '^[0-9]+$' )
. . .

Third Thought:

If you customer number is, indeed, a number, then why are you storing it in the wrong Data Type?

create table Table1
( Cust_No  NUMBER
. . .


MySql: Delete all rows greater than n entries ordered by datetime

This will keep the first 200 rows (and possibly a few more, with identical timestamps):

DELETE t
FROM
    tableX AS t
  JOIN
    ( SELECT timestampColumn AS ts
      FROM tableX
      ORDER BY ts ASC
      LIMIT 1 OFFSET 199
    ) tlimit
    ON t.timestampColumn > tlimit.ts

Additional question: Keep the first 200 rows for every user (by the uid column):

DELETE tt
FROM
      ( SELECT DISTINCT uid             --- these 3 lines can be replaced
        FROM tableX                     --- with:   UserTable AS du
      ) AS du                           --- a table that you probably have
  JOIN
      tableX AS tt
    ON  tt.uid = du.uid
    AND tt.timestampColumn >
        ( SELECT timestampColumn AS ts
          FROM tableX
          WHERE uid = du.uid
          ORDER BY ts ASC
          LIMIT 1 OFFSET 199
        )

A (uid, timestampColumn) will be useful with a big table.


Backupset server name on dev server shows Prod server name

This is confirmed.

If you restore a database from one server to another, but never back it up, and run the code I have above, it will show the server name from the original server it was backed up from.

BUT if you then do a backup of that same database, it will then show the current server it was backed up from which is what I expected.


Can you combine database mirroring with AlwaysOn Availability Groups?

You are correct, it is impossible to mirror a database that is part of an Always On Availability group.  You can consider logshipping.

Ref:

https://blogs.msdn.microsoft.com/sqlalwayson/2010/12/08/alwayson-availability-group-and-database-mirroring/

  "Does AlwaysOn Availability Group replace Database Mirroring going
  forward?” The short answer is we recommend that you migrate from the
  mirroring configuration or even mirroring and log shipping
  configuration to using Availability Group. Database Mirroring will
  still be available in the Denali release but will be phased out over
  subsequent releases. Log Shipping will continue to be available in
  future releases.

For the error you are getting read this document especially section: Windows System Requirements and Recommendations.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/prereqs-restrictions-recommendations-always-on-availability#PrerequisitesForAGs


Yes, just use bitwise & operator:

select products.genres, genre.Id from
products inner join genre
on products.genres & genre.Id > 0
order by products.genres, genre.Id

SQL Fiddle


How to query hstore tag keys that match part of the filter string

You need to create a set of the keys in order to be able to compare them using like.

Something like:

select *
from the_table t
where exists (select *
              from skeys(t.tags) as x(tagname)
              where x.tagname like '%building%');

But this is not going to be fast.


How to know that , Is it Maintenance Cleanup Task is working properly from maintenance Plan or not through T-SQL?

If you want to be able to view the exact backup history for longer time than those four weeks that you keep the backup files on disk, make sure that you set the cleanup task not to delete the backup history from the msdb database, you can then run the backup report from SSMS (right click database, select reports, standard reports, backup and restore events) or run the following to view the backup history:

First create this function - I'll just have it in msdb - which is not a good idea.

create FUNCTION dbo.fc_FileExists(@path varchar(8000))
RETURNS BIT
AS
BEGIN
     DECLARE @result INT
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT
     RETURN cast(@result as bit)
END;
GO

Then you can run the following

SELECT
 sd.name AS database_name,
 ISNULL(bs.backup_finish_date, 0) AS backup_date,
 ISNULL(CASE bs.[type]
  WHEN 'D' THEN 'Full'
  WHEN 'I' THEN 'Differential'
  WHEN 'L' THEN 'Transaction Log'
  END, 'NEVER') as type ,
 bm.physical_device_name,
 dbo.fc_FileExists(bm.physical_device_name) as DoesFileExists,
 bs.backup_size / 1024 / 1024 AS backup_size,
 DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date) AS duration
 FROM master.dbo.sysdatabases AS sd
 LEFT OUTER JOIN msdb..backupset AS bs ON sd.name = bs.database_name
 LEFT OUTER JOIN msdb..backupmediafamily AS bm ON bm.media_set_id =     bs.media_set_id
  GROUP BY
   sd.name,
   bm.physical_device_name,
   bs.backup_size / 1024 / 1024,
   DATEDIFF(mi, bs.backup_start_date, bs.backup_finish_date),
   bs.backup_finish_date,
   bs.[type],
   dbo.fc_FileExists(filename)
 order by bs.backup_finish_date desc


How to move all non-unique and non-clustered indexes to a different filegroup?

Found the answer.  See comments on how to make it work for SQL Server 2005.


Is is possible to handle a UserAbort or ClientTimeOut within a Stored Procedure?

AFAIK there is no way in TSQL to deal with this other than the way you currently are with XACT_ABORT. There are no structures that TSQL knows (or even cares about) as everything this deals with lives outside of TSQL.

What is actually happening is called an "Attention" event which could range from a connection closing to an actual cancel (for example the sqlcommand.cancel() method). Since this lives outside of the actual query, it just tells SQL Server that something happened and that it needs to clean up some stuff. The actual query has no idea what is going on.

Try/Catch is for query execution errors such as diving by 0, but not for any other type of errors. For example, compile/parse errors are not caught as the try/catch had never run. Since attention signals are not part of query execution (again, it's outside of that scope) it doesn't know anything about it and thus is not called as no execution exception has happened (according to the query itself).


A system scope / boundary diagram is sometimes also called a level zero data flow diagram or a context diagram See here for the Wiki on DFDs and here for System Context Diagrams.

Whereas an ERD is a model of data at rest, DFDs are a model of data "moving" through processes.  Before RDBMS was the predominant data storage mechanism, data was kept in files that were accessed sequentially and was processed a record at a time.  Each time the data was processed it was written into a new file.  In this way, you could think of data "flowing" from one place to another, transforming as it went.  Each data store was shown - often as a rectangle.  Processes were shown as circles ("bubbles") and flows were shown as lines or arrows.

The level zero DFD shows the whole system as a single bubble.  Outside of the bubble are external actors (e.g. users of various types) other systems, and any other data sources and sinks that you might be concerned with.

The idea of a DFD is to incrementally decompose your system into smaller and smaller processes, until the whole system is described.  The level 0/context diagram is the starting point and you drill into each bubble as deeply as necessary to develop a complete system description.

In modern systems, especially where data is stored in RDBMS, the data flow paradigm is not always an especially good fit.  However, the context diagram, or level 0 DFD is still useful for describing the scope or boundary of the system in terms of data sources and sinks.


ORDER BY that doesn't contain repeating values

You cannot avoid in it in all cases (if more than 50% of the rows have the same value in the column.) One way to achieve a "shuffle" result, similar to what you want is by using window function:

WITH cte AS
  ( SELECT *,
           ROW_NUMBER() OVER (PARTITION BY colX) AS rn
    FROM tableX
  )
SELECT *
FROM cte
ORDER BY rn, colX ;

The above will not avoid all cases though. If for example, the values in the column are 1,1,1,1,2,2,3, you'll get:

1,2,3,1,2,1,1

and not the (better):

1,2,1,3,1,2,1


Get count of relationship rows between three tables that are not duplicates

I would need more information about your business restrictions to write an ideal query, but here's my advice given my interpretation of what you've said so far.

First thing you need to do is more clearly define what you are trying to achieve.

  So in cases where an article node is related to another node twice, both as a section and as a department, I would like to COUNT that only once.

Since your final result set will be grouped by departments and sections, "counting it only once" means you need to pick where they should be counted.  For example:

   content_field_department         content_field_section
+-----+----------------------+   +-----+-------------------+
| nid | field_department_nid |   | nid | field_section_nid |
+-----+----------------------+   +-----+-------------------+
|  1  |          2           |   |  1  |         3         |
+-----+----------------------+   +-----+-------------------+

should nid 1 be counted in the list for department nid 2 or counted for section nid 3?  Based on your question, you may want to only count it for departments.  In your current query drafts, you are trying to exclude the issue case from both counts, meaning you are effectively counting it 0 times instead of 1 (your goal) or 2 (what you are trying to avoid).

Here's a draft query as a starting point:

select
    DeptOrSect,
    nid,
    title,
    count(1)
from (
    select
        'DEPT' as DeptOrSect,
        dept.nid,
        dept.title,
        link.nid as article_nid
    from node dept
    join content_field_department link on link.field_department_nid = dept.nid
    union all
    select
        'SECT' as DeptOrSect,
        sect.nid,
        sect.title,
        link.nid as article_nid
    from node sect
    join content_field_section link on link.field_section_nid = sect.nid
    where not exists (
        select 1
        from content_field_department dept
        where dept.nid = link.nid
    )
) as x
group by DeptOrSect,
    nid,
    title;


Current Active Session on a Oracle Table

There's a feature of the Tuning Pack called Real Time SQL Monitoring. It's available in SQL Developer, OEM, or theoretically via some package calls - but you really want a GUI for this report.

You can see each step of the plan as it executes and see what the wait events are. You'll also see right away if it's not running with the degree of parallelization that you're expecting.

Our resident database performance expert Maria Colgan has an excellent write-up on the topic here.


The best way to enforce uniqueness in this scenario then is to not split the artist "types" into separate tables. Normalizing your schema will show that there are fields in common, so at the least your artists table would contain id, artist_type ("band" or "singer"), and name. Any fields that are not shared should be split into more generalized tables.

EDIT:

After thinking about schema designs I've seen for music catalogues, the following comes to mind.

Be consistent in nomenclature. Yes, an "artist" is a "band" or a "singer." But a "singer" is also a "band" in the sense that they are a performer onstage, billed under a specific name ("Tori Amos" and not "Amos, Tori"), and usually backed by a band of musicians. A "singer" is also an "artist," but so is every member of a "band."

In other words, don't get caught up in what type of performer it is, just call it an "artist" if that is the name you've decided on. "Artists" can have 1 - n "members," which are just "people" with the normal details you would want to store about people. So your schema would consist of:

CREATE TABLE artists
( id number(10) not null,
  artist_name varchar2(50) not null,
  contact varchar2(50) not null,
  artist_type varchar2(50) not null, /* ...not sure you really need it */
  CONSTRAINT artists_pk PRIMARY KEY (id)
);

CREATE TABLE people
( id number(10) not null,
  first_name varchar2(50) not null,
  last_name varchar2(50) not null,
  contact_number varchar2(50) not null
);

CREATE TABLE artist_members
( id number(10) not null,
  artist_id number(10) not null,
  member_id number(10) not null,
  CONSTRAINT members_pk PRIMARY KEY (id),
  CONSTRAINT artists_fk FOREIGN KEY (artist_id)
    REFERENCES artists(id),
  CONSTRAINT members_fk FOREIGN KEY (member_id)
    REFERENCES people(id)
);


If I were you, I would switch all data to InnoDB. Table locking/row locking has long been discussed by many. I would always choose InnoDB hands down. However, there is another profound reason for choosing InnoDB...CACHING.
While most people boast that MyISAM is faster for reads, most people forget that the many cache for MyISAM, which is called the key cache (set by key_buffer_size), only caches index pages from .MYI files. It never caches data pages. It has an official maximum of 4GB in 32-bit Systems. 8GB is best maximum for 64-bit.
The InnoDB Buffer Pool caches the data and index pages. Depending on the server your have, you can cache up to the entire dataset in RAM. You can tune InnoDB for up to 80% RAM and 10% for DB Conenctions, and leave 10% for the OS. This is true even for different operating systems.
I have recommended these things for Drupal customers with marvelous success. It applies to Wordpress just as well. I have provided DB support for clients with WordPress. Same improvements.
You can always configure memory for InnoDB more effectively that you can more MyISAM. There is always a way to tweek InnoDB to suit your performance needs. As your data grows, it will eventually become a requirement.
UPDATE 2011-11-21 11:44 EST
If your complete dataset is small enough, you could execute a SELECT query on every table you have right after mysql starts up.
For all tables that are InnoDB and/or MyISAM, run this query:
SELECT DISTINCT
    CONCAT('SELECT ',ndxcollist,' FROM ',
    db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (
    SELECT
        engine,table_schema db,table_name tb,index_name,
        GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
    FROM (
        SELECT
            B.engine,A.table_schema,A.table_name,
            A.index_name,A.column_name,A.seq_in_index
        FROM
            information_schema.statistics A INNER JOIN
            (SELECT engine,table_schema,table_name
            FROM information_schema.tables
            WHERE engine IN ('InnoDB','MyISAM')) B
            USING (table_schema,table_name)
        WHERE
            B.table_schema NOT IN ('information_schema','mysql')
            AND A.index_type <> 'FULLTEXT'
        ORDER BY
            table_schema,table_name,index_name,seq_in_index
        ) A
    GROUP BY
        table_schema,table_name,index_name
) AA
ORDER BY
    engine DESC,db,tb
;

This will output every possible SELECT query you need to run that will summon all indexes to be referenced. Place this query in a file called /root/MakeSelectQueriesToLoad.sql. Run the script and collect the output /root/SelectQueriesToLoad.sql. Finally, run it:
mysql -u... -p... -AN < /root/MakeSelectQueriesToLoad.sql > /root/SelectQueriesToLoad.sql
mysql -u... -p... < /root/SelectQueriesToLoad.sql

This will definitely preload all index pages into the InnoDB Buffer Pool and MyISAM Key Cache. If all your data is InnoDB, make two changes:

replace WHERE engine IN ('InnoDB','MyISAM') with WHERE engine='InnoDB'
replace CONCAT('SELECT ',ndxcollist,' FROM ', with CONCAT('SELECT * FROM ',

This will also populate more data pages into the InnoDB Buffer Pool.
FINAL NOTE : Make sure the InnoDB Buffer Pool is large enough to hold all your InnoDB Data


MongoDB recommends XFS in official docs. But MariaDB (and MySQL have no file system best practises/parameter tuning).

Quote from What is the best Linux filesystem for MariaDB?

  Trying to figure out which filesystem gives the best performance may
  be fun, but the filesystem won't make a large difference in the
  performance of your MariaDB server. Your hardware is the most crucial
  factor in eking out the most speed. Fast hard drives, discrete drive
  controllers, lots of fast RAM, a multi-core processor, and a fast
  network have a larger impact on performance than the filesystem. You
  can also tailor your MariaDB configuration options for best
  performance for your workloads.

Don't forget to mount fs with noatime.


SQL join on col1 if present else use col2

The problem here is that using a function or or around your join predicate means that the database engine can not use any indexes that you might have defined on the relevant columns for seeking so it is forced to table scan. On a large table this can take a long time. Worse, if this is part of a larger query you might find the scanning must be done multiple times.
If you have useful indexes on T2.col1 and T2.col2 (and T1.col1) then the following is likely to be more efficient, assuming that what you actually want is an INNER JOIN:
-- find matches for col1
SELECT *
FROM T1
JOIN T2 ON T1.col1 = T2.col1
-- join these results together
UNION ALL
-- find matches for col2 except where will have already matched col1
SELECT *
FROM T1
JOIN T2 ON T1.col1 = T2.col2 AND T2.col1 IS NULL

Because each of the UNIONed SELECTs can avoid table scanning this will be more efficient than the single SELECT, assuming appropriate indexes exist for them to use.
If you do want a LEFT JOIN (i.e. you want to include rows from T1 that don't match those in T2 at all) then you need an extra clause for this:
-- find matches for col1
SELECT *
  FROM T1
  JOIN T2 ON T1.col1 = T2.col1
-- join these results together
UNION ALL
-- find matches for col2 except where will have already matched col1
SELECT *
  FROM T1
  JOIN T2 ON T1.col1 = T2.col2
          AND T2.col1 IS NULL -- this will match above and you'll get a duplicate
UNION ALL
-- find rows in T1 where there is no match in T2
SELECT *
  FROM T1
  LEFT JOIN
       T2 ON T1.col1 = T2.col2
 WHERE T2.col1 IS NULL
   AND NOT EXISTS (SELECT * FROM T2 WHERE T2.col2 = t1.col1)

If you were not using SELECT * (i.e. had a fixed column list instead of the wildcard *) then you might find that third clause easier to understand as:
   SELECT [columns-to-project]
  FROM T1
 WHERE NOT EXISTS (SELECT * FROM T2 WHERE T2.col1 = t1.col1)
   AND NOT EXISTS (SELECT * FROM T2 WHERE T2.col2 = t1.col1)

(whether this would be more or less efficient, or exactly the same, depends on your database's query planner and the indexes you have defined).
Note that the first SELECTs assume that you have no rows in T2 with a col2 that matches T1.col1 = T2.col2 and also have a col1 that matches T1.col1 = T2.col1, and that there are none with a valid T2.col2 but a T2.col1 whcih does not match any T1.col1. If such rows do exist then extra work is needed, I've not included that because your current queries would skip those matches just as my examples will.
Note also that any claims of efficiency of the above examples relies on appropriate indexes existing.
For a more concrete answer you will need to edit your question to provide:

The table structure, particularly what indexes are defined.
A more detailed description of your data, particularly with respect to what goes in T2.col1 & T2.col2 and when.
What database system you are using (there might be specific features in your DB that could help with this sort of query and table design), unless you particularly need a database-agnostic answer.


ER Modeling; multiplicity basics - one to many or many to many?

I think your design could be stated more formally as:

One company has many projects.
One project belongs to one company.
One project has one or more activities.
One activity has exactly one activity type (prototyping, design).
One activity belongs to exactly one project.

So, if that's true, you have the following tables:

COMPANY
CompanyID
....

PROJECT
ProjectID
....

ACTIVITY_TYPE
ActivityTypeID
Name (e.g. "Prototyping", "Design")

ACTIVITY
ActivityID
ProjectID
ActivityTypeID
StartDate
EndDate
...


I could see no answer to this since couple of months. I have done my research and have put together in the below link:
http://sqlgeekspro.com/ssrs-encryption-key-backup-information/


SQLite return sum of duplicate rows based on array

Create a (virtual) table with the desired names, then join it with the actual table:

WITH my_array(name) AS (
  VALUES ('melon'), ('melon'), ('apple')
)
SELECT SUM(lbs)
FROM fruits
JOIN my_array USING (name);


Best database design for website ranking

Will you be ranking the billions of web sites?  If so, then on-the-fly and recompute-all are out of the question.

Instead, have a background task working on the problem.  For display, show the info that is in the table, possibly out of date by a day or two (depending on how long it takes to recalc).

Put the ranking in a separate table, with only website_id and rank.  This will make replacing the entire table after the recalc atomic and fast.  Perhaps a second table with website_id, country_code (CHAR(2) CHARACTER SET ascii), and rank.

You have not explained how the ranking is calculated, so I can't give you any insight in how to do it as a background task.


SQL Server has encountered occurences of I/O requests taking longer than 15 seconds

This is far less often a disk issue, and far more often a networking issue. You know, the N in SAN?

If you go to your SAN team and start talking about the disks being slow, they're gonna show you a fancy graph with 0 millisecond latency on it and then point a stapler at you.

Instead, ask them about the network path to the SAN. Get speeds, if it's multipathed, etc. Get numbers from them about the speeds you should be seeing. Ask if they have benchmarks from when the servers were set up.

Then you can use Crystal Disk Mark or diskpd to validate those speeds. If they don't line up, again, it's most likely the networking.

You should also search your error log for messages that contain "FlushCache" and "saturation", because those can also be signs of network contention.

One thing you can do to avoid those things as a DBA is make sure that your maintenance and any other data-heavy tasks (like ETL) aren't going on at the same time. That can definitely put a lot of pressure on storage networking.

You may also want to check the answers here for more suggestions: Slow checkpoint and 15 second I/O warnings on flash storage

I blogged about a similar topic here: From The Server To The SAN


Unable to Create COMMIT inside PostgreSQL 11.5 Procedure

The

BEGIN;

is wrong and will cause an error. You cannot start a transaction inside a procedure, because there is already an active transaction.

You can end a transaction, which implies that a new transaction is started immediately.

Without the BEGIN;, your procedure works just fine.

The problem must be with the way you are calling it. As the documentation says:

  Transaction control is only possible in CALL or DO invocations from the top level or nested CALL or DO invocations without any other intervening command. For example, if the call stack is CALL proc1() → CALL proc2() → CALL proc3(), then the second and third procedures can perform transaction control actions. But if the call stack is CALL proc1() → SELECT func2() → CALL proc3(), then the last procedure cannot do transaction control, because of the SELECT in between.

There is probably a SELECT in your call stack.

Another possibility is that you explicitly started a transaction with BEGIN before calling the stored procedure.

This does not work either, which is an undocumented implementation restriction that may be fixed in the future. See this thread for reference.


Rebinding a SQL Server user to its login after a restore with this user

Unfortunately no.  Remember that a database principal and a server principal are two separate objects.  Until you fix the orphan the server principal MyUser has no access to MyDatabase even though there is an orphaned user called MyUser in the database.

The other way to fix an orphan is to change the SID of the server principal to match that of the database principal.  This does have a risk of creating orphans in other databases and even more importantly in this case to do it you have to drop the principal and re-create it with the new SID.  Which of course isn't possible using that particular ID.

Your only method is going to be to fix this using a separate id with the appropriate permissions.  That could be an id with sysadmin permissions (which would have permissions to connect to the database even without an associated database principal) or an id that has DBO access on the database.  Note those are examples, there are several other permissions available that will allow you to modify server/database principals.


I'd go with the classic Cross-Tab approach together with ROW_NUMBER to have "something" to group by:

;
WITH cte as
(
    SELECT ROW_NUMBER() OVER (PARTITION BY Type ORDER By Data) as pos,
    *
    FROM -- use your table name instead of the VALUES construct
    (VALUES
    ('2012-01-25 13:48:00.000' ,1),
    ('2012-01-25 13:49:00.000',2),
    ('2012-01-26 13:48:00.000',1),
    ('2012-01-26 13:49:00.000',2)) x(Data,Type)
)
SELECT
MAX(CASE WHEN Type = 1 THEN Data ELSE NULL END) AS [1],
MAX(CASE WHEN Type = 2 THEN Data ELSE NULL END) AS [2]
FROM cte
GROUP BY pos


You have two caches, one for each Storage Engine.
MyISAM
Only caches index pages in the key buffer (sized of key_buffer_size). All data pages are read from the .MYD of the MyISAM table.
InnoDB
Three Components

This would be the InnoDB Buffer Pool (sized by innodb_buffer_pool_size). It is used to collect all data and index pages you have read to accomplish all your queries.
You also have the InnoDB's Transaction Logs catching transactional changes on disk (ib_logfile0, ib_logfile1)
The changes are written redundantly in ibdata1 in the event of a crash so InnoDB Crash Recovery can bring the data back to a consistent state.

See the Pictorial Representation of InnoDB's Architecture to see the Buffer Pool and Transaction  Logs
Every time you read from or write to an InnoDB table, the .frm is checked (disk I/O), the data dictionary in ibdata1 is checked (disk I/O), and MVCC for transaction isolation is written in ibdata1 and transaction logs if there are incoming writes to the same table (disk I/O). Even a read-heavy InnoDB environment incurs disk I/O.
EPILOGUE
You could easily expect some pages to be rotated out. This is especially the case when the caches are configured too small. In the case of MyISAM, since data pages are read from disk each time, index pages can rotate in and out. As for InnoDB, it's data and index pages.
The result sets may have to be recreated each time. Sometime, this contribute to all caches activity.


Only 'master' inside the system databases in Azure Managed instance

It was just because of the SSMS version. https://stackoverflow.com/questions/54695590/create-database-using-ssms-gui-fails-for-azure-sql-managed-instance

Used 17.9.1 and can see things lined up properly. Probably the previous version was treating as Azure database.


Maintenance Plan Backups failing since KB4013106 installed

Edit the maintenance plan and change the credentials used for Local server connection to Use Windows NT Integrated Security.

Open the Maintenance Plan in Design view.
Click Manage Connections
Select Local server connection and click Edit
Under Enter information to log on to the server, choose Use Windows NT Integrated security
Save your changes
Execute the plan/schedule to verify that this has cured the problem

No idea what causes this issue, but for us, it seems to have been directly after Hotfix 4422 for SQL Server 2016 (KB4013106) was installed automatically by Windows Update.


Stored Procedure Is Slower Than Query Due to Collation

When declaring HEX strings such as UUIDs, or when declaring other strings with limited character set needs, such as country_code, postal_code, zip_code, etc, declare them to be CHARACTER SET ascii.  It can be applied just to the column, letting the rest of your columns be utf8mb4 or whatever.

And be consistent between tables so that JOIN works efficient.  And, as Rolando says`, be consistent with Stored Routine arguments, etc.

You can either let the collation for ascii be defaulted, or you can be explicit.  But be sure to be consistent.  Mismatched collation is as bad for performance as mismatched character set.

(If such fields are already latin1, there is no need to switch to ascii.)

UUIDs have a nasty problem when the dataset becomes huge.  Because of the seeming randomness of the strings, caching becomes useless when the table is bigger than cache.  One small improvement is to convert to BINARY(16) to save space and make it slightly more cacheable.  More discussion.

BTW, SHOW CREATE PROCEDURE procname \G provides the info at the end.  For example:

mysql> show create function GCDist\G
*************************** 1. row ***************************
            Function: GCDist
            sql_mode:
     Create Function: CREATE DEFINER=`root`@`127.0.0.1` FUNCTION `GCDist`(
    ...
BEGIN
    ...
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: latin1_swedish_ci


Oracle 12c query performance varies based on schema/user

You can try to check what the current consumer group for the different sessions are. I.e. log in as SAMPLE and USER1 and run

SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION
    WHERE USERNAME IN ('SAMPLE','USER1','DBA1');

You can also check what the actual plans are assigned as DBA:

SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS
    WHERE GRANTEE IN ('SAMPLE','USER1','DBA1');

This is designed to grant different resources for users (and admins) and might explain what you see. The plan should actually be shared across all executing users (it remembers the user which parsed it first). Just to be sure, auto trace the execution for the two different users.


How do I load this SAS text file to SQL Server 2005

I would write a python script that would,

parse the columns descriptor file, get their widths and names
iterate over all the data in the input file, splitting out the data into variables
construct a sql query with these variables to the sql server with SQLAlchemy to be sent one by one or as a batch of rows or 1000 at a time

This script would take the columns descriptor file and the data file. The script would also have a database config file containing the database connection string or you can pass that as a third parameter.

I would write this for you, but I don't have time right now. This is a neat problem to solve. Python is perfect for this however. I wish my SAS problem was so easy.


how to clear only the query plans from the current session?

Marcello, it seems you have the answer in your question, albeit with one minor modification.

The query:

SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%/* GetOnlineSearchResultsMonday %';

contains the term '%/* GetOnlineSearchResultsMondy %', which is a wild-card search used to locate the specific plan to remove from the cache.  If you ran it with a search term contained within the query you are attempting to tune, it should return a plan handle that can then be used with DBCC FREEPROCCACHE:

-- Remove the specific plan from the cache using the plan handle
DBCC FREEPROCCACHE (0x05000800F7BA926C40C15055070000000000000000000000);

I would suggest perhaps using the following to identify the query:

SELECT cp.plan_handle, st.[text]
FROM sys.dm_exec_cached_plans AS cp
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE [text] LIKE N'%CASE WHEN opgv.lngpaymentID IS NULL THEN NULL%';

Assuming that string is not used in other queries, the results should only return a few rows.  You would then look at the st.[text] column to ensure you have the correct plan_handle, then use that plan_handle with DBCC FREEPROCCACHE to remove it from the plan cache.

As a side-note, I would be very careful running an SSMS session against a live production system - it can be a recipe for disaster, since it is very easy to forget your are working on the production server.


How to change default collation for any charset in MySQL?

This is futile.  Any user can explicitly state what collation to use, thereby defeating the 'default'.

A better solution is to upgrade to MySQL 8.0.  It defaults to utf8mb4 and (I think) utf8mb4_0900_ai_ci

mb4 is useful for Emoji and Chinese.

0900 comes from Unicode 9.0, which is newer than 5.20, which is newer than 4.0, which you are suggesting.


Functional dependency not detected for some columns

I found out what is the difference.

When I write

SELECT child_id, type
FROM children
JOIN parents USING (parent_id)
GROUP BY child_id, parent_id

Postgres seems to assume I mean GROUP BY child_id, children.parent_id and even though those two are guaranteed to be identical by the JOIN, they don't seem to count as functionally dependent. If instead I write

SELECT child_id, type
FROM children
JOIN parents USING (parent_id)
GROUP BY child_id, parents.parent_id

Postgres recognizes the functional dependency and allows type to be in the SELECT expression.


Use user-defined variable for to convert the date value from dd\mm\YYYY format to the date/datetime datatype value during import:

LOAD DATA INFILE
....
(field1, field2, ..., @date, ...)
SET datefield = STR_TO_DATE(@date, '%d\/%m\/%Y');


Connect to SQL Server behind NAT from remote machine

I doubt that it's easy to do hole punching with TCP or switch the process connected to a port.

Try this for a reverse proxy through the firewall:
http://www.dest-unreach.org/socat/doc/socat-gender.txt

This solutions relies on socat, a swiss-army-knife like network tool which can connect almost any two endpoints and is bidirectional, unlike netcat or the |-pipes in command processors. It should be available for Windows, too.

You can either use socat easily to forward a TCP connection, or in this case: Reverse the "gender" of it: You'll have two socats running, one on each side of the firewall. One continuously connects to the outside. On the outside, socat will wait for a connection from someone else. Only if he connects, the outside socat will accept a connection from the inside socat. Once the inside socat connects to the outside socat, it will connect to the SQL-server. Voila, you can connect from outside to the SQL-server inside, and from the point of the firewall and from the point of TCP the connection looked like it was actually from inside to outside.

To copy from the link:

  1) Start the double client on the inside server
  // every 10 seconds, it tries to establish a connection to the outside host.
  // whenever it succeeds, it forks a sub process that connect to the internal
  // service and starts to transfer data

$ socat -d -d -d -t5 tcp:outside-host:80,forever,intervall=10,fork tcp:localhost:80

  2) Start double server on the outside client
  // wait for a connection from a local client. whenever it accepted it, forks
  // a subprocess that tries to bind to the socket where the inside double
  // client tries to connect (might need to wait for a previous process to
  // release the port)

$ socat -d -d -d tcp-l:80,reuseaddr,bind=127.0.0.1,fork tcp-l:80,bind=outside-host,reuseaddr,retry=10


will subscriber be available, if publisher or distributor is down?

There are locks taken on the subscriber during data transfer which can cause blocking on the subscriber - so you can definitely encounter issues there (and there are options to modify this behaviour at the risk of not being able to rollback failed transactions).

However there aren't any other magical locks or connections between the servers.

But also when the publisher/distributor go down I've never seen the insert also fail to go down and not release the locks (aside from standard rollback time). I'd hesitate to say it wasn't possible but it would be abnormal and I haven't seen it.

The much more common scenario is that the subscriber goes down, log records accumulate in the publisher, fill the disk and your publisher goes down. A large log file and monitoring of backups, space, and the replication itself are essential.


SQL Server 2019 executes unreachable code

This is a bug with the inlining of Scalar UDFs (or perhaps a bug with the query optimiser that is being exposed more by scalar UDF inlining). You can use WITH INLINE = OFF to turn off inlining for that function.

Using a variable instead of a constant shows a bit more detail

declare @myYear int = 0

SELECT dbo.Repro(@myYear);

Node 5 defines Expr1000 = CASE WHEN [@myYear]<>(1990) THEN (1) ELSE (0) END
Node 2 defines [Expr1003] = Scalar Operator(CONVERT_IMPLICIT(datetime,datefromparts([@myYear],(1),(1)),0))

These expressions are simplified when using the literal 0 to 1 and CONVERT_IMPLICIT(datetime,datefromparts((0),(1),(1)),0) respectively.

The datefromparts(0 will throw the error when evaluated.

Node 6 defines Expr1002 = CASE WHEN [Expr1000] = (1) THEN (1) ELSE (0) END

And Expr1002 is used as a passthru predicate on a nested loops join (node 3). On the inside of that nested loops the constant scan (node 7) returns no columns.

So this looks like the same basic issue as the answer here where an expression on the inside of a nested loops protected by a passthru predicate is moved out into an unprotected region.


Is using a Trigger to Insert a new Record into a View seen as bad practice?

Short answer

  No, don't do that.

Longer answer:

What problem are you trying to solve? It seems like maybe it's "I would like to update multiple base tables with a single INSERT tsql command." If that's the case, then... Sure? I guess? Why not? As long as you accept that the scope of the implementation in question to do is pretty limited and clunky. You're really just obfuscating away the multiple steps you need to perform anyway. But what happens when you want to DELETE from that view? Or UPDATE from that view? Do you need other triggers to handle those cases?

At a minimum, if you're commited to trying it out for yourself and discovering the "why nots" on your own, I'd at least recommend modularizing out the separate INSERT statements in that trigger into their own procs. At least then you can reuse spInsertPerson 1, 'Bob', 'Smith' when all you really want to do is insert Bob Smith without mucking about with other side-effecting executions.

Still... I probably wouldn't...


Fromuser and touser are not valid using datapump. If you are trying to change the schema, you can use remap_schema:

REMAP_SCHEMA=source_schema:target_schema

https://docs.oracle.com/database/121/SUTIL/GUID-619809A6-1966-42D6-9ACC-A3E0ADC36523.htm#SUTIL927

Or you can list which schemas to import:

SCHEMAS=schema_name [,...]

https://docs.oracle.com/database/121/SUTIL/GUID-6FEDE3AF-0ED5-46D6-BE91-C3F840291735.htm#SUTIL931


Automatic seek on an index on a calculated column

You’re basically adding a helper-predicate, and that’s not something the SQL engine will do out of the box most of the time. It does for LIKE, in that if you have WHERE col LIKE 'A%', then the query plan will show that it can seek on col >= 'A' AND col < 'b', but most others don’t get picked up.

It’s not a ton of extra work, because it worked out the column value when it populated the index, and the parameter only needs to be shortened once. But I get that you might have a lot of queries to change. Sadly, it’s probably just what you have to do.


First, if you haven't built anything don't use MySQL. If you're doing a new product -- go PostgreSQL. It's better and free.

If you insist on MySQL, at least go MariaDB. While both have their advantages MariaDB is a fork and that's where the community is at. MySQL is Oracle's newly purchased pretend-to-be-open-source-friendly PR campaign.

There is nothing wrong with cPanel for you. That said, database developers don't use cPanel. If you're going to hire someone and they can't administer their database without a 3rd party GUI tool -- RUN.

  Is giving them that link and the login credentials safe?

It depends on safe. When you give login credentials they have at least the level of access granted by intent so look that up. And if your software is out of date and they're malicious usually more than that.

  By safe I mean my website and files and stuff, they wont have any access to any of that or cPanel right?

At that point it's a cPanel question. Often times db-clients leak privileges because they're improperly sandboxed. I can't tell you how many times I get to a psql box which launches right into psql, and I can get out simply doing \!.


Query does not use index when selecting all records

You've used SELECT * which, presumably, has more columns than xploc and trandt2. So SQL Server can easily derive the count by traversing the index pages. It can't get the other columns that aren't in the index from there, so it has to go to the table.

Depending on how many columns are in the table (and a host of other factors), SQL Server can usually make one of two choices in a case like this:

Perform a seek (which looks like a range scan in your case), and then perform key/RID lookups to retrieve the other columns not covered by the index.
Suck it up and perform a scan.

In your case there are probably enough rows satisfied by the WHERE clause that it is actually more efficient to perform the scan.

Some ways to make this more efficient:

Stop using SELECT * - just list the columns you need.
Once you have whittled down to the actual columns you need, change the index to INCLUDE the other columns in the actual SELECT list you need.
You could consider making an index filtered by adding a WHERE clause, depending on whether this exact query is one that is run frequently (and if there is a lot of data pre-2012).

As an aside, why is there no clustered index on this table?


Is it wise to create indexes for the replication server only?

This is a common requirement.  Transactional Replication is typically used to off-load reporting to another server/instance, which can be near real-time in a best case scenario. One of the benefits of Transactional Replication is you can place different indexes on the subscriber(s) to optimize reporting.  Another benefit is that you can choose to replicate only a portion of the data if only a subset is needed for reporting.

So to answer both of your questions:

You are taking the correct approach.
This is a common requirement and is not a bad idea at all.

Just keep in mind that the indexes that you place on the subscriber(s) will need to scripted out and included in a post-snapshot script in case you ever reinitialize the subscriber(s).


Cassandra in a Public Cloud - Which Snitch?

The biggest advantage of the GosspingPropertyFileSnitch is that you can easily migrate your cluster to another cloud without downtime, just by creating a new DC in another cloud, replicate data to that DC and decomission old DC. With provider-specific snitches, such migration may not be possible at all.


Cannot remove Database from Distributed AlwaysOn Group on Secondary Windows Cluster

You can use the below statement to remove the database from "DistAG".

Execute the below statement on primary node of "DistAG".

ALTER DATABASE Genesis SET HADR OFF;

Once you execute the above statement, the database state will turn to restoring mode, and then you can drop the database.


If you have Management Studio Express installed (any version prior to SQL Server 2012 SP1), it simply doesn't contain any facility for SQL Server Agent (no matter which edition you're trying to manage). You can fix this by getting the very latest version from here:

http://www.microsoft.com/en-us/download/details.aspx?id=43351

You want the file SQLManagementStudio_x64_ENU.exe (or the one with x86 in the name if you have a really old 32-bit machine). This is the first version of Management Studio that has been offered fully functional and license-free.

Once you install this version of Management Studio, you will have access to SQL Server Agent when managing any instances of the SQL Server engine that support SQL Server Agent. Of course that won't help you create jobs on instances of SQL Server Express, since it doesn't have Agent at all.


Is it possible to query uncommitted data of a different session?

no, "Oracle Database doesn't use dirty reads, nor does it even allow them"


What happened to my-*.cnf in MySQL 5.6.8?

You know what they say, about "when all else fails..."

  This release continues the process begun in MySQL 5.6.6 of making
  changes to the default values of server parameters. The motivation for
  these changes is to provide better out-of-box performance and to
  reduce the need for database administrators to change settings
  manually. These changes are subject to revision in future releases as
  we gain feedback.

This is all explained in the Release Notes for MySQL 5.6.8.

  On Unix platforms, mysql_install_db now creates a default option file
  named my.cnf in the base installation directory. This file is created
  from a template included in the distribution package named
  my-default.cnf. You can find the template in or under the base
  installation directory. When started using mysqld_safe, the server
  uses my.cnf file by default. If my.cnf already exists,
  mysql_install_db assumes it to be in use and writes a new file named
  my-new.cnf instead.

  The my-default.cnf template replaces the older sample option files
  (my-small.cnf, my-medium.cnf, and so forth), which are no longer
  distributed.

Without question, innodb_buffer_pool_size is the single most important tuning parameter for a MySQL installation.

For "sensible defaults," start with the defaults.

To put it another way...

  "Tuning" your server can be a stunning waste of time.

-- Schwartz, Baron; Zaitsev, Peter; Tkachenko, Vadim (2012-03-05). High Performance MySQL: Optimization, Backups, and Replication (Kindle Location 12182). OReilly Media - A. Kindle Edition.


(mysql) Database replication between 2 DB while 3rd DB as backup

Dual-Master, where you write to both masters, is full of problems.  You need to carefully set the two auto_increment_* "variables".  You need to make sure users cannot insert the same PRIMARY or UNIQUE values into different masters.  If 'East' goes down, the Slave is hard to reconnect.  If a hurricane hits the East, you lose both the master and slave there.  Etc.

A slight improvement is "Dual-Master, Single writer".  That is, you have the configuration you described, but clients never write to more than one of the Masters.  The configuration is more stable, but the failover process is perhaps more complex.

Instead, consider Galera (in MariaDB or PXC), which solves many of the problems.


I turned your PS into a function, just changing the calls, ran it through the PS ISE, and it worked just fine for me in testing, outputting the data to the location. I would ensure that you have the ability to write to the root of the C: drive. Typically this can require admin permissions. It's possible you are swallowing the error somewhere.

function ProcToXmlFile {
param
(
    [string]$outputType,
    [string]$filename
    )

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sys.databases for XML AUTO"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0]
if ($outputType  -eq "Text")
{
$DataSet.Tables[0] | format-table -auto > $filename
}

if ($outputType  -eq "xml")
{
$DataSet.Tables[0] |Export-Clixml $filename
}
}

ProcToXmlFile "xml" "c:\temp\test.xml"

Created the file in the correct location with the contents:

<Objs Version="1.1.0.1" xmlns="http://schemas.microsoft.com/powershell/2004/04">
  <Obj RefId="0">
    <TN RefId="0">
      <T>System.Data.DataRow</T>
      <T>System.Object</T>
    </TN>
    <ToString>System.Data.DataRow</ToString>
    <Props>
      <S N="XML_F52E2B61-18A1-11d1-B105-00805F49916B">&lt;sys.databases name="master"/&gt;&lt;sys.databases name="tempdb"/&gt;&lt;sys.databases name="model"/&gt;&lt;sys.databases name="msdb"/&gt;&lt;sys.databases name="HealthCheck"/&gt;&lt;sys.databases name="RestoreAttachTEST"/&gt;</S>
    </Props>
  </Obj>
</Objs>

Here's an updated version (a little hacky, I know) that writes the header to a file, and then just the XML data that is produced. See if this gets you a little closer.

function ProcToXmlFile {
param
(
    [string]$outputType,
    [string]$filename
    )

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server=localhost\sql16;Database=master;Integrated Security=True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = "select name from sys.databases for XML AUTO"
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$DataSet.Tables[0] | Format-Wide -AutoSize
$Headerstring = '<?xml version="1.0" encoding="UTF-8?>'
$Headerstring | Out-File $filename
if ($outputType  -eq "Text")
{
$DataSet.Tables[0] | Format-Table -HideTableHeaders | out-file $filename -Width 5000 -Append
}

if ($outputType  -eq "xml")
{
$DataSet.Tables[0] |Export-Clixml $filename
}
}

ProcToXmlFile "text" "c:\temp\test.xml"


The technique to use is called a recursive common table expression, or recursive CTE. The Microsoft SQL documentation is comprehensive and there are a lot of third-party blog posts to help explain.

Here's an implementation for your specific question.

This part just creates the sample data from your question. It uses a temporary table (one that uses the # symbol)

    create table #t (BookGroupID int, GroupName varchar(100), ParentGroupID int)
    insert into #t
    values
    (0      ,'Primary',    0    ),
    (1      ,'abc',        0    ),
    (2      ,'abd',        0    ),
    (3      ,'abe',        0    ),
    (4      ,'cbc',        1    ),
    (5      ,'ceg',        2    ),
    (6      ,'cjd',        3    ),
    (7      ,'hjd',        5    ),
    (8      ,'bjy',        5    ),
    (9      ,'mfs',        4    ),
    (10     ,'ikl',        7    ),
    (11     ,'hjy',        8    ),
    (12     ,'mnf',        5    ),
    (13     ,'aws',        10),
    (14     ,'qws',        11),
    (15     ,'aqe',        13);

This is the value from which to start looking:

    declare @parent int = 2;

This is the recursive CTE. You do not have to call it "cte" as in this example. The initial semicolon is redundant. It ensures the previous statement is properly ended if the programmer has been lazy. It does no harm to leave it.

    ;with cte
    as (select BookGroupId
        from #t as t
        where BookGroupId = @parent
        UNION ALL
        select t.BookGroupId
        from #t as t
        join cte
        on t.ParentGroupId = cte.BookGroupId)
    select *
    from cte
    order by BookGroupId;


It sounds like what you probably want is to:

Create a role to own all the common tables and schema, or just use your own if you really will always be the only one with full control of the main tables.
Create another role you intend to give only read-only access to the shared tables and schemas. GRANT that role rights using GRANT SELECT ON ALL TABLES IN SCHEMA [x] for each shared schema. You may also want to ALTER DEFAULT PRIVILEGES to make sure this role has read rights on any new tables created in these schemas too.
Now GRANT each user membership of the read-only access role with INHERIT.
For the private schemas, create a schema the same as the user's username with CREATE SCHEMA [username] AUTHORIZATION [username] or the older style where you create the schema then ALTER SCHEMA ... OWNER TO.

See the postgresql manual for the detailed syntax of all of the above commands. Start with user management, part of the broader database administration topic that includes grant management etc. The PostgreSQL manual is detailed, comprehensive and readable: reading it is strongly recommended.


SQL server CPU 100% - Turn on Optimize for Adhoc Workload

You can turn on "Optimize for Adhoc Workloads" with a high confidence.

The only potential effect this has, is when a plan is compiled the second time, it takes a few microseconds longer. This is because the first time a plan is cached, it only records the stub.

After that, the full plan is already cached, and you'll see the benefits.

Aside: if you're seeing high CPU as a result of a new OR/M, I'd look at something called implicit conversion.

What happens is that on string data types (CHAR, VARCHAR, NCHAR, NVARCHAR), you might be seeing Unicode vs ASCII conversions between the database and your application code.

The best way to look for this is to make sure your ORM matches the string data types correctly. If it's VARCHAR in the database, make sure the ORM doesn't submit parameters as NVARCHAR.

More reading here: Convert Implicit and the related performance issues with SQL Server.


Can multiple cores be used by PostgreSQL to run code in sections (cursors) of a table effecting parallelism?

Actually, PostgreSQL 9.6 can run parallel (read) queries. Parallel writes aren't supported yet.

The approach you suggest would work, but some care would be required to ensure it was consistent, since each cursor would be a different session with a different snapshot of the database.

You could instead just run 10 UPDATEs from 10 different clients. Same effect, simpler, and probably faster. Just make sure the targeted row ranges don't overlap.


My take on this is that you may perhaps want to try using a parent-child dimension, so the model will allow for a theoretically infinited amount of levels. That way, you can connect facts to each level of the hierarchy.

Here's an example:

CREATE TABLE dimGeography (
    id
    parent_id
    geographyType
    geographyCode
    geographyName
    PRIMARY KEY (id)
);

CREATE TABLE factDemographics (
    date
    geography_id
    monthlyIncome
    workingPopulationAge
    numberOfPeople
    PRIMARY KEY (date, geography_id)
    FOREIGN KEY (geography_id) REFERENCES dimGeography (id)
);

The advantages of this approach are:

you're free to add or remove levels as you like over time without changing the data model
you can connect facts (demographics) to each level
no duplicate measure columns, i.e. just one column called "number of people"
future-proof: some countries or regions may use different types of levels - for instance, not all countries use "state".

The disadvantages that I can think of are:

you may have to redesign your existing (?) dimension or fact table
your BI tool may not be able to handle parent-child dimensions
more complex SQL queries when aggregating the facts on higher levels

I haven't the slightest idea if this is the "correct" solution according to Kimball, Inmon, etc. Instead, I would urge you to see if it applies to your particular needs. ;)


Why are the initial queries up to minute slow on a 400M record PostgreSQL table?

That are caching effects. The first time, data are read from disk, the other executions already find the data in RAM.
There are several options:

Have enough RAM in the machine to cache the whole table and the index. Then use pg_prewarm to load it into the cache before you begin.

Get faster storage.

Use a covering index (which may get large):
CREATE INDEX ON result (p_id) INCLUDE (id, text, answer) WHERE (dirty IS NULL);

See that the index is cached in RAM.


Efficient workflow/queries to save unique information?

My solution of this would be use relation tables instead of merging ids, use uniqueness for tables phone number, email, and website etc. and insert with IGNORE command like

Insert ignore into emails values (5,a.a@a.com);

  If you use the IGNORE keyword, errors that occur while executing the
  INSERT statement are ignored. For example, without IGNORE, a row that
  duplicates an existing UNIQUE index or PRIMARY KEY value in the table
  causes a duplicate-key error and the statement is aborted. With
  IGNORE, the row is discarded and no error occurs. Ignored errors may
  generate warnings instead, although duplicate-key errors do not.

or if you want o update on duplicates, use Replace instead of Insert ignore

  REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

This way you don't need to check for duplicates. If you want more complicated duplicate checks, you need o do it in the code, not in the database


Sql Server 2008 R2 DBCC SHRINKFILE Severe Error

Have you tried specifying both the file name and the target size in MB, e.g.

DBCC SHRINKFILE('filename', 20);

Or the TRUNCATEONLY option, which doesn't try any of the nasty stuff like reorganizing all of your physical data:

DBCC SHRINKFILE('filename', TRUNCATEONLY);

Also you could try making sure the database is in single user mode first:

ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

...and turn off any backup / log backup or other maintenance jobs that may be conflicting with your shrink operation. If it still occurs, please run a CHECKDB against this database and let us know the results:

DBCC CHECKDB (db) WITH NO_INFOMSGS, ALL_ERRORMSGS;

But just as a side note, you do know that shrinking is bad and should usually be reserved for extraordinary events and circumstances, right?


Transaction log exploding during index reorganization

One thing to consider is to do more frequent transaction log backups. When you do a transaction log backup the log is truncated and and the space can be reused.


You must be seeing different tables from different database clusters, databases or schemas.

Either you are logging into a different database by mistake or you got yourself confused with tables of the same name in different schemas of the same database.

The latter would typically happen with two different roles that have different search_path settings or even the (default) setting:

search_path = "$user",public

Without schema-qualification, tables of the same name are found in the "current" schema first ...

Your test would also find the "wrong" table:

select * from pg_tables where tableowner = 'user1';

Test instead with:

select * from pg_tables where tablename = 'my_tbl';

You should see two or more rows for the same table name ...

Of just use schema-qualified table names to avoid possible confusion:

ALTER TABLE my_schema.my_tbl OWNER TO user1;
SELECT * FROM my_schema.my_tbl;

Start by reading about schemas in the manual.


The problem is most likely some NULL values. Unfortunately, NULL LIKE 'anypattern' will not result in FALSE but in UNKNOWN and NOT UNKNOWN is still UNKNOWN. So any row where one or more of the values is Null, will not be in the result list because WHERE filters all rows that the condition is anything but TRUE (FALSE and UKNOWN get rejected).

Try this change:

select * from VHS_Metadata_Aug52014 where
(Title Not like '*' & 'News' & '*'           Or Title Is Null)
And
(Description Not like '*' & 'News' & '*'     Or Description Is Null)
And
(Tag Not like '*' & 'News' & '*'             Or Tag Is Null)
And
(Speaker Not like '*' & 'News' & '*'         Or Speaker Is Null)
And
(VIP Not like '*' & 'News' & '*'             Or VIP Is Null)
And
(FilmingLocation Not like '*' & 'News' & '*' Or FilmingLocation Is Null)
And
(Notes Not like '*' & 'News' & '*'           Or Notes Is Null)
;


AWE to allocate memory in SQL Server 2008 Express with Advanced Services

There is no point in checking AWE on express edition when SQL Server can see 'just' more than 1G memory. Although it says SQl server can take maximum up till 1G on express edition it takes bit more than that.

AWE is a windows feature in 32 bit which allow SQL Server to use memory greater than its VAS limit. VAS limit for SQL server 32 bit running on 32 bit windows machine is 2G and on WOW system is 4 G. Using AWE SQL Server data and index pages can use memory beyond its VAS limit of 2 G. But since in your case max server memory is 1G there is no point in enabling it. And this is the reason I guess why documentation says so.

As to why it is there because MS does not wants to make change. AWE on 64 bit server has also no meaning but this option is there as well.

  I would go with standard edition of SQL if I know for sure that AWE is not supported in SQL Express?

Go with standard edition 64 bit and yes you would also need 64 bit OS for that. 32 bit server these days have lots of performance restriction especially in terms of memory and VAS. 64 bit will have 8 TB VAS and enough memory to support. 32 bit server is kind of outdated now ( unless you have grave requirement)


Can CURRENT_TIMESTAMP be used as a PRIMARY KEY?

As per the documentation, the precision of the CURRENT_TIMESTAMP is microseconds. Thus, the probability of a collision is low, but possible.

Now imagine a bug which happens very rarely, and causes database errors. How hard is to debug it? It is a far worser bug than one which is at least deterministic.

The more broad context: you probably want to avoid these little nuances with the sequences, which is particularly annoying if you are accustomed to MySQL.

Furthermore, if you are using transactions (most web frameworks, particularly the Java ones, do!), then the timestamps will be the same inside a transaction! A demonstration:

postgres=# begin;
BEGIN
postgres=# select current_timestamp;
       current_timestamp
-------------------------------
 2018-08-06 02:41:42.472163+02
(1 Zeile)

postgres=# select current_timestamp;
       current_timestamp
-------------------------------
 2018-08-06 02:41:42.472163+02
(1 Zeile)

See you? Two selects, exactly the same result. I don't type so fast. ;-)

--

If you want easily IDs, avoiding the usage of the sequences, then generate some hash value from the real identifiers of the records. For example, if your database has humans, and you know that their birthdate, mother's maiden name and real name uniquely identifies them, then use an

md5(mother_name || '-' || given_name || '-' birthday);

as id. Beside that, you can use a CreationDate column, after what you index the table, but it is not a key (which is the id).

P.s. In general, it is a very good practice to make your DB so deterministic, as it is possible. I.e. the same operation should create exactly the same change in the DB. Any timestamp-based ID fails this important feature. What if you want to debug or simulate anything? You replay an operation and the same object will be created with a different id... it is really not hard to follow, and it spares a lot of work hours.

P.s.2 Anybody checking your code in the future, won't have the best opinion  seeing timestamp-generated ids, on the reasons above.


tl;dr

Your hosts were unable to resolve the hostnames you gave to actual IPs, since those were missing in the /etc/hosts file.

How to solve

The hosts files on the all the machines must resolve the IPs of the respective machines to their respective hostnames.

For example

192.168.0.1 myhost1
192.168.0.2 myhost2
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

Make sure all hostnames are resolved correctly by pinging each other host from each host, eg.

myhost1 $ ping myhost2

Please read the docs thoroughly and make sure you understood them.
Proceed to step 2 until you really got it.
You only have to call rs.initiate() once and only on one machine, then use rs.add() on the same machine to add replica set members.

With your setup, you need an additional node or an arbiter

Personal note

Please do not take this personal, but you lack basic system administration skills, and that's put politely.

For your own sake: Do not run MongoDB for production until you acquired basic system and MongoDB administration skills!

I'd look for RHCSA first (works for CentOS as well) and  M102, M202 and maybe even MongoDB DBA at MongoU later (without basic sysadmin skills, the latter won't help you much).


Optimizing sql server MIN in the select blah where blah IN (SELECT MIN(blah)

This query usually perform better:

SELECT  rn.ID, --... other columns go here
        rn.OrganisationID
FROM (
    SELECT *, n = ROW_NUMBER() OVER(PARTITION BY OrganisationID ORDER BY id)
FROM #t
) rn WHERE n= 1


ndbcluster: Error: 1114, The table is full, when using table

I had a similar issue.  Except for me it was complaining about a large table being full, even though all nodes were using 0% data and index memory.

What fixed it for me was setting MaxNoOfExecutionThreads=4 (a value of 8 caused the table to be full).

I don't know why this worked, but it did.  I wish the ndbmtd executable would provide more descriptive output than just (the table is full), such as describing exactly which internal memory or resource is full, because neither my memory nor disk were anywhere being full.

Hope this helps!


I would amend your logic to:

WHERE
    VAL_DT < CURRENT DATE
    OR
    (
        MVT_DS = 'NOSTRO'
        OR MVT_DS = 'SCREV'
        OR MVT_DS = 'SCRIN'
        OR MVT_DS = 'MAN'
    )

or even

WHERE
    VAL_DT < CURRENT DATE
    OR MVT_DS IN (
      'NOSTRO', 'SCREV', 'SCRIN', 'MAN'
    )

I changed it so that VAL_DT should be earlier than the current date or the other field should equal the respective values. So to get a "true" result - either VAL_DT < "current date" or MVT_DS is one of those values. If it's one of those values it will therefore bypass the "current date" check, effectively.


SQL Server doesn't stop the service when it hits the expiration. Instead, it is checked when the service starts. If you restart the service, or reboot, or your system crashes, when it tries to come back up, it won't start.

So I'd take your backups, clean SQL Server off the machine entirely (since other components like SSMS might still give you expiration issues) and fix this - before you have to restart the service or reboot and you're not prepared.

David points out that upgrading from eval to full license is supported, but I avoid that because I’ve had more than one experience where that definitely did not go smoothly. Hopefully this isn’t production, where eval should never have been used in the first place, but in any case I think it is cleaner and safer to nuke and pace than try an in-place upgrade. Those have a variety of potential pitfalls even when an expired eval edition isn’t part of the mix.

I have a post about fully removing evaluation edition, but please don't start this process until you've validated your backups are all good and you've scripted logins, jobs, linked servers etc.

Fun with software : uninstalling SQL Server 2008 R2 Evaluation Edition

The actual date isn't important now, IMHO. If you have sour milk in the fridge, it's sour, and you should throw it away, whether it expired on Easter or on Christmas. Also note that some components may expire sooner, e.g. if you install SSMS along with one instance of eval edition, then install another instance of eval edition later, SSMS will expire with the first one, not the second.


SQL Server Reporting Services: How to check who accessed certain report?

Suggest you not to remove people but disable their login or just revoke access.

Then after a full business cycle, if no one complains then remove those users.

  Is there a way to find out who has accessed certain reports during past month?

Below is the query that will give the report you need. There are more report queries available at this link :

Note: Adjust the query as per your need.

 – Execution Log:

– For debugging (remove next 4 lines after pasting to dataset) –

DECLARE @DateFrom Date

DECLARE @DateTo Date

SET @DateFrom = ’2008-01-01′

SET @DateTo = ’2008-06-30′

– End debugging script –

SELECT

      DATEPART(Hour, TimeStart) AS ReportYear

    , DATEPART(Month, TimeStart) AS ReportMonth

    , DATEPART(Day, TimeStart) AS ReportDay

    , DATEPART(Hour, TimeStart) AS ReportHour

    , Type

    , COUNT(Name) AS ExecutionCount

    , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum

    , SUM(TimeProcessing) AS TimeProcessingSum

    , SUM(TimeRendering) AS TimeRenderingSum

    , SUM(ByteCount) AS ByteCountSum

    , SUM([RowCount]) AS RowCountSum

FROM

(

    SELECT TimeStart, Catalog.Type, Catalog.Name, TimeDataRetrieval,

  TimeProcessing, TimeRendering, ByteCount, [RowCount]

    FROM

    Catalog INNER JOIN ExecutionLog ON Catalog.ItemID =

       ExecutionLog.ReportID LEFT OUTER JOIN

    Users ON Catalog.CreatedByID = Users.UserID

    WHERE ExecutionLog.TimeStart BETWEEN @DateFrom AND @DateTo

) AS RE

GROUP BY

      DATEPART(Hour, TimeStart)

    , DATEPART(Month, TimeStart)

    , DATEPART(Day, TimeStart)

    , DATEPART(Hour, TimeStart)

    , Type

ORDER BY

      ReportYear

    , ReportMonth

    , ReportDay

    , ReportHour

    , Type


How to find out which table/object the KEY or PAGE or EXTENT locks in the sys.dm_tran_locks belong to?

sys.dm_db_database_page_allocations() was introduced specifically to avoid having to do any of this spelunking yourself, using unsupported and undocumented commands like DBCC IND and DBCC PAGE. I talk about an even better replacement, coming in SQL Server 2019, in this tip. With the caveat that this is a pretty expensive query, and you may want to filter it down further to limit the output to just the specific rows in sys.dm_tran_locks you are investigating:

SELECT OBJECT_SCHEMA_NAME(p.object_id), OBJECT_NAME(p.object_id), *
FROM sys.dm_tran_locks AS t
INNER JOIN sys.dm_db_database_page_allocations(DB_ID(),NULL,NULL,NULL,'LIMITED') AS p
ON t.resource_associated_entity_id = CASE
  WHEN resource_type = N'OBJECT' THEN p.object_id
  WHEN resource_type IN (N'KEY',N'PAGE',N'RID') THEN p.rowset_id
  -- not quite sure about EXTENT
END
WHERE OBJECTPROPERTY(p.object_id, 'IsMsShipped') = 0;

Yes, resource_associated_entity_id is the object_id, when the resource_type is OBJECT. For other resource types, it is going to reference other types of identifiers, like rowset_id or allocation_unit_id. This is why the column isn't simply named object_id.
SELECT OBJECT_NAME(object_id); will return the object name, regardless of whether the object_id is negative or positive. #temp tables have negative object_id values, for example, in SQL Server 2012 and up, and really there is no guarantee that any object_id will be positive.


How to not allow to insert a new record if count of current records exceeds a specified limit

CREATE TRIGGER tr_bi_check_limit
BEFORE INSERT
ON team_member
FOR EACH ROW
BEGIN
    IF ( SELECT COUNT(*)
         FROM team_member
         WHERE team_id = NEW.team_id ) > ( SELECT member_limit - 1
                                           FROM team
                                           WHERE id = NEW.team_id ) THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = 'Team limit reached!';
    END IF;
END

fiddle with some explanations.


I was finally able to replicate your issue due to you noting that

LEFT JOIN Table_C c ON c.Id_B = b.Id AND c.Id_B IS NOT NULL

improved the query.

DDL & DML

CREATE TABLE Table_A (id int not null,
                     constraint ClusteredIndex_A primary key (id))

INSERT INTO dbo.Table_A(id)
SELECT top(50000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL))
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;

CREATE TABLE Table_B (id int not null,
                      Id_A int
                     constraint ClusteredIndex_B primary key (id))

-- constraint FK_C_B FOREIGN KEY (id_B) REFERENCES Table_B(id)
CREATE TABLE Table_C (id int not null,
                      Id_B int
                     constraint ClusteredIndex_C primary key (id),
                     constraint FK_C_B FOREIGN KEY (id_B) REFERENCES Table_B(id))
INSERT INTO dbo.Table_C(id,Id_B)
SELECT top(2000000) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)),
NULL
FROM MASTER..spt_values spt1
CROSS APPLY MASTER..spt_values spt2;

CREATE INDEX [NonClusteredIndex-B-A] on dbo.Table_B(id_A);
CREATE INDEX [NonClusteredIndex-C-B] on dbo.Table_C(id_B);

And as a result, your query is going slow due to all the NULL values in the Table_C table.

SELECT c.Id FROM Table_A a
LEFT JOIN Table_B b ON b.Id_A = a.Id
LEFT  JOIN Table_C c ON c.Id_B = b.Id
WHERE a.Id = 2500 OPTION (RECOMPILE);

Resulting to a starting point close to yours:

The easiest & best solution here would be changing the query as you pointed out.

SELECT c.id
FROM Table_A a
LEFT JOIN Table_B b ON b.Id_A = a.Id
LEFT  JOIN Table_C c ON c.Id_B = b.Id and c.Id_B is not null
WHERE a.Id = 2500;

A possible but far from ideal solution would be adding OPTION(loop join) with a plan guide

exec sp_create_plan_guide
@name = N'Guide_1',
@stmt = N'SELECT c.id
FROM Table_A a
LEFT JOIN Table_B b ON b.Id_A = a.Id
LEFT JOIN Table_C c ON c.Id_B = b.Id
WHERE a.Id = 2500;',
@type = N'SQL',
@module_or_batch = NULL,
@params = NULL,
@hints = N'OPTION (LOOP JOIN)';

But the issue with high estimates on dbo.Table_C persists & plan guides are a last resort (if even).

Im gonna leave this up so other people can test & replicate your problem using DDL & DML for now. Maybe statistics or indexing could prove a solution that I was unable to find.

Edit

option(FAST 1) produces single row estimates & low cost (for this single example)

SELECT c.Id FROM Table_A a
LEFT JOIN Table_B b ON b.Id_A = a.Id
LEFT  JOIN Table_C c ON c.Id_B = b.Id
WHERE a.Id = 2500 OPTION (RECOMPILE,FAST 1);

Again not ideal since bigger resultsets will be impacted and other issues might arise.

What also works is inserting one row in dbo.Table_B

INSERT INTO dbo.Table_B(id,Id_A)
VALUES(1,NULL);

& update the stats UPDATE STATISTICS dbo.Table_B

So sql server does not have to join table_C to an empty table_B

Or insert -1.

INSERT INTO dbo.Table_B(id,Id_A)
VALUES(-1,NULL);

What you then could do, is generate the statistics with the 1 row, and create these on the empty dbo.Table_B.

By right clicking the database --> tasks --> generate scripts

Choose table_B

On advanced scripting options select script out statistics & histograms:

Save to new query window, next, next, finish.

And for me I got these statistics:

/****** Object:  Statistic [ClusteredIndex_B]    Script Date: 17/01/2020 10:58:36 ******/
UPDATE STATISTICS [dbo].[Table_B]([ClusteredIndex_B]) WITH STATS_STREAM = 0x0100000001000000000000000000000019B912CC000000007F020000000000003F02000000000000380300003800000004000A000000000000000000000000000700000046C8B30045AB000001000000000000000100000000000000000000000000803F000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001000000010000000100000014000000000080400000803F00000000000080400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000190400000000000000000000000000001F00000000000000BB00000000000000C300000000000000CB000000000000000800000000000000100014000000803F000000000000803FFFFFFFFF04000002000000859CB10045AB0000000000000000F03F030000000000000001000000000000000000F03F00000000000000000000000000000000000000000000004000000000000000400000000000000000BF89B00045AB0000000000000000F03F010000000000000001000000000000000000F03F00000000000000000000000000000000000000000000F03F000000000000F03F000000000000F03F01000000000000000000000000000000, ROWCOUNT = 2, PAGECOUNT = 1
GO
/****** Object:  Statistic [NonClusteredIndex-B-A]    Script Date: 17/01/2020 10:58:36 ******/
UPDATE STATISTICS [dbo].[Table_B]([NonClusteredIndex-B-A]) WITH STATS_STREAM = 0x010000000200000000000000000000000ECB838500000000CC010000000000007401000000000000380200003800000004000A00000000000000000000000000380300003800000004000A00000000000000000000000000070000007721B40045AB000001000000000000000100000000000000000000000000803F0000803F0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000014000000000080400000803F0000803F000000000000804000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100400000000000000000000000000000800000000000000100000000000000001000000000000000000000000000000, ROWCOUNT = 1, PAGECOUNT = 1
GO

Deleted all data:

DELETE FROM dbo.Table_B

Reran the two update statistics with stat streams.

To get a better performing execution plan with correct estimates & results