Recommendations for more than one tempdb file

We have various installations at our shop and we tend to start low with four tempdb files and then add additional files if there is tempdb contention as pointed out by @Kevin3nfs comment, where he references an SQLSkills.com search.

Microsoft has a knowledge base article: Recommendations to reduce allocation contention in SQL Server tempdb database

  [...]
  As a general rule, if the number of logical processors is less than or equal to 8, use the same number of data files as logical processors. If the number of logical processors is greater than 8, use 8 data files and then if contention continues, increase the number of data files by multiples of 4 (up to the number of logical processors) until the contention is reduced to acceptable levels or make changes to the workload/code.
  [...]

SQLSKills.com has a good article The Accidental DBA (Day 27 of 30): Troubleshooting: Tempdb Contention which has some general information about tempdb contention and a bit further down a script which will retrieve information about tempdb contention from some DMVs.

  [...]
  One of the most common performance problems that exists in SQL Server instances across the world is known as tempdb contention. What does that mean?

  Tempdb contention refers to a bottleneck for threads trying to access allocation pages that are in-memory; it has nothing to do with I/O.

  Consider the scenario of hundreds of concurrent queries that all create, use, and then drop small temporary tables (that by their very nature are always stored in tempdb). Each time a temp table is created, a data page must be allocated, plus an allocation metadata page to keep track of the data pages allocated to the table. This requires making a note in an allocation page (called a PFS page – see here for in-depth info) that those two pages have been allocated in the database. When the temp table is dropped, those pages are deallocated, and they must be marked as such in that PFS page again. Only one thread at a time can be changing the allocation page, making it a hotspot and slowing down the overall workload.
  [...]

Below is a script we used for very large database server configurations. It creates a tempdb with 8 files of 11GB size with no autogrowth and a tempdb transaction log file of 11GB. This script can be adapted to your requirements.

USE [master]
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'tempdev',FILENAME = N'G:\tempdb.mdf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev2', FILENAME = N'G:\tempdb2.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev3', FILENAME = N'G:\tempdb3.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev4', FILENAME = N'G:\tempdb4.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev5', FILENAME = N'G:\tempdb5.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev6', FILENAME = N'G:\tempdb6.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev7', FILENAME = N'G:\tempdb7.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N'tempdev8', FILENAME = N'G:\tempdb8.ndf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )
GO
ALTER DATABASE [tempdb] MODIFY FILE ( NAME = N'templog', FILENAME = N'G:\templog.ldf' , SIZE = 12288000KB , MAXSIZE = UNLIMITED, FILEGROWTH = 0 )

As you can see these files were all located on the same drive.

If you still notice contention in the tempdb database, then splitting the tempdb database files over various disks might be a possible solution, but than depends on your configuration (SAN, SSD, ...) and would have to be verified with testing.


First of all, if this is homework, please tag it as such.

Secondly if it's not homework and you're doing this in a professional environment, get a professional to do it (or at least to thoroughly scrutinize your final design). Schema design underpins your application design, and flows on from clarity in business requirements and how well you understand those requirements. If you don't completely and clearly understand the requirements, your schema is going to be miles off the mark.

And, to actually answer the question (at least as best I can with the information given), I'd keep the staff in one table, however you may want to create ancillary tables for each 'type' of staff member (doctor, nurse, janitor, admin, etc) to store data specific to that staff-member-type. To give an answer that's any more specific, the requirements need to be more specific.


GROUP BY uninterrupted sequence of logs for same location

SELECT location, employee, min(created_at) AS start_ts, max(created_at) AS end_ts
FROM  (
   SELECT location, employee, created_at
        , row_number() OVER (PARTITION BY location ORDER BY created_at)
        - row_number() OVER (PARTITION BY location, employee ORDER BY created_at) AS grp
   FROM   employee_logs
   ) sub
GROUP  BY location, employee, grp
ORDER  BY min(created_at), max(created_at);

This interrupts a sequence when a different employee logs at the same location. It does not interrupt when the same employee logs at a different location (which might also make sense, but you didn't mention that).

In the subquery, have an running number over all rows for the same location and subtract the running number over rows for the same location with the same employee. The result grp is a number without meaning, that denotes each group of rows forming a sequence together.

Aggregate in the outer SELECT. It is important to remember that grp is only unique per (location, employee), so you have to group by all three columns.

SQL Fiddle.

Related, with ample explanation:

Select longest continuous sequence


I think that you missed one step. You should add an entry into the keytab file for your principal mongodb/mongodb.centos7.vm@CENTOS7.VM

Suppose that the path for your keytab file is /etc/krb5.keytab. Now run $ktutil

ktutil: add_entry -password -p mongodb/mongodb.centos7.vm@CENTOS7.VM -k 1 -e des-cbc-md4
ktuitl: wkt /etc/krb5.keytab
ktuitl: quit

Now you can run $klist -k /etc/krb5.keytab
and the new entry should appear on the output.

Now, I think you can start mongod service.


SQL "select" query for getting 0 instead of null?

You can use

SELECT IFNULL(ColumnName,0) FROM TableName

or

SELECT COALESCE(ColumnName,0) FROM TableName


Is there a less manual way of changing database properties for all databases in an instance?

I often generate T-SQL like this:

select 'ALTER DATABASE ' + quotename(name) + ' MODIFY FILE (...)'
from sys.databases
where databaseIsMisconfigured = 1

Select the entire column, past it into a new window and F5.


How to profile MySQL during a period of time

In my experience, PROFILE always said that 99% of the time was spent in some "useless"  category, such as "sending data".  Such categories 'never' translated into anything actionable.  PROFILE is being deprecated -- good riddance.
EXPLAIN SELECT ... and EXPLAIN FORMAT=JSON ... have some use.  So does the "optimizer trace".
Use the slowlog: mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog And you can do that on a live system.
Another technique for looking into what is going on is with the "Handler" counts.  This works even when a table is [currently] so small that timings are not very useful.  Details:  http://mysql.rjweb.org/doc.php/index_cookbook_mysql#handler_counts


Is it a good to move data out of the original table when it's deleted or archived?

No.

Data has a place to be based on what it is. Moving it somewhere else such as to an archive table or a deleted table is an implementation detail -- a workaround of sorts for performance.

Some databases, like PostgreSQL 10, can gracefully do this under the hood with List Partioning. In doing so, the "deleted" values are stored in a different partition (another distinct table is made available to the user), but it doesn't get in the way.

CREATE TABLE offers (...) PARTITION BY LIST (status);
CREATE TABLE offers_deleted PARTITION OF offers
  FOR VALUES IN ('deleted');

Now you could query offers WHERE status='deleted', and under the hood it would focus on just that one partition. Remove that predicate, and you'd query all of the partitions of offers. Or, you could query offers_deleted directly.

Even there though, my advice would generally be it's bad practice to move data because the status is deleted or because it's archived, unless performance demands it. Then you don't do it because you want to, you do it because you have to.


query to determine forceencryption state

To determine the setting you need to read from the registry. Replace your version/instancename in the query below:

EXEC [master].[dbo].[xp_regread]    @rootkey='HKEY_LOCAL_MACHINE',
                                    @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL<version>.<instancename>\MSSQLServer\SuperSocketNetLib',
                                    @value_name='ForceEncryption'

You should be able to get the correct path from dm_server_registry which unfortunately doesn't give me the ForceEncryption property.

SELECT registry_key, value_name, value_data
FROM sys.dm_server_registry

To determine the current connections encryption state you can just select them from sys.dm_exec_connections

SELECT encrypt_option, *
FROM sys.dm_exec_connections


Automatic creation of unique Alphanumeric ID?

This will work. There may be something more elegant but this should get you started.

 Public Function GetUID() As String
      GetUID = "G" & Right("000" + CStr((CInt(Right(DMax("[YourIDField]", "TableContainingField"), 3)) + 1)), 3)
 End Function


Plpgsql (Postgres) function for sanity check

It seems you just need the query:

UPDATE sensors
 SET seconds=(SELECT sensors.starttime-"timestamp" FROM secure_sanity
              WHERE id=*id*)
   WHERE seconds=0;

For that, it's overkill to write a plpgsql function.

Or if you really want one, just replace this *id* by a function parameter put the above UPDATE query inside a CREATE FUNCTION / BEGIN / END block.


MySql autoincrement counter resetting after server restart

MySQL fix

After 14.5 years this is fixed in MySQL version 8.0

  [6 Oct 16:40] Daniel Price Posted by developer: Fixed in 8.0.0 by WL#6204.

WL#6204: InnoDB persistent max value for autoinc columns

MariaDB fix

Also this was MariaDB 10.2.4 released on July 12, 2017

MDEV-6076 Persistent AUTO_INCREMENT for InnoDB.

I found out this change was finally added on the 2ndquadrant blog,

Why PostgreSQL is better than MySQL


INNER, OUTER, and CROSS are ignored by MySQL.  The ON and WHERE control which type of JOIN it is.

In your examples you have nothing tying the tables together, hence operates like a CROSS JOIN.

GROUP BY, etc have no impact.


When to use a multidimensional relationship in an ERD?

I'm going to share some terminology with you that may help in your research.

The relationship you pictured is what is called a "ternary" relationship.  It involves three entities.  The simple relationships we usually deal with involve only two entities, and could be called "binary".  There are "quaternary" relatinships, and so on.  The general term is "n-ary".

You may also want to look up the following:  Star schema, Ralph Kimball, Data warehousing, and multidimensional data model.  A star schema is basically the intersection of relational tables and multidimensional  modeling.  There's too much here for me to do the subject justice.  Happy hunting!


SQL - Passing a Stored Procedure Variable as a Parameter in a crystal report for end users to select

Ok here are the steps I took to reference parameters from my crystal report to the stored procedure.

Add parameter declarations in stored procedure like so:

ALTER PROCEDURE [dbo].[a_ras_TruckSummary_SP]

@location VARCHAR(10),

@dateshipped DATETIME
Then navigate to crystal report builder and select "Database>>>>Database Verify". This will refresh the database connection and present your parameters as a dialog box. From there you will enter your values to test the parameters
If they test ok the dialog box will be closed and you can then navigate to "field explorer" and under "parameter fields" you will now see your new parameters.
Then you can simply drag them over to the report.


WHERE TIME(dt) = '08:00:00' is another way to filter on exactly 8am.


You can store your images as blobs, but in practice you're usually better off storing them in the file system and just keeping the paths to the images in the database.

See: MySQL Docs: BLOB datatype


Short answer: NO!.

Longer answer: If you use synchronous NFS and you have robust STONITH / fencing, then with great caution you could run a shared-storage failover cluster over NFS without horribly corrupting your data. However, it won't do you any good for failure modes where the data is affected by the problem.

Shared-access SAS/SCSI, DRBD, shared-access SAN storage (iSCSI etc), or similar block-level sharing is slightly saner than using NFS, but still not safe.

If you don't already know what STONITH or fencing means, then see "short answer" above.

Use replication. Really.


Count number of rows and sum up a field of all tables in all relevant schemas with a readonly connection

Did you miss Tony Andrew's solution @ answer ?

Here's a dbfiddle example based on Tony's solution: find count and sum from same table w/ different schemas

For the sake of this example I'll setup a second AUDIT_ACTIONS table:

create table AUDIT_ACTIONS as (select * from SYS.AUDIT_ACTIONS where ACTION <= 50)

Create a table to store our results in:

create table results(owner varchar(100), table_name varchar(100), row_count integer, audit_sum integer);

Some minor tweaks to Tony's solution:

declare
    v_count integer;
    v_sum   integer;
begin
    for t in (select table_name, owner from all_tables
              where table_name = 'AUDIT_ACTIONS')
    loop
          EXECUTE IMMEDIATE 'select count(*), sum(ACTION) from ' || t.owner || '.' || t.table_name
              into v_count, v_sum;
          insert into results (owner, table_name, row_count, audit_sum)
          values (t.owner, t.table_name, v_count, v_sum) ;
    end loop;
end ;
/

And the results:

select * from results;

OWNER                       | TABLE_NAME    | ROW_COUNT | AUDIT_SUM
:-------------------------- | :------------ | --------: | --------:
FIDDLE_QPLKSTJBEESOQLZCONZA | AUDIT_ACTIONS |        51 |      1275
SYS                         | AUDIT_ACTIONS |       180 |     18451

NOTE: The non-SYS OWNER(SCHEMA) will change with each dbfiddle run.


The sniff comes from the NULL or the empty string. The addition of % is ignored.
You can fix this one of 2 ways:

Use parameter masking, by using another variable internally, say @IstrItem
SET @IstrItem = ISNULL(@strItem, '') + '%'
Add OPTIMIZE FOR UNKNOWN hint to the query

Other observations:

Why not an index on all 3 columns? One of these is probably best
serltnum, txdate, itemnmbr or txdate, serltnum, itemnmbr
Do datatypes and length match? eg txdate is datetime, itemnmbr is varchar(20) etc
If not, indexes will be ignored
So many non-key columns will require a key lookup, or the indexes are being ignored
See Simple Talk's covering indexes article for more


Circular dependency in schema or boolean with CHECK?

Having the origin_remote column in the repositories table allows for an intrinsic consistency, even without check, and you'll keep this consistency when changing DBMS and, more importantly, in the application code.

This also allows for a single query when you want to change the origin of a repository, while having your is_origin boolean would imply two queries which would have to be wrapped in a transaction.

In this specific case I'm of the opinion you should really use the standard relational link from repositories to origin_remote_id. The need to use alter table is just as a minor inconvenience.


Firstly this needs to pre-mentioned that only shrink files if its actually needed, as you'll cause a lot of index fragmentation which you'll need to clear up afterwards.

With that said, if you're worried about the time it takes to run the entire shrinkfile then you can do it in stages (takes longer overall but technically can be stopped and resumed with a lot more ease), personally I use this to drop it in 2GB stages (also useful on high transactional dbs to reduce contention for the operation to shrink in smaller chunks I've found)

