First add Category information
Get ID number against that Category using function SCOPE_IDENTITY() and save value to a variable
Then use product detials and that varible to insert values in Product table

.

DECLARE @CategoryName VARCHAR(200),
        @Category_id INT

SET @CategoryName='CategoryName1';

INSERT INTO Category(CategoryName)
SELECT @CategoryName;

SELECT @Category_id= SCOPE_IDENTITY();    ---

INSERT INTO Product(ProductName, Category_id_FK, ProductCompany, ProductPrice, ProductQty)
SELECT 'ProductName', @Category_id , 'ProductCompany', 'ProductPrice', 'ProductQty';


How to use imdpd to import data file into different schema including index from different tablespace as well

There is only one REMAP_TABLESPACE parameter in de command.
It would be seperated with ",".
REMAP_TABLESPACE=OLD_SCHEMA_DAT:NEW_SCHEMA_DAT,OLD_SCHEMA_IDX:NEW_SCHEMA_IDX

Oracle doc impdp 11.2
http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_import.htm#SUTIL929


You are quite right. I have created a small example at Fiddle

create table p
( a int not null
, b int
, unique (a,b)
);

create table c
( a int not null
, b int
, constraint fk_p foreign key (a,b)
                  references p (a,b)
                      on delete cascade
                      on update cascade
);

-- insert data in parent:
insert into p (a,b) values (1,null);  -- ok
insert into p (a,b) values (1,null);  -- also ok. The constraint evaluates to null,
                                      -- which is ok. The rule is that it must not
                                      -- evaluate to false

-- insert data in child
insert into c (a,b) values (1,null);  -- ok
insert into c (a,b) values (2,null);  -- also ok, foreign key evaluates to null.
                                      -- So despite there is no 2 in
                                      -- parent this is ok

-- remove data from parent
delete from p;

-- all rows present in child
select * from c;

a   b
1   null
2   null

Null makes even trivial things a bit tricky to grasp. I would not recommend candidate keys that include nulls.


sudo service mongod start returns error 100

Fixed using this answer from another post:

  Open /etc/mongod.conf and look for dbPath: /xyz/xyz

  Use the following command to provide the rights to mongodb

  sudo chown -R mongodb:mongodb /xyz/xyz


How can I pivot this data using a cte or the pivot syntax?

There are multiple ways you could do this, including those suggested by the question title, but I find a conditional MAX() the simplest to grok (and most likely to only ever access any table once). You want one row per idproject/nameproject, and then you need to find the team row that has the specific task associated with it. You could just as easily use MIN() instead of MAX(), if you trust your data it will be the same, if you don't trust your data that will just determine how to break ties.

SELECT
  p.idproject,
  p.nameproject,
  leader    = MAX(CASE t.taskTeam WHEN 'leader'    THEN t.nameTeam END),
  assistant = MAX(CASE t.taskTeam WHEN 'assistant' THEN t.nameTeam END)
FROM dbo.PROJECT AS p
INNER JOIN dbo.TEAMS AS t -- plural but project is not?
ON p.idproject = t.idproject
WHERE t.taskTeam IN ('leader', 'assistant')
GROUP BY p.idproject, p.nameproject
ORDER BY p.idproject;

Note: I assumed assistent was a typo, and that taskTeam is varchar.


Unable to get rid of orphan temporary tables

This may work -- I found it interesting in any event:  How to remove Temp Tables the proper way


You can reverse the order of the two functions, so the concatenation of group_name and id is done first - using either CONCAT() or CONCAT_WS() - and then the group concatenation:

GROUP_CONCAT( CONCAT_WS(':', rtg.group_name, rtg.id)
              ORDER BY rtg.id
              SEPARATOR ','                      -- the comma ',' is the default
            ) AS groupList


Set variable with dynamic name in trigger (MySQL)

Depending on what you are trying to achieve with this, you may be out of luck.

First of all, names cannot be parametrised in MySQL otherwise than using a prepared statement.

– So, can we use a prepared statement here?

No. Prepared statements are not allowed in triggers, according to the manual:

  SQL syntax for prepared statements can be used within stored procedures, but not in stored functions or triggers.

– All right, prepared statements can be used in stored procedures. Can we create a stored procedure that would use a prepared statement to do the job, and call the procedure from the trigger?

Nope. A stored procedure cannot reference NEW and OLD columns – those namespaces are available only in triggers.

The only option appears to be an IF ... ELSEIF ... statement comparing columnName against a fixed set of names and assigning values to corresponding columns.

IF columnName = 'colname1' THEN
   SET NEW.colname1 = '...';
ELSEIF columnName = 'colname2' THEN
   SET NEW.colname2 = '...';
ELSEIF
   ...
END IF;

This method will not work if you are trying to include non-existent names (in order to cover columns that might be added later, for instance) in the checkings. And, of course, this method will just be unsuitable for you if you are trying to stipulate for unknown names.

But then, perhaps, what you are trying to do is not what you should be doing to begin with. Perhaps, you should revisit your schema and consider a different data model (at least for some portion of your data), like EAV, for instance.


The other 700+ sessions were waiting on LATCH_EX....We recently changed the CACHE value for this sequence from the DEFAULT (50) to 200.

If you've got 700 waiting sessions, my first thought is that the cache needs to be much higher given the workload. I would recommend trying something in the range of 500-1000 (and maybe keep increasing) until you see the waits either stabilize or drop off. There should be a sweet spot you can find with a larger cache value.


I'm getting the below error while trying to ADD CONSTRAINT in Azure DataWarehouse

If you look at the list of Unsupported Table Features for Azure SQL Data Warehouse, unique constraints is still on that list as at today:

It may be that they get supported at some point in the future but I wouldn't hold your breath for this MPP system.  As a workaround, get your ELT/ETL process to ensure records are unique during ingest for example, or make an assumption where the data has been imported from an upstream system which does have unqiue constraints, that it is valid.  Do some validation via reports / queries to check.


Is there always a MySQL user that has all privileges on all databases?

Mysql by default creates a single or multiple root user accounts (this depends on the mysql version) that are indeed superusers and have full access to all databases that you create on that mysql server. However, these accounts are initialised as superusers and you can remove their access rights based on your requirements. Or at least you can rename the superuser accounts from root to something else to make it even  more harder to hack your mysql server. Any superuser account with all privileges granted to *.* and with grant option added will have full access to any newly created database on the mysql server.

Technically, it is possible not to have any  superuser account in a mysql server, but it does not make much sense. Such accounts should be used as a last resort only, but they can be handy in case of an emergency.


The problem resides here:

SET @sqlstring = N'USE ' + @dbname
EXECUTE sp_executesql @sqlstring

What's happening is this executes - changes the database in the execution context for sp_executesql but will not change the parent context. Later on the code assumes this to be true by not continuing to use dynamic sql which means it'll run in the original database context.

IF EXISTS (SELECT dp.name  As Orphan_Users
                        FROM sys.database_principals dp
                        left join sys.server_principals sp
                        ON dp.sid=sp.sid
                        WHERE sp.name IS NULL
                        AND dp.type='S' AND
                        dp.name NOT IN ('guest','INFORMATION_SCHEMA','sys'))

ALTER USER SomeUser WITH LOGIN = SomeUser
ALTER ROLE [db_backupoperator] ADD MEMBER [SomeUser]
ALTER ROLE [db_owner] ADD MEMBER [SomeUser]

This can be fixed by making it execute in the proper context by extending the dynamic sql.


What date/time literal formats are LANGUAGE and DATEFORMAT safe?

In the documentation, it is very explicitly stated that the only safe formats are the ones I demonstrated at the very beginning of the question:

yyyyMMdd                 -- unseparated, date only
yyyy-MM-ddThh:mm:ss.fff  -- date dash separated, date/time separated by T

However, it was recently brought to my attention that there is a third format that is equally immune to any language or dateformat settings:

yyyyMMdd hh:mm:ss.fff    -- unseparated date, no T separator

TL;DR: This is true. For datetime and smalldatetime.

Read on for the longer version, and about as much proof as you're going to get.

There is a loophole that explains this - while the main text body fails to acknowledge yyyyMMdd hh:... as a format safe from transposed language or date format interpretations, there is a little blurb that says the date portion of such a string is not validated depending on dateformat settings:

It is rather unlike me to just take the documentation at its word, usually. You can say I'm a bit skeptical. And the language is ambiguous here, too - it just states that this is about the combination of date and time, not calling out the space explicitly (that could be a carriage return, for all I know). It also says that it isn't multi-language, which means it could fail in certain languages,  but we'll find out shortly that that is incorrect as well.

So I set out to prove that no combination of language/dateformat could make this specific format fail.

First, I created a little block of dynamic SQL for each language:

EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';

This produced 34 rows of output like this:

EXEC sys.sp_executesql @sql, N'@lang sysname', N'us_english';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Deutsch';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Français';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'日本語';
...
EXEC sys.sp_executesql @sql, N'@lang sysname', N'简体中文';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'Arabic';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'ไทย';
EXEC sys.sp_executesql @sql, N'@lang sysname', N'norsk (bokmål)';

I copied that output to a new query window, and above it, I generated this code, which would hopefully try to convert that same date (the 13th of March) to the 3rd day of the 13th month in at least one case:

DECLARE @sql nvarchar(max) = N'
SET LANGUAGE @lang;
SET DATEFORMAT ydm;
SELECT @@LANGUAGE, CONVERT(datetime, ''20170313 23:22:21.020'');';

Nope, every language worked just find in ydm. I tried every other format, too, and also every date/time data type. 34 successful conversions to March 13th, every time.

So, I do concede to @AndriyM and @ErikE that, indeed, there is a 3rd safe format. I will keep this in mind for future posts, but I've pounded the drums about the other two in so many places, I'm not going to hunt them all down and correct them now.

By extension, you would think this one would be safe, but no:

yyyyMMddThh:mm:ss.fff    -- unseparated date, T separator

I think in every language, this will yield the equivalent of:

  Msg 241, Level 16, State 1, Line 8
  Conversion failed when converting date and/or time from character string.

For completeness, there is a fourth safe format, but it is only safe for conversions to the newer date/time types (date, datetime2, datetimeoffset). In these cases the language settings can't interfere:

yyyy-MM-dd hh:mm:...

However, I highly recommend against its use because it only works for the newer types, and the old ones are still in a massive amount of use, in my experience. Why have the dashes there when everywhere else (or in fact in that same code, if the data type changes) you have to remove them?

SET LANGUAGE Deutsch;
DECLARE @dashes char(10) = '2017-03-07 03:34';
DECLARE @d date = @dashes, @dt datetime = @dashes, @dt2 datetime2 = @dashes;

SELECT DATENAME(MONTH,@d), DATENAME(MONTH,@dt), DATENAME(MONTH,@dt2);

Even given the same source string, the conversions yielded quite different results:

März    Juli    März

