A good dataset to experiment NoSQL databases

[EDIT - added several public dataset sites].

First off, there is no real evidence that NoSQL databases are "better" at handling large datasets than traditional (OldSQL) RDBMSs. Check out Ted Dziuba's article about how he can't wait for NoSQL to die. He makes the point that Walmart continue to use RDBMSs - and they're not a small company!
He says that NoSQL is, and should remain, niche and that in all likelihood, you don't need it. He also makes the reasonable point that Facebook, Google and Twitter are not normal companies with normal data processing needs.

Google Michael Stonebraker's writings on OldSQL, NoSQL and NewSQL (e.g. 1, 2, 3). He makes the point that NoSQL throws the baby out with the bathwater - i.e. NoSQL doesn't enforce ACID transactions and that this is an abomination for a database system. As you'll see from his bio, he's been involved in databases as an academic and in industry for 40 years.

He agrees with  the NoSQL school that OldSQL (think Oracle, MS SQL Server &c.) is "old technology" and needs to be "sent to the home for retired software", and that OldSQL (in this case MySQL) has trapped Facebook in a "fate worse than death".  His point about NewSQL is that for OLTP apps, you need a shared-nothing sharded architecture (check out his VoltDB) and that for OLAP you need dedicated columnar stores, i.e. Vertica (which he sold to HP).

If this doesn't convince you, check out Brian Aker's (former MySQL chief architect) humourous take on NoSQL here.

As for large datasets, I would urge you to Google in the area which particularly interests you. I know that metereological (my dad was one) datasets can be very large and also genomic datasets can also be huge (I studied genetics in uni). This site seems to be right up your alley - with many multi-GB and multi-TB datasets.

[EDIT] Other sites of interest are to be found (1, 2, 3, 4 & 5).

I would strongly urge you to benchmark both RDBMS and NoSQL solutions. As I mentioned, Dziuba says NoSQL is niche - it may suit your particular needs, I don't know. 2GB datasets are now officially small (even tiny - they fit easily on all memory sticks. Nowadays, you've got to be getting into the multiple terabyte region for a database to be getting large. Consider the Apollo moon landing's IT capacity. There was time when 2GB was huge - no so anymore!

Finally, I leave the last word to Ted Dziuba:

  "I'm not just singling out Cassandra - by replacing MySQL or Postgres with a different, new data store, you have traded a well-enumerated list of limitations and warts for a newer, poorly understood list of limitations and warts, and that is a huge business risk".


While testing our workload on SQL Server 2019 we experience much higher CPU utilization compare to the same test on SQL Server 2014

Firstly: Is the CPU use really more for the same amount of work? As performance is 50% faster 33% more work is being done on average in any given period of time, so using 30% more CPU resource cancels down to using the same amount of CPU for the same amount of work, just over a shorter period of time. The fewer page accesses could explain this: the CPU is spending less time waiting for IO to complete between bits of work.

Of course this depends on how you are measuring CPU utilisation - remember that for modern CPUs some readings can be significantly inaccurate (see https://aaron-margosis.medium.com/task-managers-cpu-numbers-are-all-but-meaningless-2d165b421e43 amongst other similar articles). We need more details about how you are making measurements to give truly helpful answers.

we are seeing lower throughput on SQL 2019 because we max out CPU earlier

Again, we need to know how you are measuring throughput. Though as above you may be maxing out the CPU earlier because less IO is needed.
Also: have you compared query plans used between the runs on the different versions? It is possible that some of the queries being submitted are getting less optimal plans under the updated engine - the differences in things like cardinality estimates are usually beneficial but as they are only guestimates can backfire.

and start seeing errors as a result

Never simply report "I got an error". What errors are you seeing?


Economic Database Architecture Possibilities w/ Data of Varying Length

I would separate the problem into transactions and analytics -- based on the question, seems that you are trying to find a design which would be optimal for both.

From a design point -- on the logical level -- I would use something like this, and would not worry about number of tables. Also, each attribute has proper data type, etc.

From this you may periodically (daily) publish to structures which are more analytic-friendly (flat OLAP tables, data marts ...). Depending on the performance -- and user expectation -- exposing 5NF views may be good enough.

On the physical level, I am not so sure  :(

Structures like this are usually exposed to users as flat views (5NF) and via point-in-time functions. The main problem here is that the question is tagged MySQL. MySQL has a limit on number of tables that can appear in a join (61) and the query optimizer does not support table elimination; hence, forget the views. You would have to use the application level to "run-around" and join tables based on the ID and date; the application may be the ETL code that exports to analytic tables.

So, now it depends on how do you expose this to final users -- if they are supposed write custom queries this will not work.

It is a common approach to design a DB on a logical level without a regard for the target DB, but in this case the selection of the DB limits design options.


How to use BCP Utility queryout in a network path with crededential (login required) for SQL Server

The BCP utility does not have option to provide credentials for network path authentication where output file will reside. One alternative is to map the network path as local drive with "net use" providing User/password to authenticate. Then use local drive in BCP command.
e.g.
net use e: \IPAddress\d$\  /user:<domain\login>  /savecred


You can partition on a subset of the columns in the primary key. NDB
currently doesn't support dropping a partition. So to get rid of old
data one needs to use DELETE queries with proper WHERE clauses.


Both functions TO_DATE and TO_CHAR take three arguments. The two last arguments take a default value if they are unspecified.

The second argument is the format. It will default to your NLS_DATE_FORMAT session parameter value. Since you will potentially lose information during the conversion, there is no reason to assume that the two functions are the inverse of one another.

Let's take an example with the default poor choice of format DD-MON-RR:

SQL> select to_date('01-FEB-1949', 'DD-MON-RR') d from dual;

D
------------------------------
01/02/1949

SQL> SELECT to_char(to_date('01-FEB-1949', 'DD-MON-RR'),
  2                 'DD-MON-RR') c
  3  FROM dual;

C
------------------------------
01-FEB-49

SQL> SELECT to_date(to_char(to_date('01-FEB-1949', 'DD-MON-RR'),
  2                         'DD-MON-RR'),
  3                 'DD-MON-RR') d
  4  FROM dual;

D
-----------
01/02/2049

This shows that we have lost data during the second step: the century was lost! The third step has to make up for it and uses the default rule of RR:

  If the specified two-digit year is 00 to 49, then

  If the last two digits of the current year are 00 to 49, then the returned year has the same first two digits as the current year.
  If the last two digits of the current year are 50 to 99, then the first 2 digits of the returned year are 1 greater than the first 2 digits of the current year.

  If the specified two-digit year is 50 to 99, then

  If the last two digits of the current year are 00 to 49, then the first 2 digits of the returned year are 1 less than the first 2 digits of the current year.
  If the last two digits of the current year are 50 to 99, then the returned year has the same first two digits as the current year.

We're in the first case, the century chosen is the current century (20XX).

In conclusion:

Don't rely on the session parameters for conversion because they can be changed outside of your control, use explicit conversion rules.
Don't use the RR or YY format because a year has 4 digits (ever heard about the Y2K bug?)
While we're at it, don't use the MON month format, your application won't work if someone uses another regional NLS setting: months use distinct abbreviations in different languages!


Rate-limiting query execution in a batch process

\watch executes the query buffer (one statement). It doesn't loop through different statements.

\set title "EVAN SAYS HAI"
SELECT 'HELLO WORLD' AS "Evan Greets the Plebs";
\watch

Now move that window over to your fifth monitor and when the boss comes by pretend like you're waiting for it to change.

If you want to put a delay in between query executions, make a spool as @Craig Ringer said in the comments

  Write a Python or Perl script with your list of queries and have it loop over them, sleeping after each execution. – Craig Ringer Mar 5 at 11:18

You can do it one line or whatever too..

echo -e "SELECT 1;\nSELECT 2;\nSELECT 3;" |
  perl -pe'$|=1; sleep 1;' |
  psql -d foo;


What is the "last" data that get's backed up, when running pg_dump on a live server?

It's the data at the start of the command for an entire database. According to the manpage:

  It makes consistent backups even if the database is being used
  concurrently. pg_dump does not block other users accessing the
  database (readers or writers).

and in SQL Dump:

  Dumps created by pg_dump are internally consistent, meaning, the dump
  represents a snapshot of the database at the time pg_dump began
  running

Dumping in parallel (--jobs) may be problematic with changing data, but only when targeting less recent versions:

  For a consistent backup, the database server needs to support
  synchronized snapshots, a feature that was introduced in PostgreSQL
  9.2

I don't think the output format makes any difference in the rescue operation. Note that for a parallel dump, directory is the only possible format.


How do I in "Navicat for PostgreSQL" run a simple PL/pgSQL script like I would in MS SQL Server Management Studio?

DO $$
BEGIN
    INSERT INTO t1 (id) VALUES (6);
EXCEPTION WHEN unique_violation THEN
    -- Ignore duplicate inserts.

END$$;

You can not use IF outside procedure code, but Your construction possible run like this


Split date time, with only time, into minutes and seconds

Store the running time as an integer in total seconds.  For display purposes you can convert the number of seconds into a string in hours:minutes:seconds format or whatever display format you choose.


Physical co-clustering of two or more logical relations/tables in modern RDBMS

Is co-clustering still a valid option for optimization?

Sure, it is a valid option for optimization, if your DBMS offers it. As David Browne has mentioned in a comment, only Oracle does (which, in a way, tells you how practical this feature is).

As you have noted, it is useful in a very limited set of scenarios, while being detrimental to a wider range of queries. In the cases that might benefit from table co-clustering you can employ alternative optimization techniques, such as materialized (indexed) views or column-organized tables, which offer similar performance benefits while being more widely available.

Consider also that today common use of SSD storage, abundance of cheap RAM on database servers, in combination with better query optimizers, decrease the value of marginal reduction in physical I/O at the cost of possible negative side effects and additional database maintenance overhead.

TLDR: don't bother.


Insert data through linked server into two remote related tables

You have to use sp_executesql or openquery ..

E.g. pseudo code ..

declare @remoteIdentity (ID int)
insert into @remoteIdentity
EXEC LNK_AAA_2_BBB.master..sp_executesql N 'select * from #server_AAA_table_1
select SCOPE_IDENTITY() AS ID';
SELECT * FROM @remoteIdentity;

Read up : Best way to get identity of inserted row?


Why Model database in SQL Server is in FULL recovery mode

Why is the model database in SQL Server in FULL recovery mode and other system databases are in SIMPLE recovery mode?

I think you are asking why the model database is different from the other system databases.

New user databases are created with the same recovery model as the model database. The default recovery model for the model database depends on the edition of SQL Server you have installed.

From Choosing the Recovery Model for a Database:

  The simple recovery model is generally appropriate for a test or development database. However, for a production database, the best choice is typically the full recovery model, optionally, supplemented by the bulk-logged recovery model. However, the simple recovery model is sometimes appropriate for a small production database, especially if it is mostly or completely read-only, or for a data warehouse.

Following this logic, editions targeted at test and development environments (SQL Server Express and LocalDB) set the recovery model of the model database to SIMPLE. Other editions set the recovery model of the model database to FULL.


How to preserve the number of errors at the level of the package in SQL or .txt file?

Package variables to the rescue!

If you need to capture the number of times the failure is triggered, you can create a package variable then connect an Expression Task to the failure pathway that increments the variable by 1.

If you need to track the total rows affected, you need to create two variables, one for row count and row count total.  You can add a Row Count task within your Flat File Destination, then have an Expression Task afterwards that adds that to the row count total variable.

Visit https://www.simple-talk.com/sql/ssis/ssis-basics-introducing-variables/ for an introduction to SSIS variables.


Get previous and next record with two order criteria

select s2.*, @nv NXV, (@nv := id) NNXV from (
select s1.*,
@pv PRV, (@pv := id) NPRV, (@rn := @rn + 1) RN
from (
SELECT DISTINCT profiles.id,
IFNULL(profile_photos.photo_status, 0) photo_status,
IFNULL(profile_photos.photo, 0) photo,
profiles.rnd_order
FROM
`profiles`
LEFT JOIN profile_photos
ON (
  profiles.id = profile_photos.member_id
  AND profile_photos.photo_status = 1
), (select @rn := 0)  RNT
WHERE (profile_photos.photo_status = 1 OR
profile_photos.photo_status is null)
GROUP BY profiles.id
ORDER BY photo_status DESC, profiles.rnd_order DESC) s1
) s2
order by RN desc;

see http://sqlfiddle.com/#!9/409b3/20

you will find the info you wanted in columns NXV and PRV

This fiddle http://sqlfiddle.com/#!9/409b3/47 shows the query for a specific profile id.

select p.*, pf.*,
       pmax.id, pmax.rnd_order,
       pmin.id, pmin.rnd_order
  from profiles p left outer join
       profile_photos pf
       on  pf.member_id = p.id
       and pf.photo_status = 1,
       profiles pmax,
       profiles pmin
  where p.id = 100024
    and pmax.rnd_order = (select ifnull(max(rnd_order),p.rnd_order) from profiles pm where pm.rnd_order < p.rnd_order)
    and pmin.rnd_order = (select ifnull(min(rnd_order),p.rnd_order) from profiles pm where pm.rnd_order > p.rnd_order)
;

Can you check this for performance?  (explain plan looks promising)


Metric to standard unit conversion in SQL Server

There is no direct way to implement this in SQL Server, as dynamic SQL in functions is not permitted. However, as your tblUnit will most likely rarely change you can go an indirect way:

What we need is a function that can handle the conversion based on the formulas in the table. If we just could hard-code those formulas in the function and somehow update the function every time the table changes, the problem would be solved. So, can we?

Yes, we can:SQL Fiddle

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.tblRecords
    ([ID] int, [unit] varchar(3), [val] DECIMAL(10,4))
;

INSERT INTO dbo.tblRecords
    ([ID], [unit], [val])
VALUES
    (1, 'KV', 3),
    (1, 'BAR', 4),
    (2, 'KV', 7),
    (2, 'KG', 5),
    (3, 'C', 37)
;

CREATE TABLE dbo.tblUnit
    ([unit] varchar(3), [convUnit] varchar(3), [formula] varchar(15))
;
GO
CREATE FUNCTION dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4))
RETURNS TABLE
AS
RETURN
 SELECT @val convVal;