DECLARE @value int = 2048
DECLARE @target int = 1536
SET @value = (@value *1024)-2048

WHILE @value >= (@target*1024)
BEGIN
    DBCC SHRINKFILE(filename ,@value)
    SET @value = @value - 2048
END

Replace

@value with the current size of your file in MB and the

@target with what size you want to shrink down to and

filename with the name of the file you wish to be shrinking


Oracle not trusting statistics after an import?

A number of factors can change on import.

Fill factor for data blocks is likely more optimal.  Partially filled data blocks from the development system will be filled on import. This can change over time if data is updated and added, but does not appear to be a factor in this case.
Changes in block placement of rows may alter the number of block required to retrieve by index.  This can have positive or negative impacts on the data.
Cost parameter for data and or index blocks may be different on the two databases resulting in different cost weights for plans.
Block sizes may be different on the two databases resulting in different costs.
Newer versions of Oracle will recalculate statistics automatically as data changes.  The exported statistics are normally a subset of the full statistics.  There is a DBA_STATS package which can be used to export and import the full set of statistics.
Histogram sizes may be different on the two systems resulting in differences in level of detail.  (Its been a while since I tuned this kind of thing.)  If the histograms are missing, Oracle will sample the data and estimate.  Different sample sizes can cause very different behavior.
Oracle may decide to use bitmap indexes in some cases.  This can require regenerating the indexes on each query.  You can add bitmap indexes for the required columns.  This option can be disabled at the database level (it may be an undocumented setting).

Try generating cost statistics for the queries you are using on both the Development and Production systems.  You may also want to examine the Statistics on both systems.

You may get more consistent results using transportable tablespaces for the tables and indexes.  This will eliminate the import/export step.

EDIT: Added bitmap, statistics, and histogram info above.

It is possible to just run an explain plan without running the queries.  There are various tools which assist in this.  I have used Aqua Data Studio, but SQL Developer should work as well.  For parsed queries (running or not) there is a view available from which the plan can be extracted.

There are available views which can be used to view the current statistics for a table.  Comparing used blocks on both systems will give you a quick idea how much statistics should have changed.  Histogram data is can result in different plans.


Does Windows Server File Compression slow Oracle DB performance?

Indeed it is a bad idea put oracle datafiles in a compressed folder as it uses async random IO.

eg. on http://support.microsoft.com/kb/156932 we read:

  One obstruction to asynchronous operation is NTFS compression. The file system driver will not access compressed files asynchronously; instead all operations are just made synchronous.

If you have enterprise version of Oracle installed you can look into table and index compression provided by Oracle itself.

We can still use NTFS compression for almost everything in the recovery area (archive logs,rman backupsets,...),the dpdump folder and logging files.


How to sync two tables which are on different databases and servers using SSIS?

It's not clear what you're try to do.

It might be that you want a read-only copy for reporting; in which case you should probably consider log shipping, db mirroring or the like.

If you just want to be able to query data from another server, linked servers are an option. The queries would always return fresh data, but there's a performance hit to consider.

SSIS is the correct tool, if you want periodic secondary copies updated batch-style.

Service Broker is almost certainly far too complex for your scenario, but addresses asynchronous cases.


A slow query on MS-SQL-Server 2019 cannot be optimized

After carefully reviewing your fourth question on the same topic, I'm afraid my diagnosis is exactly the same I provided in your first question. Let's look at your first query here.

You can see it executes in 29 seconds. But let's look at compile time (In SSMS: F4 to enable properties and click on the SELECT node):

The number above is in miliseconds. Converting to minutes it's 77. Adding both times together you get  close to observed 78 minutes runtime.
Let's look at the second, "good" query:

6 minutes compilation time.
Now you might ask yourself is this a lot? The answer is yes in both cases. I would consider compilation time in SECONDS slow. Your cases are patological.
I cannot answer what causes it. I will only give some ideas you could try. First a question: is the problem affecting other queries in your system? If yes then maybe something on the instance is wrongly configured. Do you have any funny trace flags enabled?
Your issue actually existed in original database. It must have been carried over. Like schema. Script out all the tables from the query and everything related to it. Is there anything funny? 100 statistics on one table? 100 indexes? hypotethical indexes?
Try cutting up the query. Remove one join or predicate at the time. You will get garbage results, but at some point the reduced query should start compiling in normal times. That will give you a clue what could be causing it.
That's all I got. Good luck.


ORDER BY and comparison of mixed strings of letters and numbers

Want a sensible, efficient means of sorting numbers in strings as actual numbers? Consider voting for my Microsoft Connect suggestion: Support "natural sorting" / DIGITSASNUMBERS as a Collation option

There is no easy, built-in means of doing this, but here is a possibility:

Normalize the strings by reformatting them into fixed-length segments:

Create a sort column of type VARCHAR(50) COLLATE Latin1_General_100_BIN2. The max length of 50 might need to be adjusted based on the max number of segments and their potential maximum lengths.
While the normalization could be done in the app layer more efficiently, handling this in the database using a T-SQL UDF would allow for placing the scalar UDF into an AFTER [or FOR] INSERT, UPDATE Trigger such that you are guaranteed of properly setting the value for all records, even those coming in via ad hoc queries, etc. Of course, that scalar UDF can also be handled via SQLCLR, but it would need to be tested to determine which one was actually more efficient. **
The UDF (regardless of being in T-SQL or SQLCLR) should:

Process an unknown number of segments by reading each character and stopping when the type switches from alpha to numeric or numeric to alpha.
Per each segment it should return a fixed-length string set to the maximum possible characters/digits of any segment (or maybe max + 1 or 2 to account for future growth).
Alpha segments should be left-justified and right-padded with spaces.
Numeric segments should be right-justified and left-padded with zeroes.
If alpha characters can come in as mixed-case but the ordering needs to be case-insensitive, apply the UPPER() function to the final result of all segments (so that it only needs to be done once and not per segment). This will allow for proper sorting given the binary collation of the sort column.

Create an AFTER INSERT, UPDATE Trigger on the table that calls the UDF to set the sort column. To improve performance, use the UPDATE() function to determine if this code column is even in the SET clause of the UPDATE statement (simply RETURN if false), and then join the INSERTED and DELETED pseudo-tables on the code column to only process rows that have changes in the code value. Be sure to specify COLLATE Latin1_General_100_BIN2 on that JOIN condition to ensure accuracy in determining if there is a change.
Create an Index on the new sort column.

Example:

P7B18   -> "P     000007B     000018"
P12B3   -> "P     000012B     000003"
P12B3C8 -> "P     000012B     000003C     000008"

In this approach, you can sort via:

ORDER BY tbl.SortColumn

And you can do range filtering via:

WHERE tbl.SortColumn BETWEEN dbo.MyUDF('P7B18') AND dbo.MyUDF('P12B3')

or:

DECLARE @RangeStart VARCHAR(50),
        @RangeEnd VARCHAR(50);
SELECT @RangeStart = dbo.MyUDF('P7B18'),
       @RangeEnd = dbo.MyUDF('P12B3');

WHERE tbl.SortColumn BETWEEN @RangeStart AND @RangeEnd

