Cast string to decimal without rounding the decimal part

You don't show the column definition, but it must be similar to

"conversionFactor" decimal(10,2)

At any rate, the scale of the column must be 2, so PostgreSQL has to round the number to be able to sore it in that column.

You will have to use ALTER TABLE to change the colum definition to be able to store more fractional digits in the column.


How to determine right value for innodb_sync_array_size

This might help in understanding it more in details.


how to get data from two tables in mysql query with where Pk of one table is set as foreign key on PK column for other table?

Well, your orders table doesn't have a column orderaccid. You can use using only when the column names are equal. Use this instead:

from orderacc join orders on orderacc.orderaccid = orders.orderid


How to convert SQL Server .MDF database to Mac OS PostgreSQL database on Mac OS X?

You'll need to attach the database to a Microsoft SQL Server database, the export the data into the Postgres database.  Assuming that you've got the Windows Postgres ODBC drivers handy you can use SQL Server's Import/Export wizard to copy the data into the Postgres database.


Looks like the disk subsystem on Server B is performing worse than on Server A but, I used to see disk issues not entirely related to disk specs. You could collect some other performance counters such as physical disk --> avg disk sec write (> 25 ms very slow), memory --> page file usage (> 70% bad), cpu --> processor queue length (> 12 very bad), memory --> pages/sec(> 600 slow, > 2500 very slow disk subsystem), sql server: buffer manager --> page life expectancy (< 300 memory issue).

You can also limit the growth of 6 tempdb data files to the size they have now. See what happens.

You should check the waitstats as well on both servers. If you see a lot of PAGELATCH_XX then you will know where to dig more. Jonathan Kehayias has a good article on this theme. And Paul Randal has also a lot of analysis which could help.


Much slower replication on Innodb compared to myisam

Make sure your disks are fast enough to catch up master.

The problem is that innodb is a transactional storage engine. It writes first into undo log and only then modified data to the tablespaces. The second issue is that mysql replication by default is single threaded. It means that transactions applied on the master in parallel will be in the binlog in sequential order, and all of them will be applied sequentially on the replica. You can try parallel replication toprocess binlog events in parallel.


Using the output from mysqldiff.exe to transform database2 to match database1

As it appears on the manual, use the option --difftype=sql in order to obtain the results as ALTER TABLEs.

You have an example on a recent MySQL Performance Blog post:

$ mysqldiff --force --difftype=sql \
--server1=root:msandbox@127.0.0.1:21489 \
--server2=root:msandbox@127.0.0.1:21490 \
employees:employees


Performance issues when extracting scatter plot data

There is no reason to use derived tables for this query. It can affect performance badly with MySQL's primitive optimizer. Try this query:

SELECT
    a.foreign_id_1, a.value AS x, b.value AS y
FROM
    table AS a
  JOIN
    table AS b
      ON  a.foreign_id_1 = b.foreign_id_1
      AND a.foreign_id_2 = b.foreign_id_2
      AND a.foreign_id_3 = b.foreign_id_3
      AND a.foreign_id_4 = b.foreign_id_4
WHERE
      a.category = 'A'
  AND b.category = 'B' ;

If the efficiency of the query is crucial, you can add a covering index, with the columns in tailor made order, especially for the query:

ALTER TABLE tablename
  ADD INDEX category_foreign_ids_value          -- pick a name for the index
                                                              -- columns from:
    (category,                                                -- the WHERE
     foreign_id_1, foreign_id_2, foreign_id_3, foreign_id_4,  -- the JOIN ... ON
     value) ;                                                 -- the SELECT


Aggregate and GROUP BY conflict with HAVING?

You have a semicolon after FROM Order_T. A semicolon is a statement delimiter, it should be at the end of the entire statement:

SELECT CustomerID, COUNT(OrderID) AS OrderCount
FROM Order_T
GROUP BY CustomerID
HAVING(COUNT(OrderID) > 2);  <-- there

I am puzzled about how your query can run without errors if you just remove HAVING, but the semicolon is definitely out of place in the posted code.


Joining columns with null marks into one row

It's difficult without seeing your query, but you want to aggregate on your fields with null values if you want to remove the redundant rows.

This query would give you one row of data in this example, I'm not sure how you mean to get your output where you are removing two rows.

SELECT
     Field1
    ,Field2
    ,MAX(Field3) Field3
    ,MAX(Field4) Field4
    ,Field5
    ,MAX(Field6) Field6
    ,MAX(Field7) Field7
FROM TableName
GROUP BY
     Field1
    ,Field2
    ,Field5


How to change MongoDB slowOpThresholdMs in mongo shell

The slowOpThresholdMs setting defines what constitutes a “slow” operation. To set the threshold which the profiler considers operations “slow” (and thus, included in the level 1 profiling data), you can configure slowOpThresholdMs at runtime as an argument to the db.setProfilingLevel() operation.

For Ref setProfilingLevel and
slowOpThresholdMs

Note:  The threshold for slow operations applies to the entire mongod instance. When you change the threshold, you change it for all databases on the instance.

For Example:

{ profile: 1, slowms: 200 }

Database profiling can impact database performance. Enable this option only after careful consideration


When does SQL Server warn about an Excessive Memory Grant?

To produce this warning:

The maximum used memory must be less than 5% of the granted memory; AND
The query must use the regular (not small) resource semaphore

To use the regular resource semaphore the query must:

Have granted memory over 5MB (5120 KB, 640 x 8KB pages); OR
Have a total estimated plan cost of over 3 units and not be a trivial plan

Server version requirements:

SQL Server 2014 SP2 (12.0.5000) or later
SQL Server 2016 SP1 (13.0.4001) or later
SQL Server 2017 RTM (14.0.1000) or later


List default column values for all tables in a database

Check user_tab_cols(all_tab_cols/dba_tab_cols).  view . DATA_DEFAULT column, as its' name implies, holds default value for the column.


Mail Alert when threshold of 10000 pendingcmds are breached

The following suggestions are not fully tested.

Immediately after