GO
CREATE TRIGGER updateFnConvert ON dbo.tblUnit AFTER INSERT,UPDATE,DELETE
AS
BEGIN
  DECLARE @cmd NVARCHAR(MAX);
  SELECT @cmd = 'ALTER FUNCTION  dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4))'+
  ' RETURNS TABLE AS'+
  ' RETURN SELECT CASE @unit'+
  (SELECT ' WHEN '''+unit+''' THEN '+formula
     FROM dbo.tblUnit
      FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') +
  ' END convVal;'
  EXEC(@cmd);
END

GO

This creates the two tables, a fnConvert function stub and a trigger on the tblUnit table.

When we now alter the contents of the table with INSERT, UPDATE or DELETE like this:

Query 1:

INSERT INTO dbo.tblUnit
    ([unit], [convUnit], [formula])
VALUES
    ('KV', 'CV', '@val * .865'),
    ('KG', 'lbs', '@val / 2.205'),
    ('BAR', 'PSI', '@val / 14.504'),
    ('C', 'F', '@val * 9/5 + 32')
;

the trigger fires and generates a dynamic sql statement like this:

Query 2:

SELECT 'ALTER FUNCTION  dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4))'+
' RETURNS TABLE AS'+
' RETURN SELECT CASE @unit'+
(SELECT ' WHEN '''+unit+''' THEN '+formula
   FROM dbo.tblUnit
    FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)') +
' END val;'

Results:

|                                                                                COLUMN_0 |
|------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| ALTER FUNCTION  dbo.fnConvert(@unit VARCHAR(3),@val DECIMAL(10,4)) RETURNS TABLE AS RETURN SELECT CASE @unit WHEN 'KV' THEN @val * .865 WHEN 'KG' THEN @val / 2.205 WHEN 'BAR' THEN @val / 14.504 WHEN 'C' THEN @val * 9/5 + 32 END val; |

and then executes that statement. That statement replaces the function with a version containing all the current formulas.

Now we can just use the function in any query like this:

Query 3:

SELECT *
  FROM dbo.tblRecords AS TR
  JOIN dbo.tblUnit AS TU
    ON TR.unit = TU.unit
  CROSS APPLY dbo.fnConvert(TU.unit,TR.val) AS FC

Results:

| ID | UNIT | VAL | CONVUNIT |         FORMULA |     CONVVAL |
|----|------|-----|----------|-----------------|-------------|
|  1 |   KV |   3 |       CV |     @val * .865 |       2.595 |
|  1 |  BAR |   4 |      PSI |   @val / 14.504 |  0.27578599 |
|  2 |   KV |   7 |       CV |     @val * .865 |       6.055 |
|  2 |   KG |   5 |      lbs |    @val / 2.205 | 2.267573696 |
|  3 |    C |  37 |        F | @val * 9/5 + 32 |        98.6 |


What is the exact Query To add two rows from different coloums?

Several options such as:

SELECT (SELECT SUM(follow) FROM fb WHERE name = 20)
    + (SELECT SUM(follow) FROM tw WHERE name = 20);

Or:

SELECT SUM(follow) FROM (
    SELECT follow FROM fb WHERE name = 20
    UNION ALL
    SELECT follow FROM tw WHERE name = 20
) as u;

The query in the question has one problem:

SELECT tw.name,fb.name,SUM(fb.follow+tw.follow)
FROM fb, tw
WHERE fb.name = 20 AND tw.name=20;

values are counted twice because fb CROSS JOIN tw (i.e. fb, tw) duplicates each follow value

This is the result of the CROSS JOIN query without SUMafter duplication:

id  name    follow  id  name    follow
1   20      1       1   20      3
2   20      2       1   20      3
1   20      1       2   20      4
2   20      2       2   20      4

Note that values (all 1) have been replaced by 1, 2, 3, 4 because it is easier to see the problem.


I think it gives you an opportunity to supply a human-friendly name for the system if its hostname is not defined or is not informative, such as d33a891p.z30b.l8.mycompany.com


Which is the redundant relationship in this ER Diagram and why?

None of them are redundant. It's ok to have a loop.

You seem to be missing a many-many relationship (Employee Course) table, though.


Is a Secondary Index in MySQL the Same Thing As a Non-Clustered Index in SQL Server?

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

  In InnoDB, the records in nonclustered indexes (also called secondary
  indexes) contain the primary key columns for the row that are not in
  the secondary index. InnoDB uses this primary key value to search for
  the row in the clustered index. If the primary key is long, the
  secondary indexes use more space, so it is advantageous to have a
  short primary key.

Could have just searched for this.


When creating a Stored Procedure, you do not need COMMIT;.

I have never used it for Stored Procedures.

No one should have to because Stored Procedures are stored in a MyISAM table (mysql.proc)

Please remove the COMMIT; before the END // and try again.

I would also change line to say

DELIMITER $$

and the end clause to

END $$

and try again

I thought you were working in the mysql client. If your problem is SQLFiddle, here is post from StackOverflow by its Author (Execute triggers stored procedures on SqlFiddle. Mysql)

Based on it, stop using DELIMITER altogether. Look for the fourth button under the query pane.

I just installed MySQL 5.6.22 for Windows on my laptop and ran your code as is. It Works !!!

The only culprit left would be phpmyadmin. It cannot handle delimiters against the latest version of MySQL, which is 5.6. You may need an upgrade of phpmyadmin.


Assessing the normal form for a relation with a multi-valued attribute

You first need to know what's the relationship between those two entities: Courses and Students. But, you also need to know the abstraction of those entities into your database schema; we all know that a Student could be enrolled in many Courses, and a Course could contain many Students. The relationship between them are many-to-many.

The way to enforce a many-to-many relationship is by creating a table apart that will store as Foreign Keys the Students, and Courses Primary Keys. Both foreign keys will compound the table's primary key. This will gives to you the flexibility to store many courses, and many students.

However, check below how the tables should look like:

Courses(CourseId (PK), CourseName)
Students(StudentId (PK), StudentName)
StudentsCourses(StudentId (FK)(PK), CourseId (FK)(PK))


In general, for such a structured dataset I suspect you could write a custom data format which was faster for most daily operations (i.e. small data pulls from an arbitrary time).  The benefit of moving to a standard DB tool is likely in some of the extras, for example ad hoc queries, multiple access, replication, availability etc.  It's also easier to hire help to maintain a standards based data store.

If I were asked to set up a database to store that data, I would do the following:

Proposed schema

(1)  Core data is placed into numerous (1000's) of individual tables, each containing two columns:

time: either a SQL DATETIME data type or a numeric type from some epoch (this is the primary key)
value: typed as appropriate for your data. I would default to single precision float, however a fixed-point data type may be more appropriate for financial transactions.  This is probably unindexed.

These tables will get quite large, and you may want to manually partition them by (for example) year.  But you'll have to check system performance and tune as appropriate.

These tables need unique names, and there are a couple of options.  They could be human readable (e.g. nyse_goog_dailyhighs_2010) or (my preference) random.  Either way a set of metadata tables is required, and random table names prevent developers from inferring anything into the name that was not meant to be inferred.

(2)  Meta data is stored in separate tables, as required by the application:

An additional table or set of tables is required to keep track of the metadata.  These tables will contain data about exchange, instrument, value, frequency, date ranges, provenance (where did the data come from), plus anything else you need.  These are mapped to data table names.

If there is enough data, this lookup could actually provide a table name and database name, allowing a sort of self-implemented data sharding (if that is the correct use of the term).  But I would hold that in reserve.

Then at the application layer I would query the metadata tables to determine where my data was located, and then perform relatively simple queries on the big data tables to get my data.

Advantages:

My (relatively limited) experience is that databases can generally handle a large number of small tables easier than a smaller number of large tables.  This approach also enables easier maintenance (e.g. purging old data, rebuilding a corrupt table, creating/reloading from backups, adding a new entity).  This completely decouples the different kinds of data, if (for example) you have data at different rates, or requiring different data types.
This skinny table concept should also allow fast disk access for what I suspect is the most common query, a contiguous range of data from a single entity.  Most data applications are disk I/O limited, so this is worth considering.  As a commenter has already implied, this my be an ideal application for a column-oriented database, but I have yet to find a column oriented product that is mainstream enough for me to bet my career on.  This schema gets pretty close.

Disadvantages:

About half of your disk space is dedicated to storing time stamps, when quite frankly 100's or 1000's of the tables will have the exact same data in the timestamp column.  (In fact this is a requirement if you want to perform easy table joins).
Storing table names and performing the dynamic lookup requires a lot of application complexity and string operations, which kind of makes me cringe.  But it still seems better than the alternatives (discussed below).

Considerations:

Be careful of rounding in your time field.  You want your values round enough to enable joins (if appropriate), but precise enough to be unambiguous.
Be careful of time-zones and daylight savings time.  These are hard to test.  I would enforce a UTC requirement on the data store (which may make me unpopular) and handle conversions in the application.

Variations:

Some variations that I have considered are:

Data folding:  If the timeseries is equally spaced, then use one timestamp column and (for example) 10 data columns.  The timestamp now refers to the time of the first data column, and the othe data columns are assumed equally spaced between that timestamp and the next one.  This saves a lot of storage that was previously used to store timestamps, at a cost of significant query and/or application complexity.  Contiguous range, single entity queries now require less disk access.

Multi-plexing:  If multiple time series are known to use the same time series, then use one timestamp and (for example) 10 data columns as described above.  But now each column represents a different time series.  This requires an update to the metadata table, which is not a lookup into table and column name.  Storage space is reduced.  Queries remain simple.  However contiguous range, single entity queries now require significantly more disk access.

Mega-table:  Take the "multi-plexing" concept to the extreme, and put all data into a single table, once time series per column.  This requires large amounts of disk access for contiguous range, single entity queries, and is a maintenance nightmare.  For example adding a new entity now requires a MODIFY TABLE command on a many TB table.

For additional discussion on this format, see the various answers in:
Too many columns in MySQL

Fully normalized table:
Instead of using many 2-column tables, you could use one, three-column table, where the columns are time, dataid, and value.  Now your metadata tables only need to lookup ID values, rather than tablenames or column names, which enables pushing more logic into the SQL queries, rather than the application layer.

Approximately 2/3 of Storage is now consumed with the normalizing columns, so this will use a lot of disk space.

You can use a primary key order of (dataid, timestamp) for fast contiguous, single entity queries.  Or, you can use a primary key order of (timestamp. dataid) for faster inserts.

However, even after considering these variations, my plan for my next development is lots of tables, two-columns each.  That, or the method soon to be posted by someone wiser than I :).


try with SET SQL_MODE = ''; or add this STRICT_TRANS_TABLES with existing data  modes


MySQL out of Memory, but Only Through MSSQL Linked Server

Turns out the "fix" was to use OPENQUERY on the MSSQL server to push the processing to the client MySQL server.

-- User57112


problem with selecting correct values from database

1)

select TOP (1) ename,salary from Employee where classification='4'
order by salary desc

2)

 select did,dname from Department
 where exists
 (select 1  from Employee
 group by did,classification
 having count(*) = 2
 and classification = '3' and Department.did = Employee.did)

3)

 with cte as(
 select did,byear,budget,LAG(byear) OVER (partition by did order by budget) as preceeding,
 LEAD(byear) OVER (partition by did order by budget) as next from budget
 where budget < 70000
 )
 select distinct did,coalesce(preceeding,byear) as FirstYear,coalesce(next,byear) as NextYear from cte where cte.byear = preceeding + 1 or cte.byear = next - 1

Hope this helps!


You want to convert the pivot table into a normalized table.  Store each cell in your chart as a row in a FARE table.  The business key of this table is Origin + Destination and the non-key columns are Distance and Amount.


What is the corresponding MySQL TimeStamp in MS SQL Server?

From MSDN: "The Transact-SQL timestamp data type is not the same as the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type."

To be able to have a date automatically inserted in your table, you should have a column of type datetime with a default value of getdate() - the system function that shows the current date of the system.


How to segment account info and profile info?

Unless there is a security problem (you want someone to view some information, but not the other), there is no need to; however in many sql platforms you can deny access to specific fields in security or just create a view. The only other reason you would want to have two tables is if you have a business reason to segregate the data to two separate tables. Otherwise, if you are constantly needing to get information from table A and table B at the same time, you have to join each time which will add to overhead.


Test case.

Open 2 sqlplus sessions, run the following in the first to set the test up:

create table stall_table
(
  a number
);

create or replace trigger stall_session
before insert on stall_table
begin
  DBMS_LOCK.sleep(10000);
end;

Then, execute in session 1:

SQL> insert into stall_table values ( 1 ) ;

Execute in session 2:

SQL> create or replace trigger stall_session
before insert on stall_table
begin
  DBMS_LOCK.sleep(10000);
end;
/

You'll get, after some time:

create or replace trigger stall_session
                          *
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object

SQL>

... which answers your question.


Getting Msg 15151, Cannot alter the user 'xxxx', because it does not exist or you do not have permission

You are missing create user before you alter it.

Login - gets you entry to the server
Database User - gets you entry to a particular database

so in your script, add create user from login ...

USE [xxxx-xxxx]
create user [xxxx] from login [xxxx] <--- this is needed !
ALTER USER [xxxx] WITH DEFAULT_SCHEMA=[db_datareader]
ALTER ROLE [db_datareader] ADD MEMBER [xxxx]
ALTER ROLE [db_datawriter] ADD MEMBER [xxxx]
ALTER ROLE [db_owner] ADD MEMBER [xxxx]
GO

few things :

sys.syslogins is deprecated.
dbatools has cmdlets for Login and user management which you can leverage for automation.


Is there a neat way to build a user posts/likes/comments query?

SELECT u.id,
       u.name,
       COALESCE(p.cnt, 0) posts,
       COALESCE(c.cnt, 0) comments,
       COALESCE(l.cnt, 0) likes
FROM users u
LEFT JOIN ( SELECT user_id, COUNT(*) cnt
            FROM posts
            GROUP BY user_id ) p ON  u.id = p.user_id
LEFT JOIN ( SELECT user_id, COUNT(*) cnt
            FROM comments
            GROUP BY user_id ) c ON  u.id = c.user_id
LEFT JOIN ( SELECT user_id, COUNT(*) cnt
            FROM likes
            GROUP BY user_id ) l ON  u.id = l.user_id


You can import the data by using operations that can be minimally logged. See Operations That Can Be Minimally Logged and Prerequisites for Minimal Logging in Bulk Import.

And you should consider reducing the duration of transactions during the import. Use batches of limited size and commit periodically.


How much do foreign keys affect performance?

The link provided in bummi's comment has useful information.  Depending on what you're doing, FKs can improve performance because SQL Server's query processor can rely on there being matching data in a table.

In what context are you asking your question?  Do you have a performance problem already, or are you being cautious?

It's better to add in the FKs and then disable them later (don't remove them, just disable) then to never have them at all.


how to solve Invalid usage of the option NEXT in the FETCH statement

Upgrading to SQL Server 2012 will help but you'll still get the same error. From the documentation:

Limitations in Using OFFSET-FETCH:

ORDER BY is mandatory to use OFFSET and FETCH clause.
OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.

To return a non-deterministic 10 rows from your table you could do this in SQL Server 2012:
SELECT *
FROM AM_API
ORDER BY (SELECT NULL) OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY;

If you cannot upgrade you have a few additional options. SET ROWCOUNT is not deprecated for SELECT statements so the following should work on SQL Server 2008:
SET ROWCOUNT 10;

SELECT *
FROM AM_API;

SET ROWCOUNT 0;

You could also use a TOP expression:
SELECT TOP 10 *
FROM AM_API;


how to query nested json objects in mongodb?

I found the answer myself:

db.getCollection('mobiledashboards')
    .find
    (
        {
            "_id": ObjectId("58c7da2adaa8d031ea699fff")
        },
        {
            viewData: { $elemMatch : { "widgetData.widget.title" : "England" } }
        }
    )


Count down from 0 to 5 seconds and repeat

WaitFor should help you accomplish this.

In your case we can simply pass a DELAY time as below

WAITFOR DELAY '00:00:05';

This will simply wait for 5 seconds, then return to the caller.


Finding when a currently running Extended Events session started?

There is but it does not necessarily provide a "status" itself. If you look at the definition of sys.dm_xe_sessions in Books Online here:

  Returns information about an active extended events session. This
  session is a collection of events, actions, and targets.

The key is active extended events sessions, this DMV is only going to return those XE sessions that are running. So you basically just look at the create_time and that is when the session was started. You can verify this by looking at the system_health session as the time returned should match up to when the instance was last started.

An example is my SQL Server 2012 instance on my local machine is kept stopped most of the time until I need it. I just started it so you can see the times are pretty close:


Can't Login to SQL Server 2008 when running single user mode

This might be because you are attempting to connect using SSMS.  Inside SSMS, Object Explorer will open one connection to the database, while each subsequent query window will open another connection to the database.

What I have done in the past is open SSMS, do not connect to any database via Object Explorer, and open a Dedicated Administrator Connection (DAC) query windows by doing the following:

Click New Query
Type the following into the Server Name dialog box:  ADMIN:ServerName\InstanceName
From here, you can run any scripts needed against your database in Single User Mode.

This requires the SQL Server Browser service to be running.

The real sticking point in all of this is that Object Explorer will eat up that "single user".

Hope this helps,

Matt


Query resource quota for PostgreSQL accounts

There is no support for resource quotas in PostgreSQL.
Your best bet is statement_timeout:
ALTER ROLE baduser SET statement_timeout = '10s';

You cannot limit the number of rows returned, but perhaps statement_timeout is good enough.


Optimize performance for query with lots of OR's

One thing you can try is re-writing the query to use the UNION ALL clause instead of a bunch of OR operators in the WHERE clause's predicate. It depends on the execution plan you were getting previously vs what this one generates though.


Trigger to insert data row into new table when old table is updated

You need to use the correct table names in the trigger -- inserted and deleted are the internal table names used for the new and old data. Also, you need to associate the two tables correctly, and handle multiple rows simultaneously. Your code will need a rewrite to do all this:

CREATE TRIGGER update_price_trg
ON item
AFTER UPDATE,INSERT
AS
BEGIN
    INSERT INTO item_price_history(
        item_id,
        old_price,
        new_price,
        date_of_change
        )
    SELECT
        i.item_id,
        d.price,
        i.price,
        GETDATE()
    FROM inserted i
    LEFT JOIN deleted d
        ON i.item_id = d.item_id
    WHERE i.price != ISNULL(d.price, -1)
END;


I want to point out something. When a failover occurs, any client using the listener to connect will have its' connection closed. This doesn't matter if MultiSubnetFailover is set or not. What MultiSubnetFailover helps with is the client driver to spin up multiple connection threads (1 for each IP) without the application knowing and connecting to the currently active IP transparently to the app.

Having said that, since on a single subnet this setting isn't required or needed (but best practice is to still put it in the connection string as you don't know if it may be needed later or not) there isn't anything needing to be done.

  Is there a way to detect such failures by the application and have it retry.

Yes! It's the same error as if you were disconnected for any other reason. Depending on the driver used, the error checking may be different. For example if you're using .Net and the ADO.Net sqldata client you can use the StateChanged event to know when you connect or become disconnected. If your delegate is fired, check the connection. If you are disconnected, attempt to connect again, etc.


CROSS JOIN with temp table and CASE statement

You can do it the other way around:

SELECT *
 FROM  dbo.vwDevices AS v
CROSS APPLY ( SELECT    * ,
                    CASE v.TypeId
                      WHEN 1 THEN InGs828S
                      WHEN 2 THEN InGS828H
                      WHEN 3 THEN InGS828L
                      WHEN 4 THEN InEasyFlowWD
                    END AS InDevice
          FROM      dbo.Ports
        ) AS tempTable
WHERE tempTable.InDevice=1


How can I limit SQL Server Agent's simultaneous jobs number?

This is best done outside of SQL Agent.  Honestly, at 800+ jobs running every hour you should have moved to a different method long before now.

My preference for this would be to create a single master SSIS package that reads in a list of jobs that need to be run and then starts up the first X in the list, then sleeps until the number of running jobs is below threshold, then starts up the next X in the list.  You could randomize the list or sort it, as you choose.  I've done something similar to this in my environment where I needed to make sure that certain classes of packages (client imports) were done one at a time.

The less dynamic approach would be to just break the list of jobs that need to be run into however many simultaneous you want running.  Each of those would have a list of steps that call the actual job that needs running.  It takes less fiddling about with timing, sleeps and querying active jobs but is less dynamic (for example, changing the number of max simultaneous threads will take work.

But in any case, I would leave the jobs as they are now, just remove/disable their schedules.  Then whatever solution you want can just call the sp_start_job procedure.  This lets you still easily run any job you want out of cycle and you see independent reports of success/failure.


forcing Oracle to use hash join for a subquery

I'd try using a WITH clause in combination with the MATERIALIZE hint to force materialization of the subquery first. Something like this:

WITH x as (select /*+ MATERIALIZE */
           from [your subquery join])
SELECT *
FROM table0, x
WHERE table0.id =x.id


SQL Server 2016 - Availability Groups - How To Copy Permissions To Secondary Node

Use sp_help_revlogin procedure to safely script SQL logins and their passwords (and Windows-authenticated Logins as well), so you can re-create all those logins on secondary replica with exactly same SIDs, as they have on primary replica

https://support.microsoft.com/en-us/help/918992/how-to-transfer-logins-and-passwords-between-instances-of-sql-server

After you create all necessary logins on secondary replica with same SIDs as on primary, you need to use ScriptLoginPermissions procedure, find out if your logins on primary replica have any server-level permissions.

Those are permissions that are stored in the master database. If any of your logins have any server-level permissions on primary, you need to grant those permissions on secondary replica to these logins. ScriptLoginPermissions procedure helps you to generate T-SQL for that, it can be found here:
https://github.com/aleksey-vitsko/Database-Administrator-Tools

Database level permissions, that are granted at user-database level, you do not need to grant those at secondary replica.
And you do not need to do login to user mapping at secondary replica. Because any permission or user changes at primary, are copied to secondaries automatically, in Availability Groups.

You do not need to do any failover, all database-level permissions on primary, are also synchronized to secondary replica. You can't do any changes to permissions on secondary replica's, because user databases there are in read-only state
p.s. Important to have logins on primary and secondary replicas with matching SID (security identifiers, which can be seen at sys.server_principals)


Unable to update or insert values table with DateTime column defaulted to GETDATE

My guess is that your trigger has a "insert into ... select * from myTable" kind of structure, and now it's broken because there's one more column in the table. Change your trigger so that it lists the columns instead of using *.


MySQL: Loop over cursor results ends ahead of schedule

You should strictly check for the end of the loop inside the loop

create trigger my_trigger after delete on my_table
for each row
begin

declare my_value int;
declare num_rows int default 0;
declare done int default false;
declare my_cursor cursor for select value from table where condition;
declare continue handler for sqlstate '02000' set done = 1;

open my_cursor;

-- This is just for debugging
insert into log_table(key, value) values('foo', num_rows);

cursor_loop:repeat
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value;
    -- Do stuff
    until done
end repeat;

close my_cursor;

end

This would also eliminate have to check the count

Give it a Try !!!

Looking at your pseudcode

create trigger my_trigger after delete on my_table
for each row
begin

declare my_value int;
declare num_rows int default 0;
declare done int default false;
declare my_cursor cursor for select value from table where condition;
declare continue handler for sqlstate '02000' set done = 1;

open my_cursor;

-- This is just for debugging
insert into log_table(key, value) values('foo', num_rows);

cursor_loop:repeat
    if done then
        leave cursor_loop;
    end if;
    fetch my_cursor into my_value;
    -- Do stuff

    select some_value into some_field from other_table where some_conditions;
    if (some_field is null) then
        delete from my_table where my_condition;
    else
        update my_table set key1 = value1 where condition1;

    until done
end repeat;

close my_cursor;

end

It may not be a good idea to perform an UPDATE on my_table when you are right in the middle of an after DELETE trigger on the same table. Also, notice you are also causing a DELETE under if (some_field is null) then on the same table.

You are probably better off writing this trigger as a Stored Procedure and manually using the Call to it instead of nesting UPDATE of a table inside DELETE on the same table.


Best way to get time zone key name from SQL Server

Reading the reg key with this or SQLCLR is the only correct way I know (I personally would create a job which updates a table instead of enabling xp_regread though.).

This powershell script is an example of how to update a config table with this information.

$timeZone = (get-itemproperty 'HKLM:\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\').TimeZoneKeyName

$Connection = New-Object System.Data.SQLClient.SQLConnection
$Connection.ConnectionString = "server=.;database=myDatabase;trusted_connection=true;"
$Connection.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Connection
$Command.CommandText = "
        MERGE ConfigTable AS target
        USING (SELECT 'TimeZone', @timeZone) AS source (ConfigKey, ConfigValue)
        ON (target.ConfigKey = source.ConfigKey)
        WHEN MATCHED THEN UPDATE SET ConfigValue = source.ConfigValue
        WHEN NOT MATCHED THEN INSERT (ConfigKey, ConfigValue) VALUES (source.ConfigKey, source.ConfigValue)"
$command.Parameters.AddWithValue("@timeZone", $timeZone)
$Command.ExecuteNonQuery()
$Connection.Close()

All other systems with offset etc are giving incorrect results.


Conceptually this is what should be happening within your database when the end of the physical file is reached:

However, it sounds like you have a log header that doesn't want to wrap back around to the start of the physical log file and continues to fire off auto-growth events instead.  This can occur for any number of reasons, but the most common is a vlf in front of the logical log has a DBCC LOGINFO status of 2 for whatever reason, even after switching recovery models as you're doing now, but the way to fix it is the same regardless of the reason.  Simply perform the following steps:

Perform a Log Backup (e.g. BACKUP LOG [DBNAME]...) against the database
Perform a DBCC SHRINKFILE operation on the transaction log file specifying 1 MB as the target size
Perform another Log Backup against the database
Perform another DBCC SHRINKFILE operation on the transaction log file specifying 1 MB as the target size
Manually increase the size of the log file, incrementally per Kimberly Tripp's article, to grow the TLog back to an appropriate size.

So what's happening here?

The first TLog backup simply forces a CHECKPOINT operation and flushes the log to disk.
The first DBCC Shrinkfile operation shrinks the physical TLOG down to where the current logical log is located
The second Log Backup issues another checkpoint and will likely backup/clear any resident log operations located in front of the logical log.  These were never flushed in the past for whatever reason, but should be cleared at this point.  This step is critical to fixing your issue and sometimes this backup can take a bit of time depending on how much data needs to be written down to disk.
The second DBCC SHRINKFILE operation moves the logical log file to the head of the physical log file, eliminates excessive VLFs that are likely there now, and reduces the TLOG file back down to a very small size, in this case 1MB.  It is imperative to manually grow the file in step 5 so you don't run into vlf issues again down the road
Manually increasing the size of the tlog will allow you to control the size and number of VLFs within your physical log file.  This is an opportune time to configure this properly, so take advantage.

One final note on the process.  If your tlog is highly active, you may need to perform this a few times or during a window of reduced activity.  High activity may result in another errant auto-growth event occurring before you execute the second log backup.  This basically turns what you thought was step 3 back into step 1.  Generally Steps 1 and 2 go by quickly, and step 3 takes the longest to complete.  Make sure to follow-through with steps 4 and 5.


The "Write Conflict" dialog is discussed in the Microsoft documentation for MS Access here:

Write Conflict dialog box

  Microsoft Access displays the Write Conflict dialog box when you and another person are attempting to write to the same record in the database at the same time.

That seems pretty straightforward - if other people have access to the SQL Server table (likely), then they have possibly changed the row you're trying to change since the last time results were loaded for you.  One way to work around this problem is to periodically refresh your view of the table (for example, after each edit you make).

In practice, there are some caveats to this process with SQL Server specifically around NULL values in the data rows, as well as the presence of a Primary Key and / or Timestamp definition for the rows.  These are discussed in this Stack Overflow post:

Editing Record issues in Access / SQL (Write Conflict)


XAMPP Installation - MySQL shutdown unexpectedly

I had this problem too. If changing MySQL port doesn't solve the issue, which was my case. Simply copy the files in xampp/mysql/backup to xampp/mysql/data. Replace any conflicting files and leave the rest.
Then restart Apache and MySQL from the Xampp control panel.
This should solve the problem.


SQL Server Full-Text Index crawl: Is it done or not?

Since you have Change Tracking = AUTO configured you are telling the SQL Server to check on the changes that being made in the FULL TEXT INDEX. That means that the process is still running at a low level.

I would not be at all surprised to see some activity as the Full Text Indexing service is checking for changes.

Populate Full-Text Indexes for SQL Server 2012

A similar question was asked here back in 2013:

Full Text Search Populate Status - Processing notifications

Quoting from Frank J Garcia's answer:

  There is a small overhead associated with change tracking because SQL Server maintains a table in which it tracks changes to the base table since the last population. When change tracking is used, SQL Server maintains a record of the rows in the base table or indexed view that have been modified by updates, deletes, or inserts.


Is an AWS SQL Server RDS "log capture" the same as a transaction log backup?

Almost certainly it does trim the transaction log. (Otherwise the Amazon cloud will fill up with transaction logs.)

Some details from the Amazon site include this topic and two sub-topics that you can investigate: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_SQLServer.html

  When you create a DB instance, you are assigned to the db_owner role for all databases on that instance, and you will have all database-level permissions except for those that are used for backups (Amazon RDS manages backups for you).

For a discussion of the backup model and how you influence it, see:
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.CommonDBATasks.html

And, of course, if you change the model from FULL to SIMPLE, the retention period, and so forth that will affect your restore points.

Amazon definitely support point-in-time restores, which means that the transaction logs are being used.  But since they do this for all database server types that they support, I do not know the mechanics of what they use to do this.

On the restore process described here: http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html

  When you restore a DB instance that is using the SQL Server DB engine to a point in time, each database within that instance will be restored to a point in time within 1 second of each other database within the instance. Transactions that span multiple databases within the instance may be restored inconsistently.

So I would say that, whatever the mechanism of the log backup, it will be available within the constraints discussed in their documentation.


Aggregating Multiple COTS Databases into a Data Warehouse or similar for Reporting

It appears that you just want a copy of all your databases centrally located, which could then be used to run the same report for different regions, just by changing the datasource?

You could acheive this with a single Server and a single instance.

While I wouldn't call this a data warehouse (per se), but it might be the simplest solution to solve your requirements.

With this approach you would end up with 42 databases on your instance and you could name them anything thing you like, but I would suggest including the source location in the name of the database

- for example
   [Source Server].SourceDB    -->      [Dest Server].DestDB
  [Sydney.xyz.com].Database1   -->  [Central.xyz.com].Sydney_Database1
  [Sydney.xyz.com].Database2   -->  [Central.xyz.com].Sydney_Database2
[Brisbane.xyz.com].Database1   -->  [Central.xyz.com].Brisbane_Database1
[Brisbane.xyz.com].Database2   -->  [Central.xyz.com].Brisbane_Database2
etc...

You could achieve this via

Replication ,or
Log Shipping

Both of these solutions are relatively simple (assuming you have network access between all your nodes), and replication you only need to replicate speific objects (tables, procs etc) that you use in your reports (whereas log shipping will be the whole database), but replication can be problematic to troubleshoot.


SQL Server 2008 mirroring migration to SQL Server 2012 always on

It really depends on your downtime window.

My preferred steps would be :

Create a full backup of the SQL Server 2008 R2 database and restore it on the SQL Server 2012.
Perform all of the post-restore steps.
Add the database to an Availability Group.

With modifications as per your environment, you can probably automatically add databases using T-SQL script.


Find max sum of daily total within date range

You will need a calendar table to distribute the quantity to each date in the interval:
CREATE TABLE calendar (dt date not null primary key);
INSERT INTO calendar (dt)
VALUES ('2021-01-14'),('2021-01-15'),('2021-01-16'),('2021-01-17')
     , ('2021-01-18'),('2021-01-19'),('2021-01-20'),('2021-01-21');

Now you can use a join for that:
SELECT MAX(qt)
FROM (
    SELECT dt, SUM(quantity) as qt
    FROM timetable t
    JOIN calendar c
        ON c.dt BETWEEN date(t.dt_start) AND date(t.dt_end)
    WHERE c.dt BETWEEN '2021-01-14' AND '2021-01-23'
    GROUP BY dt
) tt;

A calendar table is very useful for many tasks so I suggest you create one and populate it. Many DBMS including newer versions of MySQL support recursive CTE:s which can be used instead.
Getting the totals for each dt:
SELECT dt, SUM(quantity) as qt
FROM timetable t
JOIN calendar c
    ON c.dt BETWEEN t.dt_start AND t.dt_end
WHERE c.dt BETWEEN '2021-01-14' AND '2021-01-23'
GROUP BY dt;

Fiddle


Move multiple databases from C: to D: at once

I use Powershell for this sort of work.  In fact, I use Powershell to generate Powershell, because I have a script that will loop through my databases and generate my final move script.  You will have to move each database one at a time, but this will at least help you script 90% of the work.

#load SMO
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100
#Added line if using SQL Server 2012 or later
Import-module SQLPS
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

#Create server object and output filename
$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server "localhost"
$outputfile=([Environment]::GetFolderPath("MyDocuments"))+"\FileMover.ps1"

#set this for your new location
$newloc="X:\NewDBLocation"

#get your databases
$db_list=$server.Databases

#build initial script components
"Add-PSSnapin SqlServerCmdletSnapin100" > $outputfile
"Add-PSSnapin SqlServerProviderSnapin100" >> $outputfile
"Import-Module SQLPS" >> $outputfile
"[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') `"localhost`" | out-null" >> $outputfile
"`$server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server " >> $outputfile

foreach($db_build in $db_list)
{
    #only process user databases
    if(!($db_build.IsSystemObject))
    {
        #script out all the file moves
        "#----------------------------------------------------------------------" >> $outputfile
        "`$db=`$server.Databases[`""+$db_build.Name+"`"]" >> $outputfile

        $dbchange = @()
        $robocpy =@()
        foreach ($fg in $db_build.Filegroups)
        {
            foreach($file in $fg.Files)
            {
                $shortfile=$file.Filename.Substring($file.Filename.LastIndexOf('\')+1)
                $oldloc=$file.Filename.Substring(0,$file.Filename.LastIndexOf('\'))
                $dbchange+="`$db.FileGroups[`""+$fg.Name+"`"].Files[`""+$file.Name+"`"].Filename=`"$newloc`\"+$shortfile+"`""
                $robocpy+="ROBOCOPY `"$oldloc`" `"$newloc`" $shortfile /copyall /mov"

            }
        }

        foreach($logfile in $db_build.LogFiles)
        {
            $shortfile=$logfile.Filename.Substring($logfile.Filename.LastIndexOf('\')+1)
            $oldloc=$logfile.Filename.Substring(0,$logfile.Filename.LastIndexOf('\'))
            $dbchange+="`$db.LogFiles[`""+$logfile.Name+"`"].Filename=`"$newloc`\"+$shortfile+"`""
            $robocpy+="ROBOCOPY `"$oldloc`" `"$newloc`" $shortfile /copyall /mov"
        }

        $dbchange+="`$db.Alter()"
        $dbchange+="Invoke-Sqlcmd -Query `"ALTER DATABASE ["+$db_build.Name+"] SET OFFLINE WITH ROLLBACK IMMEDIATE;`" -Database `"master`""

        $dbchange >> $outputfile
        $robocpy >> $outputfile

        "Invoke-Sqlcmd -Query `"ALTER DATABASE ["+$db_build.Name+"] SET ONLINE;`" -Database `"master`""  >> $outputfile
    }
}

The output will be a FileMover.ps1 script in your MyDocuments folder that looks something like this:

    Add-PSSnapin SqlServerCmdletSnapin100
    Add-PSSnapin SqlServerProviderSnapin100
    Import-Module SQLPS
    [System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') "localhost" | out-null
    $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server
    #----------------------------------------------------------------------
    $db=$server.Databases["AdventureWorks2012"]
    $db.FileGroups["PRIMARY"].Files["AdventureWorks2012_Data"].Filename="X:\NewDBLocation\AdventureWorks2012_Data.mdf"
    $db.LogFiles["AdventureWorks2012_Log"].Filename="X:\NewDBLocation\AdventureWorks2012_log.ldf"
    $db.Alter()
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012] SET OFFLINE WITH ROLLBACK IMMEDIATE;" -Database "master"
    ROBOCOPY "C:\DBData" "X:\NewDBLocation" AdventureWorks2012_Data.mdf /copyall /mov
    ROBOCOPY "C:\DBFiles\Log" "X:\NewDBLocation" AdventureWorks2012_log.ldf /copyall /mov
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012] SET ONLINE;" -Database "master"
    #----------------------------------------------------------------------
    $db=$server.Databases["AdventureWorks2012DW"]
    $db.FileGroups["PRIMARY"].Files["AdventureWorksDW2012_Data"].Filename="X:\NewDBLocation\AdventureWorksDW2012_Data.mdf"
    $db.LogFiles["AdventureWorksDW2012_Log"].Filename="X:\NewDBLocation\AdventureWorks2012DW_log.ldf"
    $db.Alter()
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012DW] SET OFFLINE WITH ROLLBACK IMMEDIATE;" -Database "master"
    ROBOCOPY "C:\DBData" "X:\NewDBLocation" AdventureWorksDW2012_Data.mdf /copyall /mov
    ROBOCOPY "C:\DBData" "X:\NewDBLocation" AdventureWorks2012DW_log.ldf /copyall /mov
    Invoke-Sqlcmd -Query "ALTER DATABASE [AdventureWorks2012DW] SET ONLINE;" -Database "master"

...

Caveats

The script moves all files, no matter their source location, to the
same destination.  You will need to tweak for custom location paths.
The script is designed to run on the server you need to move the
files on (see all usages of localhost').  Replace localhost with your
instance name if you run this remotely.
The user you run this as
needs access to all folder paths involved in the move, both for
updating the SQL server filename information and for moving the
files.
I use the InvokeSQLCmd for Offline/Online execution because of the funky nature of the .SetOffline() and .SetOnline methods.  I've found this to be more reliable.


One Big Database vs. Several Smaller Ones

I ran a system with the best part of a thousand databases, spread across multiple servers. They were all an identical structure and were synchronised with a template database which was on each of the machines.

This allowed me the ability to migrate databases from one db to another if one was getting excessively over-loaded, and as the client mix changed, I could create new databases on different servers to load balance across the servers. This was the biggest advantage I got from the system, in that I had multiple large lumps of tin performing multiple complicated queries simultaneously on the separate servers.

The great thing about this, is that you can add servers to the configuration at your own speed, as each server starts to get over-loaded, add another into the mix, migrate some dbs across to the new server and end up with a nicely load balanced set of servers. A really nice and simple way to add scale to the system as and when it is required!

The reason I went with this approach rather than the single huge database approach, was the sheer size of the potential database that would have been created... each of the 1000 databases had 200 tables, and many of the individual tables within each of the databases comprised many hundreds of millions of rows of data!

A single database configuration would have required certain tables (approx 8 of them) to have multi-billions of rows of data, and the total db size would have been over 10Tb. We were able to have multiple servers with 5Tb of RAID 10 storage, with many databases on each.

That's what I would do! Hope it helps your decision making... :)


How to add Reporting services sharePoint features in SQL Server 2008R2

The Microsoft SQL Server 2008 R2 Reporting Services Add-in for Microsoft SharePoint 2010 products is installed by the SharePoint 2010 Products Preparation tool (PreRequisiteInstaller.exe). If you want to download and install the add-in separately, the most up-to-date version of this file is available online at the Microsoft Download Center [1]

Download the add-in From https://www.microsoft.com/en-in/download/details.aspx?id=622 and follow the instructions

Step 1: Install a SharePoint technology instance.

Step 2: Install SQL Server 2008 R2 Reporting Services and specify that the report server use SharePoint Integrated mode.

Step 3: Configure Reporting Services.

Step 4: Download the Reporting Services Add-in by clicking the rsSharePoint.msi link later. To start the installation immediately, click Run. To install at a later time, click Save.

Step 5: Install the Reporting Services Add-in and configure the report server on the SharePoint technology instance.

Step 6: Set permissions and add Reporting Services content types.

Step 7: Verify the installation.

Detailed version : https://msdn.microsoft.com/en-in/library/aa905871.aspx

Known Issues, Another useful link


It might sound a silly question,

I think you have a security requirement written by someone who doesn't know what he is doing, and then implemented by someone else who doesn't know what he is doing.  "Silly" is far too benign a word to describe this.  I hope this is a toy system completely firewalled off from the internet.

sudo su - abc -c "psql"

You said you created a database user named "abc", but now you are trying to do something as the operating system user named "abc", which probably does not exist, since you didn't describe creating it.

Try psql -U abc.  It will probably get farther than your last effort, but still not work due to pg_hba.conf settings.  Once you see the error message produced (look in the server log file for postgresql for the complete error message, the one returned to the client is intentionally vague to avoid leaking sensitive information to a presumed attacker), then you can decide what to do about it.


i want to protect my backups so nobody can "steal and restore" it in
another server, and I would like to prevent data steal.

TDE would be the best built-in solution here, however if you solely care about backups and not your .mdf/.ndf/.ldf files  then you could look into backup encryption to encrypt your backups. But this will also need a: Certificate or Asymmetric Key. Third party backup solutions could also be an option.
Source
A solution that works together with TDE encryption is bitlocker:

With TDE alone, the database files themselves are encrypted (as well
as backup files of the TDE-encrypted databases). Therefore, if either
file is copied or stolen via a network, as in the above example, the
attacker cannot read it. But if the attacker gains access to the whole
drive, including SQL Server, he can start SQL Server and read the data
using SQL, because SQL Server is doing the decryption for him. Now if
both BitLocker and TDE are used concurrently, both data theft vectors
are mitigated. Both technologies complement each other, and it is
recommended that you use BitLocker together with TDE for defense in
depth.

Source

I tried masked data too, but 1) its for sql server 2016+ (and for now
we are using 2012).. and 2) it just creates another column with the
data masked, with the original still there.

Data masking is not an encryption feature, it is a masking feature based on your role assignments. It works together with restricting users and their rights to query objects such as ad-hoc querying.

Another solution to look into would be Always Encrypted but that restricts certain querying of that data such as rich computation when using deterministic or (randomized encryption before SQL Server 2019).
Source

I'm already using a good secutity measure with logins and ETC. only me
have the sa pass, I created a function where it changes the pass
everymonth, etc etc.

The better practice is to rename and disable the sa login.


SQLAlchemy: table version already exists. pgAdmin4 fails on Linux Mint 20 and 20.1

Solution
Downgrade SQLAlchemy to an earlier non-conflict version.
At the time of publishing the posts that explain the steps to install pgAdmin4 within virtualenv on Linux Mint 20, pip was implementing SQLAlchemy 1.3.23. Then, in these last days, the version used is SQLAlchemy 1.4.1
Downgrade can be done with pip install --upgrade.
--upgrade can be used for both, downgrade or upgrade.
(venv) mint@mint:~/pgAdmin4$ pip install --upgrade SQLAlchemy==1.3.23

After this, when trying to run pdAdmin4, a new problem will be displayed:
Traceback (most recent call last):
  File "venv/lib/python3.8/site-packages/pgadmin4/pgAdmin4.py", line 94, in <module>
    app = create_app()
  File "/home/mint/pgAdmin4/venv/lib/python3.8/site-packages/pgadmin4/pgadmin/__init__.py", line 347, in create_app
    if not os.path.exists(SQLITE_PATH) or get_version() == -1:
  File "/home/mint/pgAdmin4/venv/lib/python3.8/site-packages/pgadmin4/pgadmin/setup/db_version.py", line 19, in get_version
    return version.value
AttributeError: 'NoneType' object has no attribute 'value'

This new issue is resolved by removing the ~/.pgadmin directory (created during installation) and then rerunning "python pgAdmin4.py"


What's the best way to concatenate a multi-column foreign key?

It's quite common in Data Warehouse schema designs to assign a new, integer, single-column key to every table, and retain the source-system key (sometimes called the "business key") as well.

Kimball says:

  Natural keys created by operational source systems are subject to
  business rules outside the control of the DW/BI system. For instance,
  an employee number (natural key) may be changed if the employee
  resigns and then is rehired. When the data warehouse wants to have a
  single key for that employee, a new durable key must be created that
  is persistent and does not change in this situation. This key is
  sometimes referred to as a durable supernatural key. The best durable
  keys have a format that is independent of the original business
  process and thus should be simple integers assigned in sequence
  beginning with 1. While multiple surrogate keys may be associated with
  an employee over time as their proﬁle changes, the durable key never
  changes.

Natural, Durable, and Supernatural Keys

So something like:

create table new_case
(
  case_id int identity primary key,
  patient_id int not null,
  care_event_counter int not null,
  constraint uk_case unique(patient_id,care_event_counter),
  ...
)

You need to retain the other key and have it indexed because with new data comes in with the business key, you need to associate it with the new warehouse ID.


if block fails to create temp table in a procedure

Per the documentation:

  If more than one temporary table is created inside a single stored procedure or batch, they must have different names.

I ended up creating the table before the IF block like so:

DECLARE @a bit = 1;
BEGIN
    IF OBJECT_ID('[tempdb]..#bTemp') IS NOT NULL
    BEGIN
        DROP TABLE #bTemp;
    END

    CREATE TABLE #bTemp (
        [c] int);

    IF @a = 0
    BEGIN
        INSERT INTO #bTemp
        SELECT 1 AS [c];
    END
    ELSE
    BEGIN
        INSERT INTO #bTemp
        SELECT 1 AS [c];
    END

    DROP TABLE #bTemp;
END


SQL Server 2012 - Deadlock happening daily

Deadlocks are "simply" application logic errors. In SQL Server I think you can do the following:

review the application logic;
You can use transaction isolation in snapshot mode SET TRANSACTION ISOLATION LEVEL

See also:

How to Track Down Deadlocks Using SQL Server 2005 Profiler

written by Brad McGehee. I think that he is more authoritative than me. He states:

  ...Deadlocks have been the bane of many a DBA. While rare for a well-designed and written application, deadlocks can be a major problem for—how can I say this delicately?—“less efficient” application code. What is even more frustrating is there is not much the DBA can to do prevent deadlocks, as the burden of preventing them in the first place is on the developers of the application...."


First of all, you need to stop thinking in matrices.

You have 2 options. 1) Store each game & who won. 2) Store aggregates of who won.

Option 1

Store every game and the result of said game. Also allows more data about each game to be recorded within the games row.

create table players
(
  player_id integer,
  player_username varchar(50),
  other_player_related_column datatype,
  ...
  ...
  ...
);

create table games
(
  player1_id  integer, -- references players table
  player2_id  integer, -- references players table
  winner_id integer, -- the player_id of the winner
  interesting_game_fact datatype -- other game info
);

Option 2

Just store aggregates of the results of games played between two players. Losses can be derived from the number of games the opponent won. There should only be 1 row for each player_id pair - I suggest storing the lower of the two player_ids as player1_id, just to make life easier.

create table players
(
  player_id integer,
  player_username varchar(50),
  other_player_related_column datatype,
  ...
  ...
  ...
);

create table results
(
  player1_id  integer, -- references players table
  player2_id  integer, -- references players table
  player1_wins integer, -- number of times player 1 has won
  player2_wins integer -- number of times player 2 has won
);


How do I specify a linked server to a remote database over tcp/ip?

I don't see a reason why you should be using a DSN. Create the linked server using the SQL Server Native Client directly:

EXEC master.dbo.sp_addlinkedserver
    @server     = N'MAIN',
    @srvproduct = N'SQLServ', -- it’s not a typo: it can’t be “SQLServer”
    @provider   = N'SQLNCLI',
    @datasrc    = N'I .P_Address';

Then you can create the local/remote login pairs:

-- Pair local and remote logins
EXEC master.dbo.sp_addlinkedsrvlogin
    @rmtsrvname  = N'MAIN',
    @useself     = N'False',
    @locallogin  = N'Local_user_name',
    @rmtuser     = N'Remote_user_name',
    @rmtpassword = N'Remote_password';v


MySQL Syntax - Searching points within polygons using GeomFromText

I may be wrong, but I believe that MBRWithin(GeomFromText(geo),@zone), should be MBRWithin(geo, @zone) -no need to convert anything, it is stored already in spatial format.

When functions like these get bad-type operands, they return null.


If somebody looking something for solve the similare issue look for by the side of

MySQL NTILE()

You can split database on equal part based on a column value.
https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_ntile


MS Azure SQL: how do I import CSVs and set up push notifications?

Use the BCP utility to import and Export.
as the following states, Azure doesn't seem to support the Bulk insert...
see here


Allocation Size for Backup disk on SQL Server

Backups are written sequentially, and as such they are far less sensitive to allocation unit and sector sizes.

64K should be no problem at all.


Design Pattern - One of Many Parent Tables

I understand the second solution as not applicable as it does not offer one (object)-to-many (alert) relationship.

You are stuck to just two solutions because of the strict 3NF compliance.

I would design a lesser coupling schema:

CREATE TABLE Product  (ProductID  int identity(1,1) not null, ...)
CREATE TABLE Customer (CustomerID int identity(1,1) not null, ...)
CREATE TABLE News     (NewsID     int identity(1,1) not null, ...)

CREATE TABLE Alert (
  -- See (1)
  -- AlertID     int identity(1,1) not null,

  AlertClass char(1) not null, -- 'P' - Product, 'C' - Customer, 'N' - News
  ForeignKey int not null,
  CreateUTC  datetime2(7) not null,

  -- See (2)
  CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertClass, ForeignKey)
)

-- (1) you don't need to specify an ID 'just because'. If it's meaningless, just don't.
-- (2) I do believe in composite keys

Or, if integrity relationship shall be mandatory, I might design:

CREATE TABLE Product  (ProductID  int identity(1,1) not null, ...)
CREATE TABLE Customer (CustomerID int identity(1,1) not null, ...)
CREATE TABLE News     (NewsID     int identity(1,1) not null, ...)

CREATE TABLE Alert (
  AlertID     int identity(1,1) not null,
  AlertClass char(1) not null, /* 'P' - Product, 'C' - Customer, 'N' - News */
  CreateUTC  datetime2(7) not null,
  CONSTRAINT  PK_Alert Primary Key CLUSTERED (AlertID)
)

CREATE TABLE AlertProduct  (AlertID..., ProductID...,  CONSTRAINT FK_AlertProduct_X_Product(ProductID)    REFERENCES Product)
CREATE TABLE AlertCustomer (AlertID..., CustomerID..., CONSTRAINT FK_AlertCustomer_X_Customer(CustomerID) REFERENCES Customer)
CREATE TABLE AlertNews     (AlertID..., NewsID...,     CONSTRAINT FK_AlertNews_X_News(NewsID)             REFERENCES News)

Anyway...

Three valid solutions plus another to be considered for many (objects)-to-one (alert) relationships...

These presented, what's the moral?

They differ subtly, and weight the same on the criterias:

performance on insertions and updatings
complexity on querying
storage space

So, choose that comfier to you.


What to do if all columns should be queried

If there are only a thousand rows, the query will run fast enough without a suitable index.

If you have a million rows, you really need an index.

Also, this might work faster in some situations:

SELECT u.UserName AS Name,
       d.Score
    FROM ( SELECT UserID, Score
               FROM PlayerData
               ORDER BY Score DESC
               LIMIT 20
         ) AS d
    JOIN Players AS u  USING (UserID);

Or...

SELECT ( SELECT UserName FROM players WHERE UserID = d.UserID ) AS Name,
       Score
    FROM PlayerData
    ORDER BY Score DESC
    LIMIT 20

(I removed the OFFSET 1 in the presumption that you don't want to skip the highest score.)


How to decipher the transaction message in innodb status log.

The output is truncated when there are many running transactions. The size depends on your version. See "Standard InnoDB Monitor output is limited..." for your version on https://dev.mysql.com/doc/refman/5.5/en/innodb-monitors.html


This seems a whole lot simpler than what you have:

CREATE TABLE #tmp
(
  db          sysname,
  sch         sysname,
  obj         sysname,
  name        sysname,
  is_disabled bit,
  def         nvarchar(max)
);
GO

INSERT #tmp SELECT DB_NAME(),
  s.name, o.name, t.name,
  t.is_disabled, m.definition
FROM sys.triggers AS t
INNER JOIN sys.sql_modules AS m
ON t.object_id = m.object_id
INNER JOIN sys.objects AS o
ON t.parent_id = o.object_id
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
WHERE parent_class = 1;

When you're ready to create them again:

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

SELECT @sql += def
  + CHAR(13) + CHAR(10) + N'GO'
  + CHAR(13) + CHAR(10)
FROM #tmp;

SELECT @sql += N'DISABLE TRIGGER '
  + QUOTENAME(sch) + N'.' + QUOTENAME(name)
  + N' ON '
  + QUOTENAME(sch) + N'.' + QUOTENAME(obj) + N';'
FROM #tmp WHERE is_disabled = 1;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Yes, you won't be able to validate the entire script is there, because SSMS won't output the entire value of @sql. See this post for workarounds.

Also, I don't know why you need the database as part of the dynamic script if you always know the database you're affecting, but there's a much safer way to make the database name dynamic without concatenating values and inviting SQL injection:

DECLARE @db sysname = N'AdventureWorks';

DECLARE @exec nvarchar(max), @sql nvarchar(max);

SET @exec = QUOTENAME(@db) + N'.sys.sp_executesql';

SET @sql = N'SELECT DB_NAME();';

EXEC @exec @sql;


How to get a working and complete PostgreSQL DB backup and test

You can dump the whole PostgreSQL cluster with pg_dumpall. That's all the databases and all the globals for a single cluster.  From the command line on the server, I'd do something like this.  (Mine's listening on port 5433, not on the default port.) You may or may not need the --clean option.

$ pg_dumpall -U postgres -h localhost -p 5433 --clean --file=dump.sql

This includes the globals--information about users and groups, tablespaces, and so on.

If I were going to backup a single database and move it to a scratch server, I'd dump the database with pg_dump, and dump the globals with either

pg_dumpall --globals-only, or
pg_dumpall --roles-only (if you only need roles)

like this.

$ pg_dump -U postgres -h localhost -p 5433 --clean --file=sandbox.sql sandbox
$ pg_dumpall -U postgres -h localhost -p 5433 --clean --globals-only --file=globals.sql

Outputs are just text files.

After you move these files to a different server, load the globals first, then the database dump.

$ psql -U postgres -h localhost -p 5433 < globals.sql
$ psql -U postgres -h localhost -p 5433 < sandbox.sql

  I thought pg_dumpall would at least backup foreign keys, but even that
  seems to be an 'option'. According to:
  http://www.postgresql.org/docs/9.1/static/app-pg-dumpall.html even
  with pg_dumpall I need to use a -o option to backup foreign keys

No, that reference says "Use this option if your application references the OID columns in some way (e.g., in a foreign key constraint). Otherwise, this option should not be used." (Emphasis added.) I think it's unlikely that your application references the OID columns. You don't need to use this option to "backup foreign keys".  (Read the dump file in your editor or file viewer.)


SQL Server - Select where using split - without declaring function

A possible solution might be Splitting Delimited Strings Using XML in SQL Server.  From that post:

  Problem

  This article will help developers looking for a way to split delimited
  strings in a single query using XML. We generally use a user defined
  function to do this, which you have probably found in many places that
  splits the string based on the delimiter passed. But, when it comes to
  separating the string in a single query without any help of a user
  defined function there are not many options. I have found a much
  simpler and shorter way of splitting any string based on a delimiter.
  I will be using the power of XML to do the splitting of the string
  instead of a user defined function.

  Solution

  Let's say for example there is a string 'A,B,C,D,E' and I want to
  split it based on the delimiter ','.

  The first step would be to convert that string into XML and replace
  the delimiter with some start and end XML tags.

DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT @xml

Once the string is converted into XML you can easily query that using XQuery

DECLARE @xml as xml,@str as varchar(100),@delimiter as varchar(10)
SET @str='A,B,C,D,E'
SET @delimiter =','
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)

| value |
|-------|
| A     |
| B     |
| C     |
| D     |
| E     |

UPDATE 2019-12-09

Based on your updated question, here is a sample solution

set nocount on;
drop table if exists Service;
CREATE TABLE Service
(
      ServiceId int
      , CustomerIds varchar(255)
);
drop table if exists Customer;
CREATE TABLE Customer
(
    CustomerId int
    , CustomerName varchar(30)
);
INSERT INTO Customer (CustomerId, CustomerName)
VALUES  (1, 'John')
    ,(2, 'Niels')
    ,(3, 'Frank')
    ,(4, 'Barbie')
    ,(5, 'Holly')
    ,(6, 'Ebeneezer');
INSERT INTO Service (ServiceId, CustomerIds)
VALUES  (1, '4')
    ,(2, '2;3');
-----------------------
;WITH ServicesAsXml
AS (
    SELECT ServiceId
        ,cast(('<X>' + replace(CustomerIds, ';', '</X><X>') + '</X>') AS XML) AS xmlcol
    FROM Service
    )
    ,ServicesSplit
AS (
    SELECT ServiceId
        ,T.N.value('.', 'varchar(10)') AS CustomerId
    FROM ServicesAsXml
    CROSS APPLY xmlcol.nodes('X') T(N)
    )
SELECT CustomerName
FROM Customer c
JOIN ServicesSplit ss ON ss.CustomerId = c.CustomerId

| CustomerName |
|--------------|
| Barbie       |
| Niels        |
| Frank        |


Right Join vs Left Join - My joins are broken and I don't understand why

Why is @@ERROR bad juju?

In SQL Server 2005, using @@ERROR makes life harder than it needs to be for detecting and trapping errors.

1) You must test after every statement. Just from a maintenance perspective, my dog the goggles will do nothing to protect you. You can also argue that all the copy/paste logic for error handling will obscure the "true" purpose of your code.

2) @@ERROR is too volatile. In the following code, line 2 throws error 8134. Line 3 I select the value and 8134 is consumed. Why? Because the SELECT statement didn't raise an error. Even if it had, it would have been a different error than the one that failed.

DECLARE @ErrorCode int
SELECT 1/0 AS div_zero
SELECT @@error AS [@@error]
SET @errorCode = @@error
SELECT @errorCode AS errorCode

3) Even if you handle the @@ERROR well with the above 2, my challenge around it is that error still "comes through" at least in SSMS. Run the first half of the query and even though I have logically handled the error, it will still percolate back to the caller.

SET NOCOUNT ON

DECLARE @ErrorCode int;
SELECT 1/0 AS div_zero;
SET @errorCode = @@error;
IF @ErrorCode <> 0
BEGIN
    -- I did something here to handle the error
    -- SSMS still reports that the Query completed with errors
    print 'developer electrocuted';
END
GO

Contrast that with

-- error handled *and* query executes successfully
BEGIN TRY
    -- This will report query executed successfully
     SELECT 1/0 AS handled_div_zero;
END TRY
BEGIN CATCH
SELECT
    ERROR_NUMBER()AS error_number --returns the number of the error.
,   ERROR_SEVERITY() AS error_severity --returns the severity.
,   ERROR_STATE()AS error_state  --returns the error state number.
,   ERROR_PROCEDURE() AS error_procedure --returns the name of the stored procedure or trigger where the error occurred.
,   ERROR_LINE() AS error_line --returns the line number inside the routine that caused the error.
,   ERROR_MESSAGE() AS error_message; --returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
END CATCH

SQL Server 2012 finishes what 2005 started and provides THROW This allows you to handle your error in your CATCH block and then instead of smashing your call stack, you are now able to percolate that same error back up to the caller. Otherwise, you're reduced to having to raiserror each time you try to fix a level in the call stack.


You need to use SUM() of TOTALFEE and GROUP BY the remaining column names.

The query will be:

SELECT ad.STUDENTMASTERID, ad.ADVERTISEMENTCODE, cod.POSTNAME,
       ad.CANDIDATENAMEINNEPALI, ad.PAYMENTSTATUS, SUM(cd.TOTALFEE) AS TOTALFEE, cod.CLASSNAME
FROM CANDIDATEAPPEARAGAINSTADVTCODE ad
INNER JOIN CANDIDATEAPPEARTOADVTNUMBER cd ON ad.STUDENTMASTERID = cd.STUDENTMASTERID
INNER JOIN ADVERTISEMENTCODE cod ON cd.ADVERTISEMENTCODE = cod.ADVERTISEMENTCODE
WHERE ad.STUDENTMASTERID = '415562'
    AND ad.PAYMENTSTATUS = 'Pending'
    AND cod.STATUSOFLASTDATEFORMSUBMISSION = 'Live'
GROUP BY ad.STUDENTMASTERID, ad.ADVERTISEMENTCODE, cod.POSTNAME,
       ad.CANDIDATENAMEINNEPALI, ad.PAYMENTSTATUS, cod.CLASSNAME;


sp_WhoIsActive: Are Stats Cumulative for Session or Batch?

sp_WhoIsActive reports reads, writes, and CPU at the batch level as opposed to the session level. This is something that can be tested.

Session ID 57 has the following code in just a single batch:

SELECT MAX(t1.high + t2.high)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

WAITFOR DELAY '00:01:00';

Session ID 56 has the same code but split into two batches:

SELECT MAX(t1.high + t2.high)
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
OPTION (MAXDOP 1);

GO

WAITFOR DELAY '00:01:00';

Here's a screenshot of sp_whoisactive after both sessions have been running for a few seconds:

As you can see, those columns reset in session ID 56 because GO defines a new batch.

Waits aren't at the batch or the session level. It's a real time snapshot of what's happening with the query. It's not uncommon to see the wait_info column change wait types or durations while a query is executing.

Your final question is a bit too general for me to attempt an answer, but you could try reading through this resource.


How can I do an incremental `mysqldump` without dropping table?

You can use replace into flag (--replace) and where condition (--where)  while also omitting the create statement (-t):

mysqldump -t --replace --where="id between 901 and 1000" my_db1 table my_table


Database design for voting module with long-run and high-load capability

Is there really a limit to that design and if yes, how can it be dealt with? 1.1. If the select query on votes table will be getting slower, what can I do to speed it up?

I don't think the number of votes is likely to be the problem.  The questions will have to do in part with questions of how well you can index, how your db does caching, etc.  Standard performance tuning applies and that isn't really your design per se.  I will answer more below on what to consider if you run into the wall of being unable to get your design to work fast enough.

  Is there a better way to design this kind of relations?

Not really.

  How do I cache that data? Or is that even needed with proper indexing?

My preference in this case would be to start out without caching, and then to implement a caching layer when you need one.  A caching layer might include something like memcached, or you could build one on a NoSQL solution like Mongo.  At that point you can look at optimizing the areas which are the largest problems.

  What kind of indexes would you recommend for the votes table? Am I correct that I need a simple double-field index (user_id, content_id)?

I know that MySQL and PostgreSQL are different enough to make cross-db somewhat dangerous here but I am thinking you'd want two indexes, one on content_id and one on user_id.  I am thinking this because aggregating by user_id and content_id are likely to be different queries and these are different join conditions.

  Most of the load will go on recent content pieces, maybe I should create something like recent_votes table, which will hold duplicate data, but only for the last say 24 hours and most load will go on it, and if user wants some data that is older, he will work with much bigger and slower table with all votes? Does that make any sense?

Keep in mind that db's frequently do a good job of caching recent content pieces.  I would expect that MySQL can do this too.  If it can't go with PostgreSQL instead.  Don't cache it yourself in the db.

what to do if you hit the wall will depend on your DB choice.  If you are using MySQL, your traditional answer is to look at something like memcached or create a caching layer in a NoSQL db.  If you are using PostgreSQL, you get those choices plus something like Postgres-XC which gives you an ability to do teradata-style scaling out and clustering in OLTP environments.


Restore a SQL Server bacpac file in Linux

I'd recommend trying the restore process suggested in the Microsoft Docs - https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-migrate-sqlpackage

It looks like this may help:

RESTORE DATABASE AdventureWorks
FROM DISK = '/var/opt/mssql/backup/AdventureWorks2014.bak'
WITH MOVE 'AdventureWorks2014_Data' TO '/var/opt/mssql/data/AdventureWorks2014_Data.mdf',
MOVE 'AdventureWorks2014_Log' TO '/var/opt/mssql/data/AdventureWorks2014_Log.ldf'
GO

Hope this helps with the path issue you're running into.


What's the owner of SqlServerAgent job for?

The job owner determines the execution context of the job's steps.

For a T-SQL job step: If the job owner is a sysadmin then the step will execute as either the SQL Server Agent service account or a specified database user (set on the Advanced page of the Job Step Properties dialog). If the job owner is not a sysadmin then the step will execute as the job owner.

For other job steps: If the job owner is a sysadmin then the step will execute as either the SQL Server Agent service account or a specified Proxy. If the job owner is not a sysadmin then the step will execute as a specified Proxy (which requires the job owner to have permission to use the proxy).

A SQL Agent Proxy is created based on a SQL Server Credential which stores the secret associated with some external security principal. For example, sysadmin Kim creates a Credential for ADVENTUREWORKS\Bob and then creates a Proxy based on that Credential. She makes the Proxy active for the CmdExec subsystem. She gives (nonsysadmin) Qin the rights to use that Proxy. Qin can now create a CmdExec job step that executes as ADVERNTUREWORKS\Bob.

See Implement SQL Server Agent Security in Books Online.


Select only records that have different values for a particular column

SELECT DISTINCT t1.*
FROM table t1, table t2
WHERE t1.accnr = t2.accnr
  AND t1.name != t2.name


Async and Slow are not the same thing. If you start putting real load on the replication stream, you will start to see delays, but not with a single test command.

From the PostgreSQL Wiki entry you linked to:

  In SR, XLOG records in partially-filled XLOG file are shipped too, implementing record-based log-shipping.'

So streaming replication is exactly that: The stream of updates is transported from master to slave.


Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to float

Tha sollution is (Thanks @Akina) To use the CAST() functino and convert all the select statements to datatype CHAR.

Sample code:

SELECT CAST(SUM(try_convert(numeric(38, 2), Rüsten_Ist)) AS CHAR) AS Rüsten, 1 AS filtering
          FROM     [Test].[dbo].[ZLA01_Lang_DETAIL]
          WHERE  (ArbPl_Ist = 103100) AND (YEAR(BuchDatum) = 2019) AND (DATEPART(week, BuchDatum) = DATEPART(week,GETDATE())-8)


Trying to create a table with foreign keys and its missing a few rows

The foreign keys are defined on course_no and student_id. Assuming the error is a foreign key violation (big assumption, please post the full error in future questions) it must be one of those two at fault. Other rows reference course EE200 so it's not that; so student 018 doesn't exist in the student table. Similarly MA220 so student 021 is missing. With EE220 / 016 there are no comparators so either or both could be missing. Given your record I'd suggest the student.

This is basic stuff and fundamental to the concept of foreign keys. You should read up on the concept and ensure you write data in a sequence that respects the constraints you have defined. Otherwise you are in for a world of pain.


Share centralized queries for multiple databases

Some other ideas:

Use readily available tools to compare two databases after a change is deployed (you can automate this perhaps, depending on your existing deployment process), and synchronize them
Automate the deployment of changes such that changes to procedures always occur against both databases (again this depends on what your current deployment process entails)
Implement dynamic SQL carefully such that data is pulled from the right database depending on the user connecting.
Use a central database with views or synonyms under different schemas, each set pointing to the two different databases. Construct the calls to procedures dynamically, or leave off the schema prefix and set the default schema per user (you can double up on the security by granting database A's user access to B's schema and vice versa).


Does mysql stop a search automatically after finding 1 record when searching primary key with no limit?

It depends.
First, let me put aside WHERE id=1 versus WHERE id IN (1).  The Optimizer turns the IN version into the = version.  Hence, there is no performance difference when there is only one item in the in-list.  For multiple items, the answer gets more complicated (and not covered here).
Does id have an index?  Let me address 3 cases:

No index.  Then it may have to read each row of the table, checking each for id=1.  Sloooow.
A non-unique index.  And not LIMIT.  Now MySQL does not know whether there are no rows, one row, or thousands.  And WHERE id=1 simply says "get all of them".  So the underlying code will find the first one in the index, then read until id > 1.  So if there are N matching rows, it will "read" N+1 rows (but deliver only the N that you asked for).
A UNIQUE index.  Note: The PRIMARY KEY is UNIQUE.  [This probably your only question.]  First, it will have to dig into the index to see if there is a row with id=1.  But after that, it is smart enough to know to stop after 1 row.

In my experience, I think the difference between stopping at 1 and doing 2 is insignificant.  There are so many other things going on in a query, that I would be hard-pressed to measure the timing difference.
But I am not through lecturing.  What about ORDER BY x LIMIT 1?  Now the efficiency depends on other things.  Most important is whether both the WHERE and the ORDER BY can use the same index.  If not, the data is gathered, sorted, then finally 1 row is pealed off.  Corollary:  For a million-row table, this may mean that adding the LIMIT does not help performance.
To counteract that example, you are unlikely to say WHERE id=1 ORDER BY x -- why bother sorting a single row.


Unusual issue with localhost:3000 after watching ruby on rails 4 tutorial: Lynda.com

This is how I have always set up my rails projects, using SQLite in your test and development will save you this heartache!

#   Ensure the SQLite 3 gem is defined in your Gemfile
#   gem 'sqlite3'
development:
  adapter: sqlite3
  database: db/development.sqlite3
  pool: 5
  timeout: 5000

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: sqlite3
  database: db/test.sqlite3
  pool: 5
  timeout: 5000

production:
  adapter: mysql2
  encoding: utf8
  database: #your db name
  username: #your db username
  password: #your db password
  host: #your db hostname
  port: 3306


Look at sys.dm_io_virtual_file_stats. It will give you per-file (you can aggregate them manually per-filegroup) for things like number of reads, number of writes, IO read stalls and io write stalls. These server lifetime aggregates, so you cannot analyze spikes and trends, but is none the less a good starting point to differentiate hot files vs. cold files.


BULK INSERT always impersonates Windows logins for file access.  But your configuration is a double-hop, so Kerberos would be required for impersonation.  So, as stated, you are stuck.

Common workaround is to create a Credential and a SQL Agent Proxy, and kick off the load from a cmdexec or powershell SQL Agent job step.  Agent will use the stored credentials to logon locally and run the job, so it's no longer a double-hop.

A TSQL job step won't work as the credentials aren't used to LogonUser in that case, Agent just connects as the Agent Service Account and does SQL Server impersonation of the job owner.

You can also access the file from the application server and either copy the file to the database server, or drive the bulk load from the client, eg with BCP or SqlBulkCopy.


Postgresql: Optimize finding all distinct JSON keys from a colulmn

You could add a trigger on insert and update of each row which walks the keys of the new value and makes sure each key is in a side-table which lists all keys in any row of the main table.

Making sure that a key gets atomically deleted from the side-table when its last entry in the main table goes away would be much harder to do efficiently.

The GIN index created with the jsonb_ops operator class does contain the data you want, but as far as I know it provides no way for you to extract that info.  (It also has the same problem with atomic deletes).