Both the ORDER BY and the WHERE filter should use the binary collation defined for SortColumn due to Collation Precedence.

Equality comparisons would still be done on the original value column.

Other thoughts:

Use a SQLCLR UDT. This might could work, though it is unclear if it presents a net-gain as compared to the approach described above.

Yes, a SQLCLR UDT can have its comparison operators overridden with custom algorithms. This handles situations where the value is being compared to either another value that is already the same custom type, or one that needs to be implicitly converted. This should handle the range filter in a WHERE condition.

With regards to sorting the UDT as a regular column type (not a computed column), this is only possible if the UDT is "byte ordered". Being "byte ordered" means that the binary representation of the UDT (which can be defined in the UDT) naturally sorts in the appropriate order. Assuming that the binary representation is handled similarly to the approach described above for the VARCHAR(50) column that has fixed-length segments that are padded, that would qualify. Or, if it was not easy to ensure that the binary representation would naturally be ordered in the proper way, you could expose a method or property of the UDT that outputs a value that would be properly ordered, and then create a PERSISTED computed column on that method or property. The method needs to be deterministic and marked as IsDeterministic = true.

Benefits of this approach are:

No need for an "original value" field.
No need to call a UDF to insert the data or to compare values. Assuming that the Parse method of the UDT takes in the P7B18 value and converts it, then you should be able to simply insert the values naturally as P7B18. And with the implicit conversion method set in the UDT, the WHERE condition would also allow for using simply P7B18`.

Consequences of this approach are:

Simply selecting the field will return the binary representation, if using the byte ordered UDT as the column datatype. Or if using a PERSISTED computed column on a property or method of the UDT, then you would get the representation returned by the property or method. If you want the original P7B18 value, then you need to call a method or property of the UDT that is coded to return that representation. Since you have to override the ToString method anyway, that is a good candidate for providing this.
It is unclear (at least to me right now since I have not tested this part) how easy/difficult it would be to make any changes to the binary representation. Changing the stored, sortable representation might require dropping and re-adding the field. Also, dropping the Assembly containing the UDT would fail if used in either manner, so you would want to make sure that there was nothing else in the Assembly besides this UDT. You can ALTER ASSEMBLY to replace the definition, but there are some restrictions on that.

On the other hand, the VARCHAR() field is data that is disconnected from the algorithm so it would only require updating the column. And if there are tens of millions of rows (or more) then that can be done in a batched approach.

Implement the ICU library which actually allows for doing this alphanumeric sorting. While highly functional, the library only comes in two languages: C/C++ and Java. Which means you might need to either do some tweaks to get it to work in Visual C++, or there is the off chance that the Java code can be converted to MSIL using IKVM. There are one or two .NET side projects linked on that site that provide a COM interface that can be accessed in managed code, but I believe they have not been updated in a while and I have not tried them. The best-bet here would be to handle this in the app layer with the goal of generating sort keys. The sort keys would then be saved into a new sort column.

This might not be the most practical approach. However, it is still very cool that such an ability exists. I provided a more detailed walk-through of an example of this in the following answer:

Is there a collation to sort the following strings in the following order 1,2,3,6,10,10A,10B,11?

But the pattern being dealt with in that question is a bit simpler. For an example showing that the type of pattern being dealt with in this Question also works, please go to the following page:

ICU Collation Demo

Under "Settings", set the "numeric" option to "on" and all of the others should be set to "default". Next, to the right of the "sort" button, uncheck the option for "diff strengths" and check the option for "sort keys". Then replace the list of items in the "Input" text area with the following list:

P12B22
P7B18
P12B3
as456456hgjg6786867
P7Bb19
P7BA19
P7BB19
P007B18
P7Bb20
P7Bb19z23

Click the "sort" button. The "Output" text area should display the following:

as456456hgjg6786867
    29 4D 0F 7A EA C8 37 35 3B 35 0F 84 17 A7 0F 93 90 , 0D , , 0D .
P7B18
    47 0F 09 2B 0F 14 , 08 , FD F1 , DC C5 DC 05 .
P007B18
    47 0F 09 2B 0F 14 , 08 , FD F1 , DC C5 DC 05 .
P7BA19
    47 0F 09 2B 29 0F 15 , 09 , FD FF 10 , DC C5 DC DC 05 .
P7Bb19
    47 0F 09 2B 2B 0F 15 , 09 , FD F2 , DC C5 DC 06 .
P7BB19
    47 0F 09 2B 2B 0F 15 , 09 , FD FF 10 , DC C5 DC DC 05 .
P7Bb19z23
    47 0F 09 2B 2B 0F 15 5B 0F 19 , 0B , FD F4 , DC C5 DC 08 .
P7Bb20
    47 0F 09 2B 2B 0F 16 , 09 , FD F2 , DC C5 DC 06 .
P12B3
    47 0F 0E 2B 0F 05 , 08 , FD F1 , DC C5 DC 05 .
P12B22
    47 0F 0E 2B 0F 18 , 08 , FD F1 , DC C5 DC 05 .

Please note that the sort keys are structure in multiple fields, separated by commas. Each field needs to be sorted independently, so that presents another small problem to solve if needing to implement this in SQL Server.

** If there is any concern about performance regarding the use of User-Defined Functions, please note that the proposed approaches make minimal use of them. In fact, the main reason for storing the normalized value was to avoid calling a UDF per each row of each query. In the primary approach, the UDF is used to set the value of SortColumn, and that is only done upon INSERT and UPDATE via the Trigger. Selecting values is much more common than inserting and updating, and some value are never updated. Per each SELECT query that uses the SortColumn for a range filter in the WHERE clause, the UDF is only needed one time per each of the range_start and range_end values to get the normalized values; the UDF is not called per-row.

With regards to the UDT, the usage is actually the same as with the scalar UDF. Meaning, inserting and updating would call the normalization method once per each row to set the value. Then, the normalization method would be called once per query per each range_start and range_value in a range filter, but not per row.

A point in favor of handling the normalization entirely in a SQLCLR UDF is that given it is not doing any data access and is deterministic, if it is marked as IsDeterministic = true, then it can participate in parallel plans (which might help the INSERT and UPDATE operations) whereas a T-SQL UDF will prevent a parallel plan from being used.


Passing in multiple instances into the dbatools command GET-DBADBFILE

Yes, that is expected because you are passing in a DataRow type. Instead you could pass it as a string type $sqlinstance.sqlinstance (the column name) as a parameter for Get-DbaDbFile to get the results.
Get-DbaDbFile -SqlInstance $sqlinstance.sqlinstance #...

For example...


USE yourDb;

CREATE TABLE temp_sp_who2
    (
      SPID INT,
      Status VARCHAR(1000) NULL,
      Login SYSNAME NULL,
      HostName SYSNAME NULL,
      BlkBy SYSNAME NULL,
      DBName SYSNAME NULL,
      Command VARCHAR(1000) NULL,
      CPUTime INT NULL,
      DiskIO BIGINT NULL, -- int
      LastBatch VARCHAR(1000) NULL,
      ProgramName VARCHAR(1000) NULL,
      SPID2 INT
      , RequestId INT NULL --comment out for SQL 2000 databases

    )

INSERT  INTO temp_sp_who2
EXEC sp_who2

SELECT  *
FROM    temp_sp_who2
WHERE   DBName = 'yourDb'

Link
Just tested it on SQL server 2012


Havin can only use colmuns that are in the GROUP By or SELECT. But START_TS as columns doesn't exist, so you must use an ALIAS  to get your dates.
Schema (MySQL v5.7)
CREATE TABLE tableName (
  `START_TS` date,
  `resource_key` int,
  `INTERACTION_RESOURCE_ID` varchar(4)
);

INSERT INTO tableName
  (`START_TS`, `resource_key`, `INTERACTION_RESOURCE_ID`)
VALUES
  ('2020-08-27' , 772122, 'test'),
    ('2020-08-27' , 772122, 'test')
;

Query #1
Select date(START_TS) date_ts, MIN(resource_key), count(INTERACTION_RESOURCE_ID)
From tableName
Group By date(START_TS), resource_key
Having date_ts >= '2020-08-27' and resource_key in (772122, 772134);

| date_ts    | MIN(resource_key) | count(INTERACTION_RESOURCE_ID) |
| ---------- | ----------------- | ------------------------------ |
| 2020-08-27 | 772122            | 2                              |

View on DB Fiddle


How to get last IP address of DB update request ?

No.

The file access.log contains connections, but that is not accessible via SQL.  (And your hosting service may not let you access that file.)


Does my associated ASM Instance have any play in Re-naming my SID?

This blog post shows you the required steps. I'd think long & hard before doing so, though as recreating control files is not something to betaken lightly. You don't need to do anything with the +ASM instance.


MySQL DB Migration from on-premises to AWS through replication

Here's pretty detailed blog post about how to do this:
How to Migrate to MySQL RDS Without Downtime

Our approach was to configure M/M replication between our MySQL instance hosted on EC2 and the new RDS, then gracefully switch traffic from our application servers one at a time without any downtime for our customers.
To clarify, the Non-RDS server in this article refers to MySQL instance running either in your data center or on an Amazon EC2 instance


SQL Server Backups-Order of FULL & T-log backups

Can anyone think of a good reason for taking a FULL and then a t-log
  directly after? I tried asking the employees that have been here but
  they are clueless.

The reason I can think of is to 'truncate' the Transaction Log since the last full backup happened. If you would not do these log backups, the log would keep on growing.

If truncating the transaction log is the main reason then you should just set your database to the SIMPLE recovery model instead. This means that you will lose data up until the last full/diff backup.

But it would be no different with the current method of taking backups.

Unless some restore procedure is using STOPAT to restore to a point in time after the log backup has happened. (this still means data loss up untill the last backup, possibly +10h)

Another reason could be that the database is part of an AlwaysOn AG, but this should give you even more incentive for more frequent log backups.

The main issue

The main thing is, the log backups cost the same, wether you run them every minute,every 15 minutes or every 10 hours. Take a look at this blog post by Brent Ozar as to why it should not matter how often we schedule this (without getting too crazy).

Running them once before every full backup is not what they where designed to do, you should schedule them more often.

Something I prefer is this backup solution by Ola Hallengren, instead of maintenance plans.

Long story short, I would schedule log backups more often, the default people tend to use is every 15 minutes, but it depends on your RPO.

  But are there any unobvious implications by taking the Full and then
  the log directly afterwards?

There should not be any issues with doing this, other than the remarks mentioned above.


SQL Server Transactional Replication over Internet

You have probably read that you are able to deliver the initial snapshot via FTP.  This is covered in Deliver a Snapshot through FTP.

It is possible to replicate two SQL Servers across a VPN.  This is covered in Publish Data over the Internet Using VPN.

Once the VPN is setup, you should be able to connect and authenticate as though the servers are on a LAN or WAN.

If you have anymore questions please let me know.  I hope this helps.


should we replace some non-clustered indexes with clustered indexes

Your problem will come in when you insert data into the ReportDataCosts table, as it will have to perform splits to insert the data, with a clustered index on the FK.

If you're so hell-bent on a clustered index on the FK, why not widen the PK to include the FK and make that clustered?


Trying to load a file into a database on virtual machine

COPY doesn't understand the shell-script shorthand ~ for "my home directory". You must specify the path explicitly, e.g.

COPY nh FROM '/home/MYUSERNAME/Dropbox/0ptimus-Jaspin/VoterMapping--NH--03-17-2014-HEADERS.tab';

at which point you'll probably get a "permission denied" error, because COPY runs as the postgresql server user, which can't normally read files from user homedirs. If you're on RHEL/CentOS/Fedora then setting permissions won't be enough, you'll need to change SELinux rights too.

Instead, I suggest using \copy from psql.

cd ~
psql
\copy nh FROM 'Dropbox/0ptimus-Jaspin/VoterMapping--NH--03-17-2014-HEADERS.tab';


Prevent drop column with index on it in PostgreSQL

There is no way to do that except writing an event trigger that throws an exception whenever certain users try to run schema modifications.

You should make sure that any database user that connects and doesn't know better than randomly dropping columns without understanding the consequences does not own any database object and doesn't have the CREATE privilege on any schema.


Select rows with line breaks: Unexpected result

DUMP returns a VARCHAR2.

select f1, dump(f1) from test1;

F1            DUMP(F1)
------------- ----------------------------
Note          Typ=1 Len=4: 78,111,116,101
Nota          Typ=1 Len=4: 78,111,116,97

2 rows selected.

So searching for '%,10%' will only return 'Note' because it's dump representation contains 101, where as 'Nota' does not.

If you insert those values with a return character/line break, the result would be:

select  f1, dump(f1)
from test1 where
dump(f1) like '%,10%'
and  Not (dump(f1) like '%100%');

F1            DUMP(F1)
------------  -----------------------------------------------
Note          Typ=1 Len=4: 78,111,116,101
Note          Typ=1 Len=5: 78,111,116,101,10
Nota          Typ=1 Len=5: 78,111,116,97,10

You get 3 rows returned. The 2 rows ending with ',10' contain the the line breaks.


Database design for logging varied types of data

I've thought of a way to do it which isn't ideal but it should work.

Rather than naming the fields in relation to what they store (in terms of the log data) you could simply name them LOG_ARGUEMENT_1, LOG_ARGUEMENT_2, LOG_ARGUEMENT_N ...
And have a permanent field for ACCOUNT_ID which is a foreign key to an ACCOUNT table containing information about each user (such as if their an admin or not and when they last completed and action) Also have an EVENT / EVENT_SUB_TYPE table as you suggested linked to the main LOG table. Finally you could also have a field with the full log on a separate table linked to the log table for error checking.


How should I update ID field values for all records based on previous values?

A couple options come to mind.  If you have a charish data type:

update table set column=concat('999',column);

If you have an integer type find the max value, let's say it's under 10,000 then

update table set column=9990000+column


Is there a way to specify an table/index is sorted by more than one column?

You can create a clustered index on both id and timestamp columns and even define different sorting order for each column if you want to:
create unique clustered index idx on TestTable(id asc, [timestamp] asc)
But it won't make much sense in this case for these reasons:

The engine does not know that timestamp increases along with id, so if you run a query with condition on timestamp, it'll still perform a full scan.
All additional columns that are included in clustered index will also be included in all non-clustered indexes.
An older timestamp still could be inserted into the table with newest id.

So you are gaining nothing and only wasting space when you create a new index and slowing down inserts as timestamp order still will have to be maintained.

Creating an index only on timestamp column seems like a waste of resources (2 out of three columns of entire table will be included in an index), but actually it's very common for size of all indexes be greater than the size of the table. Slow queries are also wasting a lot of resources, so just create it if it'll boost select queries and if slower inserts will not be a problem.

To finally answer your question: as you can't implement this logic using indexes and constraints, the only way I see is to create an INSTEAD OF INSERT Trigger. It'll hurt insert performance, but prior to insert you can check if older timestamp already exists in the table and then modify the timestamp, not insert the data, raise an error - up to you.


First Normal Form, why is it good and how does it reduce redundancy

The primary importance of first normal form is not that it eliminates redundancy, but rather, it's that it eliminates repeating groups.

Instead of having multiple columns of the same kind of data in a record, (0NF) you remove the repeated information into a separate relation and represent them as rows.  This is what constitutes 1NF.

Tables that have columns like: phone_1, phone_2, phone_3 or that contain list-oriented data like: 212-555-1212, 212-555-1234, 416-967-1111 violate 1NF.

1NF is important because it is much more flexible than 0NF while being much easier to use when inserting, updating and reading data.  This is because every type of data element (e.g. customer phone number) has exactly one column in which to find it and that column has only one piece of data for each record.  This means that you can use simple SQL statements to read or write individual data elements without having to parse delimited strings or use constructions like: where phone_1=@Number or phone_2=@Number or phone_3=@Number and so forth.

Regarding 1NF vs 3NF, the normal forms are cumulative.  A table in 3NF is also in 1NF, so it is just as true to say that "Every table in a relational database should be in 1NF" as it is to say that "Every table in a relational database should be in 3NF."  I would say both of these are true, but I would add "unless you have a really good, well considered reason to denormalize".


How does SQL Server store the information in Job Schedule Properties?

Every 20 minutes (this seems to NOT be configurable), the 'next run' information in sysjobschedules is updated by looking at the information you can see in sysschedules.

You know how when you define a job you have to go through a slightly convoluted process of rather than just saying 'run this job every monday', instead defining a schedule that says that, and associating the job with the schedule? This is why - they have independent existences.


Identical Servers but different query execution plans

To me it looks like that the statistics are way off on the server with the bad plan.
For instance, Audit_New.dbo.RevOrder shows an estimated rows of 40k vs. actual rows = 8k.
I'd recommend to rebuild/reorganize the indexes involved in the query on the poorly performing server.


This isn't a complete answer, as I admit I don't know the complete answer, but can't fit into a comment.

Short detour - regarding SQLOS memory there is a wealth of information coming from a guy that worked on the internals of SQL Server storage team - Slava Oks. He has lots of articles on MSDN blogs regarding the SQLOS memory manager and they are all worth a read. Now I know we're on to SQL Server 2012, but the info is valuable nonetheless.

Now, regarding your first question, if there's a limit of max memory taken by db page cache before being cleared out and how is it controlled, I'll quote his word on it.

From article SQLOS's memory manager and SQL Server's Buffer Pool:

  Buffer Pool commits pages on demand. Depending on internal memory requirements and external memory state, it calculates its target, amount of memory it thinks it should commit before it can get into memory pressure. To keep system out of paging target is constantly recalculated. Target memory can't exceed max memory that represents max server memory settings. Even if you set min server memory equal to max server memory Buffer Pool will only commit its memory on demand. You can observe this behavior by monitoring corresponding profiler event.

From this article's comment I understand that SQL Server uses a LRU algorithm (LRU = least recently used). If you really need to go in depth, SQL Server's LRU algorithm in particular is described in the following research paper: The LRU-K Page Replacement Algorithm For Database Disk Buffering.

Now, for your second question, the log caches are not really exposed so they can't be changed/tuned, as they are a part of the SQLOS internal memory structure. Quoting from an answer from MSDN forum:

  Essentially each database has a log buffer/cache for its transaction
  log which is a small contiguous memory allocation, that is 60K in size
  and used by active transactions, and flushed when a transaction
  commits, or when the buffer space fills up.  Multiple active
  transactions can be interleaved in the log buffer, and if any one
  transaction commits, the buffer memory is written to the transaction
  log file by the log writer.  If the space fills up, the contents get
  flushed to disk allowing the buffer/cache memory to be reused.  This
  is why log IO can range for 512bytes to 64K (its actually 60K), but it
  is always sequential so varying write sizes doesn't matter that much.
  The format of the log records is different than data pages as
  documented in the BOL
  (http://msdn.microsoft.com/en-us/library/ms190925.aspx), so the size
  of a log record is variable, but the size of the log buffer/cache is
  consistent.


This is a bug in SQL Server (from 2008 to 2014 inclusive).

My bug report is here.

The filtering condition is pushed down into the scan operator as a residual predicate, but the memory granted for the sort is erroneously calculated based on the pre-filter cardinality estimate.

To illustrate the issue, we can use (undocumented and unsupported) trace flag 9130 to prevent the Filter from being pushed down into the scan operator. The memory granted to the sort is now correctly based on the estimated cardinality of the Filter output, not the scan:

SELECT
    T.TID,
    T.FilterMe,
    T.SortMe,
    T.Unused
FROM dbo.Test AS T
WHERE
    T.FilterMe = 567
ORDER BY
    T.SortMe
OPTION (QUERYTRACEON 9130); -- Not for production systems!

For a production system, steps will need to be taken to avoid the problematic plan shape (a filter pushed into a scan with a sort on another column). One way to do this is to provide an index on the filter condition and/or to provide the required sort order.

-- Index on the filter condition only
CREATE NONCLUSTERED INDEX IX_dbo_Test_FilterMe
ON dbo.Test (FilterMe);

With this index in place, the desired memory grant for the sort is only 928KB:

Going further, the following index can avoid the sort completely (zero memory grant):

-- Provides filtering and sort order
-- nvarchar(max) column deliberately not INCLUDEd
CREATE NONCLUSTERED INDEX IX_dbo_Test_FilterMe_SortMe
ON dbo.Test (FilterMe, SortMe);

Tested and bug confirmed on the following builds of SQL Server x64 Developer Edition:

2014   : 12.00.2430 (RTM CU4)
2012   : 11.00.5556 (SP2 CU3)
2008R2 : 10.50.6000 (SP3)
2008   : 10.00.6000 (SP4)

This was fixed in SQL Server 2016 Service Pack 1. The release notes include the following:

  VSTS bug number 8024987
  Table scans and index scans with push down predicate tend to overestimate memory grant for the parent operator.

Tested and confirmed fixed on:

Microsoft SQL Server 2016 (SP1) - 13.0.4001.0 (X64) Developer Edition
Microsoft SQL Server 2014 (SP2-CU3) 12.0.5538.0 (X64) Developer Edition

Both CE models.


How to avoid hardcoding a database link name in a package body

Changing each call of a component of the DBMS_HS_PASSTHROUGH to a dynamic sql statement (EXECUTE IMMEDIATE) is a possibility but I would prefer the following approach.

For tables and views of the remote database one creates views on the local database that wraps this object.
For packages, functions and procedures one creates  packages, functions and procedures on the local database that wrap these object.

You already implemented this approach by creating the views for the remote tables.
Conceptually the dbms_hs_passthrough package is a package on the remote database. Therefore its component are called with the database link appended and not with the database link supplied as string paramter.
So you should write a wrapper package for the dbms_hs_passthrough package. The definition of the dbms_hs_passthrough-components can be found in the Database PL/SQL Packages and Types Reference or you can get it from the database dictionary (e.g. by issuing a describe command in sqlplus). If you change the database link name you only have to change your views and the small body of the wrapper package.


You are returning 1 million rows.  Returning 1 million rows is going to take some time.  Especially when it has to do a pattern matching recheck of each row.  How fast do you expect this to be?

Your best bet is fixing things on the application side.  Why do you need a million rows?  What are doing with them?  And if you do need to return that many, why do you need to do it so often that it matters if it takes an extra second to do?  But beyond that:

Get a faster computer.
Upgrade to a recent version of PostgreSQL and see if you can get parallel execution (that would depend on what percentage of your table is being returned by this query)
Get rid of the "lower" function and use "ilike" rather than "like".  Indexes based on gin_trgm_ops support "ilike" queries naturally with no special steps needed.
Use an ordinary btree index on reverse(lower(email)) so that the wild card is at the end rather than the beginning of the query string.  Better yet, store the email in that form to start with, so expression indexes are not needed.


SQL Syntax Error - Unsure if it is empty value or backslash

If you want to omit a column you can either apply a column list:

INSERT INTO mw_reward_point_order (col_1,col_2col_3,col_4,col_6)
VALUES(9477,0,0,144,'100/1')

or specify NULL:

INSERT INTO mw_reward_point_order VALUES(9477,0,0,144,NULL,'100/1')


Can I add a unique constraint that ignores existing violations?

The answer is "yes".  You can do this with a filtered index (see here for documentation).

For instance, you can do:

create unique index t_col on t(col) where id > 1000;

This creates a unique index, only on new rows, rather than on the old rows.  This particular formulation would allow duplicates with existing values.

If you have just a handful of duplicates, you could do something like:

create unique index t_col on t(col) where id not in (<list of ids for duplicate values here>);


Selecting latest row for user grouped by day help

One way (not necessarily the most efficient) is to use the Postgres special DISTINCT ON modifier in a subquery, and then sum:

SELECT
    SUM(score) AS score,
    COUNT(*) AS count,
    created_at_date
FROM
  ( SELECT DISTINCT ON (user_id, created_at::date)
        created_at::date AS created_at_date,
        (DATA ->> 'score')::integer AS score
    FROM
        scores
    ORDER BY
        user_id, created_at::date, created_at DESC
  ) AS g
GROUP BY
    created_at_date ;

Tested at SQLfiddle.


ALTER TABLE with missing FROM-clause entry for table "inventory"

The problem is in the SET DEFAULT (inventory.subtitle_subtitle_id_seq). Postgres interprets this as "the column subtitle_subtitle_id_seq from the table inventory" which is clearly not your intention.

First of all, you can't get a value from a sequence with just the bare sequence name like that. Generally, you would use nextval('sequence_name').

You probably want something like:

ALTER TABLE inventory.subtitles
    ALTER COLUMN id SET DEFAULT nextval('inventory.subtitle_subtitle_id_seq');


Unique constraint on 3 different columns but a specific value in 3rd column allows duplicate row entries

This did not work because auto_increment columns cannot be referenced by a generated column, but I will add it anyhow since it demonstrates a technique that can be useful. The idea is to use a generated column, that when user_status = 0 maps to something guaranteed unique (primary key) and otherwise maps to a constant. Then this column can be included in a UNIQUE constraint together with the columns that should be unique under condition:

CREATE TABLE users
( user_id BIGINT PRIMARY KEY  -- auto_increment had to be removed
, user_email VARCHAR(255) NOT NULL
, user_companyid BIGINT NOT NULL
, user_status enum('1', '0')
, gencol BIGINT GENERATED ALWAYS as
    ( CASE WHEN user_status = 1
           THEN -1
           ELSE user_id
      END
    ) NOT NULL
);

ALTER TABLE users ADD CONSTRAINT ak1
    UNIQUE (user_email, user_companyid, gencol);

INSERT INTO users(user_id, user_email, user_companyid, user_status)
  VALUES (1,'test1@gmail.com','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status)
  VALUES (2,'test2@gmail.com','555','1');
INSERT INTO users(user_id, user_email, user_companyid, user_status)
  VALUES (3,'test1@gmail.com','777','1');

-- Fails
-- INSERT INTO users(user_id, user_email, user_companyid, user_status)
-- VALUES (4,'test1@gmail.com','555','1');

UPDATE users SET user_status = '0' WHERE user_id = 1;

-- Succeeds
INSERT INTO users(user_id, user_email, user_companyid, user_status)
VALUES (4,'test1@gmail.com','555','1');


SSIS/SSAS 2016 intermittent fault: An error was encountered in the transport layer

I think we solved this or at least found a workaround.

I believe the problem is a timeout when connecting to SSAS when it has been inactive for some time. It has nothing to do with the actual processing of the cube.

We added a step to our ETL process which queried the OLAP cube before the processing step was triggered. This causes SSAS to 'wake up' before it is needed.

Since we added this step we haven't had a repeat of this problem.


Database consistency checker in postgresql

There is no built-in consistency check command or tool in PostgreSQL.

The general view is that one should not be necessary, as corruption and inconsistency should not be possible on a quality hardware/software stack. If problems do arise, there's no guarantee any kind of consistency check would find them, so it'd just create a false sense of security. I don't agree with that sentiment, but it's what seems to come out when this is periodically discussed on pgsql-hackers.

As usual, the underlying problem is that nobody particularly needs a consistency checker tool to meet their immediate needs, so nobody's spending the time to write one to scratch an itch and nobody's funding the development of one on a commercial contract or in-house basis. Volunteering? :p

PostgreSQL (until 9.3) didn't support block level checksums. So one of the main things that you're used to verifying did not exist and could thus not be verified. A tool to scan all relations and validate checksums does not exist in PostgreSQL 9.3, but would be desirable to add and may appear in a future version.  In the mean time all you can do is SELECT * from each relation individually - but due to the fact that PostgreSQL uses operating system buffer cache for reads there's no gurantee that'll actually force a read of the underlying disk block anyway. A new tool would be needed to do this.

PostgreSQL tends to avoid redundantly storing information where possible, so there's often nothing to check against, just a single authorative source. A consistency checker can't do much unless the same information appears, or can be derived from, multiple different places.

It's also very hard to do any kind of useful checking concurrently, on a database that's still busy and active. Most installations aren't going to be willing to lock the entire database, or at least several major relations at a time, to run some kind of consistency check. So the checker would need to be able to operate on a database that's subject to concurrent modifications, making it even harder to write and able to detect fewer problems reliably.

There's still plenty that a validator tool could do if one were written, especially if it was allowed to take multiple relation exclusive locks:

Check that all tablespaces exist on disk.
Check that each pg_class entry has file(s) corresponding to its relfilenode in the correct tablespace.
Inspect visibility maps, free-space maps, etc, ensuring they're present when they should be, readable, and appear to match the relation they're associated with.
Report orphan on-disk file nodes. (These are normal due to transactional DDL and lazy unlinking, but a checker could force eager unlinking and lock all relations before running the check).
Read every block of each relation and look for obvious problems. For heap relations that'd be things like:

an xmin greater than xmax (after considering xid wrap-around)
Tuples created by transactions in the future
broken HOT chains / broken ctid chains
tuple structures that fail to match the table attributes
any Datum that doesn't round-trip its _in and _out functions unchanged or throws an error
NULL bitmap fields set on NOT NULL table attributes
Re-execution of CHECK constraints fails

Re-check foreign key and exclusion constraints after locking all tables involved

... and probably lots more I don't know enough about Pg's guts to figure out, such as attempts to detect torn pages, b-tree structure validation, sanity checking GIN and GiST indexes, sanity checking pg_control, and more I wouldn't know where to even start with.

If you're keen to have such a tool, the best thing to do is learn enough to come up with a concrete proposal about how it should work - and make the time to work on it, or to fund others to spend time on its development.

Personally I'd be really quite happy to have something that could check a stopped database cluster using a special startup mode for the postgres backend, so I could (somewhat) validate physical database copies taken with pg_basebackup, with pg_start_backup(), rsync and pg_stop_backup, with file-system level atomic snapshots, etc.

Alternately, you could do what most others do: Ensure your hardware and software stack is robust and properly configured, keep good backups, and monitor your logs. There's no substitute for proper testing of the whole stack before commissioning a server - and for good backups, both physical (streaming / PITR) and logical (dumps). Do plug-pull testing on a loaded database - repeatedly - before going live to make sure your supposedly reliable I/O subsystem really is. Use multiple forms of backup.


DBATools has a convenient command Copy-DbaAgentProxy in its PoSh module that I highly recommend.

By default, all proxy accounts are copied. The -ProxyAccounts parameter is auto-populated for command-line completion and can be used to copy only specific proxy accounts.
If the associated credential for the account does not exist on the destination, it will be skipped. If the proxy account already exists on the destination, it will be skipped unless -Force is used.

Or, to generate the files, you can also use Get-DbaAgentProxy and Export-DbaScript.


What happens during RCSI while tempdb is being populated?

Of course that means when a row/page/table lock occurs, the current value of those rows has to be copied to tempdb.

It has nothing to do with locks. The previous version of a modified row is copied to the version store in tempdb just before the actual modification is made. Physical integrity is protected by a latch on the page.

The reading query does not have to wait (in the way you suggest), because the version it needs is guaranteed to be in the version store before the data change is made. Since modifications are made a row at a time, the question of waiting for the whole table to be copied to tempdb does not arise.

It may have to wait to acquire a latch on the page containing the row, while the data modification is being made, but this is not specific to using a versioning isolation level.

In a pathological case, where the update and select process the same pages in the same order, at an equal rate, the select would have to wait (on a latch) for each page while the update changed those rows and copied to the version store, but this is exceedingly unlikely to happen in practice. As soon as the update yields even briefly, the select can easily get ahead, and so no longer have to wait.


Create folder in Sql Server management Studio 2008

SQL Server/SSMS does not have this functionality. However, you can use SQL Server Data Tools (ideally with source control integration) to organize database objects to your liking independently of the physical database deployment(s).


Is it acceptable to use the presence of a value in a NULLable column as a flag?

NULL is not a value of the column but an internal metadata flag showing the column has no value.  So duplicating this in user space by adding another flag column is a waste. NULL is a well-supported part of the relational model. Use it for its intended purpose.


From my experience, simpler characters are more commonly used, e.g. 1-1 to mean one-to-one, and 1-* for one-to-many, and *-* for many-to-many.
You can also use the infinity symbol (U+221E) for the "many" symbol if you really wanted but I prefer the previous simpler examples.
By the way, that website is helpfully searchable and you can find other similar arrow characters to what you're looking for as well such as the following:

Rightwards Two Headed Arrow (U+21A0)
Rightwards Double Arrow (U+21D2)
Rightwards Triple Arrow (U+21DB)
Rightwards Arrow with Double Vertical Stroke (U+21FB)
Rightwards Two-Headed Arrow with Double Vertical Stroke (U+2901)
Rightwards Double Arrow-Tail (U+291C)


Galera - why can't I use port 4567 for replication?

4567 is the default Galera Replication Port for replication between nodes within a Galera cluster. That is not what you want to use here - you are doing primary-secondary replication (master-slave).
3306 is the default TCP/IP port you want to use for primary-secondary (master-slave) replication.


Lock All Column Changes, Except One Column On Trigger

The best thing is to use permissions. If datamoduser is the database user that performs the data modifications, make sure the user has no permissions except
GRANT UPDATE (point) ON mytable TO datamoduser;

Then all UPDATE statements except the ones that update point will fail.


Simple way to list order of referential integrity in Postgres

You can "dive" into the system tables and get all the tables that reference a specific table (and all those that reference them, etc). It will be a recursive query, not very difficult to write.

However, for the simple operation you want to to perform, there is a simple solution:
TRUNCATE with the magic option CASCADE:

truncate table a cascade ;

Tested at rextester.com


Preventative measures to avoid wrong cardinality estimate statistics

The first line of defense is a nightly statistics gathering process.  Ola Hallengren's rather ubiquitous set of maintenance scripts, for example, have a number of options for index and statistics maintenance.  That should keep your statistics from getting too out of date.
The next line of defense are the database settings to enable the automatic updating of statistics, to enable statistics to be gathered asynchronously, and to enable the automatic creation of statistics
alter database [your database]
  set auto_create_statistics on;
alter database [your database]
  set auto_update_statistics on;
alter database [your database]
  set auto_update_statistics_async on;

If auto_update_statistics is enabled, SQL Server will re-gather statistics that it deems stale (the threshold differs by version) when it encounters them.  If you allow that to be done asynchronously, the query that first encounters the stale statistics will use the old stats while a background thread updates the stats for a future execution.  In any sort of OLTP system, I'd much prefer that to the synchronous approach where the query that encounters stale stats waits to execute until fresh stats are available.  In a data warehouse system, I'd be a lot happier for the query to wait for fresh stats.
There are downsides to allowing auto stats updates, though.  When you refresh stats, you're going to force SQL Server to purge all the plans that use those stats from the cache and to recompile them which can be expensive.  You're also introducing additional background stats gathering processes that will consume CPU during the day.
A third line of defense involves a DBA or developer being thoughtful about updating statistics as part of a load or based on information they know about the system in question.  If you're writing a warehouse load, for example, it probably makes sense to update statistics as a final step when you know that you've made a large number of changes to a table (maybe you do this once a week rather than every day depending on the windows that you have).  If you're a DBA running an issue-tracking system and you know a bunch of queries are highly dependent on a particular statistic on a filtered index for all the open issues and you know that there are generally very few open issues overnight when statistics run but the number grows steadily to a peak around 2pm, and then slowly declines, you might schedule a job to update that particular filtered index statistic hourly.


What should I consider when deciding between passing a comma-delimited string to a stored procedure instead of calling it individually per record?

Personally I would choose to pass a list of id's in as a table parameter to the stored procedure this would then allow you to do a set-based update instead of a row by row one which is less efficient.

I have never personally used the EF but a good artical on performing the above using ADO is below (ignore the fact it says it is for SQL 2008 as it also works on 2005). The same strategy would work better for you in this situation but you may need to adapt the implementation based on the fact you are using the Entity Framework.

http://www.mssqltips.com/sqlservertip/2112/table-value-parameters-in-sql-server-2008-and-net-c/

EDIT

As you rightly pointed out I am wrong about the fact this works on 2005 - sorry about that!

However, I have some alternate suggestions.

As SQL Server 2005 does support table variables (just not as parameters to stored procedures as you pointed out) you could parse the delimited string and insert the id's into a table variable. You could then use the table variable to perform a set-based update.

Alternatively the link below provides a different take on the same problem by persisting the values to a table first thereby avoiding serialization and de-serialization of the id values:

http://weblogs.sqlteam.com/jeffs/archive/2007/06/26/passing-an-array-or-table-parameter-to-a-stored-procedure.aspx

I hope this helps you.


If you don't back up the tail of the log you lose any transactions that ocurred since you last backed up the database.


The syntax is valid SQL. I'm formatting a bit differently and adding parentheses to show how it is / should be parsed:

SELECT UserRole.Name
FROM
    Opportunity
  LEFT JOIN
    (   Account
      JOIN
        (   User
          JOIN
            UserRole
          ON
            User.UserRoleId = UserRole.Id
        )
      ON
        Account.UserId = User.Id
    )
  ON
    Opportunity.AccountId = Account.Id

It seems that the version of MySQL you use does not parse this correctly when the internal joins are INNER ones. If they were all LEFT joins, it wouldn't have a problem.

There are 2 solutions:

A) Use the above, with the parentheses.

B) Rewrite, changing the order of the joins, so the they not nested. You'll have to replace the LEFT to a RIGHT join but you don't need parentheses:

SELECT UserRole.Name
FROM
            User
          JOIN
            UserRole
          ON
            User.UserRoleId = UserRole.Id
      JOIN
        Account
      ON
        Account.UserId = User.Id
  RIGHT JOIN
    Opportunity
  ON
    Opportunity.AccountId = Account.Id


large insert script (generated from tasks) - errors when running from sqlcmd

I don't have a 5gb file to test against, so I have no idea if this proposed solution will work for you.  Your data may not lend itself to this proposed solution, but I thought I'd offer it anyway.

I created a table called SqlCommands

USE [Test]
GO
CREATE TABLE [dbo].[SqlCommands](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [TextData] [varchar](max) NOT NULL,
 CONSTRAINT [PK_SqlCommands] PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

For testing purposes, I used the SSMS scripting wizard to script one of my small tables (data) as 'inserts' into a test file.

I then used a Powershell script to read that test file (which are basically SQL insert commands into the above SqlCommands table.  The table has an identity column and the Powershell script only references the 'TextData'  column.  The ID identity column will allow me to select from this table in the correct order.  Here is the Powershell script:

$database = "test"
$server = "."
$table = "dbo.SqlCommands"

$ff="C:\Test\TestData.txt"
write-host "Input file used is $ff"
$inrec=""
$counter=0

if ([System.IO.File]::Exists($ff))
{
  $obj=new-object System.IO.StreamReader($ff)

  $inrec=$obj.ReadLine()
  while ($inrec -ne $null)
  {

     #replace single quotes with double quotes so the insert will work for literals
     $inrec = $inrec -Replace("'","''")

     Invoke-SqlCmd -Database $database -ServerInstance $server -Query "insert into $table (TextData) values('$inrec')"
     $counter++

     $inrec=$obj.ReadLine()
  }
  $obj.close()

  write-host "$counter lines read"
}
else
{
   write-host "Source file ($ff) does not exist."
}

write-host "All done now!"

Now, I have my SQL file in a table.  I wrote a cursor to select from the table ordering by ID to make sure I'm executing the commands in the correct order.  The cursor script reads each row from the SqlCommands table and checks to see if it is a 'GO' statement ('GO' statements were automatically added by the scripting wizard when I scripted the table data).  If the row is NOT a 'GO', the current row is concatenated to @cmd and the next row is read.  If the current row is a 'GO', I execute the accumulated @cmd and blank @cmd out for the next series of statements.  The EXEC is wrapped inside a TRY/CATCH.  In the event of an error, I'm able to display the row number (Identity ID column) so I can run selects against the SqlCommands table 'around' that row number to find any problems.

SET NOCOUNT ON

DECLARE @cmd VARCHAR(max) = ' '
DECLARE @Id INT
DECLARE @textdata VARCHAR(max)

DECLARE _CURSOR CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT ID
    ,TextData
FROM [Test].[dbo].[SqlCommands]
ORDER BY ID

DECLARE @count INT

SET @count = 0

OPEN _CURSOR

FETCH NEXT
FROM _CURSOR
INTO @Id
    ,@textdata

WHILE @@FETCH_STATUS = 0
BEGIN
    IF @textdata = 'GO'
    BEGIN
        BEGIN TRY
            --print @cmd
            EXEC (@cmd)
        END TRY

        BEGIN CATCH
            PRINT 'Error occurred at line ' + convert(VARCHAR(100), @Id)
            PRINT error_message()
        END CATCH

        SET @cmd = ' '
    END
    ELSE
    BEGIN
        SET @cmd += @textdata
    END

    FETCH NEXT
    FROM _CURSOR
    INTO @Id
        ,@textdata
END --End While

CLOSE _CURSOR

DEALLOCATE _CURSOR

PRINT @count

If any errors exist, I can use the row number to try and find the error

select * from [Test].[dbo].[SqlCommands] where id between 25 and 30


Can creating indexes colide with future database upgrades?

What I would do is maintain a SQL script for the indexes that you've added to the "virgin" database. Store this script in a version control system, as you'll be adding indexes to fix performance problems.

I would employ a naming strategy to make it easy to identify which indexes are "yours" and not part of the database as delivered. I'm not sure what features MySQL has, but in Oracle for example you can store these indexes in a different tablespace making it very easy to identify them as "added".

When your vendor supplies an upgrade, first remove your added indexes, then apply your vendor's upgrade and afterwards execute your script to add your indexes.

Of course, you should also have a QA environment where you can test all of this before applying it to your production system. You'll need to do this to discover if any indexes will conflict with indexes that the vendor may create in a future version.


1. Why is there a difference?

Simply because different code within SSMS generates the script differently. The SSMS GUI is well-known for being inconsistent (and broken in places). It's quite likely the features were just implemented by different developers or teams.
A more definitive answer would involve call to Microsoft Support.

2. Does it matter?

No, it doesn't matter.
SSMS added square brackets around the column and table names, so the script will work in cases where users named tables or columns using, for example, reserved keywords or spaces.
The result is semantically identical albeit inconsistent. SQL Server Books Online states the default float precision is 53:
float and real (Transact-SQL)
From that link:

float [ ( n ) ]
Where n is the number of bits that are used to store the mantissa of the float number in scientific notation and, therefore, dictates the precision and storage size. If n is specified, it must be a value between 1 and 53. The default value of n is 53.


How to choose suitable naming convention for Dimensional Table Column in a Data Mart?

I just completed a 4 day course with the Kimball group and they had this to say on naming conventions:

Always use full descriptive names ie. Brand Name. and don't use abbreviations. Otherwise you may need to spend time training users.
Don't use Dim or Fact in your table names because you end up spending a lot of time deleting these from the reporting layer.
Don't use camel case or underscores, use spaces. Yeah I thought this was bizarre, but again they said you'll spend a lot of time adding in space at the reporting layer and most databases now handle spaces in column names without a problem.

If you subscribe to the Kimball methodology, then this is their approach. But like the other commenter said, it's all about comprehension. So if it makes sense for the business users, then you're on the right track.


Access query to select multiple records with matching field

Select [FirstName], [Surname], [Age]
From People
Where DCount("Surname", "People", "Surname='" & SurName & "'") > 1

OR

Select P.*
From (Select Surname From People Group by Surname Having Count(*) > 1) AS G
JOIN People AS P ON P.Surname = G.Surname


mariadb 10.3.13 table_open_cache problems

10.3.3 was a beta release. As the is a new upgrade, use the latest GA 10.3 release. Lots of these sort of bugs pop up in earlier versions.

ref: https://mariadb.com/kb/en/library/release-notes-mariadb-103-series/


SSAS : An error occurred when instantiating a metadata object

The solution was removing the empty files E:\MSAS11.DWH_AS\OLAP\Data\Cube Fraude.0.db\MY_DATABASE OLAP.56.cub.xml which allowed the aggregation to continue


varchar(MAX) text cuts off while going more than 8000 characters inside a procedure

SSMS cannot display a varchar greater than 8000 characters by default.

However, it can store a varchar(max) value up to 2GB.

You could set up a loop and display "chunks" of the @str data, using an 8,000 character chunk size.

This answer will show you how confirm the actual length of the @str value in memory.

And this MSDN article discusses execute. In particular:

  In earlier versions of SQL Server, character strings are limited to 8,000 bytes. This requires concatenating large strings for dynamic execution. In SQL Server 2005, the varchar(max) and nvarchar(max) data types can be specified that allow for character strings to be up to 2 gigabytes of data.

This implies that the error you are receiving is something else, not a result of a truncate operation on your @str value.


Postgresql table with one integer column, sorted index, with duplicate primary key

I think you're asking how to impliment a solution you'e already decided on for a more general problem you don't describe. If you were to outline the actual problem that this is supposed to solve you might get better suggestions about how to solve it.

Working within the very limited information provided:

Update: I found your other question, which you really should've linked to. You seem to be trying to roll your own message queue. Don't do that. Read these:

https://stackoverflow.com/q/11914804/398670
https://stackoverflow.com/q/13005410/398670
https://stackoverflow.com/q/14515543/398670
https://stackoverflow.com/q/15937377/398670
https://stackoverflow.com/q/15736640/398670
https://stackoverflow.com/q/11761281/398670

Have I convinced you that you shouldn't try to do this yourself yet? Look into:

RabbitMQ
ZeroMQ
Job::Machine
ActiveMQ
PGQ
Celery

Some of what you want isn't available in current PostgreSQL versions. For example:

INSERTs should not do any query in that table or any kind of unique index. INSERTs shall just locate the best page for the main file/main btree for this table and just insert the row in between two other rows, ordered by ID.`