The format that works for datetime (yyyyMMdd) will also always work for date and the other new types. So, IMHO, just always use that. And given the third format for types with date/time (yyyyMMdd hh:...), this will actually allow you to be more consistent - even if the date component is always a little bit less readable.

Now it will just take me a few years, give or take, to get into the habit of demonstrating the three safe formats when I'm talking about string representation of dates.


pg_restore ERROR: "Relation does not exist" and creating new database

When --create and -d are used together, the argument to -d is not the name of the database to create, it's the name of an existing database to connect to run the CREATE DATABASE statement, because it's impossible to create a database if you're not already connect to another database.

This is documented as:

  When this option is used, the database named with -d is used only to
  issue the initial DROP DATABASE and CREATE DATABASE commands. All data
  is restored into the database name that appears in the archive.

This is why pg_restore errors out with database temp does not exist

You should add a createdb step in your restore procedure since you want to create a specific database name anyway, not one that comes from the backup.

For the second problem, it's a completely different question, hard to guess why it happens without complete error messages.

Also note that -p is not followed by a password but by a port number.


Dropping and recreating indexes for performance reasons?

Instead of dropping the indexes, I'd recommend disabling them then enabling them.  Preferably through a cursor (ignoring the clustered index) so that if an index is added or removed from the archive table the stored procedure doesn't need to be modified.

This is pretty standard when doing large data warehouse loads, which is basically what you are doing.

UPDATE: Scrub that.  With the very small workload that you are doing just insert the records without dropping and readding the indexes.  If you were moving hundreds of millions of rows then removing the indexes would be worth it.


Troubleshooting high memory usage on MariaDB 10.5.4

POSSIBLE MEMORY LEAK ISSUES
There have been three releases since version 10.5.4

03 Nov 2020 : MariaDB 10.5.7
07 Oct 2020 : MariaDB 10.5.6
10 Aug 2020 : MariaDB 10.5.5

There have been some memory issues fixed since version 10.5.4
Memory Issues Fixed in 10.5.5
Revision #d1bb7f9143

2020-06-24 12:51:55 +0300
Fixed memory leak in item_sum.cc::report_cut_value_error()

Revision #572e53d8cc

2020-06-19 15:24:16 +0530
MDEV-22931:  mtr_t::mtr_t() allocates some memory

Memory Issues Fixed in 10.5.6
None (just branch merges from previous versions)
Memory Issues Fixed in 10.5.7
Revision #0d581562d8

2020-10-22 17:09:18 +0300
Fix memory leak on Alter_drop allocation

Revision #d98ccbe1e1

2020-08-21 11:54:16 +0300
MDEV-23526 InnoDB leaks memory for some static objects

WHAT CAN YOU DO NEXT
You might need to update to MariaDB 10.5.7 on a Staging Server, load data into it.Once you do that, you can do some benchmarking.

Perhaps you can mysqlbinlog dump everything from the binaries logs in the 10.5.4 server and import the SQL generated into the Staging Server running 10.5.7.
You could also import the slow log from 10.5.4 server into 10.5.7 Staging Server.

See if memory bloats up or not.
If memory does not bloat, then you should upgrade the prod server to 10.5.7.


Why after the database suffers transaction ID wraparound would
  transactions that were in the past appear to be in the future ?

They don't. The quoted text just explains why postgres needs to use modulo 231 arithmatic (which means transactions can wrap around as long as old transactions are 'frozen' early enough):

  Normal XIDs are compared using modulo-2^31 arithmetic. This means that
  for every normal XID, there are two billion XIDs that are "older" and
  two billion that are "newer"

to be specific:

  old row versions must be reassigned the XID FrozenXID sometime before
  they reach the two-billion-transactions-old mark

or wrapping the XID around would cause things to break. To prevent that, postgres will start to emit warnings, and eventually shut down and refuse to start new transactons if necessary:

  If for some reason autovacuum fails to clear old XIDs from a table,
  the system will begin to emit warning messages like this when the
  database's oldest XIDs reach ten million transactions from the
  wraparound point:

WARNING:  database "mydb" must be vacuumed within 177009986 transactions
HINT:  To avoid a database shutdown, execute a database-wide VACUUM in "mydb".

  (A manual VACUUM should fix the
  problem, as suggested by the hint; but note that the VACUUM must be
  performed by a superuser, else it will fail to process system catalogs
  and thus not be able to advance the database's datfrozenxid.) If these
  warnings are ignored, the system will shut down and refuse to start
  any new transactions once there are fewer than 1 million transactions
  left until wraparound

In other words "transactions that were in the past appear to be in the future" and "data loss" are entirely theoretical and will not be caused by transaction ID wraparound in practice.


mysqldump slow on Windows unless piped to file

Based on the bounty message Looking for an answer drawing from credible and/or official sources., the only thing I could do is just mention that mysqldump is already buffering as little as possible. Why can I say that ???

By default, the option --opt is enabled. The MySQL Documentation says this about --opt:

  This option, enabled by default, is shorthand for the combination of --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It gives a fast dump operation and produces a dump file that can be reloaded into a MySQL server quickly.

  Because the --opt option is enabled by default, you only specify its converse, the --skip-opt to turn off several default settings. See the discussion of mysqldump option groups for information about selectively enabling or disabling a subset of the options affected by --opt.

Please note that --quick is already enabled because --opt is enabled.

What does --quick do ???

  This option is useful for dumping large tables. It forces mysqldump to retrieve rows for a table from the server a row at a time rather than retrieving the entire row set and buffering it in memory before writing it out.

Since --quick is already enabled, each row is being dumped directly to stdout without buffering. You may have to just bite the bullet and disable --quick by using --skip-quick. What that will do is buffer a whole table into RAM (which you didn't want to do in the first place) and then spill it out to stdout.

In your question, you said something interesting

  Linux and Mac there is NOT a difference if I pipe it to a file or just let it output.

Since you said that, you have reached a critical moment

I can say that mysqldump is not really the problem. The problem is the OS and/or hardware.

Evidently, the computer you are using for Windows is not performant for screen I/O.

Since MacOS runs like Linux, I would tend to blame Windows OS. My guess is that even with a far better computer (faster CPU, more RAM), mysqldump may still not perform better if Windows cannot buffer screen I/O effectively.

Your fix for this would be to tune Windows. Godspeed, Spiderman !!!


SELECT first value from 3 tables that is not null

There are couple ways of get what you want. From the top of my head I can think about version with UNION, FULL JOIN, and LEFT JOIN. The latter seems to me as most preferable from readability and performance point of view :

SELECT NVL(t1.value,NVL(t2.value,t3.value)) as value
-- Or use coalesce instead of NVLs :
--SELECT COALESCE(t1.value,t2.value,t3.value) as value
FROM
(SELECT 'name_to_search' as name FROM DUAL)a
LEFT JOIN table1 t1 ON (t1.name= a.name)
LEFT JOIN table1 t2 ON (t2.name= a.name)
LEFT JOIN table1 t3 ON (t3.name= a.name)


It might depend on what is meant by "too long":

Length: If you mean strings of many characters, then try converting some of them to a MAX type (i.e. NVARCHAR(MAX) or VARCHAR(MAX) ). For example:

CONVERT(VARCHAR(MAX), 'This is a test ') + CHAR(13) + CHAR(10) + ' string'

Concatenations: If you mean operations involving many concatenations, there is definitely a maximum number that can be done in a single statement. I tested this a while ago and was going to blog about it but got distracted ;-). I just reviewed my testing and while there are a few different ways to concatenate, the simplest way (which is what you are doing) has a limit of around 3300 per statement. I say "around" 3300 because it varies based on some other factors related to memory. Sometimes I can get 3310, then I tried 3311 and it works, try again and it fails, try again with 3310 and it fails, try again with 3309 and it works.

If you have over this amount then you have no choice but to split the operation into multiple steps (i.e. multiple SET / SELECT statements). Although, the error for reaching the max here is:

  Msg 8631, Level 17, State 1, Line XXXX
  Internal error: Server stack limit has been reached. Please look for potentially deep nesting in your query, and try to simplify it.

And you aren't getting exactly that, so not entirely sure yet until you do more testing, or at least provide the error number. In either case, you can reduce the number of concatenations by combining the "\r" and "\n" into a single variable or VARBINARY literal. The following example shows that both methods produce the same output:

PRINT 'This is a test ' + CHAR(13) + CHAR(10) + ' string';
PRINT '-----------------------------';
PRINT 'This is a test ' + CONVERT(CHAR(2), 0x0D0A) + ' string';

PRINT '-----------------------------';
DECLARE @CRLF CHAR(2) = CHAR(13) + CHAR(10);
PRINT 'This is a test ' + @CRLF + ' string';

The benefit of the variable is that it is shorter, which helps keep large scripts a little more readable and not as large. But, you can't highlight one or more random lines and execute, unless you also highlight the DECLARE but that might not always work. The benefit of the VARBINARY literal is that you can easily execute random groupings of statements, or move statements to other scripts  / section of this script and not worry about forgetting the DECLARE. In either case, this might reduce a few hundred of your concatenations (assuming you already have around 3300 of them), but it doesn't prevent the error if you get a string value that requires hundreds more concatenations.

It's also quite possible that there's a different and/or simpler approach to what you are ultimately trying to accomplish that doesn't rely on concatenations. So it would help if you could please update the question to specify why you are doing this in the first place :-).

  There are 6301 instances of char(13) and the same of char(10).

Given three concatenations per set of "CR" + "LF" (i.e. + CHAR(13) is one, + CHAR(10) is two, and + '...' is the third), that is 18,903 concatenations right there. Yep, that's a bit over 3300 ;-).

  I want the insert into statements to be oneliners.

Ok. But, why? What do you actually gain from this? Embedded CRLF's are just fine for inserting.

Also:

What is the source of this data? Is it coming from SQL Server?
Are the strings VARCHAR or NVARCHAR? In the example in the question, it is only VARCHAR, yet in a comment on the question, you give an example of converting a VARBINARY literal into NVARCHAR.

In either case, doing either of the two approaches I noted above to reduce concatenations by one of the two pieces per CRLF won't help here as that will still leave you with 12,602 concatenations. So, you have two options:

Use a replacement / escape sequence In a comment on the question, Martin Smith suggested using {crlf}. My preference would be one of the following:

CHAR(31) : This is the non-printable "Unit Separator" character. It's only a single byte, quite unlikely to be in your string data, and won't get converted to something else if you accidentally change encondings. Unfortunately, it's not very readable as it won't even show up as a space, hence the words it separates will appear to have nothing between them.

PRINT REPLACE('one' + CHAR(31) + 'two', CHAR(31), CHAR(13)+CHAR(10));

'\n' : This is the almost universal "newline" escape sequence. This is two bytes and definitely more readable.

PRINT REPLACE('one\ntwo', '\n', CHAR(13)+CHAR(10));

In either case, you just INSERT INTO Schema.Table (Column) VALUES (REPLACE(....));
Convert entire string to VARBINARY : this results in a very portable, single-line value. This isn't readable, but there won't be any unexpected character conversions either, and you can use a standard ANSI / code page 1252 file encoding, whereas you would need to use either UTF-8 (with signature / BOM) or Unicode / UTF-16 (with signature / BOM) for the file encoding if the string data is Unicode / NVARCHAR.

You mentioned in a comment on the question something about having a byte array. If the source of the data is .NET, then the strings data is already UTF-16 LE (i.e. NVARCHAR) so simply doing INSERT INTO Schema.Table (Column) VALUES (0x{hex_from_byte[]}); will work just fine. If the destination is a VARCHAR column, then you can either:

convert in .NET using something along the lines of: Encoding.GetEncoding(1252).GetBytes(_StringVariable)
convert in T-SQL using: INSERT INTO Schema.Table (Column) VALUES (CONVERT(NVARCHAR(MAX), 0x{hex_from_byte[]}));. Please note that we are converting to NVARCHAR because the hex representation is UTF-16 LE. The return value from the CONVERT function will then implicitly convert to VARCHAR as it is inserted into the column.


MVCC in PostgreSQL: visible tuple with xmin > current txid

This depends on the isolation level. The default level for postgresql is "Read Committed" at which level this can happen. In postgresql it won't happen at "Repeatable Read" or "Serializable".

This behavior is often not a problem and higher serialization levels can slow down the server which is the reason why they are not the default.

For an extensive explanation please see the manual https://www.postgresql.org/docs/9.6/static/transaction-iso.html


Perform admin tasks (e.g. create database, add diagram support) in SQL Server when not connected to domain

Another solution is to create an SQL Server Log on and give it sysadmin permissions.  This avoids using windows entirely for authentication.  This is what the SA account on SQL Server is, a special SQL Server log in (which i normally disable log on once installed and another admin user is created) with sysadmin privileges.

This would probably be the best bet as it does not require creation of a local user on your machine and having to run SSMS in a different manner every time, just select Sql Server authentication when logging in and use the new credentials.


How do you set the working directory to a mapped network drive for R language in SSMS?

Each user session on Windows has its own set of network drives.  Just because your user session has the drive mapped doesn't mean the session being run by the SQL Server service has that drive mapped.

You can check if the mapped drive is visible to SQL Server by running this:

EXEC sys.xp_cmdshell 'net use';

This will output something similar to:

output
New connections will be remembered.
NULL
NULL
Status       Local     Remote                    Network
-------------------------------------------------------------------------------
OK           Z:        \\machine\share           Microsoft Windows Network
The command completed successfully.
NULL
NULL

In order to successfully run the xp_cmdshell extended stored procedure, you may need to enable it, using this code:

EXEC sys.sp_configure 'show advanced options', 1;
RECONFIGURE
GO
EXEC sys.sp_configure 'xp_cmdshell', 1;
RECONFIGURE
GO

You can determine what account SQL Server is using by looking at the output from this command:

EXEC sys.xp_cmdshell 'whoami';

The account referenced in the above output must have access to the share, as well as the file system referenced by the share.

I don't have access to R here, however you might try mapping the drive, prior to running the R script, like so:

EXEC sys.xp_cmdshell 'net use Z: \\server\share /user:[DOMAIN]\Username <password>';
EXEC sys.sp_execute_external_script @language = N'R',
    @script = N'setwd("Z:/")
          mytextvariable <- getwd();
          OutputDataSet <- as.data.frame(mytextvariable);',
    @input_data_1 = N'SELECT 1 as Temp1'
WITH RESULT SETS (([col] char(20) NOT NULL));

The above code will cause the drive to be mapped for SQL Server's session until SQL Server is restarted.  Be advised, it's not a great idea to save the password in a script unless the script's location is guaranteed safe, otherwise an attacker might see the password.


Are LOBs also backed up if I take dump of all partitions of table individually?

Yes, they should be. Unless, you have configured datapump to exclude them somehow. Please share the command (and parameter file) that you use to make the datapump export.

Reasons why the dump file is smaller could be that you have large LOB indexes (which are not included, and should not be), or you are using compression.


You have to make the event stay put using ON COMPLETION PRESERVE
DELIMITER //
DROP EVENT IF EXISTS daily_delete //
CREATE EVENT
IF NOT EXISTS daily_delete
ON SCHEDULE EVERY 1 DAY STARTS (CURDATE() + INTERVAL 1 DAY + INTERVAL 0 SECOND)
ON COMPLETION PRESERVE
DO
BEGIN
    DELETE
    FROM bariq_DB.invoice
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 2;
    delete from bariq_DB.order_details
    where timestampdiff(MONTH,created_at,NOW()) > 2 and payment_status = 3;
END;//
DELIMITER ;

See my examples in the following posts

Jun 08, 2015 : Use event schedule and stored procedure to automatically average every hour MySQL
Jun 08, 2015 : Choosing what table fields to partition on
Sep 19, 2014 : Creating Event that execute at end of each month
Aug 28, 2014 : Adding time to an event that runs on a weekly basis

This will kickoff the event starting midnight tomorrow
You never start an event in the past. You must always start an event in the future.


How bad is adding new column each time to compare the fixed values?

If the values in one row are not related to each other, adding columns is pointless.

Writing values into the new columns requires rewriting entire rows, and reading a value requires reading the entire row (up to the desired column). So that schema becomes increasingly inefficient.

Put the week information into a separate column:

week  name       value
---------------------------------
  1   Title      SomeTitle
  1   SomeProp   SomeVal
  2   OtherProp  PropValue
  2   Title      SomeUpdatedTitle

With this schema, you do not have to pick specifics weeks to compare; you can also search for weeks where something changed, or compute distances between changes.


Enabling Service Broker on a database involved in an availability group

I wanted to add that the database is a restore from a SQL Server 2005

When you restore a database the Service Broker in it gets disabled and must be explicitly enabled (there are reasons why this is necessary, but I won't go into them). If you restored the DB and then set up the AG w/o enabling broker first, you have set up the AG w/o the Service Broker enabled and now you cannot run the ALTER DATABASE SET ENABLE_BROKER.  Unfortunately to set it up correctly you must enable broker before adding it to the AG, which means you must tear apart the AG, enable broker, then set set up the AG again.


Try this

    drop table if exists data  cascade;
    create table time_table (id int, entry_date date, hours numeric);
    insert into time_table
    values
    (1, to_date('01-01-2019', 'dd-mm-yyyy'), 1),
    (2, to_date('01-02-2019', 'dd-mm-yyyy'), 2),
    (3, to_date('01-03-2019', 'dd-mm-yyyy'), 3),
    (4, to_date('01-04-2019', 'dd-mm-yyyy'), 4),
    (5, to_date('01-05-2019', 'dd-mm-yyyy'), 5),
    (6, to_date('01-06-2019', 'dd-mm-yyyy'), 6);

    select *,
       sum(hours) over(order by entry_date asc rows between unbounded preceding
     and current row) as running_total
     from time_table

db fiddle here: https://www.db-fiddle.com/f/ojsazNcvgfaX66CzdRDRXm/1


You can simplify further:

SELECT date_trunc('month', c.created_at) AS year_month
     , p.reference,
     , count (distinct c.id) as nb_crea,
     , (sum(o.amount_ati_cents) / 100) as ca_euros
from   creations   c
join   products    p on p.id = c.product_id
join   order_items i on i.creation_id = c.id
join   bundles     b on b.id = i.bundle_id
join   orders      o on o.id = b.order_id
where  o.status NOT IN ('canceled', 'new')
and    c.name like '% - Premium(%'
and    p.reference like 'FLA_%'
GROUP BY year_month, reference
ORDER BY year_month DESC;

Or even:

SELECT to_char(c.created_at, 'YYYY-MM') AS year_month
...
GROUP  BY 1, reference
ORDER  BY 1 DESC;

date_trunc() and to_char() in the manual.


Should I align my SQL Server file and growth sizes with the block sizes on my SAN?

The 42MB unit (Hitachi calls this a page too, how convinient). is the basic allocation unit at which space is allocated to a LUN. So with Dynamic provisioning, a LUN will always grow in 42MB steps. These, Hitachi 42MB pages are taken from different Array groups with in a pool.

So whenever more space is needed for the LUN a new 42MB Hitachi page is allocated. When you write your first 8KB SQL Data page to a file, a 42MB Hitachi page will be allocated. Subsequent writes of SQL data pages go to this 42MB area, until it's full.

Now, what is important to understand is that a 42MB page goes to one parity raid group. If you have small RAID groups, for example : RAID-1+0: 2D+2P (2+2) (4 disks) and your data access is mostly sequential, this could be a problem when you have one file.

Lets say you have 4 RAID10 groups each having 4 disks. You just happen to have only 1 important table with 1 clustered index that is 168MB in size.

When you start inserting into this table, the first 42MB alloc page will be placed on one of the RAID groups, once this 42MB is full, the second alloc page will be allocated on another RAID group, most likely all 4 42MB alloc page will be devided across the 4 RAID GROUPS. (we are excluding tiering for simplicity at this moment).

What's important to understand is that anything you do sequentially in the order of the clustered key, will only have 4 disks at any given moment to handle the IO. (2 disks if you are writing..) even though the whole table is spread across 16 disks..

If you would want to be using all 16 disks. you would have to create 4 files (on 4 seperate LUNS) in a filegroup and create the table on that. At least you now know that SQL is "striping the data across 4 42 MB alloc pages"

This can be most problematic  with the transaction log file. It's only 1 file and sequential. The only "tuning" you can do here is make the RAID groups used in that pool as big as possible. Contradictionary a concatinated array group RAID5 14+2 OR RAID5 28+4 might be the fastest even though it's RAID5 as opposed to RAID 10, since as far as I know the biggest RAID group in RAID 10 that can be created is 4+4 is 8 disks.

Please be aware this becomes far less important if you are running a server with multiple databases with multiple LUNS all sharing the same pool, or even more generic multiple servers all having LUNS in the same pool. SInce obviously the general load would then be evenly distributed across all the raid groups.

Regarding tiering, I wouldn't be to worried about it from a performance objective. (if you are not allowed to pin..) Heavy 42MB alloc pages are moved to a high performance tier, so you could have the situation where one table is spread across 3 different tiers. Apperently you have parts of your table that are IO intensive and parts that are not...

worry about the combination small raid groups AND single files (actually single LUNs) AND  (high incremental inserts OR heavy table localized updates/deletes OR heavy seqeuential reads)

hope this helps a bit..

further reading:

Hitachi Tiering design guide

Hitachi SQL Server best practise

Blog post about 42 alloc unit


create table #MyCategory ( Category varchar(20), SortOrder int )
create table #MyResultData ( Category varchar(20), Count int )

-- in no particular order...
insert into #MyCategory ( Category, SortOrder ) values
     ('51-60',       4 )
    ,('61 and Over', 5 )
    ,('31-40',       2 )
    ,('Under 30',    1 )
    ,('41-50',       3 )

-- in no particular order...
insert into #MyResultData ( Category, Count ) values
     ('61 and Over', 50 )
    ,('41-50',       30 )
    ,('31-40',       20 )
    ,('51-60',       40 )
    ,('Under 30',    10 )

-- sorted
select t1.Category, t1.Count
from #MyResultData as t1 join #MyCategory as t2 on t1.Category = t2.Category
order by t2.SortOrder


ibdata1 grows big again after using innodb_file_per_table and having shrunk it

Ibdata1 file holds a lot of information and allocates space for different innodb functionality (double write buffer, undo logs, tablespace header, etc.). Therefore even with innodb_file_per_table you cannot drop/replace the ibdata1 file.

One common reason why ibdata can grow is because it has the undo space. If you have long running transactions the previous version of rows can pile up in the undo log easily. More about this can be read on person's blog:
https://www.percona.com/blog/2013/08/20/why-is-the-ibdata1-file-continuously-growing-in-mysql/

In MySQL 5.7 there are a lot of improvements around undo tablespace management including truncation and separate files to hold this.


You seem to have two questions -- one about efficiency of FKs, one about composite keys.

FOREIGN KEYs are are inherently inefficient.  This is because they involve a lookup in another table to check for consistency.  This happens even if you have thoroughly debugged your application, or if there is not need to the check.

So, if efficiency is the question, I say "get rid of FKs".

FOREIGN KEYs implicitly create the index needed for the above check.  This index could have been manually created.  Either way, the index probably benefits some JOIN in some queries.  That is efficient, although there might be an even better index than the one that the FK decided to create.

Check to see what the FK generated by doing

SHOW CREATE TABLE table_name

You also mentioned "dubious keys".  Here's a short list of "redundant" indexes to watch out for:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#redundant_excessive_indexes


Execution plan doesn't show memory grant

Memory is granted at runtime, so the information you are looking for can only be seen in a post-execution (a.k.a "actual") execution plan, not a pre-execution ("estimated") plan.

If you are using SQL Server Management Studio as a client, CTRL-L is mapped by default to show an estimated execution plan. You can turn on post-execution plans using CTRL-M.

The tooltip on the root node of the plan only gives very limited information. For more detail, open the Properties window and look at the memory grant information there when selecting the root node of the "actual" execution plan.


Shrinking database which is not having any insert/update

Instead of doing a backup/restore, consider moving data across into a new, presized database, and creating new indexes on this data. This should keep your file size small and your indexes unfragmented. Also make sure you have compression turned on.


There are values that you need to graph with absolute values, such as

Innodb_buffer_pool_bytes_dirty
Threads_connected
Threads_running

There are also values whose delta needs to be calculated and graphed, such as

Queries

Com_select
Com_delete
Com_update
etc ...

Incoming and Outgoing Traffic

Bytes_received
Bytes_sent

You should consider using Nagios XI or MONyog to do all that for you.

If you want to be a hero, use Google Charts

Google Chart API - A Real World Example
Sample Chart You Need to Craft in a Bash Script Using the Deltas and Absolute Values

I used Google Chart a few times and got away from it. It does work, but you have a lot of coding to store the absolute values and calculate deltas for specific variables. Then, you script the URL from the values and render the URL in a browser.

GIVE IT A TRY, HERO !!!


ORA-12154 TNS: could not resolve the connect identifier specified with SQLPLUS

So you don't need to specify the full path when you run sqlplus.exe:

Set your %TNS_ADMIN% variable to  the full path of the relevant folder (C:\programs\Oracle\Ora11g\TNS_ADMIN ?). Instructions for doing so are a google away.  At the same time, make sure %ORACLE_HOME% is set to C:\programs\Oracle\Ora11g and that your %PATH% has C:\programs\Oracle\Ora11g\bin added to it.

The problem you're actually having is because of the parameters you're passing to sqlplus.

You've been doing:

C:\programs\Oracle\Ora11g\BIN\sqlplus username@schema_name

Which is wrong & interpreted by sqlplus as:

C:\programs\Oracle\Ora11g\BIN\sqlplus username@database_name

Also, in Oracle, the owner of a schema (hence the schema name) is the user name. Obviously, grants can be given to other users to perform DML/DDL on other schemas, but you still have to login with a user with sufficient rights. (Proxy logins exist, as does alter session set current_schema=blah, but both are beyond the scope of this answer).


in ANSI SQL the DISTINCT is totally pointless here. UNION automatically filters duplicates:

test=# SELECT 1 UNION SELECT 1;
 ?column?
----------
        1
(1 row)

There is a sharp distinction between UNION and UNION ALL:

test=# SELECT 1 UNION ALL SELECT 1;
 ?column?
----------
        1
        1
(2 rows)

in your case the second subselect can never have the same rows as the first one (due to the empty column) so UNION ALL is already definitely fine here (saving a pointless duplication filter).


No results for match...against in boolean mode search with fulltext index (MySQL)

I ended up asking a DBA, and he said that the comma is recognized as a word separator in MySQL. So, that meant that it was looking for "asp" and "net", both of which are under the 4 character minimum for words to be included in a fulltext index. I added the configuration directive ft_min_word_len=3 to my.cnf, restarted mysqld, and rebuilt the fulltext index on the table. Using MATCH(column-name) AGAINST ('+asp +net' IN BOOLEAN MODE); now locates the proper records.

Thanks everyone for your help!


Collection of different types of data in a relational database

There are many ways to do it, depending on the requirements. That being said, this sounds like a homework question which is designed to make you think about it.

However, one way to do it would be to create a table that contains columns that occur in all the different types. Then create a table for each unique type that contains information specific to that type.

Then in your list table you can just create a foreign key to the base table (in this example "things")

create table "things"
(
    id int identity not null primary key
    -- add shared columns here
);
create table "cars"
(
    id int not null,
    -- add car details here
    constraint fk_cars_id foreign key (id) references "things" (id)
);
create table "myfavoritethings"
(
     id int not null,
     constraint fk_myfavoritethings foreign key "things" (id)
);

I prefer this approach because you can maintain referential integrity. Also, you are able to create a nice view from it like so knowing that the id's are unique across all type tables:

create view "vw_myfavoritethings"
as
select t.id
     , -- add car fields here
  from "things" as t
  join "cars" as c on c.id = t.id
  -- additional joins to type tables
  join "myfavoritethings" as mft on mft.id = t.id;

Another way you could do it would be to just create your individual tables and then just store the id's and type in your list table like so:

create table "myfavoritethings"
(
    id int not null primary key
);

If you use this design it would be recommended to create a sequence and then use that to generate your primary key id's across all type tables to ensure that the id value is unique. You can of course use auto increments/identities on each table but then you would need to work out what id = 1 refers to in your list yourself.


There are many things that can causes this issue:

You are running under 64Bit mode

in the project properties window, go to Debugging tab, and set Run64BitRuntime as false. and try again.

If SSIS Server and DEV Computer have different Connector/.Net Versions

This will occur after deployment

You are missing the provider in the connectionstring

You have to add ;Provider=MySQLProv or ;Provider=MSDASQL or other provider name (if you are not using these providers) to the connection string

References

https://stackoverflow.com/questions/18120800/ssis-error-could-not-create-a-managed-connection-manager-vs-2010-adonet
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/392c89cb-19d1-48c8-8d54-e8ae0e75f8e8/could-not-create-a-managed-connection-manager-ssis-package-from-ssms-windows-7-64-bit?forum=sqlintegrationservices
https://www.connectionstrings.com/mysql/


Are there any performance benefits to using a hash table with no clustered index?

I would put a clustered index (not a primary key) on the sid column.  If you do lookups against the mid column sometimes as well then add an index to the mid column with the sid column as an included index.

If you don't want to create a clustered index, then two non-clustered indexes one on each column with the other column as an included column should give you the same basic read performance.


Using CROSS JOIN LATERAL

The output is similar to your output, but please check again. Here's, I using lateral (as loop) to find the max value of someNume for the 3 surrounding days (it doesn't mention about performance).

SELECT tbl.date, tbl.somenum, tbl.name, tbl.elem, t2.max_somenum
FROM tbl
CROSS JOIN LATERAL (
  SELECT MAX(t2.somenum) AS max_somenum
  FROM tbl t2
  WHERE t2.date >= (tbl.date - interval '3 days') AND t2.date <= (tbl.date + interval '3 days')
) t2
ORDER BY date;


But recently I came across some database on a server where every database had multiple mdf files.

That is because of incorrect naming convention. Microsoft says that every database has one primary data file but it does not means it can have only one "mdf data file", database can have many  data file with .mdf extension but only one will be primary data file. It is better to give mdf extension to primary data file and ndf to secondary data file to have proper demarcation but this is no hard and fast rule you can also give .abc extension to primary data file so what you witnessed is normal. As a fact you can give any extension you like.

  Is there any wisdom in having multiple .mdf files for a database?

If you mean:

  Is there any wisdom in having multiple primary files for a database?

The answer is no, a database can have only one primary data file.

But if you mean:

  Is there any wisdom in having multiple data files (.mdf, .ndf, or differently named) for a database?

It depends, you can and cannot have advantage with multiple data files. If they are spread across different physical drives (I am talking about spindles) you would see some advantage with write intensive application. If they are all on same logical partitions it won't have any advantage because underlying they would be using common resource. Using files and filegroups improves database performance, because it lets a database be created across multiple disks, multiple disk controllers, or RAID (redundant array of independent disks) systems. For example, if your computer has four disks, you can create a database that is made up of three data files and one log file, with one file on each disk. As data is accessed, four read/write heads can access the data in parallel at the same time.

As per this MSDN BOL article

Filegroups use a proportional fill strategy across all the files within each filegroup. As data is written to the filegroup, the SQL Server Database Engine writes an amount proportional to the free space in the file to each file within the filegroup, instead of writing all the data to the first file until full. It then writes to the next file. For example, if file f1 has 100 MB free and file f2 has 200 MB free, one extent is allocated from file f1, two extents from file f2, and so on. In this way, both files become full at about the same time, and simple striping is achieved.

Other advantage which I know is consider a 1 TB database if you would have single data file for it and you want to restore this database on some other server its highly unlikely you would have 1 TB of free space. Now if the same database is spread on different files each having size 250 G it becomes easier in restoration. This might not be actually your scenario but it does helps a lot its little easy to find a server with four 250 G drives than one 1 TB drive

I would say instead of many data files its better to have different file groups but again there are not many environments. Databases made up of multiple filegroups can be restored in stages by a process known as piecemeal restore. Piecemeal restore works with all recovery models, but is more flexible for the full and bulk-logged models than for the simple mode. Files or filegroups in a database can be backed up and restored individually. This allows you to restore only the damaged files without having to restore the rest of the database. Files in a filegroup backup can be restored individually or as a group


A simple GROUP BY would suffice I think:

SELECT name,
       1.0 * COUNT(*) / (SELECT COUNT(*) FROM names) AS percentage
FROM names
GROUP BY name
HAVING 1.0 * COUNT(*) / (SELECT COUNT(*) FROM names) < 0.05 ;


Determine free space on "nonfixed" drive

Alternative could be to use PowerShell to grab the information. All you need is a few commands tied together:

$measure = Get-WmiObject Win32_LogicalDisk | Where {$_.DriveType -eq 5}
if ($measure.FreeSpace -lt 20 { "More logic"} else {"something else"}

You could add in the logic to measure for your limit (value is in bytes). Then use Send-MailMessage cmdlet to actually send out your message. Wrap all that up and then have a SQL Agent job call the script on a scheduled basis.


To give a user all privileges except the 'Create user' privilege

This works:

As root:

mysql> CREATE USER 'myuser'@'%' IDENTIFIED BY 'billy';
Query OK, 0 rows affected (0.04 sec)

Then, on the sport schema, grant ALL to myuser

mysql> GRANT ALL ON sport.* TO 'myuser'@'%';
Query OK, 0 rows affected (0.02 sec)

Exit and then log on as the new user myuser

mysql> exit;
Bye
[pol@localhost inst]$ ./bin/mysql -S ./mysql.sock -u myuser -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.22-log Source distribution

Show all the schemas myuser can see

mysql> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| sport              |
| test               |
+--------------------+
3 rows in set (0.00 sec)

There are many more schemas on this server - it's just that myuser can't see them.

mysql> use sport;
Database changed

Then, try to create a user as myuser.

mysql> create user 'fred'@'%' IDENTIFIED BY 'mary';
ERROR 1227 (42000): Access denied; you need (at least one of) the CREATE USER privilege(s) for this operation

Fails.

But, I can do this.

mysql> create table test (a int, b varchar(10));
Query OK, 0 rows affected (0.53 sec)

mysql> insert into test values(3, 'testvalue');
Query OK, 1 row affected (0.04 sec)

Take a look at the three answers here, here and here for a better idea of what's going on. Always test your users after creation - it's very easy to give users too much power.

You can also do it this way (a more elegant solution perhaps)

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
    INDEX, ALTER, CREATE TEMPORARY TABLES
ON sport.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Finally, you could just remove the CREATE USER privilege (and, of course, the WITH GRANT OPTION) from this answer to have a "super user" who can do everything except create new users.

I think @sqlbot may have figured out the root of your problem.


Why does a Set Returning Function (SRF) run slower in a FROM clause?

Let's start by comparing execution plans:

tinker=> EXPLAIN ANALYZE SELECT * FROM generate_series(1,1e7);
                                                           QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
 Function Scan on generate_series  (cost=0.00..10.00 rows=1000 width=32) (actual time=2382.582..4291.136 rows=10000000 loops=1)
 Planning time: 0.022 ms
 Execution time: 5539.522 ms
(3 rows)

tinker=> EXPLAIN ANALYZE SELECT generate_series(1,1e7);
                                           QUERY PLAN
-------------------------------------------------------------------------------------------------
 Result  (cost=0.00..5.01 rows=1000 width=0) (actual time=0.008..2622.365 rows=10000000 loops=1)
 Planning time: 0.045 ms
 Execution time: 3858.661 ms
(3 rows)

Okay, so now we know that SELECT * FROM generate_series() is executed using a Function Scan node, while SELECT generate_series() is executed using a Result node. Whatever is causing these queries to perform differently boils down to the difference between these two nodes, and we know exactly where to look.

One other interesting thing in the EXPLAIN ANALYZE output: note the timings. SELECT generate_series() is actual time=0.008..2622.365, while SELECT * FROM generate_series() is actual time=2382.582..4291.136. The Function Scan node starts returning records around the time the Result node finished returning records.

What was PostgreSQL doing between t=0 and t=2382 in the Function Scan plan? Apparently that's about how long it takes to run generate_series(), so I'd wager that's exactly what it was doing. The answer starts to take shape: it seems like Result returns results immediately, while it seems like Function Scan materializes the results and then scans them.

With the EXPLAIN out of the way, let's check the implementation. The Result node lives in nodeResult.c, which says:

 * DESCRIPTION
 *
 *      Result nodes are used in queries where no relations are scanned.

The code is simple enough.

Function Scan lives in nodeFunctionScan.c, and indeed it appears to take a two phase execution strategy:

/*
 * If first time through, read all tuples from function and put them
 * in a tuplestore. Subsequent calls just fetch tuples from
 * tuplestore.
 */

And for clarity, let's see what a tuplestore is:

 * tuplestore.h
 *    Generalized routines for temporary tuple storage.
 *
 * This module handles temporary storage of tuples for purposes such
 * as Materialize nodes, hashjoin batch files, etc.  It is essentially
 * a dumbed-down version of tuplesort.c; it does no sorting of tuples
 * but can only store and regurgitate a sequence of tuples.  However,
 * because no sort is required, it is allowed to start reading the sequence
 * before it has all been written.  This is particularly useful for cursors,
 * because it allows random access within the already-scanned portion of
 * a query without having to process the underlying scan to completion.
 * Also, it is possible to support multiple independent read pointers.
 *
 * A temporary file is used to handle the data if it exceeds the
 * space limit specified by the caller.

Hypothesis confirmed. Function Scan executes upfront, materializing the function's results, which for large result sets results in spilling to disk. Result doesn't materialize anything, but also supports only trivial operations.


Testing data integrity porting from old database to new

This answer may not be what I was originally looking for, but it's a sequence of events I went through to insure only unique data was inserted into the database vs. trying to purge it after the fact (and never being certain).  As I indicated in my comment above, I investigated using INSERT IGNORE INTO to prevent duplicate data.  To do so, I created a composite unique key that used a primary key from another table, the time stamp, and user name.  If those were not unique, don't insert the data.  That looked like this, the unique key is the last statement shown:

CREATE  TABLE IF NOT EXISTS `uniqueTest`.`events` (
  `Event_ID` INT(11) NOT NULL AUTO_INCREMENT ,
  `PCBID` INT(11) NOT NULL ,
  `EventType_ID` INT(11) NOT NULL ,
  `DateTime` DATETIME NOT NULL ,
  `User` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`Event_ID`) ,
  UNIQUE KEY `PDU_Index` (`PCBID`, `DateTime`, `User`),...

And then used string.Format to build the insert string like this:

INSERT IGNORE INTO EVENTS (PCBID, EVENTTYPE_ID, DATETIME, USER)
VALUES (@eventPCBID, @EVENTTYPE_ID, '{2}', @user);

Along with this I came up with (with lots of SO help), methods to further ignore any child inserts by setting the LAST_INSERT_ID (set to variable @INSERT_ID) of the follow on statements to null.  If it were null, simply don't do the insert.  You can read about that here.  Here's an example of how I used variables to determine the value to use for LAST_INSERT_ID:

SET @EVENT_ID = IF(ROW_COUNT() > 0, LAST_INSERT_ID(), null);

I soon realized that I had to do more work because I frequently had child data where I was using multiple VALUES statements, and needed to be able to insert that data if the ID wasn't null, or ignore it as before.  This was done with UNION ALL, and you can read about it here.

The last thing that became a problem was that I'd frequently have NULL data, and MySQL didn't like having that in the sub-selects, so it became necessary to alias all the values something like this (keep in mind all these statements were being generated in a LinqPAD set of C# statements):

measurementInsertString += String.Format("SELECT @TestSteps_TestSteps_ID AS e, {0} AS MeasurementName, NULL AS MeasuredValue, NULL AS Min, NULL AS Max, {1} AS MeasuredString, {2} AS ExpectedString UNION ALL\n",
                                    stringMeasurements.First().measurementName,
                                    stringMeasurements.First().measuredString,
                                    stringMeasurements.First().expectedString);

Hopefully this will help someone else who's going down a similar path.


MongoDB: Index intersection does not eliminate the need for creating compound indexes

There are a couple of reasons for this at present (I say at present, because improvements may be introduced in the future that reduce the relevance of these points):

Index intersection is limited to 2 indexes (this was the case in 2.6 but I don't see anything in subsequent release notes to suggest this has been increased)
If you can predict your query patterns and cover them with compound indexes then this will likely perform better
Index intersection is currently essentially a last resort to provide some index based coverage (as opposed to a table scan for example) for query patterns that are not already covered by a more efficient method

This is true for most purposes and most query profiles. A counter example here would be a set of queries, regularly run, which would require a large number of compound indexes. The work being done to keep those indexes up to date, the amount of memory they take up might mean that index intersection is a better strategy. This, however, is an edge case - most database usage can be profiled and indexed efficiently so that index intersection is not required.


We had the same issue.  Put this at the top of the mysql start script.

ulimit -u 102400 -n 102400

Has to be at mysql start time.


Why would DBMS_METADATA.GET_DDL Fail when i have the SELECT_CATALOG_ROLE Role?

It seems there was some issue with TOAD. I opened TOAD after they granted me DBA Role, yet it was like it did not take effect.

I could query the USER_ROLE_PRIVS table and see I had the role, I could GRANT myself the SELECT_CATALOG_ROLE, but when I called DBMS_METADATA.GET_DDL it did not work.

Since I had open TOAD after the access had been granted, I didn't think that was the issue. At last, as a desperate measure, I tried closing everything and Opening it again and it worked.

I was going to delete this question, but I guess it could be useful for someone else in the future.


In general terms is safe as long as the databases are "independent". With this I mean each database has references only to itself.

For MySQL in particular VIEWs to missing tables are a problem.

Let me illustrate with an example:

create schema database1;
create table database1.mytable (id int);
create schema database2;
create view database2.myview as select * from database1.mytable;

We take separate dumps: database1.sql and database2.sql

If you try to restore them on a different server, we must pay attention to the restore order, let's say we are starting with empty schemas database1 and database2

This will work:

mysql -u root database1 < database1.sql
mysql -u root database2 < database2.sql

But this will fail:

mysql -u root -p database2 < database2.sql
mysql -u root -p database1 < database1.sql

Because the database2.myview references a table that does not exist.

TLDR: if you are running database restores in parallel and there are references between them you could get into trouble.


SQL Server 2014 Central Management Server

Apart from the T-SQL

Executing policy's across your environment, using different groups to evaluate different policy's. http://pietervanhove.azurewebsites.net/?p=1151
Working together with other dba's, this will enable you to list all the servers, and let your other dba's know that there is a new server. You will get an easy overview of everything installed in your environment.
Quickly open object explorer or logs from several servers and perform actions like restarting sql server service

In any environment, I do not see a reason to not use CMS. It just makes your life easier.


Releasing disk space by using DELETE or TRUNCATE?

From the docs on VACUUM

  There are two variants of VACUUM: standard VACUUM and VACUUM FULL. VACUUM FULL can reclaim more disk space but runs much more slowly. Also, the standard form of VACUUM can run in parallel with production database operations. (Commands such as SELECT, INSERT, UPDATE, and DELETE will continue to function normally, though you will not be able to modify the definition of a table with commands such as ALTER TABLE while it is being vacuumed.) VACUUM FULL requires exclusive lock on the table it is working on, and therefore cannot be done in parallel with other use of the table. Generally, therefore, administrators should strive to use standard VACUUM and avoid VACUUM FULL.

You essentially need to issue a command to rewrite the whole table (from the same doc),

  Tip: Plain VACUUM may not be satisfactory when a table contains large numbers of dead row versions as a result of massive update or delete activity. If you have such a table and you need to reclaim the excess disk space it occupies, you will need to use VACUUM FULL, or alternatively CLUSTER or one of the table-rewriting variants of ALTER TABLE. These commands rewrite an entire new copy of the table and build new indexes for it. All these options require exclusive lock. Note that they also temporarily use extra disk space approximately equal to the size of the table, since the old copies of the table and indexes can't be released until the new ones are complete.

In the same document,

  Tip: If you have a table whose entire contents are deleted on a periodic basis, consider doing it with TRUNCATE rather than using DELETE followed by VACUUM. TRUNCATE removes the entire content of the table immediately, without requiring a subsequent VACUUM or VACUUM FULL to reclaim the now-unused disk space. The disadvantage is that strict MVCC semantics are violated.

And, from the docs on TRUNCATE

  TRUNCATE quickly removes all rows from a set of tables. It has the same effect as an unqualified DELETE on each table, but since it does not actually scan the tables it is faster. Furthermore, it reclaims disk space immediately, rather than requiring a subsequent VACUUM operation. This is most useful on large tables.


How to transpose rows into columns in SQL?

This is an example of pivoting:

select worker_name, max(_in) as _in, max(_out) as _out
from (
    select worker_name
         , case when in_out = 'IN' then log_time end as _in
         , case when in_out = 'OUT' then log_time end as _out
    from shift
) as t
group by worker_name;

The idea is to map log_time to _in when in_out = 'IN', ditto for _out. In the outer select an aggregate function is used to filter out nulls. Try running the inner select first to get an idea of what is happening.

In general, the presentation of data is better handled by the application layer. So I would suggest reading the table as is, and let the presentation of the data be handled by the application.


Connection string for MariaDB using ODBC

Although this is late answer, but MariaDB have that documented here

SQLWCHAR *ConnStr= L"Driver={MariaDB ODBC 2.0 Driver};Server=localhost;UID=odbc_user;PWD=odbc_pw;DB=odbc_test;Port=3306";


Using a trigger function to constrain values according to those already in the table (postgresql)

With an Exclusion Constraint and a UNIQUE Partial Index

So long as you don't care about people that pass retaking and later failing you can easily do this without even using a trigger using a exclusion-constraint. It's a bit clever, but it's pretty efficient.

CREATE TABLE foo (
  id         serial    PRIMARY KEY,
  student_id int,
  subject    text,
  year       smallint,
  grade      "char"    NOT NULL
);

CREATE UNIQUE INDEX no_double_fail
  ON foo ( student_id, subject ) WHERE upper(grade)='F';

ALTER TABLE foo
  ADD CONSTRAINT fail_once_fail_always
  EXCLUDE USING gist (
    student_id WITH =,
    (CASE WHEN upper(grade) = 'F' THEN 0::smallint ELSE 1::smallint END) WITH <>
  );

Test suite.

-- fails error msg below
INSERT INTO foo (student_id,subject,year,grade) VALUES
  (1,'Art',2000,'F'),
  (1,'Art',2001,'A');

ERROR:  conflicting key value violates exclusion constraint "fail_once_fail_always"
DETAIL:  Key (student_id, (
CASE
    WHEN upper(grade::text) = 'F'::text THEN 0::smallint
    ELSE 1::smallint
END))=(1, 1) conflicts with existing key (student_id, (
CASE
    WHEN upper(grade::text) = 'F'::text THEN 0::smallint
    ELSE 1::smallint
END))=(1, 0).

-- works
INSERT INTO foo (student_id,subject,year,grade) VALUES
  (2,'Math',2000,'B'),
  (2,'Math',2001,'A');

-- fails error msg below
INSERT INTO foo (student_id,subject,year,grade) VALUES
  (3,'Science',2000,'F'),
  (3,'Science',2001,'F');

ERROR:  duplicate key value violates unique constraint "no_double_fail"
DETAIL:  Key (student_id, subject)=(3, Science) already exists.

If you care about it, to compliment the condition to protect against people that pass retaking and later failing you'll need the ability to do a non-commutative exclusion constraint. I'm not sure that's even possible. This may force you to use a trigger.


How to design a schema to store search keywords

First, let me address your concern about data redundancy. I agree with you that the second schema is more likely to reduce the redundancy, and is probably closer to what I would go with. One thing to be aware of, though, is this will be unique search terms. So unless you have some way of normalizing data, misspellings will get through and be viewed as different search terms.

Also, I would switch the way you link the searchTerm to the date searched, so that dateofSearchTerm links to the search term (not the other way around).

CREATE TABLE SearchTerm (
 ID             INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 SearchTerm     VARCHAR( 255 )  NOT NULL,
 Hit           INTEGER         NOT NULL,
);

CREATE TABLE DateofSearchTerm (
 DateID         INTEGER         PRIMARY KEY AUTOINCREMENT NOT NULL,
 searchID       INTEGER         NOT NULL,
 Date           DATE            NOT NULL
);

It would also be a good idea to make searchID a foreign key that references searchTerm.ID to ensure there are no orphan rows (rows in DateofSearchTerm that do not have a matching parent in SearchTerm).

As for your anecdotal story about your friend, the biggest thing I see you missing in your designs are indexes. A table 3-million strong should have no problem running quickly with proper indexes.

Using your design, you would want an index on the searchTerm.searchTerm column, and on DateofSearchTerm.date.

I think if you follow the advice to make searchID a foreign key, that will also become a proper index. I don't use SQL Server, so I don't know if foreign keys require an index, so make sure that it does.


If I were you I would run a trace specific to hits on that table.  It shouldn't be overly intensive since you are restricting it to just queries against that table, from your application.  Run just the minimum needed by the DTA (Database Tuning Advisor).  Run it for a day here, a day there, make sure you get some end of week days and some end of month days.  Then run the whole lot through the DTA.

Here is why, I'm willing to bet that you have specific combinations of columns that are going to come up more often than not.  You can create more complex indexes based on that information.  You might also find that you can create some correlated statistics. Basically statistics that have more than one column.  For example creating a statistic on City and State together may improve queries against those two columns.

However make sure you don't create to many indexes.  On a table that large I'm guessing you do a fair number of writes and every additional index added will slow them down.  Of course you may do most of your writes during a batch process.

Also make sure that you put an automatic process to update your statistics periodically.  With that many rows the statistics aren't going to update on their own very often.  Only once 500+20% of the rows have changed, in at 500 mil rows that's a LOT.


MSSQL: High number of logical reads when inserting into fragmented heap

When inserting into a table without specifying TABLOCK hint the operation is fully logged (every row insert is logged) and the number of logical reads reported by query statistics is roughly equal to the number of inserted rows; so it's the same in case of fragmented/not fragmented heap (1 insert = 1 destination table page access), but what is not counted here is PFS-pages access, and the Profiler does count it as logical reads.

When you insert into rebuilt heap, there is no need to try to insert in any existing page because they are all full, but when inserting to a fragmented heap there is free space in the heap and it should be checked for every row inserted (where it's possible to insert it).

IMHO, the fact that there is no more need to search in PFS pages is revealed at the first attempt to find a free space in the existing pages when all pages allocated for this heap are checked without success for a rebuilt heap, and the number of possible accesses the PFS pages grows as the fragmentation grows.
As soon as the searching for free space failes when trying to insert the Nth row, for the remaining rows it allocates new pages directly


How to efficiently create a new PRIMARY KEY CLUSTERED definition online

If you're running Enterprise edition, you can add and remove indices, including clustered indices, with ONLINE.

Since your existing PK isn't being used, you can start by dropping it.  Then create your new clustered index, and finally drop your old non-clustered index which is now obsolete.

 ALTER TABLE MyTable DROP CONSTRAINT PK_MyTbl_SrgKey WITH (ONLINE=ON)
 ALTER TABLE MyTable ADD CONSTRAINT PK_MyTbl_BizKey PRIMARY KEY (X) WITH (ONLINE=ON)
 DROP INDEX IX_MyTbl_BizKey ON MyTable WITH (ONLINE=ON)

At no point will the biz key field be un-indexed, and there should be minimal blocking.

I'd guess you're already familiar with this syntax and you're hoping for something simpler or more atomic.  Hopefully someone can offer this!


Dynamically changing an order number to end in _ERR

It's going to be a different concatenation operator depending on the RDMS you're using. For instance:

Oracle uses ||
SQL Server uses +
MySQL uses concat

...but everything else looks like:

UPDATE table1 SET order = order ||'_ERR' WHERE [your condition]

Since the question is tagged for SQL Server, the applicable syntax is:

UPDATE table1 SET order = order +'_ERR' WHERE [your condition]


Oracle database not open and ORA-00600 error

It looks like your database experienced an incomplete recovery. This is why it complains about open resetlogs. After an incomplete recovery you need resetlogs. To be able to get this database up and running again, you need to know what caused the current situation and some skills to get it out of there.

If the data is not important at all, just trash the database and create a new one. The actions needed for this depend a little on the platform where you are on.

the documentation looks like a good starting point.


You would use the CONVERT_TZ function like this:

SELECT CONVERT_TZ('2012-01-01 12:00:00','-06:00','+00:00')

Example SQL Fiddle

But, why are you using varchar for your date/time fields? It would be better to use the proper data types DATE and TIME.


Are shards distributed randomly across machines in MongoDB or are they in order?

In general, no they are not distributed in order.  However, there is a way that this (or at least approximately this) distribution can occur during normal operations.  To explain:

If you have all of your data on a single shard (a-z) and then add a second shard, the balancer will redistribute the data to the new shard by migrating chunks.

The way the balancer picks a chunk to migrate in general is pretty simple, it will pick the lowest available range on the shard with the most chunks (the "a" chunk for example), and move it to the shard with the lowest number of chunks.  Rinse and repeat until the chunks are balanced.

Hence, if you have all your data on one shard and add a second shard, the low range chunks (a-m) will all be moved from the original shard to the new shard, and you can end up with this almost-in-order distribution.

In your example, you just end up with an oddly uniform distribution of data, no real harm done.  However, it might be problematic from a performance perspective for range based queries across a large portion of the data.  Think about a query that would walk the index in order - it would only ever be hitting one shard (new shard, then old shard) at a time rather than using the resources of both with better distribution of data.

In other scenarios this behavior can lead to a particularly poor data distribution.  Let's say you had a time based shard key (bad for various reasons, but still quite common) and you deleted old data on a regular basis.  Combine that with the scenario above and the new shard would eventually have no data on it (the data in the low range, old, chunks would all be deleted).  Remember that an empty chunk (no data) counts just as much as a 60MB chunk in terms of balancing (the balancer is purely looking at the number of chunks on each shard, not the amount of data in them).

Also, none of this would happen if you had 2 shards to start with and then inserted your data, because the splits and migrations would be more random and more distributed in general.  MongoDB will also attempt to move the maximum chunk as well to help this distribution happen.

To summarize - if you have spotted this type of distribution problem, you can take steps to fix it by moving chunks around yourself.  Take a look at the moveChunk command (it's generally a good idea, but not required, to have the balancer off when you do this or you can have trouble getting the required lock).

The easiest way to view all this is to run the sharding status command and pass and argument of true, i.e. sh.status(true).  This will print details of all sharded collections, where the chunks live etc.  It pulls its information from the config database and uses a simple Map Reduce to aggregate the information.

Armed with that information you can also inspect the changelog collection to determine what has been migrated and when it was done (this will also be recorded in your primary mongod logs but can be tough to parse out on busy systems).

Finally, I realize this is somewhat beyond the scope of this answer, but I have written a couple of Javascript functions (that you can use from the MongoDB shell) that will let you determine the object and data distribution on your shards for a given collection, something not easily available elsewhere.  Hopefully you will find them useful :)

Please Note: these functions can be intensive to run, so use with caution in a production environment.

This first function prints out the chunk information in CSV format for a given namespace.  It uses the estimate option in the datasize command to use counts (rather than a scan of all the objects) to figure out the size of each chunk.  Removing the estimate option would give a more accurate result (particularly if your document size varies) but if the data set is not in RAM it can be very resource intensive.

AllChunkInfo = function(ns){
    var chunks = db.getSiblingDB("config").chunks.find({"ns" : ns}).sort({min:1}); //this will return all chunks for the ns ordered by min
    //some counters for overall stats at the end
    var totalChunks = 0;
    var totalSize = 0;
    var totalEmpty = 0;
    print("ChunkID,Shard,ChunkSize,ObjectsInChunk"); // header row
    // iterate over all the chunks, print out info for each
    chunks.forEach(
        function printChunkInfo(chunk) {

        var db1 = db.getSiblingDB(chunk.ns.split(".")[0]); // get the database we will be running the command against later
        var key = db.getSiblingDB("config").collections.findOne({_id:chunk.ns}).key; // will need this for the dataSize call
        // dataSize returns the info we need on the data, but using the estimate option to use counts is less intensive
        var dataSizeResult = db1.runCommand({datasize:chunk.ns, keyPattern:key, min:chunk.min, max:chunk.max, estimate:true});
        // printjson(dataSizeResult); // uncomment to see how long it takes to run and status           print(chunk._id+","+chunk.shard+","+dataSizeResult.size+","+dataSizeResult.numObjects);
        totalSize += dataSizeResult.size;
        totalChunks++;
        if (dataSizeResult.size == 0) { totalEmpty++ }; //count empty chunks for summary
        }
    )
    print("***********Summary Chunk Information***********");
    print("Total Chunks: "+totalChunks);
    print("Average Chunk Size (bytes): "+(totalSize/totalChunks));
    print("Empty Chunks: "+totalEmpty);
    print("Average Chunk Size (non-empty): "+(totalSize/(totalChunks-totalEmpty)));
}

You can call this on any sharded namespace from a mongos as follows:

mongos> AllChunkInfo("test.users");
ChunkID,Shard,ChunkSize,ObjectsInChunk
test.users-_id_MinKey,shard0000,0,0
test.users-_id_10000.0,shard0000,525420,26271
test.users-_id_36271.0,shard0001,1120640,56032
test.users-_id_92303.0,shard0001,153940,7697
***********Summary Chunk Information***********
Total Chunks: 4
Average Chunk Size (bytes): 450000
Empty Chunks: 1
Average Chunk Size (non-empty): 600000

The next lets you find the information for a particular chunk, again using an estimate.  This is somewhat safer to use without the estimate, but should still be used with caution:

ChunkInfo = function(ns, id){
    var configDB = db.getSiblingDB("config");
    var db1 = db.getSiblingDB(ns.split(".")[0]);
    var key = configDB.collections.findOne({_id:ns}).key;
    var chunk = configDB.chunks.find({"_id" : id, }).limit(1).next();
    var dataSizeResult = db1.runCommand({datasize:chunk.ns, keyPattern:key, min:chunk.min, max:chunk.max, estimate:true});
    print("***********Chunk Information***********");
    printjson(chunk);
    print("Chunk Size: "+dataSizeResult.size)
    print("Objects in chunk: "+dataSizeResult.numObjects)
}

Sample Output:

mongos> ChunkInfo("test.users", "test.users-_id_10000.0")
***********Chunk Information***********
{
        "_id" : "test.users-_id_10000.0",
        "lastmod" : Timestamp(3000, 0),
        "lastmodEpoch" : ObjectId("518373f0a962406e4467b054"),
        "ns" : "test.users",
        "min" : {
                "_id" : 10000
        },
        "max" : {
                "_id" : 36271
        },
        "shard" : "shard0000"
}
Chunk Size: 525420
Objects in chunk: 26271


Well... I figured it out!

Here's what I did:

Deploy all the reports from the BIDS project to a main folder, say Source Reports.
On the ReportServer, create site-specific folders in another folder, say Site-Specific Reports.  Also in that folder, create a Master Reports folder.
Copy the Master reports to the Master Reports folder.
For each site report, create a linked report in their site-specific folder under Site-Specific Reports.

Now here's the "tricky" part... if you edit the report in the Source Reports folder in Report Builder, you can set up the sub-report to point to a specific folder structure on the ReportServer.  This enables you to use sub-reports anywhere on your ReportServer, not just in the same local directory.  My assumption is you can also type in the full path of the deployed report you wish to use as a sub-report into BIDS, but with Report Builder you can browse and click.

So, in this case, I have made all of the site-specific reports point to the Master report in the Master Reports folder.

This is a great solution for us.  Now we can grant user access at the folder level without creating excessive burden on the report deployment process.


The .frm drives the parsing of rows.  So if the datatype or number of columns changes, it seems very likely that the rows cannot be read.

Consider using a VIEW to "change" the structure of a table.


SQL Query with ODBC, Joins and Group Bys

You have a working query, but you are selecting:

FROM CF30, EC01, OC02, OM01U1, RS2101F

With no explicit joins and only one implicit join:

WHERE OM01U1.OM01015 = RS2101F.OUTNUM

This is going to lead to problems. Can you find which fields (columns) match to which in each table? You could then say:

FROM OM01U1
INNER JOIN RS2101F
ON OM01U1.OM01015 = RS2101F.OUTNUM

For example.

You may also find it useful to add a derived table:

SELECT OUTNUM, Max(RS2101F.DELDATE) FROM RS2101F

So that would then read:

FROM OM01U1
INNER JOIN (
     SELECT OUTNUM, Max(RS2101F.DELDATE)
     FROM RS2101F
     GROUP BY OUTNUM) As t
ON OM01U1.OM01015 = t.OUTNUM


Database design for monthly leaderboards

After thinking around a bit more, a solution I think could work would be to have a new table of monthlyScores, structured like this:

+----+--------------+----------+--------+
| ID |  userID (FK) |   month  |  score |
+----+--------------+----------+--------+
|  1 |            1 |  03/2019 |     12 |
|  2 |            1 |  04/2019 |     23 |
|  3 |            2 |  03/2019 |      3 |
|  4 |            1 |  05/2019 |     22 |
|  5 |            2 |  06/2019 |     11 |
+----+--------------+----------+--------+

Where month is the current month, I could increment this value for the current months' games, thus meaning I could provide a leaderboard of the leaders for the current month. Similarly I could display scores for each user for previous months.

Posting this here as an answer as I feel like it could work. That said, as you can probably gather I'm very new to databases and so would be interested to hear about other solutions, and/or if this is advisable!


Loop through slices of 2-dimensional array in plpgsql function?

A FOR loop over array_upper(_fields,1) like you found is a simple and good option.

Even simpler for your case (for Postgres 9.1 or later): FOREACH with SLICE. The manual:

  With a positive SLICE value, FOREACH iterates through slices of the
  array rather than single elements.

Your function could look like this:

CREATE OR REPLACE FUNCTION public.mj_rubbish(_fields text[])
   RETURNS void AS
$func$
DECLARE
   m text[];
BEGIN
   FOREACH m SLICE 1 IN ARRAY _fields
   LOOP
      RAISE NOTICE '% EXECUTING fieldname: %, stats: %', now()::text, m[1], m[2];
   END LOOP;
END
$func$  LANGUAGE plpgsql;

This also works regardless of actual array indices - which do no have to start with 1 (but do by default).

Related:

Loop over array dimension in plpgsql
Step through multiple arrays in parallel with FOREACH
Normalize array subscripts for 1-dimensional array so they start with 1


what is relation between sql server back file size and space required for restoring database

A few points to consider - some may apply in your case:

SQL Server backups only contain pages used

From Books Online:

  The backup contains only the actual data in the database and not any
  unused space.

This means that if your database has a lot of free space, the backup might be small.

You can check space usage using sp_spaceused or a view like sys.master_files.

Database restores recreate the data files exactly as they were backed up

Say you have a database with a 10GB data file and a 5GB log file, which you've backed up. When you restore the database, it will recreate these files as they were when they were backed up; it won't change the data layout in any way.

So it seems like your original database had a 12GB file - when restoring this file is being recreated.

Compression can be applied

Depending on your version/edition you can compress backup files (Enterprise only in SQL Server 2008) - this reduces the backup file size so might be misleading.

What can you do?

Easiest answer is to increase the size of your drive or restore to a different location.

Another option would be to look at the size of the original DB and consider if it has been maintained correctly, e.g. it may have a large transaction log file that hasn't been backed up correctly. Questions about appropriate database size and actions might be best suited to dba.stackexchange.com.


Extract start and end per group of rows, where only the end can be identified

You can use count() as window function to identify groups like this:

SELECT vehicle, min(date_from) AS starts, max(date_to) AS stops
FROM  (
   SELECT vehicle, date_from, date_to
        , count(description LIKE 'RET%' OR NULL)
             OVER (PARTITION BY vehicle ORDER BY date_from DESC) AS grp
    FROM  tbl
   ) sub
GROUP  BY vehicle, grp;

Count in descending order, since the end of each group is significant. Then just extract minimum start and maximum end per grp in the outer SELECT.

SQL Fiddle.

Assuming all timestamp columns are defined NOT NULL.

Since timestamps are in ascending order (due to the logic of the problem), we don't need the id column at all for this.

This works for any number of vehicles, not just the one you demonstrate in your example.

Related answer with more details:

Select longest continuous sequence

About my counting method count(<expression> OR NULL):

For absolute performance, is SUM faster or COUNT?


Add info to mysql view around an IP address - 10.10.1.100 to https://10.10.1.100

SELECT Matrix.Name AS Name,
  CONCAT('https://',Matrix.DracIP) AS DracIP,
  Matrix.Asset
FROM Matrix
WHERE Matrix.Type = 'Physical-Lou1'


Need a log based replication / CDC solution to publish postgres to oracle subscribers

There is no such feature at this time and the transaction logs lack some of the key information you would need to implement such a feature.

Work is ongoing for PostgreSQL 9.4 to implement logical and bi-directional replication between PostgreSQL instances. Part of this work involves enhancements to the xlogs to add some key information required for logical row replication, adding a new wal_level = 'logical' option. There's also a pluggable infrastructure for decoding the WAL and streaming it to remote hosts, writing SQL to a file, etc.

That's where it becomes useful to you. Your best bet at this point is probably to use that infrastructure to decode PostgreSQL WAL into a change stream you can send to your remote Oracle or SQL Server systems. This will involve a considerable amount of development, as the logical replication code is somewhat immature and still has some limitations even with Postgresql-to-PostgreSQL limitations. DDL is totally unsupported at this time, for one thing.

If you do this you'll need to understand that the BDR work will probably not be committed into PostgreSQL 9.4 in a fully compatible form so you'd have to do re-work to make it compatible with main-line PostgreSQL later.

You'll want to start by adapting the existing text output plugin for the logical walsender to generate more database-agnostic SQL.

If this all sounds way too hard then your only other option is to implement a trigger-based replication solution or adapt an existing trigger based replication system to meet your needs. This will be much (much) higher overhead on the server, but a fair bit simpler.

I should disclose that I work for 2ndQuadrant, who're developing bidirectional replication for PostgreSQL, and I've recently started working on testing and documentation as part of the project.

Update as of 2014-07: Logical changeset extraction / logical decoding was committed to PostgreSQL 9.4, and is due to be released soon. Beta 2 came out a few days ago.

See:

the 9.4 docs on logical decoding
Robert Haas's post on the topic
depesz's post on the topic
Michael Paquier's post on the topic

Logical decoding makes it possible to implement what you want, but does not provide the feature you want directly. To stream changes into MS SQL, Oracle, etc, you would need an output plugin for logical decoding that can stream data to a streaming logical log receiver. The receiver application would maintain a connection to the target server and apply the changes as it receives them from PostgreSQL.

This is the basis of the PostgreSQL BDR/UDR architecture. BDR is not directly useful for your purposes, but the logical changeset decoding feature that was built to make BDR possible will be.

A good starting point would be to look at the test_decoding plugin


Query to List all hierarchical children with parent, grandparent etc. from entire table

you just cannot do recursive on millions of rows.you have to put some filter

Try this,

F object_id('tempdb..#table') IS NOT NULL
    DROP TABLE #table

CREATE TABLE #table (
    id INT identity(1, 1)
    ,[Name] VARCHAR(16)
    ,ParentID INT NULL
    )

INSERT INTO #table
VALUES('John',null)
,('Doe',1)
,('Mike',1)
,('Neil',3)
,('Sylvia',4)
,('Mary',2)

    WITH CTE
AS (
    SELECT id
        ,ParentID,1 DirectP
    FROM #table

    UNION ALL

    SELECT r.ID
        ,t.parentID,0 DirectP
    FROM #table t
    INNER JOIN CTE r ON t.id = r.parentID
    WHERE t.ParentID IS NOT NULL
    )
SELECT r.ID
    ,r.ParentID
   ,DirectP
FROM CTE r

ORDER BY id
    ,ParentID DESC


"Errors with a severity of 20 or higher that cause the Database Engine to terminate the connection will not be handled by the TRY…CATCH block. However, TRY…CATCH will handle errors with a severity of 20 or higher as long as the connection is not terminated."

Could it be because of this?

Source


Correct me if I'm wrong, but this is how I understand the process of copying widgets.

Copy widgets from the site servers (A and B) to the Central Server
If a widget needs additional processing, copy that widget from the central server to its site server A or B
At this point, site A has its own records plus X site B records
Transaction replication is sending widgets from A and B to the Central server
The Site B records on Site A may already exist on the Central server because Site B may have already sent them to the Central server via transactional replication.
As published tables must have a primary key, the replication would fail when Site A attempts to replicate Site B's widgets to the central server.

You could skip those errors via the Agent Profile, if you had the specific error number.

From SSMS
1. Right Click on Replication

Select Launch Replication Monitor
Expand My Publishers
Expand the Publisher you want to Modify
Select the Publication.
In the subscriptions window to the right, right click on the Subscription and select Agent Profile.
Create a new profile and select skip_errors
In the SkipErrors row, add in the error numbers seperated by : that you wish to skip.

That is probably not what you are trying to accomplish, as the replication would crash every time you copied widgets from Site B to Site A.

Question, are you trying to partially or fully replace the existing process of copying widgets between sites with transactional replication?

Note

There may be a more elegant way to do this, such as using TSQL to copy records in batches from Site A and Site B to Central, if they don't yet exist. You could accomplish that using OPENQUERY or SSIS.

Here is a crude example using OPENQUERY

/* From Central server,
insert all records from SITEA except those that already
exist on Central
*/
INSERT INTO widgets (widgetID, widgetName, widgetDate, widgetDesc)
SELECT widgetID, widgetName, widgetDate, widgetDesc
FROM openquery(SITEA_SERVER, '
 select widgetID, widgetName, widgetDate, widgetDesc
 from widgets_database.dbo.widgets
')
EXCEPT
SELECT widgetID, widgetName, widgetDate, widgetDesc
FROM widgets WITH (nolock)


Sort a varchar type column (Alphanumeric Values)

i am assuming before - you would have integer value always

declare @temp table
(val nvarchar(99))
insert into @temp values ('1-2'  )
insert into @temp values ('3-5'  )
insert into @temp values ('1-2A')
insert into @temp values ('6-12')
insert into @temp values ('6-7')
insert into @temp values ('6-12D')
insert into @temp values ('20-30')
insert into @temp values ('15-18')
--insert into @temp values ('3')
--insert into @temp values ('53')

QUERY

select * from @temp
order by CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)

Exactly Ordered

select * from @temp
order by CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)
,CAST(LEFT(RIGHT(val,LEN(val)-CHARINDEX('-',val)), patindex('%[^0-9]%', RIGHT(val,LEN(val)-CHARINDEX('-',val))+'.') - 1)  AS INT),
RIGHT(val,LEN(val)-CHARINDEX('-',val))

after run second query the output would be

UPDATE AFTER COMMENT: sometime varchar val won't come (3-5) or (1-12A) instead of whole number 3 or 5... at the time the above answer getting an error... how to correct way to sort.

-- DECLARE TABLE
DECLARE @temp TABLE
(val nvarchar(99))

--INSERT INTO TABLE
INSERT INTO @temp VALUES
('1-2'), ('1'), ('3-5'), ('6'), ('1-2A'), ('6-12'), ('7-8'), ('9-12D'), ('9-12'), ('17'), ('9-12B'), ('19'), ('20-30'), ('15-18')

-- QUERY
SELECT * FROM @temp
ORDER BY
CASE
    WHEN ISNUMERIC(val) = 0 THEN CAST(LEFT(val,CHARINDEX('-',val)-1)  AS INT)
    WHEN ISNUMERIC(val) = 0 THEN CAST(LEFT(RIGHT(val,LEN(val)-CHARINDEX('-',val)), patindex('%[^0-9]%', RIGHT(val,LEN(val)-CHARINDEX('-',val))+'.') - 1)  AS INT)
    ELSE val
    END ,val

RESULT:


Find partial match with textsearch query

Postgres text search supports prefix matching:
SELECT *
FROM   renns
WHERE (
   to_tsvector('english', coalesce(numar_admin, '')) ||
   to_tsvector('english', coalesce(nume_strada, '')) ||
   to_tsvector('english', coalesce(nume_prenume, ''))||
   to_tsvector('english', coalesce(bloc, '')) ||
   to_tsvector('english', coalesce(apartament, ''))
) @@ to_tsquery('simple', $q || ':*');

($q being a single word.)
This can use an index on your expression:
(to_tsvector('english', coalesce(numar_admin , '')) ||
 to_tsvector('english', coalesce(nume_strada , '')) ||
 to_tsvector('english', coalesce(nume_prenume, '')) ||
 to_tsvector('english', coalesce(bloc        , '')) ||
 to_tsvector('english', coalesce(apartament  , '')))

Using the 'simple' dictionary disables stemming. If you want stemming, use the 'english' dictionary like you had before.

"caisului 498:*"  the "CAISULUI" string is from one column and the number from another column of the renns table

To pass multiple lexemes, apply 'english' stemming and do prefix matching for all of them:
@@ (to_tsquery('english', column_a || ':*') &&
    to_tsquery('english', column_b || ':*') -- more?
   )

Detailed explanation:

Get partial match from GIN indexed TSVECTOR column

Prefix matching matches at the start of lexemes. For any partial match, you can use LIKE or regular expression matches (~) in combination with a trigram index. Different approach! Requires a different query and a different index! See:

How to create an index to speed up an aggregate LIKE query on an expression?
Faster query with pattern-matching on multiple text fields
How is LIKE implemented?
Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

There are smart ways to integrate both. The manual has an example.


File creation dates of archived WAL files not in line with archive_timeout in PostgreSQL 9.6

The documentation has an answer to this:

  When this parameter is greater than zero, the server will switch to a new segment file whenever this many seconds have elapsed since the last segment file switch, and there has been any database activity, including a single checkpoint (checkpoints are skipped if there is no database activity).

Based on your observation and the above quote, I guess you have a checkpoint every 20 seconds or so, and a very low rate of changes.


Oracle INSERT with If statement for values

This has nothing to do with the insert.

select
  decode(col2b, 1, 'A', 2, 'B', 'C'),
  col2b
from table2;


Yes, use the same cert on each instance. Your restore process would be clumsy if you had different certs on different instances.

I would suggest you read the documentation (including the relevant links) as encryption and key hierarchy in SQL Server is a topic too large for a single answer on a Q&A site, but essentially you need to:

Create a database master key
Create a certificate
Backup your certificate and keys
Import the certificate to each node of the AG and any other instance you plan on restoring to
Use the ENCRYPTION option in your backup statements