Insert INTO #pendingcmds (pendingcmdcount, estimatedprocesstime)
select * FROM OPENROWSET('SQLOLEDB',
'Server=Server\Instance;Trusted_Connection=yes;',
'set fmtonly off;
exec distribution..sp_replmonitorsubscriptionpendingcmds
@publisher=''Server\Instance'',
@publisher_db=''testproduction'',
@publication=''Summary_Inventory_TestProduction'',
@subscriber=''Server'',
@subscriber_db=''TestReplAzure'',
@subscription_type=0')

I would delete any rows where your threshold was not met.

DELETE FROM #pendingcmds WHERE pendingcmdcount <= 10000

Then, I would change your test for whether to send the email or not

From

IF LEN(@HTML) > 10

            EXEC msdb.dbo.sp_send_dbmail
            @recipients = 'tester@test.com',
            @body_format =  'HTML',
            @body= @HTML,
            @subject = @MailSubject,
            @profile_name = 'MailRelay'

TO:

IF (SELECT COUNT(*) FROM #pendingcmds) > 0
    EXEC msdb.dbo.sp_send_dbmail
    @recipients = 'tester@test.com',
    @body_format =  'HTML',
    @body= @HTML,
    @subject = @MailSubject,
    @profile_name = 'MailRelay'


You can use a combination of MIN() OVER() and SUM() OVER():

SELECT *,
       MIN(Balance) OVER(PARTITION BY CaseId) -
       SUM(Amount) OVER(PARTITION BY CaseId ORDER BY Id) Total
FROM dbo.YourTable
ORDER BY Id;

Here is a demo of it.


How to pass multi-valued characters in SSRS Report?

I found a solution. I did not properly split the values in the stored procedure with a UDF.

To pass multi-values correctly in this stored procedure, I would need to add the following code to the dataset parameter that I am using:

=join(Parameters!<your param name>.Value,",")

This is basically going to join multiple values into an array and pass it through the @Flag parameter. The next step is adding SQL to the stored procedure to receive and digest the values correctly so it reads the values with the IN clause.

Google search any UDF string parser online. There are many to choose from. I used dba_parseString_udf from Michelle Ufford http://sqlfool.com.

Once I had my UDF installed, I can now alter my IN clause to receive the new multi-valued parameter being passed by SSRS as follows:

WHERE [Flag] IN (SELECT * FROM dba_parseString_udf(@Flag, ','))

Therefore, SSRS will pass the following value:

@Flag = 'A,B,C'

Then my UDF will parse that string out correctly to:

A
B
C

And populate my @Flag parameter correctly with SELECT * FROM UDF()...


Database Schema for Blank Database is large. 20MB

If you create a database without specifying a file size, the size is mapped from model. You can see what this is by running:

EXEC model.sys.sp_helpfile;
-- on my system, size of primary data file -> 3264 KB

Now, if I create a database just using CREATE DATABASE, I get an exact replica of those files.

CREATE DATABASE [floob1];
GO
EXEC floob1.sys.sp_helpfile;
-- size of primary data file -> 3264 KB

I can try to shrink it after the fact, and it will shrink some, but not entirely.

DBCC SHRINKFILE(floob1, 1);
GO
EXEC floob1.sys.sp_helpfile;
-- size of primary data file -> 2368 KB

So you can do that, but perhaps you should first find out what's in your model database and why it's 20 MB. Perhaps you can fix this problem by removing objects and/or shrinking that file. If shrinking the file doesn't reduce the size, then check for objects:

SELECT s.name, t.name, rows = SUM(p.rows)
  FROM model.sys.partitions AS p
  INNER JOIN model.sys.objects AS t
    ON p.[object_id] = t.[object_id]
  INNER JOIN model.sys.schemas AS s
    ON t.[schema_id] = s.[schema_id]
  WHERE p.index_id IN (0,1)
  ORDER BY rows DESC;

SELECT s.name, o.name, codesize = DATALENGTH(m.definition)
  FROM sys.objects AS o
  INNER JOIN sys.schemas AS s
    ON o.[schema_id] = s.[schema_id]
  INNER JOIN sys.sql_modules AS m
    ON o.[object_id] = m.[object_id]
  ORDER BY codesize DESC;

It's possible that objects were created there by mistake (in which case drop them), or have more rows than they should (in which case prune them and rebuild).

Note that if you try to override model and make a smaller database, you can't:

CREATE DATABASE [floob2]
ON (NAME = floob2_data,
  FILENAME = 'C:\...wherever...\floob2.mdf',
  SIZE = 1MB, FILEGROWTH = 2MB);

This results in an error:

  Msg 1803, Level 16, State 1, Line 18
  The CREATE DATABASE statement failed. The primary file must be at least 4 MB to accommodate a copy of the model database.

I assume you'll get a similar error if you try to create a database with an explicit data file < 20 MB, because it sounds like your model database is about 20 MB. So going back to my previous point, you're going to need to dig in and see why model is 20 MB, and fix that. (And note that I have no idea how you're measuring 20 MB - it could be the log file, for all I know, so you may need to look at that file instead.)

Of course, if space is a real issue and you put thousands of databases there under the assumption that they'll never grow, and then they all start growing, this is going to be a problem.


How to group by when querying duplicate records?

SELECT
  *
FROM
  foos
  INNER JOIN
  (
    SELECT
      name
    FROM
      foos
    GROUP BY
      name
    HAVING
      COUNT(*) > 1
  ) AS dup
  ON foos.name = dup.name
ORDER BY
  name


Database copy for Hyperscale is now in Public Preview as announced here on Sep 22, 2020.
If you don't like the current functionality, another option is creating a new database by restoring an existing backup.
When a copy is needed to another region use a geo-restore of the database. Create a SQL Database server in the target region if you do not already have an appropriate server there. This server should be owned by the same subscription as the original (source) server. Finally perform steps provided here.


Return available rooms depending 2 others tables

SELECT ro.ROO_Id, ro.ROO_Name, ra.RAT_Price
FROM ___Rooms ro, ___Rates ra, ___Availabilities av
WHERE (FIND_IN_SET(ro.roo_id, ra.RAT_RoomsList) OR ra.RAT_RoomsList='')
  AND av.AVA_Status = 'Open'
  AND av.AVA_RoomId = ro.roo_id
  AND av.AVA_Date BETWEEN GREATEST(ra.RAT_DateStart, '2019-04-02')
                      AND LEAST(ra.RAT_DateEnd, '2019-04-04')
GROUP BY ro.ROO_Id, ro.ROO_Name, ra.RAT_Price
HAVING COUNT(*) = 1 + DATEDIFF('2019-04-04', '2019-04-02')

fiddle


When previously faced with a similar issue I ended up

writing a pre import parser (c)which ran through the extracted data and created a file of valid entries and a file of malformed entries
Import the valid entries Into
Sql server using the import wizard which was used to create a schedule job for each of the import file types
Once imported there were several jobs that further manipulated the data and copied to the final end user tables.

The main issue is identifying the malformed data
And handling it and ensuring that you use the same pattern where possible for all files.


MySQL: SELECT more data than necessary or use multiple SELECT queries?

The answer We should forget about small efficiencies, say about 97% of the time: premature optimization is the root of all evil. Yet we should not pass up our opportunities in that critical 3% is at softwareengineering.stackechange.com


Best way to automate running several MySQL commands

Create a text file containing all your commands. Call it newuser.sql. Then when you want to set up a new user, let's call him gaius, you would simply do

$ cat newuser.sql|mysql --user=gaius --password=abc123 somedb

Obviously you could even create new users in a script, let's call it newuser.sh

#!/bin/bash

echo "create user '$1'@'%' identified by password('$2');"|mysql --user=root --password=secret
cat newuser.sql|mysql --user=$1 --password=$2 somedb

Then do

$ ./newuser.sh gaius abc123


Cannot import database exported by phpPgAdmin

Looking at this tutorial, seems like phpPgAdmin make a call for pg_dump to export the data.

To process plain files with psql, you must use the -foption, eg:

psql \
  -U postgres  \
  -d database_name \
  -f phppgadmin_dump_file.sql

I strongly recommend using pg_dump manually with the option --format=custom to make the restore more flexible (and use the tool pg_restore).

Please, take a look at this URL to restore de file using phpPgAdmin itself:

http://www.inmotionhosting.com/support/website/databases/postgresql/import-database


Find entries, meant to only be available on specific days, being set on incorrect days

There are a number of ways to do this. Here's one suggestion:

First, you need to be able to compare the dates for a given Entity to the available dates for its Type.

The available dates for a Type are given in terms of cycle week and week day. The dates for an Entity are actual calendar dates.

So, to my way of thinking, we should convert the calendar dates to cycle week and week day. You could do the reverse, of course, but you'd have to determine different dates for different cycles, which could get confusing.

Not sure how you're calculating days of the week and weeks; I would look at getting the number of days from Cycle.BeginDate to Entity.EntityDate, using % to get the number of days that is into the current cycle (this assumes that the Cycle.BeginDate is set once and left alone - the modulo may be unnecessary if the begin date is updated to match the current cycle). Then, that number of days should also determine the week in the cycle for that EntityDate ((NumDays + 1 / 7) + 1, assuming NumDays is an integer, should work).

I would, in addition, create a temp table with the expanded weeks and days when each Type is available. That is, replace rows with Week = 0 with 4 (or 5, depending on the Cycle.Weeks) rows (Week = 1, Week = 2, etc.), and then rows with Day = 0 with 5 (or 7, if you can book on weekends) rows, (Day = 1, Day = 2, etc.). This will allow a direct one-to-one match between the week and day values for each EntityDate, and the values from TypeAvailable. It's certainly possible to set up the comparison to handle the Week = 0 and Day = 0 values, but creating the temp table shouldn't be too expensive (depending on the number of rows in TypeAvailable), and leaves thing smuch easier for the next person to follow.

Now that you can directly compare the available weeks and days to the scheduled weeks and days, it's simply a matter of doing so, and reporting the entities without an available match. LEFT OUTER JOIN from the Entity values to the TypeAvailable temp table, and record all rows where there's no match in the temp table.


SELECT t2.`date`, SUM(t1.scal_val * t2.state) state
FROM table1 t1
JOIN table2 t2 ON t1.id = t2.id
GROUP BY t2.`date`


SQL Server 2008 R2 high Buffer I/O causes

If you look carefully at the waits graph, the coloured bars actually indicate lock waits, not I/O waits.

Also, the I/O graph shows increased system disk usage, not SQL Server disk usage.

Given that the memory graph is approaching your system memory size as the system utilization increases over time, I believe your instance is overcommitted for the current max server memory setting (or the max server memory setting isn't set at all), and one or more of the SQL Server memory pools are swapping to disk.

The solution is to lower the max server memory setting (or set it to an appropriate value if it isn't set). Please see my answer here for a more in-depth answer from a similar scenario, and my blog post:

Is my SQL Server's memory over-committed?


mysql query only works when value is compared with primary key

Look at the output of your select statement: it seems you have a leading blank in your values.


What permissions are needed to view dependencies?

The 2012 Books Online topic "Get Information About a View" states that the permissions required for this specific task are:

VIEW DEFINITION permission on the database; and
SELECT permission on sys.sql_expression_dependencies

Note that database-level VIEW DEFINITION is required to allow the user to see information in sys.sql_expression_dependencies; object-level VIEW DEFINITION and SELECT on the DMV will not work (you will receive an empty result set).

Example:

USE Sandpit;
GO
CREATE VIEW dbo.V1 AS SELECT 1 AS const;
GO
CREATE VIEW dbo.V2 AS SELECT v.const FROM dbo.V1 AS v;
GO
CREATE USER Bob WITHOUT LOGIN;

-- These two permissions are not sufficent
GRANT VIEW DEFINITION ON OBJECT::V1 TO Bob;
GRANT VIEW DEFINITION ON OBJECT::V2 TO Bob;

-- This one permission is enough (covers the two above)
GRANT VIEW DEFINITION ON DATABASE::Sandpit TO Bob;

-- Also required
GRANT SELECT ON OBJECT::sys.sql_expression_dependencies TO Bob;
GO
EXECUTE AS USER = 'Bob';
GO
SELECT OBJECT_DEFINITION(OBJECT_ID(N'V1', N'V'));
SELECT OBJECT_DEFINITION(OBJECT_ID(N'V2', N'V'));

-- Show DMV info for the test views
SELECT
    referencing_object_name = OBJECT_NAME(sed.referencing_id),
    sed.referencing_class_desc,
    sed.is_schema_bound_reference,
    sed.referenced_class_desc,
    sed.referenced_schema_name,
    sed.referenced_entity_name
FROM sys.sql_expression_dependencies AS sed
WHERE
    sed.referenced_id = OBJECT_ID(N'dbo.V1', N'V');
GO
REVERT;
GO
DROP USER Bob;
DROP VIEW dbo.V1, dbo.V2;

Regarding sp_depends: that requires only membership of the public role but is deprecated:

  This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. Use sys.dm_sql_referencing_entities and sys.dm_sql_referenced_entities instead.


Allow MongoDB Read/Write But Disallow Delete Permission

As per MongoDB documentation here Collection-level access control allows administrators to grant users privileges that are scoped to specific collections.

Administrators can implement collection-level access control through user-defined roles. By creating a role with privileges that are scoped to a specific collection in a particular database, administrators can provision users with roles that grant privileges on a collection level.

Required Access

To create a role in a database, you must have:

the createRole action on that database resource.
the grantRole action on that database to specify privileges for the
new role as well as to specify roles to inherit from.

Built-in roles userAdmin and userAdminAnyDatabase provide createRole and grantRole actions on their respective resources.

To create a role with authenticationRestrictions specified, you must have the setAuthenticationRestriction action on the database resource which the role is created.

use admin
db.createRole(
   {
     role: "myClusterwideAdmin",
     privileges: [
       { resource: { cluster: true }, actions: [ "addShard" ] },
       { resource: { db: "config", collection: "" }, actions: [ "find", "update", "insert", "remove" ] },
       { resource: { db: "users", collection: "usersCollection" }, actions: [ "update", "insert", "remove" ] },
       { resource: { db: "", collection: "" }, actions: [ "find" ] }
     ],
     roles: [
       { role: "readWrite", db: "admin" }
     ]
   },
   { w: "majority" , wtimeout: 5000 }
)

Roles

In the roles field, you can specify both built-in roles and user-defined roles.

{ role: "<role>", db: "<database>" }

For your further ref here , here and here


SSAS Tabular QueryMode Confusion. Why Hybrid mode is useful?

You are correct that currently (up through SSAS 2017 Tabular) is is not possible to have mixed mode partitions. The whole model can operate in DirectQuery or In-Memory. The DirectQueryMode connection strong property can specify whether SSAS should use DirectQuery or In-Memory for answering the current query. But that's not honestly too much better than just deploying the model to two different databases, one DirectQuery and one In-Memory.

There is a similar connection string DataView=Sample which has SSAS answer the query using a special cached partition marked as the sample partition.

But this is still not what you want because a particular query won't union results from DirectQuery partitions and sample In-Memory partitions.

I will try to update this answer if a future release of SSAS adds the feature you are wanting.


The sys.dm_xe_object_columns DMV shows metadata for each of the columns available for each event. Included is a "description" column, which is useful in answering your question.

select
    name
    , object_name
    , type_name
    , description
from
    sys.dm_xe_object_columns
where
    name = 'cpu_time' -- any XE column name here

This will show you all of the extended event objects that have a cpu_time column, and the description will tell you what unit of measure the column uses.

You can see here that units may vary depending on the object. For example:

sql_statement_completed: Indicates the CPU time (in microseconds) that is consumed by the statement.
sql_batch_completed: The CPU time (in milliseconds) used by the batch.


By default you are not allowed - by Windows - to create files into the C:\ drive root, except when running as Administrator. Make sure you create a folder and create the database file there. Also note that it is customary (but not required) to give Firebird databases the extension .fdb.

Also make sure that C:\Remake does not already exist as a folder (directory), see also https://stackoverflow.com/questions/42280679/firebird-permission-denied


What is the "Chaos" Isolation level and when should it be used?

You can't use it against SQL Server.

It was in DTS:

Chaos Isolation Level In SQL Server - SQL Server Code,Tips and Tricks, Performance Tuning

And is in SSIS:

DtsContainer.IsolationLevel Property - MSDN

More references, originally added by Nick Chammas in a comment:

Rollback is not supported
SQL Server and Oracle don't support the chaos isolation level


Looking for an efficient way to build some kind of "FIFO" table

I would store day as a DATE. You can easily query the next seven days from that and generate the "day number" using a window function:
select id, row_number() over (order by day) as day_nr, value
from the_table
where day >= current_date
  and day <= current_date + 7

To get rid of old rows, you simply run a DELETE statement with the necessary WHERE condition. To make that even more efficient, you can define the table with a range partition. Dropping a partition is a lot faster then running a DELETE on a large table.


Constrain unique value between multiple tables

You can enforce this with an Indexed View (or a trigger, of course).  EG

create table Animal(Id int primary key, Alive bit)

create table Fish(Id int primary key references Animal, FishEspecieId int)

go
create or alter view vFish_Alive
with schemabinding
as
select f.Id, f.FishEspecieId, a.Alive
from dbo.Fish f
join dbo.Animal a
  on f.Id = a.Id
where a.Alive = 1

 go

 create unique clustered index pk_vFish_Alive on vFish_Alive(FishEspecieId)

 go
 insert into Animal(id,alive) values (1,1),(2,1)

insert into Fish(id,FishEspecieId) values (1,1)

insert into Fish(id,FishEspecieId) values (2,1)
--Cannot insert duplicate key row in object 'dbo.vFish_Alive' with unique index 'pk_vFish_Alive'. The duplicate key value is (1).


sql*plus 11.2 client connection to oracle timing out on Linux

I had a similar problem after a firewall upgrade. Users could connect to the database just fine, but if their session was idle for some time, the connection would be terminated by firewall. Our solution was to add

SQLNET.EXPIRE_TIME = 10

to sqlnet.ora file. This keeps connections alive by probing them in 10 minute intervals.


Lock Pages in Memory keep SQL database engine stuck at allocating 60MB ram?

As you might know, the reason it allocates 1.7 GB of working set is 32-bit memory limitations. On 32-bit editions of Windows, by default each process gets 2 GB of usable virtual address space, and by default 32-bit editions of SQL Server leave a few hundred MB outside of its largest consumer of memory, the buffer pool.

It is most likely just a misunderstanding that SQL Server allocated only 60 MB of memory when you enabled locked pages. Memory allocated through AWE doesn't show up on the processes tab in Task Manager (or other tools that report a figure based on the processes' working set size) because it isn't part of the process' working set. If SQL Server used a smaller amount of memory when you enabled Lock Pages in Memory, this suggests that AWE was working and the SQL Server process had most likely allocated more than 60 MB of memory.

There are several ways to view memory allocated by AWE, including but not limited to the following.

Query sys.dm_os_memory_clerks, for example as follows:

SELECT SUM(awe_allocated_kb) AS [Total KB AWE Allocated]
FROM sys.dm_os_memory_clerks;

View the Performance Monitor counter SQL Server: Memory Manager\Total Server Memory (KB)

A few notes on your question:

As you probably know, basically anyone who does this for a living will tell you that it's long past time to migrate off the 32-bit operating system and 32-bit SQL Server. You'll be able to use far more RAM without any hassles on a 64-bit platform.
It's highly unusual for SQL Server to be running as the Network Service account.
Processes allocate memory, not RAM (which may not be the same thing)
You shouldn't need to add /PAE to boot.ini. Windows has booted a PAE-aware kernel by default since Windows 2003 SP1 unless you also disable Data Execution Prevention with the switch '/noexecute=alwaysoff'. (You don't want to do that, by the way.)
Using locked pages can cause out-of-memory errors if you don't set a reasonable memory limit in SQL Server or if something other than the SQL Server buffer pool -- either another program or another part of SQL Server -- is competing for memory in Windows.

References:

How It Works: SQL Server 32 bit PAE/AWE on (SQL 2005, 2008, and 2008 R2) – Not Using As Much RAM As Expected!

Fun with Locked Pages, AWE, Task Manager, and the Working Set…

Great SQL Server Debates: Lock Pages in Memory


Not using foreign key constraints in real practice. Is it OK?

Nothing is free. Sometime not having something isn't free either. Both having and not having declared foreign keys come with costs and benefits.

The point of a foreign key (FK) is to ensure that this column over here can only ever have values that come from that column over there1. This way we can be sure we only ever capture orders for customers that actually exist, for products we actually produce and sell. A lot of people think this is a good idea.

The reason we declare them inside the DBMS is so it can take care of enforcing them. It will never ever allow in any data that breaks the rules. Also it will never allow you to get rid of data required to enforce the rules. By delegating this task to the machine we can have confidence in the integrity of the data, no matter what its source or when it was written or which application it came through. Of course this comes with a cost. The DBMS has to check the rules are being followed, for each and every row, for each and every query, all the time. This takes time and effort, which is a load on the server. It also requires the humans to submit DML in a sequence that respects the rules. No more slyly forcing in an Order, then catching up with the admin afterwards. Oh no naughty human, you must first create the Customer, then the Product (and all the prerequisite rows) and only then may you create an Order.

Without foreign keys we are much freer with what we can do, and the order in which we can do it. Problematical rows can be removed ad hoc to allow critical processes to complete. The data can be patched up afterwards, when the panic is over. INSERTs are generally a little quicker (which adds up over time) because no FKs checks are done. Arbitrary subsets of data can be pulled from the DB as desired without having to ensure all supporting data is included. And so on. This can be absolutely fine if the people involved know the system, take careful notes, have good reconciliation routines, understand the consequences and have the time to tidy up after themselves. The cost is that something, somewhere is missed one time and the database deteriorates into barely creditable junk.

Some teams put the checks in the application rather than in the database. Again, this can work. It seems to me, however, that the total server load will be much the same (or slightly higher) with this approach, but the risk of forgetting some check in a bit of code somewhere is much higher. With DRI the rule's communicated to the computer once and is enforced forever. In application code it has to be re-written with each new program.

For my two cents' worth, I'm all for foreign keys. Let the computers do what they're good at doing, like routine repetitive checking that column values match. We humans can concentrate on dreaming up new and interesting stuff. Having taken responsibility for a system a few months back I'm adding FKs to most tables. There are a few, however, where I will not be adding them. These are where we collect data from external sources. The cost of rejecting these rows is greater than the cost of accepting bad data and fixing it up later. So, for these few tables, there will be no foreign keys. I do this open-eyed, knowing we have monitoring and corrective procedures in place.

1I acknowledge the existence of multi-column foreign key constraints.


Select Permissions Across Multiple Schemas Within the Same Database

Granting select permission on the view would be sufficient if the view and tables all had the same owner. This is called Ownership Chaining:

  When an object is accessed through a chain, SQL Server first compares the owner of the object to the owner of the calling object. This is the previous link in the chain. If both objects have the same owner, permissions on the referenced object are not evaluated.

Where the objects have different owners, ownership chaining does not work.

So, the simplest answer is to change the ownership of the tables to match the view:

ALTER AUTHORIZATION ON Pupil.Table1 TO dbo;
ALTER AUTHORIZATION ON Provider.Table2 TO dbo;
ALTER AUTHORIZATION ON Security.Table3 TO dbo;

Granting select on the view:

GRANT SELECT ON dbo.vTestPermissions TO USER = 'your_user';

...will then allow access through the view, via ownership chaining, while preventing direct table access.

If that is not suitable for your needs, we will need to look at a more complex arrangement.


Yes, shouldn't be too difficult, try:

SELECT a.ID, a.ID_Fruit,,, so on b.ID, b.ID_Fruit, b.ID_Crate, b.[DateTime]
FROM TABLEA a
INNER JOIN TABLEB b ON a.ID_Fruit = b.ID_Fruit
INNER JOIN (SELECT ID_Fruit, MAX([DateTime]) FROM TABLEB GROUP BY ID_FRUIT) maxtime
      ON b.[DateTime] = maxtime.[DateTime]

Its not quite clear if you mean that it should be the most recent, but I have assumed that is what you mean.


A SQL Server database backup/restore issue

If you have a copy of the master database which holds the DBBackupEncryptCert then you'll want to restore that and grab the copy of the certificate, then import it into your current instance for use.

  If there is a way we can restore this database using the current encrypted backup without the certificate key ...

That would defeat the whole purpose of encrypting the backup - so, unfortunately, the answer is that there is no way. There is no back door or gated access to get the data, it is encrypted by the certificate of which no one has a copy but you. That's what makes it safe and secure (well, partially).

  We also have a one-week earlier non-encrypted backup of the database.

Without the certificate, this is what you're going to be able to recover.


Authentication type differences in SQL Server

instance means this is a sql login, while windows means this is Windows login, and none means it does not need authentication (such as database role).

BTW, the two columns are in sys.database_principals


How do I make the zero values show up in count?

The thing is you are doing all pruning on Mobile or Desktop or both, you haven't add any condition for other than this values.
What I have done is other than this 3 Mobile or Desktop or both values in table, it will count the row & calculate amount if present.
with
mobile_users as (select distinct user_id
                 from spending
                 where platform = 'mobile'),
desktop_users as (select distinct user_id
                  from spending
                  where platform = 'desktop')
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount
 from spending
 where user_id not in (select user_id
                       from mobile_users)
 group by spend_date)
union all
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount
 from spending
 where user_id not in (select user_id
                       from desktop_users)
 group by spend_date)
union all
(select spend_date, 'both' as platform, count(user_id) as total_users, sum(amount) as total_amount
 from spending
 where user_id in (select user_id from
                   desktop_users)
   and user_id in (select user_id
                   from mobile_users)
 group by spend_date)
 union all
(
  /* here the code begins */
select spend_date,  platform, count(user_id) as total_users, sum(amount) as total_amount
 from spending
 where user_id not in (select user_id from
                   desktop_users)
   and user_id not in (select user_id
                   from mobile_users)
 group by spend_date
)
order by spend_date


Unable to trace "Arithmetic overflow error converting numeric to data type numeric" error in profiler

Using the follow link you can set everything up to track the exception This link

In short: Set up a blank trace, add the following events :

Exception under Errors and Warnings
RPC: Completed under Stored Procedures
RPC: Starting under Stored Procedures
SQL: BatchCompleted under TSQL
SQL: BatchStarting under TSQL

From there on, you can see the error popping up in the trace, the query above the error will be your query that's giving the error.

If that's not giving you the exception, i'd debug the .NET code to get the SQL code that's being executed, if you have access to the source code.


In nutshell that what you are looking for.
You do need composite primary key "binding" those 2 FK in intersection table.
(all attributes are omitted for brevity)

USERS can receive zero - or - more messages
Messages can be sent to one - or - more users.

ps.

Tool i used to generate image is a bit buggy - line bewteen mail_box and Users should be dotted.


Error 1366: Incorrect string value: '\xF0\x9F\x98\x8A\x0AC…' for column 'text_body' at row 1

The CHARACTER SET of the column needs to be utf8mb4.  Please provide SHOW CREATE TABLE for confirmation.

Also, the connection needs to be utf8mb4 (or UTF-8, depending on the client language).  Let's see the connection parameters.


master-master replication or master slave replication?

You do not want writes to be replicated from A to B then A better be slave of B, master.

again, to complicate things you can use master-master with replication filters but let's not get there if you do-not wish to.

That said, writing on slave is sort of risky business where you're going to "accidentally" write on databases being replicated from master and that's not good!
As long as you're making sure you're only writing to slave-only databases it should be fine. You should choose to create dedicated users for such activity.
You might also want to wonder:
- What about backups of master data?
- What about backups of slave data?

just saying... but yea to answer your question go with master-slave.


SQL Server Replication with 2 systems in different locations

Probably an entry in your hosts file (in case of a default instance) or a SQL Client alias  will solve the issue.

However, be aware that if you don't configure any security measure, all your TDS traffic will be clear and sniffable. A VPN is your best option in my opinion.


SQL GroupBy Select min return multiple columns

;WITH CTE AS
    (
    SELECT Id, UID, STP, A, B ,C
        ,ROW_NUMBER() OVER (PARTITION BY UID ORDER BY STP) AS rn
    FROM [drp-data].[dbo].[schedules]
    WHERE StartDate <= CONVERT(date, '2011-05-23') AND EndDate >= CONVERT(date, '2011-05-23')
    )
    SELECT  Id, UID, STP, A, B ,C
    FROM CTE
    WHERE rn = 1

please see this post too: Retrieving n rows per group


Difference between ASYNC & SYNC real time apply

In this Oracle page entitled Database High Availability Best Practices it discusses Data Guard Deployment Options:

Requirement of Zero data loss protection and availability for Oracle Database:

Data Guard Maximum Protection or Maximum Availability (SYNC transport) and Redo Apply (physical standby)

Requirement for Near-zero data loss (single-digit seconds) and availability for Oracle Database:

Data Guard Maximum Performance (ASYNC transport) and Redo Apply
NOTE: Asynchronously committed transactions are not protected by Oracle Data Guard against loss until the redo generated by those transactions has been written to the standby redo log of at least one synchronized standby database.

So, yes Maximum Performance (ASYNC transport) and Redo Apply have real meaning, but part of the meaning is that some data can be lost in case of a failure.

You should also consider the network latency for the ASYNC transport target server, since that will also affect responsiveness.


It sounds like your machine is memory starved.  If your app uses a lot of memory and there are two sql instances, one standard and one express, with the default max memory setting if 2 tb, you're probably seeing the sql process cpu spikes as sql's memory is being paged out or returned to the os.  You should set you max and min memory for each instance based on the workload, and also set the maximum memory your application will use if possible.  That way you can ensure all three applications plus the Os have enough memory to coexist.


I think Event Notifications for DATA_FILE_AUTO_GROW and LOG_FILE_AUTO_GROW are the appropriate way. Since the autogrowth events are not errors they cannot be detected by the SQL Agent eventing infrastructure so EN is pretty much the only reasonable alternative.


According to the SQL standard a single SQL query sees a consistent state of the table(s) it operates on. So it must not see any changes while it's running. But not all DBMS products completely comply with that (notably those that allow dirty reads). For example Postgres or Oracle will not block the INSERT while the SELECT is running (or vice versa). But different DBMS do behave differently in that situation depending on their configuration. - a-horse-with-no-name


Oracke RMAN: Recover DB when backup folder has changed

The entries of backup pieces are still pointing to /xxx/yyyy in the controlfile.

After you reach MOUNT stage, catalog the backup pieces from their new location. In RMAN:

catalog start with '/aaa/bbb';

After this, continue as you normally would.

Update:

For 9i, the above is not possible, there is no such option in 9i, you can not catalog backup pieces, this feature was added in 10g. The solution is in the below note:

how to restore or clone a 9i database to different host when backup location is different (Doc ID 1451140.1)

It suggests mounting the database (controlfile) with a 10g software, use the above catalog command, then shut down the instance, and continue with the 9i software.

I think it is easier to create a symlink /xxx/yyyy pointing to /aaa/bbb.


The performance impact from multiple logical reads could be a result of your UDF.

Provided is a great article which describes using inline table-valued UDFs to reduce the row-by-row calls to the scalar UDF. UPDATE: I noticed you were already using table-valued UDF.

http://dataeducation.com/scalar-functions-inlining-and-performance-an-entertaining-title-for-a-boring-post/

To replicate your scenario, I executed the following query:

DECLARE
   @Measurements TABLE(Precidence int, -- the relative scale of a measurement
             Measurement varchar(max), -- Grams, Kilograms, Liters, etc
             MeasurementType varchar(max)); -- Weight, Volume, etc`

DECLARE
   @Items TABLE(ItemType varchar(max),
            Quantity float,
            Measurement varchar(max),
            ToMeasurement varchar(max));

insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','G');
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','KG');
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','ML');
insert into @items (ItemType, Quantity, Measurement) values ('Widget','1','L');

insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','G','Weight');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','KG','Weight');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('1','GAL','Volume');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('2','L','Volume');
insert into @Measurements (Precidence, Measurement, MeasurementType) values ('3','ML','Volume');

Afterwards, I complied and executed the following update statement:

WITH Items_CTE AS
   (
        SELECT A.ItemType,
               A.Quantity,
               A.Measurement,
               (SELECT TOP 1 M.Measurement FROM @Measurements M
                JOIN @Items C ON C.Measurement = M.Measurement
                WHERE M.MeasurementType = B.MeasurementType
                AND  C.ItemType = A.ItemType
                 ORDER BY Precidence)ToMeasurement
       FROM @Items A
       JOIN @Measurements B
       ON A.Measurement = B.Measurement
    )
UPDATE @Items
SET ToMeasurement = cte.ToMeasurement
FROM ITEMS_CTE as cte
JOIN @Items as i
ON cte.measurement = i.measurement;

The UPDATE statement uses a CTE and the results were as follows:

ItemType | Quantity | Measurement | ToMeasurement
Widget   |    1     |    G        |    KG
Widget   |    1     |    KG       |    KG
Widget   |    1     |    ML       |    L
Widget   |    1     |    L        |    L


Old question I know, but if anyone is still looking for one potential workaround...

I wrote a small utility (shared on my blog) that makes life easier when working with this DMV.  This utility takes a baseline of the query stats to act as an effective zero.

The utility then queries the DMV repeatedly and works out the changes to show what has executed over the time since the last call into the DMV.

For more details, see:
http://cbailiss.wordpress.com/2014/10/01/free-tool-sql-workload-profiler-for-on-premise-and-azure-sql-db/


Disable MEMORY_TARGET after being locked out of database?

Find the name of the spfile on the filesystem, it should be something like this:

/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora

You should not edit the spfile directly as it contains binary data, but you can create a pure, text-based parameterfile from it:

sqlplus /nolog
create pfile='/tmp/initXE.ora' from spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora';
exit

Edit /tmp/initXE.ora, modify the values you want, then recreate the spfile from it:

sqlplus /nolog
create spfile='/u01/app/oracle/product/11.2.0/xe/dbs/spfileXE.ora' from pfile='/tmp/initXE.ora';
exit

Once it's done, try restarting the database.


Can I use Try_Cast to give me a query result column with different datatypes?

All output of the case expression must be of the same datatype.

If maintaining the XML datatype for the valid XML data is worth a little manipulation of the invalid data, then one workaround could be to wrap the invalid data up like a declaration before casting it to XML:

cast('<?invalid ' + RunLog + ' ?>' as xml)

Also, you shouldn't need to use both try_cast and cast in the same expression.

coalesce(try_cast(RunLog as xml),cast('<?invalid ' + RunLog + ' ?>' as xml))


You can accomplish this a couple of different ways, but probably the best way would be with a table partition.  First you create your filegroups in the database and add at least one file to each.  Make your existing table a partitioned table by following the wizard (there's more here than meets the eye.  You will need to read up on table partitioning).  Create a staging table that matches the structure of your existing table, but without any indexes or constraints or anything. Load your 1TB of data into the staging table. Once it is all loaded, then add your indexes and contraints and stuff.  Then, you ALTER TABLE stagingtable SWITCH TO existingtable.

Since you didn't even know that table partitioning existed, you'll want to educate yourself on what a table partition is before you up, jump and implement in production:
http://www.brentozar.com/sql/table-partitioning-resources/

Not only should you read up on best practices, but you will want to try it in your test environment first:  http://msdn.microsoft.com/en-us/library/cc966380.aspx


MySQL write performance after truncate & optimize

TRUNCATE TABLE throws away the table and creates a new, empty table, with the same schema.  There is nothing left to OPTIMIZE.
If you are really throwing away the data, why did you keep it?  Or, maybe you would prefer to toss the "oldest" part of the data each week?  And do it with virtually no impact on writes?  If so, you have a classic case for PARTITIONing.  Use PARTITION BY RANGE(TO_DAYS(..)) and a weekly task to DROP the  oldest partition and deal with making a new partition.  Details:  http://mysql.rjweb.org/doc.php/partitionmaint
Be sure to have innodb_file_per_table=ON.
(If this does not address your Question, please elaborate on it.)


Extract Transform and Load is the preparation of foreign data to be inserted into your database or data warehouse
Looking at the basics of the ETL, noted Data Warehouse Designer Bill Inmon notes:

Once upon a time in the not so distant past, there was no ETL (extract, transform and load) software. If you wanted to build a data warehouse, you had to write code in order to get data from one source to the appropriate target. There was lots of code – lots of repetitive code.
After you wrote your code, you had to maintain it. Every time a legacy system changed, you did manual maintenance to your code. Every time a target definition changed, you had to do manual maintenance to your code. Every time an end user wanted something new, you had to do maintenance to your code.

From here, a plethora of ETL products proliferated, as Inmon describes in his brief history of ETL products. They were popular because they were software tools designed to extract data from changing systems, transform it according to specific rules, and load it into data warehouses. This software process meant that humans were involved only in the critical element of the loop: untangling loaded data errors. By automating to the maximum possible extent, the ETL process provided companies a seamless way of not only loading their current databases into a data warehouse, but the ability to load future data sets of the same databases in, so that the data warehouse can continuously provide future results.
To answer your question specifically, different databases provide different extracts. Transformations are applied to normalize the data. Normalization is both in the database-specific sense, changing the patterns of the data to match the receiving data warehouse, but also in the human sense, insuring that the same data in different systems appears the same to the incoming system.
Data sources can be anything you can code a transform for, as the purpose of the transform is to apply rules to the incoming data such that it fits your data model. Joining different data sets should only be performed if necessary. Rely on your recipient database instead to synchronize results.


Is Overwriting the PFILE with the database on dangerous?

Why do you need it? pfile is read only once when database starts.
Use spfile and alter system set parameter = value scope=memoryor alter system set parameter = value scope=both.


SQL Server - Varbinary Column - Extremely Slow Statistics Update

This can be addressed in two additional ways:

Enable Asynchronous Auto Update Statistics and the database level (not my preference). If your production is stuck because due to this issue then this is the quick fix as it is instantaneous but it globally affects the database and can produce sub-par query plans since the plan is generated prior to the stats update.

ALTER DATABASE [DatabaseName] SET AUTO_UPDATE_STATISTICS_ASYNC ON WITH NO_WAIT

Remove the auto generate stat and replace it with a manually created one with no-recompute. The downside is that, while this is a surgical approach, it takes some time to create the statistic and will cause blocking.

DROP STATISTICS [dbo].[TableName].[_WA_Sys_00000004_7D78A4E7]
GO
CREATE STATISTICS [_Manual_Stat_Name] ON [dbo].[TableName]([Column]) WITH NORECOMPUTE;
GO

When we were forced to deal with a similar issue we went with option 1 first and later option 2. During a "green zone" we reverted the database wide setting and then replaced the column's statistic with the manually created NORECOMPUTE one.


optical_road {OPTR_ID, OPTR_NAME, OPTR_CLIENT}
          PK {OPTR_ID}

fiber {FIB_ID, FIB_NUMBER, FIB_OBJECT, FIB_COLOR}
   PK {FIB_ID}

fiber_road {FIB_ID, OPTR_ID}
        PK {FIB_ID}

       FK1 {FIB_ID}  REFERENCES fiber {FIB_ID}
                     ON DELETE RESTRICT

       FK2 {OPTR_ID} REFERENCES optical_road {OPTR_ID}
                     ON DELETE RESTRICT

Note:

PK  = PRIMARY KEY
AKn = ALTERNATE KEY (UNIQUE)
FKn = FOREIGN KEY


Huge logs are generated in mongo db shard logs

If you are using Linux or Unix as your platform, you could setup a shell script that monitors the size of the log files, and send the SIGUSR1 signal to mongod which forces it to cycle the log files once the files reach a certain size.

This Mongo documentation shows how to send the signal here

kill -SIGUSR1 <mongod process id>

You can get the <mongod process id> using:

ps -e | grep -i "mongod"


On reboot listeners don't start but restarting the service after will start them

This was a problem with the hostname not getting set consistently on boot, and was not correctly available at that time but was later.


I got this same error on an Amazon Linux box (AWS EC2). Here's what I did to get Percona toolkit installed.

# Installs Percona's Perl deps...
yum install perl-Time-HiRes
yum install perl-DBD-MySQL
yum install perl-IO-Socket-SSL     # I actually didn't need this one,
                                   # but based on your error you probably will.

# Install Percona toolkit
rpm -Uhv http://www.percona.com/redir/downloads/percona-toolkit/LATEST/RPM/percona-toolkit-2.2.7-1.noarch.rpm

I'd suggest getting the URL for the Percona toolkit RPM from their website under the "Download Latest" link. (Latest for me at the time of this post was 2.2.7.) The website offers RPM and deb formats; just choose RPM, and then use the link it shows.

I know this doesn't address the OP's problem as described, but it does address his root problem (can't get Percona toolkit installed). I agree with Peter's answer in that a package manager like yum or apt should be used... if for no other reason than handling nasty dependency hell, but I don't know Percona's yum or apt repo... or if they even still have one.

Edit: Also, those are just the Percona toolkit dependencies that I had missing on my box. You may have more or less. Another EC2 instance of mine already had all of the Perl dependencies installed, so all I needed to do was run the rpm command. A Percona repo sure would be great to have!


oracle to PostgreSQL database conversion

PostgreSQL has the SQL_standard information_schema which is actually a bunch of views backed by the pg_catalog schema.

information_schema has a fixed structure as mandated by the standard, pg_catalog is liable to change between major PostgreSQL versions.

For the information you want it's probably the table information_schema.constraint_table_usage.


Oracle has pretty extensive auditing capabilities. This page is part of the Oracle 2 Day + security guide. It will run you through some of the capabilities, plus has a tutorial on setting up an audit trail.

Each hour you could query DBA_AUDIT_TRAIL and write the results out to a text file.


Why use Ola Hallengren DatabaseBackup method with Idera SQLSafe?

Ola's solution blends with third party softwares

LITESPEED     LiteSpeed for SQL Server
SQLBACKUP     Red Gate SQL Backup Pro
SQLSAFE Idera SQL Safe Backup

It allows you to leverage automation and has a lot of flexibility in-terms of different parameters e.g. blocksize, compression, etc that you might end up writing when deploying the software to many machines.

See the comparison chart : https://www.idera.com/productssolutions/sqlserver/sqlsafebackup/best-sql-backup-software


How to grant multiple users privileges; MySQL

The GRANT syntax allows you to specify multiple users in a single query. You can't do it with a wildcard, but if you already have the list you can do this:

GRANT SELECT,INSERT,UPDATE ON db1.*
TO 'user1', 'user2', 'user3', 'user4', 'user5', 'user6';


If dirty reads is not an issue in this scenario, you could try the table hint readuncommitted or nolock..

pub.name n WITH(NOLOCK)

join pub.[family-guardian] fg WITH(NOLOCK)


When performing a backup of a database, MySQL locks the tables by default in order to ensure that the data does not change while the backup is under way. Instructing MySQL to skip this process opens the data to the possibility that some user will insert or delete or do some other operation that would result in an incomplete backup. Performing a normal query should not be a problem since data is not being changed.

You might want to try an insert or delete operation while a backup is under way to test more fully. The type of database ISAM/Innodb is also an issue since the behaviour may be different.

See for example MySQL docs and a similar question here.


What happens when the buffer pool size is insufficient in mysql

If MySQL properly started and InnoDB initialized with the configured buffer pool size then there is no reason for any error (barring some bug of course). The buffer pool is just that - a memory area used to buffer your IO. When there is not enough of it to keep all the active data, the server just reads them from disk.

If you had for example 100GB table then many servers won't have enough RAM to contain it and still you may execute a query running full table scan.

The performance will be worse (probably substantially, 1-2 orders of magnitude or more) than in case there were enough memory.


How to join two table without getting duplicate from both left & right table in Oracle


You seem to have already the solution yourself:

UPDATE thetable SET c = CONCAT(a, b)


Ok, so everything I read says best practice is to us NT authentication in SQL
  server

Read more. Please. Because while there are good reasons to do that in certain scenarios, there are good reasons NOT to do it in other scenarios.

  And I can see some advantages to that but using NT auth how do I force users
  to access the data using only my application and not some other tool such as
  SSMS?

Since maybe 25 years or so a standard approach to program applications is to have a SERVER. The user connects to the SERVER and ONLY the server connects to the database. Problem solved.

  Am I missing something here?

Stating the obvious: A lot more reading than you did so far.

  Is there a way to block SSMS and other database tools from connecting to my
  application's database?

Your reading did not get to application passwords in the documentation (https://msdn.microsoft.com/en-us/library/ms181491.aspx is titled CREATE APPLICATION ROLE) which would allow an application to post-authenticate with a password. Not that this is a good approach - but it sis AN approach that was introduce some versions back. This way rights could be on an application, and unless the user knows the role password of the application - out of luck.

But really, the standard approach is to use an application server and only the app server can connect. And often this does not use NT authentication as the connection password is coded in a server config file.


mysqldump: Got error: 1044: Access denied for user 'username'@'localhost' to database 'databasename' when using LOCK TABLES

I know it's really late, but I found this question when I faced the same problem. So, in case anyone need, I found two possible solutions, either:

your user is missing the LOCK privilege, so you should ask your database administrator to grant it to you
run the same mysqldump command, simply adding the --single-transaction flag, eg. mysqldump --single-transaction -u user -p ...


Getting "invalid authorization specification" when creating linked server

Figured out the problem. Even though the user I was adding to the Linked Server security section had permissions into the target database, the user I was logged into the SQL Server instance at the moment didn't. Since the error message is incredibly unhelpful (it doesn't say which user failed to log in), I discovered this entirely by accident.


They can read/write in every schema because they have db_datareader and db_datawriter fixed roles assigned. These roles allow the user to read and write to any user table in the database ( http://msdn.microsoft.com/en-us/library/ms189121.aspx ).

(btw db_ddladmin also allows them to run ANY DDL commands in the database, which might cause you some problems if they start dropping/modifying each others objects)

If you revoke these role grants, you can then assign explicit schema permissions to a user, ie:

GRANT select, insert ON SCHEMA :: schema1 to user1;

More details at http://msdn.microsoft.com/en-us/library/ms187940.aspx

If you can't revoke these roles because they're in use elsewhere, then you'll need to explicitly deny permissions on the schema for the users you don't want to have access. (A deny at the schema level overrides the database level grant from the fixed role).


A Customized Table with Chronological order

CREATE TYPE [tvf_id] AS TABLE
(
    [id] [int] NULL
    ,[OrdCol] [INT] NOT NULL
)
GO

CREATE PROCEDURE [sp_Test]
    @pID tvf_id READONLY
as
begin
    set nocount on
    SELECT a.*
    FROM datatable a inner join @pID b on a.id = b.id
    ORDER BY b.OrdCol ASC
end

GO

--------------------------------------------------------

DECLARE @data tvf_id INSERT INTO @data([id],[OrdCol])
VALUES (8,1), (2,2), (4,3), (1,4), (3,5);

exec sp_Test @pID = @data


How to sync two MySQL high load tables with 10M rows?

pt-table-sync from Percona Toolkit will do this sort of thing.


There's no right or wrong answer to this without considering the requirements, but if you need to do anything except return to the client the entire XML blob -- based on some other criteria than the XML contents itself -- XML fields are not a good idea.

For searching, while XML indexes can mitigate some of the performance problems, there is a big storage penalty to pay for those. Since this question is tagged data-warehouse, this kind of solution (your option 2) is almost certainly inappropriate for all kinds of reasons.

The one thing you definitely want to do is shred the XML and put all commonly-used (searched, selected) attributes in relational fields. Depending on the structure of the XML, this could mean a single table, or it could mean multiple tables.

The only question is what to do with everything else that doesn't fit. The remaining fields could continue to live as XML if the schema has to be really flexible (probably a good idea to strip out everything else to save storage space). You could also consider implementing an architecture based on sparse columns (SQL Server 2008+) if the structure is more predictable.

If you need to put things back into XML for client applications, FOR XML will likely be your best friend.


How to calculate total ON hours and Total OFF hours in day of a motor using PHP and MySQL

A table and some data. I ignored your primary key here, but I wouldn't do that in production.

CREATE TABLE motor_status_log (
  user_id integer not null,
  tank_id integer not null,
  motor_id integer not null,
  timestamp datetime not null,
  runningstatus varchar(3))
;

INSERT INTO motor_status_log VALUES
    (1, 1, 1, '2013-06-26 09:53:29', 'off'),
    (1, 1, 1, '2013-06-26 10:45:42', 'on'),
    (1, 1, 1, '2013-06-26 10:55:29', 'off'),
    (1, 1, 1, '2013-06-26 17:55:39', 'on'),
    (1, 2, 2, '2013-06-26 17:56:39', 'off'),
    (1, 2, 2, '2013-06-26 08:53:29', 'off'),
    (1, 2, 2, '2013-06-26 10:45:42', 'on'),
    (1, 2, 2, '2013-06-26 12:55:29', 'off'),
    (1, 2, 2, '2013-06-26 14:55:39', 'on'),
    (1, 3, 3, '2013-06-26 17:56:39', 'off');

You can get the basic data you need with this query. You probably want to take a hard look at the subquery that starts on the second line, and returns the column "run_end".

select t1.tank_id, t1.motor_id, t1.timestamp as run_start
  , (select min(timestamp)
     from motor_status_log t2
     where t2.tank_id = t1.tank_id
       and t2.motor_id = t1.motor_id
       and t2.timestamp > t1.timestamp
       and t2.runningstatus = 'off') as run_end
from motor_status_log t1
where t1.runningstatus = 'on'
order by t1.tank_id, t1.motor_id, t1.timestamp;

You can use that in a FROM clause, and calculate the minutes of run time with timestampdiff().

select *, timestampdiff(minute, run_start, run_end) elapsed_min
from
  (select t1.tank_id, t1.motor_id, t1.timestamp as run_start
    , (select min(timestamp)
       from motor_status_log t2
       where t2.tank_id = t1.tank_id
         and t2.motor_id = t1.motor_id
         and t2.timestamp > t1.timestamp
         and t2.runningstatus = 'off') as run_end
  from motor_status_log t1
  where t1.runningstatus = 'on') t
order by tank_id, motor_id, run_start

TANK_ID  MOTOR_ID  RUN_START                   RUN_END                     ELAPSED_MIN
1        1         June 26 2013 10:45:42+0000  June 26 2013 10:55:29+0000  9
1        1         June 26 2013 17:55:39+0000  (null)                      (null)
2        2         June 26 2013 10:45:42+0000  June 26 2013 12:55:29+0000  129
2        2         June 26 2013 14:55:39+0000  June 26 2013 17:56:39+0000  181

I'd presume that the 'off' time for each day would be 24 hours minus the run time. I could be wrong.

You need to watch for 'on's that don't have corresponding 'off's, and vice-versa. (Motor 3 has an 'off' with no corresponding 'on'.) It's not clear what you might want to do about that kind of situation.  You could

ignore it,
treat it as a maximal run period (which would probably guarantee that preventive maintenance would get done earlier rather than later),
treat it as an average run period, or
something else I haven't imagined yet.

I ignored it.

Summing elapsed time

To sum elapsed time the simplest way, wrap the previous query in another select. But this "simplest way" can give you misleading numbers if you have motors that are on over a period of days. (You can solve that problem with an auxiliary table, though.)

select motor_id
       , date(run_start) as start_date
       , sum(elapsed_min)/60.0 as hrs_on
       , 24 - sum(elapsed_min)/60.0 as hrs_off
from
  (select *, timestampdiff(minute, run_start, run_end) elapsed_min
   from
     (select t1.tank_id, t1.motor_id, t1.timestamp as run_start
       , (select min(timestamp)
          from motor_status_log t2
          where t2.tank_id = t1.tank_id
             and t2.motor_id = t1.motor_id
             and t2.timestamp > t1.timestamp
             and t2.runningstatus = 'off') as run_end
    from motor_status_log t1
    where t1.runningstatus = 'on') t
  ) tt
group by motor_id, start_date
order by motor_id, start_date

motor_id  start_date  hrs_on  hrs_off
--
1         2013-06-26  0.15    23.85
2         2013-06-26  5.17    18.83


Getting the exact and closest Search using CONTAINS/CONTAINSTABLE in SQL

Your latest query allows the same row to be returned twice. Also it,'s possible to limit the results considering a predefined weight for the match in order to get a more relevant output.

You should get a more precise result with this:

WITH CTE
AS
(
    SELECT 10000 AS RANK,
        P.id,
        P.value
    FROM ProductCodes P
    WHERE P.value = 'pepper'

    UNION

    SELECT k.RANK,
        P.id,
        P.value
    FROM ProductCodes P
        INNER JOIN
          CONTAINSTABLE(ProductCodes, value, 'ISABOUT (pepper WEIGHT(0.2),
            "pepper*" WEIGHT(0.1))',
            5) AS K
          ON P.id = K.[KEY]
    WHERE value <> 'pepper'
)
SELECT TOP (5) RANK, id, value
FROM CTE
ORDER BY RANK DESC;

References:

Search for words or phrases using weighted values (Weighted Term)

Examples of Using RANK to Limit Search Results

WITH common_table_expression (Transact-SQL)


The statement "Since last month we had several occasions of DBCC CHECKDB crashing to this error..." makes me think that there was a change done on your system last month that may have caused this.  Was anything implemented last month to your knowledge?  For example any patches, updates to SQL Server, and applications were installed or upgraded on the SQL Server, etc...

Have you tried the below post to see if the issue is an OS Memory issue or if the error is caused by SQL?

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ff936d46-d210-4326-a112-0501a366ea41/dbcc-checkdb-failure-sql-2012-there-is-insufficient-system-memory-in-resource-pool-internal-to?forum=sqldatabaseengine

You could try to split out the DBCC CheckDB command to help alleviate any resource constraints.

Dividing DBCC CHECKDB over multiple days


If you are running the batch file through xp_cmdshell you are using a different set of permissions.  Here is the BOL entry for xp_cmdshell.

Basically though if you are running it as a sysadmin then xp_cmdshell uses the service account for SQL Server, not the service account for the Agent.  If you are not running it as a sysadmin then it uses a proxy account.  The proxy account is stored in credential called ##xp_cmdshell_proxy_account##.


The current security context cannot be reverted

There is no way to find it out, but you can try all the databases accessible by that login, i.e. under impersonated login execute the following code:

select 'use ' + name + '; revert;'
from sys.databases
where HAS_DBACCESS(name) = 1;

This way you find all the databases from which you could impersonate this login. Then you copy the output to query window and try it row by row until success.

But the easiest way to return yourself is just to change connection (right click in query window)


SQL Server 2019 Database stuck In Recovery state after restart

There's not really anything you can do. Your database is going through crash recovery, and it needs to process the transaction log. I've written about this exact scenario more in depth here.
If SQL Server hadn't completed a CHECKPOINT recently, there would be database changes that were committed, but had only been made in memory & in the transaction log, but not in the data files. These changes need to be redone from the log file so that the changes aren't lost. This is what your server is doing right now. I describe the phases in more detail in the article I linked to earlier.
If your transaction log is unnecessarily very large, this can cause SQL Server to do checkpoints less frequently. A too large log file, or too many VLFs can both result in a longer recovery time after a crash.
For a planned restart you can minimize/eliminate the delay by performing a manual CHECKPOINT; before shutting down and by allowing the SQL Server service to gracefully stop. Rebooting the server without first stopping the service can result in a forced termination of the sqlservr.exe process, leading to recovery like you're seeing on startup.
To get your database back online, just be patient. Don't restart, don't try to get rid of your log file. Just be patient. That estimated recovery time is notoriously incorrect, so hope that it's online sooner, rather than later.


Allow multiple users to change functions in MySQL

As MySQL's documentation says:

  The ALL or ALL PRIVILEGES privilege specifier is shorthand. It stands
  for “all privileges available at a given privilege level” (except
  GRANT OPTION). For example, granting ALL at the global or table level
  grants all global privileges or all table-level privileges.

Just use:

GRANT ALL ON test.* TO 'admin'@'%';
FLUSH PRIVILEGES;

Test:

mysql> CREATE USER 'admin'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE USER 'devel1'@'%' IDENTIFIED BY 'test';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON test.* TO 'admin'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT ALL ON test.* TO 'devel1'@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW GRANTS FOR 'devel1'@'%';
+-------------------------------------------------------------------------------------------------------+
| Grants for devel1@%                                                                                |
+-------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'devel1'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'devel1'@'%'                                                      |
+-------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW GRANTS FOR 'admin'@'%';
+------------------------------------------------------------------------------------------------------+
| Grants for admin@%                                                                                |
+------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'admin'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'admin'@'%'                                                      |
+------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)


How to implement paging in an effective way?

The query will be executed every time. SQL Server does not cache results.

Proper paging won't be implemented until SQL Server 2011, but until then your options (as you identified) are:

ROW_NUMBER() and queries on demand
client caching

A prepared SQL statement will be executed every time too.

If you have a fat client then caching is local to the client. This is mostly OK unless you have millions of blobs for example.

In our web client we render all the results but only show the top 100 and have a "Show all" button than expands a hidden DIV with rows 101+. We don't cache in the web server and we don't offer paging.


Configuration required for a big mysql database around 20GB

The main configuration settings are in my memory blog.  Most important is 20% of RAM for key_buffer_size.  (However, it does not need to be more than the sum total of Index_length of all the SHOW TABLE STATUS.  This will also do the arithmetic:

SELECT  ENGINE,
    ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",
    ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",
    ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB",
    COUNT(*) "Num Tables"
FROM  INFORMATION_SCHEMA.TABLES
WHERE  table_schema not in ("information_schema", "performance_schema")
GROUP BY  ENGINE;

Beware of a large WP installation -- it uses one database, hence one directory, for each 'user'.  After thousands of users, the OS will bog down because of this.

TokuDB's niche is elsewhere; I would not expect it to be useful for this task.


How to send Unicode symbols using SQL Server Database Mail?

Make sure you always use nvarchar as the data type throughout the whole flow, and always prefix Unicode strings with N. There is a big difference:


Pass a table array as a parameter to an upsert function in postgresql

I think yes.  Haven't tried with actual INSERTs or UPDATEs yet, but this function works:

CREATE TABLE a (id integer);

CREATE OR REPLACE FUNCTION arraytest2(rec a[]) RETURNS SETOF integer AS
$body$
SELECT b.*
FROM unnest($1) b;
$body$
LANGUAGE sql;

This way you can write your INSERT statement as

INSERT INTO tbl_weightment
SELECT (r).*
FROM unnest(rec) r;

And UPDATE as

UPDATE tbl_weightment
SET
    vname = r.vname,
    iquality = r.iquality,
    vhooks = r.vhooks,
    tstamp = r.tstamp
    vtare = r.vtare,
    vgross = r.vgross
FROM unnest(rec) r
WHERE id=rec.id;


PostgreSQL user can not connect to server after changing password

It's possible that you're being bitten by this PgAdmin bug (changelog):

  2012-11-28 AV  1.16.1  Date picker controls returns a full timestamp
  by
                         default, which can cause inadvertent date changes
                         on jobs and role validty dates. Ignore the time part.

This bug has been seen to set password expiry dates far in the past, such as 1/1/1970. In this case the error message when trying to connect is no different than with a wrong password.

You can check these expiry dates with:

SELECT usename,valuntil FROM pg_user;

and if they're wrong, reset them with:

ALTER USER username VALID UNTIL 'infinity';

and upgrade pgAdmin.


Connecting to a SQL server whose IP changes

The most efficient way to connect to a server is to use it's hostname and not an IP address. The local DHCP should be updating the DNS records so connecting to a hostname should always work, if not talk to the people configuring your network.


MS SQL Back up not restore the MS SQL 2014?

You are trying to restore a database that was backed up from a version of sql server that's Enterprise and is using Enterprise features like data compression - my guess is you are trying to restore this backup to either Standard or Express which don't support those Enterprise features until Sql 2016 SP1 - if possible, upgrade your 2014 edition to 2016 SP1.

The steps involved in upgrading are too numerous for this Q/A environment.

If you decide to upgrade, check the information in this link Upgrade SQL Server.  Additional information can be found by searching for

  upgrade sql 2014 to 2016

Sql 2016 SP1 has a number of features available in ALL Sql Server editions (Standard, Express, etc.) that used to be only available in Enterprise edition.  For more information on those new features, please check

SQL Server 2016 Service Pack 1 (SP1) released

Upgrading from one release of Sql Server to another needs planning and testing to prevent unexpected surprises.

Instead of upgrading the current Sql Server 2014 environment that you are trying to restore to, you might be better off installing a brand new Sql Server 2016 Express instance and applying SP1 to that new install.  Then restore your database to that new instance.  Be aware that Sql Server 2016 Express edition can only accommodate database sizes of 10GB, so depending on the size of the database you are trying to restore - that may limit your ability to use Sql Server Express


function to check whether the current server is involved in an availability group and if so, whether it is the primary

We use Distributed Availability Groups (and some servers that are just in a straight AG) and ran into a similar issue.  We eventually settled on this query as our end all, be all solution. It's probably more complicated than it needs to be, but it does work.

DECLARE @DBName sysname;
DECLARE @IsPrimary BIT = 0;

--Determine if the database selected is online.
;WITH CTE_DAG
AS
    (   SELECT AG.[name] AS DAGName
               , AG.is_distributed
               , AR.replica_server_name AS UnderlyingAG
               , ARS.role_desc
        FROM sys.availability_groups AS AG
            INNER JOIN sys.availability_replicas AS AR ON AR.group_id = AG.group_id
            INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = AR.replica_id
        WHERE AG.is_distributed = 1)
     , CTE_LocalAG
AS
    (   SELECT AG.[name] AS LocalAGName
               , AG.is_distributed
               , AR.replica_server_name AS UnderlyingAG
               , ARS.role_desc
               , D.[name] AS DatabaseName
               , DRS.is_primary_replica
        FROM sys.availability_groups AS AG
            INNER JOIN sys.availability_replicas AS AR ON AR.group_id = AG.group_id
            INNER JOIN sys.dm_hadr_availability_replica_states AS ARS ON ARS.replica_id = AR.replica_id
            INNER JOIN sys.dm_hadr_availability_replica_cluster_states AS ARCS ON ARCS.group_id = AG.group_id
            LEFT OUTER JOIN sys.dm_hadr_database_replica_states AS DRS ON DRS.replica_id = ARCS.replica_id
                                                                           AND DRS.group_id = ARCS.group_id
            INNER JOIN sys.databases AS D ON D.database_id = DRS.database_id
        WHERE AG.is_distributed = 0
            AND ARCS.replica_server_name = @@SERVERNAME)
     , CTE_Composite
AS
    (   SELECT L.DatabaseName
               , L.role_desc
               , L.is_primary_replica
               , COALESCE(D.role_desc, 'NONE') AS DAG_Role
               , IsAllPrimary = CASE WHEN L.is_primary_replica = 1
                                         AND COALESCE(D.role_desc, 'NONE') IN ('NONE', 'PRIMARY')
                                          THEN 1
                                    ELSE  0
                                     END
        FROM CTE_LocalAG AS L
            LEFT OUTER JOIN CTE_DAG AS D ON D.UnderlyingAG = L.LocalAGName
        WHERE L.DatabaseName = @DBName)
     , CTE_Grouping
AS
    (   SELECT DatabaseName
               , SUM(IsAllPrimary) AS TotalPrimary
               , COUNT(DatabaseName) AS TotalCount
        FROM CTE_Composite
        GROUP BY DatabaseName)
SELECT TOP (1)
       @IsPrimary = 1
FROM CTE_Grouping
WHERE COALESCE(TotalPrimary, 0) = COALESCE(TotalCount, 0);


Changing all character varying to text across all tables and columns

For one-off changes, create a query to generate the SQL commands so that you can review them:

SELECT format('ALTER TABLE %I.%I ALTER COLUMN %I SET DATA TYPE text;',
              table_schema, table_name, column_name)
FROM information_schema.columns
WHERE udt_name = 'varchar'           -- '_varchar' for arrays
  AND table_schema = 'my_schema';


T-SQL OUTPUT DELETED columns by information schema

I find out the way to do it with the dynamic sql, we just need to compose query, where we can select all required column names using information schema and execute it.

Get column names first:

DECLARE @ColumnNamesTable Table([Name] NVARCHAR(MAX));
INSERT INTO @ColumnNamesTable
SELECT [Name] FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'TableFirst') AND [Name] <> 'Id'
INTERSECT
SELECT [Name] FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'TableSecond')

Compose OUTPUT DELETED and column name strings:

DECLARE @OutputDeleted NVARCHAR(MAX), @ColumnNames NVARCHAR(MAX);

SELECT @OutputDeleted = STUFF((SELECT ', DELETED.' + Name FROM @ColumnNamesTable  FOR XML PATH ('')), 1,1,'')
SELECT @ColumnNames = STUFF((SELECT ',' + Name FROM @ColumnNamesTable  FOR XML PATH ('')), 1,1,'')

Compose query:

DECLARE @Query NVARCHAR(MAX), @MyId INT = 5;
SELECT @Query = CONCAT('DELETE FROM dbo.TableFirst OUTPUT', ' ', @OutputDeleted, ' ','INTO dbo.TableSecond(', @ColumnNames,') WHERE Id = ', @MyId)

Finaly we just execute the composed query:

exec sp_executesql @Query;

But we can get issues when there are some FKs.


MySQL using AUTO_INCREMENT of secondary coloum of index, and FOREIGN KEY at the same time

I discuss the problem here, including a couple of workarounds.

The simple workaround is to have local_id be AUTO_INCREMENT and abandon the requirement for the values starting over at 1.

Keep in mind that ROLLBACK may cause you to lose ids.


Data Warehouse Kimball Customer and Customer Address Dimensions

The right schema depends on how you intend to use this data. Let's talk about your options:

a) Bridge table.

I would not use this approach, because bridge tables are used to express N:N relationships. If you go for it, that would not be possible to precisely say what is the right address for your transaction; so it would be useless.

Following Kimball group -> "Bridge tables are used...where a many-to-many relationship can’t be resolved in the fact table itself..." https://www.kimballgroup.com/2008/09/design-tip-105-snowflakes-outriggers-and-bridges/

b) Create another dimension DimAddress (DimGeography?)

This looks a good approach, especially if this Address information could be related to other Fact tables. Maybe you could expand it into a DimGeography, or something like that. In this case, DimGeography and DimCustomer would not be related directly.

Other options

c) You could also change the granularity of your Customer dimension to the Address level; so you would have one row per address and the "master customer data" would be the same for the same customer in this dimension. -> Do that if most of your data is related to the Address level.

d) Another approach would be snowflaking your schema. Creating then a DimCustomerAddress that can be used on this fact; where your fact table points to DimCustomerAddress and DimCustomerAddress points to DimCustomer. In this way, you don't change your Customer table neither the other fact tables and can create the Customer -> Address hierarchy.

e) If you have to track historical address changes (based on comments)

This solution is similar to d), but the DimCustomerAddress would be an SCD type 2. This will make the granularity of your new DimCustomerAddress be the "address history".
I.E: I would assume that your business key for this dimension is the Customer ID, AddressID and Address type, so you should add a new row everytime you get an address change; keeping tracking of the start and end dates that the address is valid. Your fact table should be related with DimCustomerAddress by the surrogate key "CustomerAddressKey", and DimCustomerAddress should relate with DimCustomer using "CustomerKey". The Start and End dates can be used during the ETL time to populate the Fact table to determinate the right "CustomerAddressKey".


Is MySQL more scalable than PostgreSQL due to the difference in how they handle connections?

I'll use a dated example. Suppose you have a webserver serving some static pages and php pages with a database backend. Back in the day, the default config was:
1- Web client connects and apache process/thread is created
2- php interpreter is instanciated
3- database connection is opened
4- queries are done, some html is generated while being sent to client at the same time
5- wait for all data to be transferred to client
6- release php interpreter, database connection, and apache process/thread
This didn't work back then, and it still doesn't work now, because step 5 can take a while. Suppose you need 2 milliseconds of SQL queries and 10 milliseconds running php to generate the response, and 500ms for the HTTP transfer to complete.
The database connection is opened and lasts 500ms, using up valuable RAM and resources on the database server for the whole 500ms, but it is only being actually used for 2ms. That's an efficiency of 0.4%. This means: if the database connection was only used to perform the queries during 2ms, then released for other clients to use, it could perform 250 times more queries per second using the same amount of RAM on your database server. This also means the dumb config that doesn't scale will use hundreds of times more RAM precisely on the server where RAM is the most precious resource of all: the database. Which in practice means it will come down in flames.
The webserver process and php interpreter are only doing useful work for 10ms, but they will occupy valuable RAM on the webserver for 500ms. That's an efficiency of 2%. So, for the same reason as above, this setup would need 50x more webserver RAM than the smart setup.
The smart setup is very simple, it's basically a funnel.
On the wide side of the funnel, you have many slow incoming client connections.
On the small side of the funnel, you have a few webserver processes or database connections.
The funnel is software like g-wan or lighttpd, or a connection pool, which:

gathers input data while using minimum resources, especially NOT using one thread per client

when enough input data is available to generate a response, use a pre-spawned process in a pool to process it, buffer the response, and release the process as fast as possible

transfer data back to the client while using minimum resources

Using this setup, your valuable resources are always working instead of waiting for clients, so you need a lot less of them to produce the same amount of output. With few database connections open, ideally a few connections per core, there is much less contention, locks, and context switching on the database server. In particular, locks are held for a much shorter time. This all makes the database server much faster.
So you are asking the wrong question, it is not about how many concurrent connections you can have on your database server before it falls apart. It is about using tools that allow you to have the ideal number of database connections that make your database perform at maximum throughput, no matter the actual number of client connections on the other side of the funnel.
Postgres does tends to scale better especially when you have reads, writes, and long complicated queries at the same time.


Replication to subscriber with different name

WAL-based replication is physical replication, i.e., it replicates all the changes made on the disk of a database cluster. In other words, all databases and their contents are exactly the same.

If you want replicate with different database names, you can use logical replication, which replicates changes made to specified tables.

However, logical replication cannot change the schema name. If you want to copy changes to a table in a different schema, you could do it manually with triggers and postgres_fdw.


How can I fill in a foreign key value after creating the first entry of the table the foreign key is referencing?

I think your main challenge is the requirement that the Order is in between your User and Cart_Item.

I am assuming you are planning on storing items put in the shopping cart in Cart_Item (items planned to be purchased but not currently purchased.  Also there is only 1 list of items pending purchase per user).  And then items actually purchased by the user (coming from he shopping cart) will land in Order.  In my opinion, there should not be any kind of link between the "order" and the "shopping cart".  If there was, you would need to store some kind of Cart_ItemId on the Order table, not an orderId on Cart_Item.  If you were to keep a Cart_ItemId, you would then have to have some kind of shopping cart history, which to me, seems redundant since that data would be saved in your "order history" though Order. You may have another purpose that I am not thinking of so let me know if I am missing something.

Assuming my understanding of what you are looking for is correct.  I believe this below schema is much closer to giving you what you are looking for, and getting you around this Foreign Key issue. (I apologize, your relationship diagram was so much nicer than mine.  Trying to make it work in Visio instead):

Transferring data from your Cart to Your Order could look something like this:

CREATE PROCEDURE [FinalizeOrder] (@UserID INT, @OrderDetails VarChar(500))
AS
BEGIN
    INSERT INTO Order_Item
    (
        userID,
        orderProductId,
        quantity,
        price,
        [date],
        details
    )
    SELECT
    CI.userID,  --userID,
    CI.cartItemProductID,   --orderProductId,
    CI.quantity,    --quantity,
    P.price,    --price,
    GETDATE(),  --[date],
    @OrderDetails --details
    FROM Cart_Item CI
        INNER JOIN Product P
            ON CI.cartItemProductID = P.ProductId
    WHERE CI.userID = @UserID

    DELETE FROM Cart_Item
    WHERE CI.userID = @UserID
END

Now if it was me, I would create Order and OrderItems tables.  That way you don't duplicate data like the User, or address information on each item.  I know you want to limit tables that are created, but I think that will help you a lot.  If that is a route you end up going, than you will have a schema a little like this:

For a quick example.  Lets pretend I am a user (UserID 13 because that is my favorite number).  I fill out a cart with the 5 items.  The records from the Cart_Item table looks like:

+------------+--------+-------------------+----------+
| CartItemId | UserID | CartItemProductID | quantity |
+------------+--------+-------------------+----------+
|          1 |     13 |               123 |        1 |
|          2 |     13 |               456 |        2 |
|          3 |     13 |               789 |        5 |
|          4 |     13 |            123456 |       77 |
|          5 |     13 |                56 |       45 |
+------------+--------+-------------------+----------+

When I hit the last "submit" in the check out process we would then write 1 record to Order and 5 rows to Order_Item.  Those rows would look something like:

[ORDER]
+----------+--------+----------+--------------------------------------------------------+
| OrderId  | UserId |   Date   |                        Details                         |
+----------+--------+----------+--------------------------------------------------------+
| 98564745 |     13 | 4/6/2020 | Order Placed by Kirk Saunders on 4/6/2020 for 5 items. |
+----------+--------+----------+--------------------------------------------------------+

[ORDER_ITEM]
+----------+--------------------+----------+-------+
| OrderID  | OrderItemProductID | Quantity | Price |
+----------+--------------------+----------+-------+
| 98564745 |                123 |        1 |     1 |
| 98564745 |                456 |        2 |   1.5 |
| 98564745 |                789 |        5 |  7.98 |
| 98564745 |             123456 |       77 | 85.99 |
| 98564745 |                 56 |       45 |  1065 |
+----------+--------------------+----------+-------+

A Stored Procedure to handle this might look like:

CREATE PROCEDURE [FinalizeOrder] (@UserID INT, @OrderDetails VarChar(500))
AS
BEGIN
    DECLARE @OrderId INT

    INSERT INTO [Order]
    (
        userID,
        [date],
        details
    )
    VALUES
    (
        @userID,    --userID,
        GETDATE(),  --[date],
        @OrderDetails --details
    )

    SET @OrderId = scope_identity()

    INSERT INTO Order_Item
    (
        OrderId,
        OrderItemProductID,
        Quantity,
        Price
    )
    @OrderId,   --OrderId,
    CI.cartItemProductID,   --OrderItemProductID,
    CI.Quantity,    --Quantity,
    P.Price --Price
    FROM Cart_Item CI
        INNER JOIN Product P
            ON CI.cartItemProductID = P.ProductId
    WHERE CI.userID = @UserID

    DELETE FROM Cart_Item
    WHERE CI.userID = @UserID
END

Hopefully this helps.  Let me know if I need to explain something better or if this doesn't answer your question.


performance of Replace vs If exists update else insert vs update if row_count()=0 insert

Usually the answer is simply "which one sends more requests to the server".

IODKU - 1
REPLACE - 1
EXISTS - 2 (SELECT, plus either UPDATE or INSERT)
ROW_COUNT - 1 or 2 (ROW_COUNT does not count)

But we have a tie with that metric.  So, let's dissect the winners:

IODKU - Find the row, decide what do, then do it.
REPLACE - DELETE all rows that match on any UNIQUE (or PRIMARY) KEY, then INSERT.

It feels like IODKU will be less work.


Mongodb authentication database best practice

Best practice is keep all users at admin database. However, some people don't like that they need to use --authenticationDatabase admin (or equivalent) when they login. Especially, if they use only one database.


Optimize complicated sql query by either changing query or indexing

I dont have the reputation to add comments so I will take a stab at an answer. My experience is with MSSQL but I think it is transferable here.

It seems like this query is doing a bunch of redundant checks.

Do you get different results from this:

SELECT distinct job_id, truck_id, customer from jobs where driver_id = '$driver'
AND truck_id in
(Select distinct truck_id from jobs where driver_id ='$driver' and time like '$date%'
AND customer IN
(Select distinct customer from jobs where driver_id ='$driver' and time like '$date%' ))
and time like '$date%' ORDER BY customer, truck_id ASC;

than you do from this?:

SELECT distinct job_id, truck_id, customer from jobs where driver_id = '$driver'
and time like '$date%' ORDER BY customer, truck_id ASC;

The check for customer and truck in the table with the same conditions as the main query should not be necessary.


Cpu% usage alert is not working for 'alert if counter=raises above'

I am only able to get SQL Server 2012 to fire an alert based upon the "Workload Group Stats -> CPU Usage %" if I enable a dummy Resource Governor classifier function that classifies all connections into the default Resource Governor group.

I used the following classifier function, with the associated code to enable that function:

USE master;
GO
CREATE FUNCTION dbo.fnDummyClassifier()
RETURNS sysname
WITH SCHEMABINDING
AS
BEGIN
    RETURN N'default'; --every session is assigned to the "default" pool.
END
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = [dbo].[fnDummyClassifier]);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;
GO

I created an alert using this code, that alerts whenever CPU usage rises above 10%:

EXEC msdb.dbo.sp_add_alert @name=N'CPUUsage'
        , @enabled=1
        , @delay_between_responses=0
        , @include_event_description_in=1
        , @performance_condition=N'Workload Group Stats|CPU usage %|default|>|0.1'
        , @job_id=N'00000000-0000-0000-0000-000000000000';
GO

EXEC msdb.dbo.sp_add_notification @alert_name=N'CPUUsage'
    , @operator_name=N'Max'
    , @notification_method = 1;
GO

I then used this query to force CPU usage in a session that is connected to SQL Server after the classifier function has been enabled, and the Resource Governor has been reconfigured:

SELECT CRYPT_GEN_RANDOM(10)
FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(num)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(num)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v3(num)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v4(num)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v5(num)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v6(num)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v7(num)

This query shows the status of the alert, as well as when it was last fired:

SELECT AlertName = sa.name
    , Enabled = sa.enabled
    , LastOccurrence = CASE WHEN sa.last_occurrence_date > 0
        THEN dbo.agent_datetime(sa.last_occurrence_date, sa.last_occurrence_time)
        ELSE NULL
        END
    , OccurrenceCount = sa.occurrence_count
    , PerformanceCondition = sa.performance_condition
FROM dbo.sysalerts sa
WHERE sa.name = 'CPUUsage';


Database is not accessible after 'taking it offline' process failed

You should never do this using detach. Until we know more about exactly what state your database is in now, for next time, you should do the following:

ALTER DATABASE SET OFFLINE
For each file, ALTER DATABASE MODIFY FILE
copy the files to the new location
ALTER DATABASE SET ONLINE
when everything is working, delete the original files

The reason you don't want to do this with detach, and the reason you want to copy the files instead of moving them, is because when something goes wrong in either of those processes, you have exactly 0 copies of your database. Or the situation you're in now.


Table data transfer using SSIS and triggers on destination table

If these triggers won't be fired while transferring data from source to destination table, is there anything can be done to fire these triggers during this data transfer?

In the OLEDB connection manager there are various options to load data. If

table or view using fast-load options is selected, then you have to explicitly specify FIRE_TRIGGERS in the advance editor as shown below :

If table or view data access mode is chosen then your triggers should fire normally because the insert is done row-by-row.