That'd require an index-organized table, which PostgreSQL doesn't have yet. The closest you'll get would be a one-column table with a PRIMARY KEY. With regular VACUUM on PostgreSQL 9.2 you'd be able to use index-only scans to access it most of the time.

As for allowing duplicates, you don't really seem to want to permit them at all, you're just saying you want to work around concurrency issues by temporarily permitting them.

You can remove such duplicates during INSERT so the table its self doesn't need to permit them. However, that'll cause issues with:

INSERTs will happen in bulk (about 1000 per transaction) and must not fail, expect for disc full, etc. There must not be any chance for deadlocks.

... assuming that those inserts occur concurrently from multiple transactions. You'll have races between the checks for existence and the insert that can cause insert batches to fail and have to be re-tried.

I suspect that your best bet is to have a one-column table without a PRIMARY KEY. Just create an ordinary b-tree index on it, and leave the table without a PRIMARY KEY. Since it genuinely has no primary key (the only column may have duplicates) this is entirely reasonable.

(BTW, given that SQL is supposedly all about sets, it astounds me how awful it is at "add this entry to the set if not already present").


Setting Up Named SQL Server Instance Network Connection

As Ronaldo mentioned - SQL Server Browser is needed when using dynamic port.  There are numerous reasons that it may not work correctly, though, too - for example if UDP port 1434 is blocked. (More about Browser here).  Because you said that the IP address was "originally something outside of our firewall" - presumably not the home IP 127.0.0.1 - I'm guessing that you've checked to ensure that TCP is enabled via Configuration Manager->Network Configuration->Protocols for instance. If not, check that:

SQL Server will report the IP and port it's using in its log as well.  Here's an example from my test VM:

Then should be able to connect using either servername,port or IP,port.  You might want to test both methods, both on the local VM and remote boxes to isolate issue.  For example:
.
If connecting locally always works, but remotely doesn't, check firewall.
If connecting with IP always works, but instance name never does, check Browser.
If connecting with IP always works, but servername doesn't remotely, check DNS.
Of course there are numerous other possibilities, but this is where I'd start.


Chunks not splitting on MongoDB sharded clusters

Your chosen shard key for a collection defines the granularity of possible chunk splits. Chunks represent a range of values for the shard key. With a single field in your shard key (TxnMonth), that means the smallest possible range is a single value such as TxnMonth: 3.

The chunk size setting determines when MongoDB will try to split chunk ranges in order to help distribute data in a sharded cluster, but does not put an upper bound on the size of the data in a chunk range.

When the maximum chunk size is reached for a given value, that chunk will be marked as an indivisible  jumbo chunk and the balancer will no longer attempt to split or migrate that chunk. New data will continue to be added to that chunk leading to an imbalance in data distribution as you observed in your testing.

  there are 8 distinct values of TxnMonth from 1 to 8

This limits your sharded collection to a maximum of 8 chunks (or the number of unique TxnMonth values in this collection).

For effective sharding you need to choose a more appropriate shard key with higher cardinality.


Do I need to Vacuum DB after massive insert?

Databases format the data for quick future reference and modification, and under ACID conditions.  This involves adding various kinds of headers and dividing the data into pages, and optionally reserving some free space in each page for future use.  But 3 fold seems extravagant if you really have no indexes.  I get each row taking a little over 101 bytes, 81 tuples per 8192-byte page, so quite a bit less than a two fold expansion.
You can 'select ctid from my_table' to see how many tuples are generally packed into each page.  It would be interesting to know if you get less than 81 tuples per page consistently, or if there are inconsistent gaps or regions of lower density.  If the table fillfactor is set to 100 and your load started with an empty table and had no false starts, then you shouldn't have either issue.  If you do, then perhaps a VACUUM FULL will fix it (at the expense of taking a huge amount of time, and scratch space to do the work).  Aside from that, for an insert-only table VACUUM would only be needed to set hint bits, freeze tuples, or set the visibility map; not for space reasons.
If your main goal is to make your data smaller, there are excellent compression tools available.  PostgreSQL is not one of them.


QUESTION #1

  I issue or schedule mysqldump on master servers.

ANSWER TO QUESTION #1

DEPENDS ON STORAGE ENGINE

If your database is all InnoDB, you can launch mysqldump to get a live snapshot of the Master, but you can run the great risk of slowing down transactions. (See my old post MySQL quick database transfer between two servers for caveats)

DEPENDS ON DB TRAFFIC

The slowdown of transaction incurred would be due to maintaining snapshots of data to support repeatable reads.

MORAL OF THIS STORY

It is best not to do it on a live server, even if it is possible

QUESTION #2

  I allow developers to make changes directly connecting to master servers with the GUI tools(workbench)

ANSWER TO QUESTION #2

You can and will break the transaction issued by the mysqldump --single-transaction. See my old post row locking within ACID transaction innodb to see the commands that will disrupt a consistent backup if executed on the master during a mysqldump

MORAL OF HIS STORY

Use a MySQL Replication Slave and issue mysqldumps without disturbing the Master


How to restore a .backup file from a single table on postgres?

I am not sure what you tried so far, please post error message for better understanding,

Try below and post a comment if this works for you or not.

you can use pg_restore to restore a single table :

pg_restore -t my_table -d database_name database.dump

If the dump is not too big, the easiest thing to do is restore the full backup to a temporary database (gzcat backup_file.gz | psql -h host -U user database_name) dump the one table (pg_dump -t my_table), then restore it.


Show the error message, after I accidentally clicked "Yes" on "don't show error again"

You could try shutting down all instances of SSMS and deleting the file SqlStudio.bin, which works for clearing out things like the MRU list for server connections. Just note that this will also wipe out other preferences, so you may want to export certain settings (like if you've customized fonts and colors, startup options, etc).

Also, why is SSMS RTM? You should at the very least apply Service Pack 2, if not use a newer version altogether.


Is it possible to validate more than one parameter simultaneously into a Stored Procedure in sql?

There are a few options that you could use for this.  The first would be to just set default values within the procedure declaration to guarantee that there would never be NULL values passed in for these parameters:

CREATE PROCEDURE [dbo].[sp_Example]
  @parameter1 char (8) = 'test',
  @parameter2 char (20) = 'test2'....

The second alternative to checking them one at a time is to just have a single statement that will set them based on a NULL value.  Something along the lines of

  ALTER PROCEDURE [dbo].[sp_Example]
  @parameter1 char (8),
  @parameter2 char (20),
  @parameter3 char (20),
  @parameter4 char (20),
  @parameter5 char (20),
  @parameter6 char (20),...

  Select @parameter1 = CASE when @parameter1 is null then 'test' else @parameter1 end
  ,@parameter2 = Case when @parameter2 is null then 'test2' else @parameter2 end...

The other alternative is that if you just want to see if any of the values is NULL before performing the action, you could pretty easily do a single if statement that checks for that sort of thing

alter PROCEDURE [dbo].[sp_Example]
  @parameter1 char (8) = NULL,
  @parameter2 char (20) = NULL....

  as
  if @parameter1 is null or @parameter2 is null.....
  begin
        print 'You passed in a NULL value for one of your parameters'
  end
  Else
  begin
        --your statement here
  end

Depending on the value of the CONCAT_NULL_YIELDS_NULL setting, you could also use:

IF (@Parameter1 + @Parameter2 + @Paramater3 ...) IS NULL
BEGIN
    RAISERROR (N'One or more parameters is NULL', 1, 1);
END
ELSE
    -- other statements that will run if all @parameters are NOT null.
BEGIN

I am sure there are other alternatives as well, but I would think one of these would get you where you need to go.


Performance problem with big query on first run

What can I do to make sure the query executes quickly enough every time?

It's unlikely that this is a query performance issue. You're doing very little IO, using very little CPU time, and the query isn't processing very much data. The likely solution will be to resolve the hardware or configuration issue that is causing your query to run for so long sometimes. In one of your executions the query 92636 ms of elapsed time but only used 546 ms of CPU time  That means that SQL Server was waiting for something for over 92 seconds during query execution. Based on your description of the problem it's likely that there's a severe IO problem on the server. 11000 physical reads isn't that much and what you're seeing isn't normal.

You didn't include your version of SQL Server, but to get more information about the problem I would look at wait stats. You seem to have control over the server so you can try taking a before and after snapshot of the sys.dm_os_wait_stats DMV and seeing which waits increase after the query finishes executing. That DMV is server wide so you'll want to avoid running other queries if possible. You can also reset the DMV immediately before running your query by running the following T-SQL:

DBCC SQLPERF ("sys.dm_os_wait_stats" , CLEAR);

The wait stats should let you confirm if IO is indeed the problem. You may need to work with the admin in charge of the VM or storage to resolve this issue.


Network Maintenance and High Availability Group Prep

If you change the failover mode to "manual" then they should not failover automatically.


MySQL Cluster automatically replicates data nodes, based on the number of replicas and the number of data nodes you specify.  Your tables are automatically sharded across these nodes, and updates are replicated between them for HA.

Worth watching the short demo to see more:
http://www.oracle.com/pls/ebn/swf_viewer.load?p_shows_id=11464419

For more detail - take a look at the 'Cluster overview' section of the docs:
http://dev.mysql.com/doc/refman/5.5/en/mysql-cluster-basics.html


MySQL Slow Query Log - SELECT /*!N SQL_NO_CACHE */

These types of SELECT are always performed by mysqldumps.

If you look in the slow log for the start time of the query and the time of each of these SELECTs is the same time every day, it is definitely coming from a mysqldump you have crontab'd somewhere.

To eliminate this, you may need to run this

SET GLOBAL slow_query_log = 'OFF';

Then go run the mysqldumps, and then run

SET GLOBAL slow_query_log = 'ON';

This should totally eliminate this type of SELECT from landing needlessly in the slow log.


Can't connect to Oracle 12c Release 1 in docker container from host

I'm running Oracle 11g Personal edition docker container

$ docker ps
CONTAINER ID        IMAGE                                    COMMAND                  CREATED             STATUS              PORTS                                                                     NAMES
d84af138d77a        oracleinanutshell/oracle-xe-11g:latest   "/bin/sh -c '/usr/sb…"   22 minutes ago      Up 22 minutes       0.0.0.0:32777->22/tcp, 0.0.0.0:32776->1521/tcp, 0.0.0.0:32775->8080/tcp   oracle-xe-11g

As you can see port 1521 was  mapped to 32776.

Replace 0.0.0.0 with ipaddress of docker vm running on Virtualbox i.e 192.168.99.100

 sqlplus test/test@192.168.99.100:32776/XE

SQL*Plus: Release 19.0.0.0.0 - Production on Fri Aug 9 13:12:19 2019
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

If you're trying to login as sys user add sysdba at the end


The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

You probably receive the error because you have orphaned records in the [rpt].[ReportLessonCompetency] table.
An orphaned record is a record in a child table without a corresponding parent record in the parent table. In your case the [rpt].[ReportLessonCompetency] will contain values
for [Grade] that don't exist in the [rpt].TraineeGrade(Id) table.

There are 2 options to get the foreign key created (though there's only 1 valid option imo).

Cleanup your data
First you could look up the records in the child table that don't have a corresponding parent record. Next, you should either delete/update those records in the child table or add the missing parent records to your parent table. Afterwards you'll be able to create the foreign key constraint. This is the best option by far since your referential integrety is guaranteed and your foreign key will be trusted.
You can find orphaned records by executing following query:

SELECT *
FROM [rpt].ReportLessonCompetency rlc
WHERE NOT EXISTS
(
    SELECT 1
    FROM [rpt].TraineeGrade tg
    WHERE tg.Id = rlc.Grade
)

WITH NOCHECK
The other option would be to create the foreign key with WITH NOCKECK. SQL Server will create the foreign key without verifying the existing data in the table. When you update/insert data in the child table, those records will still be checked.
Consequently, your foreign key will be marked as untrusted and the query optimizer won't consider your constraint to generate an execution plan.
Here you can find an example of how performance can be impacted.


How does SQL Server choose an index key for a foreign key reference?

The (lack of) documentation suggests that this behaviour is an implementation detail, and is therefore undefined and subject to change at any time.

This is in stark contrast to CREATE FULLTEXT INDEX, where you have to specify the name of an index to attach to -- AFAIK, there is no undocumented FOREIGN KEY syntax to do the equivalent (though theoretically, there could be in the future).

As mentioned, it does make sense that SQL Server chooses the smallest physical index with which to associate the foreign key. If you change the script to create the unique constraint as CLUSTERED, the script "works" on 2008 R2. But that behaviour is still undefined and should not be relied upon.

As with most legacy applications, you'll just have to get down to the nitty-gritty and clean things up.


Having a unique ID for 2 different tables

This should do it I think.

CREATE TABLE Foo
(
FooId INT PRIMARY KEY,
Active BIT NOT NULL,
UNIQUE(FooId, Active)
)

CREATE TABLE FooActive
(
FooId INT PRIMARY KEY,
Active AS CAST(1 AS BIT) PERSISTED,
FOREIGN KEY (FooId, Active) REFERENCES Foo(FooId, Active)
)

CREATE TABLE FooInActive
(
FooId INT PRIMARY KEY,
Active AS CAST(0 AS BIT) PERSISTED,
FOREIGN KEY (FooId, Active) REFERENCES Foo(FooId, Active)
)


To move logins and users from the source server, I use the following solution. This script helps completelly transfer logins with user permissions  (see description on the top of the script how t use it and to specify the databases and logins).


Is there any way to change the size of preallocate data file in mongodb?

There is no way to specify your own sizes, the only way to alter the sizes of data files is to specify (or not) the smallfiles option.  That will bring the file sizes down to 25% of the default (max of 512MB rather than 2GB etc.)

You can pre-allocate the files yourself in advance (with dd, fallocate or similar).  I have a sample bash script using fallocate for just such a purpose here on GitHub. You still have to do the progression of sizes up to 2GB first, then allocate the remainder at 2GB though.  If nothing else, full pre-allocation would let you determine if allocation is having any kind of impact on your testing.


Does SQL Server 2016 Standard Edition include replication?

Yes. (See the REPLICATION section in Editions and supported features of SQL Server 2016
Yes. The distributor (Distribution Server) is located on a SQL Server instance and has to be correctly licensed, be this a production server or development server.
Yes. License required. A replication from a production server to another (dev) server is considered a move of productive data and so the target is no longer a DEV server, but a productive server.

The basic premise regarding Microsoft Licenses for SQL Server is: If it contains productive data, it is a productive server and requires (correct) licensing.

IMHO : Do no try to circumvent MS licensing fees by being clever and innovative. It will almost always backfire.

  DISCLAIMER: Please contact a Microsoft SQL Server licensee/specialist/re-seller or Microsoft themselves to obtain information about correct licensing. No guarantee given for above made statements.


MySQL Table Schema Design - Relationship with Junction or User Table

Query 1:

SELECT them.user_id, them.first_name, them.last_name, them.email
    FROM user AS me
    JOIN user AS them  ON me.organization_id = them.organization_id
    WHERE me.user_id = ?

Query 2:

INSERT INTO notification (requester, receiver, ...)
    VALUES
    (?, ?, ...)

(I assume you have your id, his id, and other stuff to supply; do not supply notification_id, it will be generated.)

Query 3 is phrased imprecisely:

SELECT first_name, last_name, email,
       timestamp,   -- Don't you need a date&time in the table?
       ...   -- don't you want more fields?
    FROM notifications
    WHERE email = ?  -- Leave out if you want all users; change to search by name
    ORDER BY `timestamp` DESC  -- I assume by 'current' you mean 'recent'?
    LIMIT 10;


Database Doesn't Contain Current Data After Restoring Last Full Backup from Production

This is a common issue when you back up to the same file over and over again. Without WITH INIT, the new backup gets appended to the file, it doesn't overwrite it. So in this example:

BACKUP DATABASE model TO DISK = 'c:\temp\model.bak' WITH COMPRESSION;
GO
USE model;
GO
CREATE TABLE dbo.newtable(id int);
GO
BACKUP DATABASE model TO DISK = 'c:\temp\model.bak' WITH COMPRESSION;

We can see from RESTORE HEADERONLY that there are two backups:

RESTORE HEADERONLY FROM DISK = 'c:\temp\model.bak';

Partial results:

Position        BackupFinishDate
-------------   -----------------------
1               2019-09-03 14:01:08.000
2               2019-09-03 14:01:11.000

Guess which one has dbo.newtable? Guess which one is used as the default when you just say RESTORE ... FROM DISK ...;?

If you're going to continue using this method to back up your databases, you'll need to determine the Position to pass to RESTORE ... WITH FILE = <position> so that you get the latest backup. A better practice IMHO is to always use BACKUP ... WITH INIT and append timestamps to the file name. This makes it easier both to ensure you're using the latest backup and also to remove older backups.