From an email on the MySQL lists sent earlier this month, Shawn Green (a MySQL Principal Technical Support Engineer) reports:

  Thanks to the corporate changes of the last few years, the sole developer has been too busy doing his primary job to really put any more time into the project and no person or group from the community has stepped forward to help push the project to a final deliverable state.

So I suppose the latest state of development is it's on hold.


Check out Brent Ozar's stored procedure called sp_BlitzIndex.
You will find a lot of usefull applications of it.


Is it faster to truncate destination and copy all source data or lookup and keep only non-matching data with SSIS transfer packages?

Is it faster...

It depends on a great many things so there is no single correct straight answer to this:

What proportion of the target rows are you needing to delete, update or insert anew, if you don't use the truncate+replace method
The performance metrics of the source and target systems
The network performance between where your SSIS packages are running and the source and target resources

If you are replacing or updating most of the rows then truncate and recreate will probably be faster, if you only need to affect relatively few then partial updates are likely to be faster but this is not always the case.

If truncate+replace does turn out to be faster (or only slightly slower) you may find dropping all indexes (apart from the clustered index) and recreating them after the copy will improve matters further.

  assuming the majority of rows in each table are not new this week/month

In this instance if you know exactly what timestamp you need data from (this can usually be derived from the target's current data, or you could store the relevant value for future reference after each process) and you can efficiently ask the sources for only the updated data, then this will definitely be faster. Even if for some reason you only know roughly and have to request data a bit further back to make sure you get everything, you will not only be writing less at the destination but will be transferring less from each target (just the recent rows) and from the destination if needing to merge (again, just the recent rows) than you would pulling all the data from the sources.

  I can't currently test which is faster as the initial transfer is still going

You can do some useful analysis at this point though: what seems to be the current bottleneck? It could be:

waiting for data to be read from the source(s) - i.e. their IO subsystem or DB design is limiting the processes speed, in thsi case you'll see high IO at that end from your process and less activity elsewhere
waiting for data to be transferred from the source(s) - i.e. bandwidth is a limiting factor, is that link saturated while everything else is more idle?
processing by SSIS if your ETL is more complicated than "read from here, write to there" - i.e. are you seeing a lot of CPU and/or IO activity from SSIS?
waiting for data to be transferred to the target - as above but in reverse
waiting for data to be written at the target - as above but in reverse
... or of course some combination of the above

Keep watching as the tasks proceed: does it look like the same key bottleneck(s) most of the way through the process?


How can I create a test copy of my production database that is updated daily on SQL Server?

The answer to your question is NO. Even rolling back all changes to the test database, you are still stuck with the fact that you cannot restore a differential backup to a database that is already recovered. You will always have to start over by restoring the latest FULL backup, then restore the latest DIFF that belongs with the latest FULL. Even if you save a copy of a 'FULL' backup on the test server (as Aaron points out in step1), you will eventually have to copy over a newer FULL backup to allow you to restore DIFFS that belong with that FULL.


Query slower after upgrade from sql server 2008R2 to 2014sp2

I understand your disappointment with the query plan regressions that you experienced. However, Microsoft changed some core assumptions about the cardinality estimator. They could not avoid some query plan regression. To quote  Juergen Thomas:

  However to state it pretty clearly as well, it was NOT a goal to avoid any regressions compared to the existing CE. The new SQL Server 2014 CE is NOT integrated following the principals of QFEs. This means our expectation is that the new SQL Server 2014 CE will create better plans for many queries, especially complex queries, but will also result in worse plans for some queries than the old CE resulted in.

To answer your first question, the optimizer appears to pick a worse plan with the new CE because of a 1 row cardinality estimate from Object2. This makes a nested loop join very attractive to the optimizer. However, the actual number of rows returned from Object2 was 34182. This means that the estimated cost for the nested loop plan was an underestimate by about 30000X.

The legacy CE gives a 208.733 cardinality estimate from Object2. This is still very far off, but it's enough to give a plan that uses a merge join a lower estimated cost than a nested loop join plan. SQL Server gave the nonclustered index seek on Object3 a cost of 0.0032831. With a nested loop plan under the legacy CE, we could expect a total cost for 208 index seeks to be about 0.0032831 * 208.733 = 0.68529 which is much higher than the final estimated subtree cost for the merge join plan, 0.0171922.

To answer your second question, the cardinality estimate formulas for a query as simple as yours are actually published by Microsoft. I recommend referencing the excellent white paper on differences between the legacy and new CE found here. Focus on why the cardinality estimates are 1 for the new CE and 208.733 for the legacy CE. That's unexpected because the legacy CE assumes independence of filters but the new CE uses exponential backoff. In general for such a query I would expect the new CE to give a larger cardinality estimate for Object2. You should be able to figure what's going on by looking at the statistics on Object2.

To answer your third question, we can get general strategies from the white paper. The following is an abbreviated quote:

  Retain the new CE setting if specific queries still benefit, and “design around” performance issues using alternative methods.
  Retain the new CE, and use trace flag 9481 for those queries that had performance degradations directly caused by the new CE.
  Revert to an older database compatibility level, and use trace flag 2312 for queries that had performance improvements using the new CE.
  Use fundamental cardinality estimate skew troubleshooting methods.
  Revert to the legacy CE entirely.

For your problem in particular, first I would focus on the statistics. It's not clear to me why the cardinality estimate for an index scan on Object3 would be so far off. I recommend updating statistics with FULLSCAN on all of the involved objects and indexes before doing more tests. Updating the statistics again after changing the CE is also a good step. You should be able to use the white paper to figure out exactly why you're seeing the cardinality estimates that you're seeing.

I can give you more detailed help if you provide more information. I understand wanting to protect your IP but what you have there is a pretty simple query. Can you change the table and column names and provide the exact query text, relevant table DDL, index DDL, and information about the statistics?

If all else fails and you need to fix it without hints or trace flags, you could try updating to SQL Server 2016 or changing the indexes on your tables. You're unlikely to get the bad nested loop plan if you remove the index, but of course removing an index could affect other queries in a negative way.


Be sure the ORACLE_HOME and ORACLE_SID environment variables are correctly set. Then run sqlplus / AS SYSDBA as a user who is a member of the oradba group on the computer.

Get the machine down forcibly:

SHUTDOWN ABORT

Start it up, but try not to let stuff run wild:

STARTUP RESTRICT

Shut it down more gracefully. Don't stop until recovery is complete.

SHUTDOWN IMMEDIATE

Start it up.

STARTUP


Accidentally change database name (SQL Server 2008)

Right click on the database, go to files. You can see the original file names. This will help you to find the correct database name easily. The file names won't be changed on a rename.

You can also try to take a look at your fn_dblog. It's undocumented but you can see (and filter) for the latest actions.

SELECT *
FROM fn_dblog(NULL,NULL)


And that is one of the reasons why everyone should forget DBMS_JOB and use DBMS_SCHEDULER.
DBMS_JOB jobs do not have history or any logging enabled. If a DBMS_JOB job fails, that should be reported in the database alert log. If a DBMS_JOB fails 16 times in a row, it will be automatically marked as broken.
Your job either failed 16 times in a row and was marked broken, or someone manually set it as broken.


Export mutliple query output into one excel sheet using Redshift and SQL Developer

If you want to export the data from Redshift to excel you can use some software like Talend Open Integration or export directly to CSV with the UNLOAD functionality that Redshift has.
https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html


Your database files were created by PostgreSQL version 13

I decided to abandon the idea of downgrading from 13 to 12. I reinstalled Pg 13 and am sticking with it.
I work with a platform that recommends Pg 12. However, I won't come back to 12 unless something goes wrong with 13.


How to resolve this 'XML Validation: Declaration not found for element' error?

An XML Schema Collection for that particular piece of XML would look more like this:

CREATE XML SCHEMA COLLECTION XS_ProductDoc
AS
'<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="Product">
        <xs:complexType mixed="true">
            <xs:sequence>
                <xs:element name="ProductID" type="xs:unsignedByte" />
                <xs:element name="ProductName" type="xs:string" />
                <xs:element name="SupplierID" type="xs:unsignedByte" />
                <xs:element name="CategoryID" type="xs:unsignedByte" />
                <xs:element name="QuantityPerUnit" type="xs:string" />
                <xs:element name="UnitPrice" type="xs:decimal" />
                <xs:element name="UnitsInStock" type="xs:unsignedByte" />
                <xs:element name="UnitsOnOrder" type="xs:unsignedByte" />
                <xs:element name="ReorderLevel" type="xs:unsignedByte" />
                <xs:element name="Discontinued" type="xs:unsignedByte" />
            </xs:sequence>
        </xs:complexType>
    </xs:element>
</xs:schema>'

The reason your code does not work is because your XML does not contain any namespaces, whereas the original AdventureWorks XML does.  Learn more about XML Namespaces here.


Convert datetime to europe default without time

Instead of your hack with replace, why not just cast to the right data type? This doesn't need to be nvarchar and the only reason it contains time is because you included too many characters. You can of course apply your replace here as well (or you could do that at the client).

SELECT CONVERT(CHAR(11),GETDATE(),13)
UNION ALL
SELECT CONVERT(CHAR(11), DATEADD(DAY, 10, GETDATE()), 13);

Results:

27 Aug 2012
06 Sep 2012


If you have a flat JSONB structure, ie. no objects and arrays exist as values, then the solution is simple.

Given the following table and data:

CREATE TABLE jsonbtest (data jsonb);

INSERT INTO jsonbtest (data) VALUES ('{"city":"Mesa","county":"Maricopa","mayor":"John Smith"}');
INSERT INTO jsonbtest (data) VALUES ('{"other city":"Mesa but with a longer name","county":"Maricopa","mayor":"John Smith Jr"}');

you can get the key-value pairs by doing

SELECT (jsonb_each_text(data)).* FROM jsonbtest;

    key     │   value
────────────┼────────────
 city       │ Mesa
 mayor      │ John Smith
 county     │ Maricopa
 mayor      │ John Smith
 county     │ Maricopa
 other city │ Mesa
(6 rows)

Having this, getting the maximal length is a matter of a simple GROUP BY query:

SELECT key, max(char_length(value))
  FROM (SELECT (jsonb_each_text(data)).* FROM jsonbtest) t
 GROUP BY key;

    key     │ max
────────────┼─────
 city       │   4
 other city │  27
 county     │   8
 mayor      │  13
(4 rows)

See all this at work on dbfiddle.  See what else you cana use for processing your JSONB values in the docs.


Given that a game can only ever have two players, I suggest the following design:

create table PLAYER
(
  playerid   INTEGER PRIMARY KEY,
  -- add your other columns here
);

create table GAME
(
  gameid     INTEGER PRIMARY KEY,
  gamedate   DATETIME,
  player1id  INTEGER,
  player2id  INTEGER,
  -- add your other columns here
);

If you required a differing number of players per game, then I would have designed it differently - with your requirements there is no need to add an extra table & increase the query complexity.

Also note that I have made gameid the PRIMARY KEY. Using a DATETIME as the PK is generally not a good idea - what would happen if two games were created simultaneously?

You'll also probably want to add foreign keys to/from GAME.player1id/player2id and PLAYER.playerid.


The answer is, unfortunately, it depends. Your database might be 70/30 write/read, but every table in the database won't be 70/30. Some tables, such as reference tables and lookups, may only be written to once during initial deployment then very rarely after that. Those tables should have a high fill factor.

You transactional tables that have the 70/30 write/read split may be candidates for lower fill factors, but again, it depends on the index. For example, filtered indexes that may actually only have a 50/50 read/write ratio may be okay with a higher fill factor, but your clustered indexes may want a lower fill factor to prevent excessive page splits.

You need to analyse your tables and indexes individually and make the call based on the expected read/write split for that specific index rather than trying to set it for your database as a whole.

Another consideration is your number of transactions per minute/hour/day. If your OLTP system has to handle millions of transactions a day, then fill factor is an important design consideration, if your system doesn't need to scale beyond a few thousand transactions per day, then fill factor is less important due to the low volumes of data ingress.

If you're architecting this database from scratch, then take the time to correctly identify the required fill factor per index, and ensure any new indexes\tables have this analysis performed as well, rather than creating them with default fill factors.

EDIT: As per Erik Darling's comment, another thing to be aware of is that your fill factor only applies when you create, rebuild or reorganise an index. There is a great article on Brent Ozar's site about fill factor which should help.


PLS-00302: component 'COMP_FOR_OLTP' must be declared

The terminology changed in 12c. See the constants here.

You could use DBMS_COMPRESSION.COMP_ADVANCED instead.


Prevent duplicate record counts in one to many query

SELECT DISTINCT is not the answer to the issue. You are right that the join is the problem. It produces multiple rows for the same student, when the student is connect to a teacher more than once.

One way to solve this is by using an EXISTS subquery:

SELECT Count(*) AS CountOfEthnicity,
       s.Ethnicity
FROM dbo.StudentsSDRC AS s
WHERE EXISTS
      ( SELECT *
        FROM dbo.TeacherStudents AS t
        WHERE s.StudentNum = t.StudentNum
          AND t.teacherDCID = 43652
      )
GROUP BY s.Ethnicity ;

Other irrelevant to the problem notes / improvements:

The LEFT JOIN was acting as an INNER JOIN as one of the right table's columns was in the WHERE condition.
It's good to alias your tables with shorter names. Improves readability of the queries.
It's good to prefix the tables with the schema names (eg. dbo).
The pile of parentheses was not needed at all.

If you prefer the join for some reason, then this would work, too. I personally don't find it as explanatory as the 1st query.

It assumes that StudentNum is UNIQUE in table StudentsSDRC:

SELECT Count(DISTINCT s.StudentNum) AS CountOfEthnicity,
       s.Ethnicity
FROM dbo.StudentsSDRC AS s
    JOIN dbo.TeacherStudents AS t
        ON s.StudentNum = t.StudentNum
WHERE t.teacherDCID = 43652
GROUP BY s.Ethnicity ;


Why would a query in code be slower than a query in a SQL tool

Different execution plans.

When you run the query from different environments many things must be the same in order to reuse the cached query plan.

Including the query text for adhoc queries, various SET options and the default schema of the logged in user if the query relies on implicit name resolution.

This is usually a sign of a parameter sniffing issue where the optimum plan generated can vary according to the value of the parameters passed in. When you run the query in SSMS if it can not reuse the cached plan the application is using you get a new plan compiled that is suited for the parameter values under test.

See Slow in the Application, Fast in SSMS? Understanding Performance Mysteries for more.


Modify linked server definition (Oracle) in SQL Server 2012

How you solved the issue is the way to solve it because we can't modify a Linked Server. And SQL Server doesnt read the tnsnames.ora. Though the solution is drop and recreate we can use SQL Management Studio to edit the Drop and Create script as follows:
1. Right click on the linked server.
2. Choose Script Linked Server as.
3. Choose New Query Editor Window.
4. Now we can modify the script as our requirements and run. This simply drops and recreates the Linked Server.

Thanks


Data from two table in SQL server 2008 R2

Assuming Code is a VARCHAR(32) (just change the first line if my assumption is incorrect):

ALTER TABLE dbo.History ADD Code VARCHAR(32);

UPDATE h
  SET Code = c.Code
  FROM dbo.History AS h
  INNER JOIN dbo.Codes AS c
  ON h.Name = c.Name;

-- verify first, then:

ALTER TABLE dbo.History DROP COLUMN Name;

If you absolutely need the columns to be in the same order (you shouldn't), you can rebuild the table as follows:

-- first, drop all constraints that point to dbo.History. Then:

SELECT Code, BU, Plan
  INTO dbo.HistoryCopy
  FROM dbo.History;

EXEC sp_rename 'dbo.History', 'dbo.HistoryOld', 'OBJECT';
EXEC sp_rename 'dbo.HistoryCopy', 'dbo.History', 'OBJECT';

-- now re-create the constraints / indexes etc. on the new copy

In order to prevent user activity from affecting or being affected, you'll want to do this during a maintenance window, or surround the above with a serializable transaction.

When you're happy that the change is successful, you can drop the old copy:

DROP TABLE dbo.HistoryOld;


No, partitioning is NOT supported for FileTable.

  Partitioning is not supported on FileTables. With the support for multiple FILESTREAM file groups, pure scale-up issues can be handled without having to resort to partitioning in most scenarios (unlike SQL 2008 FILESTREAMs).


Users who are connected into the database

You can check connected logins with the following query:

SELECT
    sdes.session_id,
    sdes.login_time,
    sdes.last_request_start_time,
    sdes.last_request_end_time,
    sdes.is_user_process,
    sdes.host_name,
    sdes.program_name,
    sdes.login_name,
    sdes.status,
    sdec.num_reads,
    sdec.num_writes,
    sdec.last_read,
    sdec.last_write,
    sdes.reads,
    sdes.logical_reads,
    sdes.writes,
    sdes.client_interface_name,
    sdes.nt_domain,
    sdes.nt_user_name,
    sdec.client_net_address,
    sdec.local_net_address
FROM
    sys.dm_exec_sessions AS sdes
    INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id

However, this query will only retrieve currenty connected sessions. if you would like to know which logins were logged into the server, you need to set up a trace, enable the audit for error log or use extended events.

Here's a solution to enable audit for error log:

Using SSMS, right click your server and go to Properties.
On Security, below Login auditing click either "Successful logins only" or "Both failed and successful logins", depending if you also want failures or not.
Restart the SQL Server service (unfortunately).
Information will be stored on the Error Log. Data will be deleted over a time so make sure to review it's retention policy. To read the information on the error log you can use sys.sp_readerrorlog. The first parameter is the error log number (0 = current, 1 = first archive, etc.), second parameter determines the source to read (1 for error log) and the third parameter is the literal you want to filter info from:

EXEC sys.sp_readerrorlog 0, 1, 'login'

A more visual alternative to read the log is going to Management (on SSMS), then SQL Server Logs and double click on any log.

To see amount of logins by time you can use the following script (I'm using the "failed login" event because it's the one my server is tracking):

IF OBJECT_ID('tempdb..#FailedLogins') IS NOT NULL
    DROP TABLE #FailedLogins

CREATE TABLE #FailedLogins (
    LogDate DATETIME,
    ProcessInfo VARCHAR(100),
    Message VARCHAR(MAX))

INSERT INTO #FailedLogins (
    LogDate,
    ProcessInfo,
    Message)
EXEC sys.sp_readerrorlog 0, 1, 'login failed'

SELECT TOP 5 * FROM #FailedLogins AS F

Result:

LogDate                 ProcessInfo Message
2018-07-16 09:47:52.240 Logon       Login failed for user 'User1'. Reason: Password ...
2018-07-16 12:31:51.930 Logon       Login failed for user 'User1'. Reason: Password ...
2018-07-17 13:38:05.520 Logon       Login failed for user 'User2'. Reason: Could not ...
2018-07-17 13:42:14.130 Logon       Login failed for user 'User3'. Reason: Password ...
2018-07-17 13:42:28.690 Logon       Login failed for user 'User3'. Reason: Password ...

How to obtain user from these messages (this will highly depend on the message being shown on the error log):

DECLARE @Message VARCHAR(MAX) = 'Login failed for user ''User1''. Reason: Password did not match that for the login provided. [CLIENT: X.X.X.X]'

SELECT
    Original = @Message,
    UserStartPoint = SUBSTRING(@Message, 24, 100),
    TrimmedUser = LEFT(
        SUBSTRING(@Message, 24, 100),
        CHARINDEX(
            '''',
            SUBSTRING(@Message, 24, 100)) - 1)

Result:

Original: Login failed for user 'User1'. Reason: Password did not match that for the login provided. [CLIENT: X.X.X.X]
UserStartPoint: User1'. Reason: Password did not match that for the login provided. [CLIENT: X.X.X.X]
TrimmedUser: User1

Group by and count between a period:

DECLARE @PeriodStartDate DATE = '2018-07-16'
DECLARE @PeriodEndDate DATE = GETDATE()

SELECT
    Amount = COUNT(1),
    TrimmedUser = LEFT(
        SUBSTRING(F.Message, 24, 100),
        CHARINDEX(
            '''',
            SUBSTRING(F.Message, 24, 100)) - 1)
FROM
    #FailedLogins AS F
WHERE
    F.LogDate BETWEEN @PeriodStartDate AND @PeriodEndDate
GROUP BY
    LEFT(
        SUBSTRING(F.Message, 24, 100),
        CHARINDEX(
            '''',
            SUBSTRING(F.Message, 24, 100)) - 1)

Result:

Amount  TrimmedUser
7       User1
3       User3
1       User2


Significantly Slower DB Dumps on AWS RDS After Turning on Encryption?

mysqldump reads all of your data.  If the encryption is at the disk, then it takes time to decrypt the data before mysqldump can access it.  Hence, the total action is slower.

Do you have any metrics on how much slower (percentage)?

Also, since RDS does key management, there may be a slight, one-time, delay to get the key.


How to GRANT Permission on many views? Azure SQL

If you were still developing database, you could create views within a single schema, and add that user or group of users/role , permissions to alter that schema alongside permission to create view.
Such as :

GRANT ALTER ON SCHEMA::ViewSchema to ViewsEditorUser
GRANT CREATE VIEW to ViewsEditorUser

When you are all set and done, you can transfer those views from ViewSchema to dbo or some other more meaningful schema with command:

ALTER SCHEMA NewSchema TRANSFER ViewsSchema.FirstView

If views are already created, you can either add grant alter schema (name of the schema where the views reside) and add permission to user to create a view. But be aware that since you granted a user alter schema, he has all the rights on that schema, including truncating/droping tables etc..

Last option is creating a dynamic query that will grant a user/role to alter each of these views

Update

Using dynamic query would look something like this:

     DECLARE  @ViewTable as table (ID int, ViewName varchar(50))
     DECLARE @ID int = 1,@MaxID int,@ViewName varchar(50),@Query varchar(200)
     INSERT INTO @ViewTable
   select ROW_NUMBER() over (order by(select null)) as ID, sys.schemas.name + '.' + sys.all_views.name as name from sys.all_views
   inner join sys.schemas on all_views.schema_id = sys.schemas.schema_id
       where sys.all_views.name like '%FilterOnDesiredViews%'

    SET @MaxID = (select max(ID) from @ViewTable)

       while(@ID <= @MaxID)
       BEGIN
       SET @ViewName = (select ViewName from @ViewTable where ID = @ID)
       SET @Query = 'GRANT VIEW DEFINITION on ' +  @ViewName +' to SomeUser'
       exec (@Query)
       SET @ID = @ID + 1
        END

Note that i filtered the view names retrieval query with some silly name, you should apply your own filtering.


Enable Lock Pages in Memory for SQL Server on VM

I believe the general consensus these days is that if you are deploying onto a modern OS such as Windows 2008 R2, and the server is dedicated to SQL Server, then lock pages in memory is generally not required. The modern OS is much better at managing memory than earlier versions, even Windows 2003. However, it'll be prudent to test both scenarios.


@Kin yes he wants just to remove the partition so it becomes non- partitioned table.
 as Aaron said I would   select * into new_table from old_paritioned and rename the new table and drop the partitioned table.I am sure they are better ways of doing that though.
If it is large table(billion of rows) copying all rows takes time and resources.


How can I optimize performance on a MySQL ndbcluster?

First of all, Cluster is a storage engine. It doesn't actually execute queries because it doesn't speak SQL. That is why you use a MySQL server, which parses your queries and sends low-level storage engine API calls to the Cluster data nodes. The data nodes know how to retrieve or store data. Or you can talk to the data nodes directly using the NDB API(s).

MySQL Cluster has various means of executing queries. They boil down to:

Primary key lookup
Unique key lookup
Ordered index scan (i.e., non-unique indexes that use T-trees)
Full table scan

Let's say you have 4 data nodes in your cluster (NoOfReplicas=2). This means you have 2 node groups and each one has half the data. Cluster uses a hash on the primary key (unless you've controlled the partitioning using the 5.1 partitioning features). So for any table, half the rows are in one node group and half the rows are the in other node group.

Now for the 4 types of query execution. You can verify which type of execution is used with EXPLAIN. Here's how each ones works:

Primary key lookup - the MySQL server can calculate the hash on the primary key and know which data node the relevant row resides in. Then the MySQL server contacts that data node and receives the row.
Unique key lookup - the MySQL server cannot know which data node the row might be stored in. So it contacts a pseudo-random node. That data node has a hidden table that maps the unique key values to the primary key. Then the hash on the primary key reveals where the row resides.
Ordered index scans are sent in parallel to all data nodes, where they search their local t-tree.
Full table scans are send in parallel to all data nodes, where they scan their primary fragment.

Summary: primary key lookups are best. If you have more than 2 nodes, throughput goes up because all nodes are actively serving different fragments of data. Ordered index lookup and full table scans are done in parallel, so more nodes leads to better performance.


If you did it with a GRANT, run the corresponding REVOKE.

Or you could run this.  (Suggest you dump mysql.user first, just in case.)

DELETE FROM mysql.user
    WHERE user = 'root'
      AND host = '%';


Set MultiSubnetfalilver=Yes to linked servers in standalone SQL Server 2016 implications

I dont see a benefit of using MultiSubnetFailover for a sandalone server since you dont have failovers, no listener is configured and its a single point of failure. There is obviously a performance gain when using MultiSubnetFailover in a DR situation.


Is the runtime of checking for duplicate primary keys in SQL Server O(n)?

A primary key in SQL Server is always backed by an index (in fact, a B-tree for non-Hekaton tables). An index allows for O(log N) lookup (and duplicate checking).

In practice it's hard to measure the difference to O(1) behavior even if you aim at it. The upper index levels tend to be cached and the tree is very flat. The maximum tree level in SQL Server is 15 levels (you need to load the index pages with ~900 byte keys for that to force minimum fanout).

Each index row must be 807 bytes in size to fit at most 9 of them on a page. Then, the index size on level L is 807*9^L. L starts at 1. L == 15 => 807*9^15/10^12 = 166154 TB. Maximum database size: 524,272 TB. So the maximum level is 15.


Oracle 11g connectivity over windows based client-server net

Assuming that these machines are sitting in the same network domain, have no firewalls between them and that you're running with everything "as standard", then you should be able to access the Database Instance running on PC1 from PC2 using these parameters in SQL Developer:

Connection Type: Basic
Hostname:        PC1
Port:            1521
SID:             SID of database instance (ORACLE_SID from PC1)

For connecting your Application, check out https://www.connectionstrings.com/

If you're getting Oracle (ORA-?????) errors, add them into the question so we can [try to] diagnose the problem.


Finding the sum of the lowest two values from a table in MySQL

SELECT Price
    FROM Trading
    ORDER BY Price
    LIMIT 2;

will find the 2 lowest values.

Wrap that to get the SUM (or AVG or whatever):

SELECT SUM(Price) as sum_price
    FROM ( SELECT Price
            FROM Trading
            ORDER BY Price
            LIMIT 2 )

Then (I think) use that instead of the subquery you have.


How to recover database with MDF file only

A torn-page detection error on page (1:0) indicates the primary page of the primary database file is corrupted.  This page contains a multitude of important details about the database.  A sample output of DBCC PAGE for one of my databases shows:

Allocation Status

GAM (1:2) = ALLOCATED               SGAM (1:3) = NOT ALLOCATED          PFS (1:1) = 0x44 ALLOCATED 100_PCT_FULL
DIFF (1:6) = CHANGED                ML (1:7) = NOT MIN_LOGGED

File Header Data:

Record Type = PRIMARY_RECORD        Record Attributes =  NULL_BITMAP VARIABLE_COLUMNS
Record Size = 157
Memory Dump @0x000000000CFEA371

0000000000000000:   30000800 00000000 17000000 0016004b 004b004d  0..............K.K.M
0000000000000014:   004f0053 0057005b 005f005f 00690069 0069006d  .O.S.W.[._._.i.i.i.m
0000000000000028:   00710075 00790079 00790079 0083008d 009d004c  .q.u.y.y.y.y......L
000000000000003C:   3f4c23ac 53f24583 29615c2f 5ddaba01 00010000  ?L#¬SòE)a\/]Úº.....
0000000000000050:   19000000 32000080 0c000000 0000007b 000000d8  ....2..........{...Ø
0000000000000064:   0000000a 00000200 00000000 00ffffff ff001000  .............ÿÿÿÿ...
0000000000000078:   00000000 00000000 000000da 0000000c 0a000049  ...........Ú.......I
000000000000008C:   0015eb49 62ffe8ab 43be1bdc 9847daaa 7d        ..ëIbÿè«C¾.Ü.GÚª}
BindingID = 234c3f4c-53ac-45f2-8329-615c2f5ddaba                         FileIdProp = 1
FileGroupId = 1                     Size = 6400                         MaxSize = 12800
Growth = 3200                       Perf = 0                            BackupLsn = [NULL]
FirstUpdateLsn = (123:216:10)       OldestRestoredLsn = [NULL]          FirstNonloggedUpdateLsn = [NULL]
MinSize = 512                       Status = 0                          UserShrinkSize = 65535
SectorSize = 4096                   MaxLsn = [NULL]                     FirstLsn = [NULL]
CreateLsn = (0:0:0)                 DifferentialBaseLsn = (218:2572:73)
DifferentialBaseGuid = 6249eb15-e8ff-43ab-be1b-dc9847daaa7d              FileOfflineLsn = [NULL]
FileIdGuid = [NULL]                 RestoreStatus = [NULL]              RestoreRedoStartLsn = [NULL]
RestoreSourceGuid = [NULL]          HardenedSkipLsn = [NULL]            ReplTxfTruncationLsn = [NULL]
TxfBackupLsn = [NULL]               FstrContainerSize = [NULL]          MaxLsnBranchId = [NULL]
SecondaryRedoStartLsn = [NULL]      SecondaryDifferentialBaseLsn = [NULL]
ReadOnlyLsn = [NULL]                ReadWriteLsn = [NULL]               RestoreDifferentialBaseLsn = [NULL]
RestoreDifferentialBaseGuid = [NULL]RestorePathOrigin = [NULL]          DatabaseEncryptionFileState = [NULL]
FCBFileDEK = [NULL]

If the first page is corrupted, that likely means you've not correctly fixed the deleted filesystem.

Clearly, having a well-tested recovery plan, even for development databases, might be considered A Good Thing™


Optimal placement of tempdb, mdf and ldf files in SQL Server 2012 on SSD's?

But, from reading into it more, separate physical disks for mdf and ldf files don't really apply when it comes to SSD's. Correct?

The original reason for splitting log and data files off onto seperate disks was 2 fold - latency and bandwidth on the drives.

SSDs don't remove these restrictions, but they do decrease/increase the limits quite significantly (7.9ms for a read with a single HDD vs 0.1ms for a read in a single SSD, roughly).

So ultimately yes and no - it doesn't apply AS MUCH as with HDDs, but those limits are still there and can still be met.  It all depends on your workload.

  Is my "thinking" setup good or just simply a waste (i.e. no reason to separate out tempdb) where I now have an extra SSD to make use of elsewhere?

Assuming that

You have 3 physical SSD's
You have 1 physical HDD
You need the data to be redundant, but not necessarily the system itself

Your proposed setup would have a few issues (as mentioned before), and a single drive failing is the main one.

You could go for something like this.

Single 7200rpm drive - Windows OS
RAID 5 array (3 SSDs) - Broken down into 4 drives (D for Data, L for Logs, S for Swap and T for Temp)

OR

Single 7200rpm drive - Windows OS
Single SSD - Temp and Swap
RAID 1 array (2 SSDs) - Data and Logs

It's personal preference of mine offloading Windows onto a non-SSD drive when you only have a limited number, but this entirely depends on what the server is doing and how much of a risk you're willing to take.


conversion of a non correlated sub query to a join statement

Your initial query shows that configurations must have no more than one row (or it would fail). You could rewrite this as a join using

SELECT student_id,
       COUNT(attendance)
FROM   student_attendance
       CROSS JOIN configurations c
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = MAX(c.max_allowed);

Or as

SELECT student_id,
       COUNT(attendance)
FROM   student_attendance CROSS JOIN
       configurations c
WHERE  attendance = 'ABSENT'
GROUP  BY student_id, c.max_allowed
HAVING COUNT(attendance) = c.max_allowed;

Or as

SELECT t.*
FROM   (SELECT student_id,
               COUNT(attendance) AS count_attendance
        FROM   student_attendance
        WHERE  attendance = 'ABSENT'
        GROUP  BY student_id) AS t
       JOIN configuration AS c
         ON t.count_attendance = c.max_allowed;

But I don't see any benefit of doing so. The original query is clearer and likely to be equally or more efficient.

The execution plan I see is

The select against configurations is executed once - then the rest of the query is run as an independent sub tree with the final elimination of aggregate rows not matching the HAVING happening in the INNER JOIN.

To optimise this query you should consider creating an index on attendance, student_id. This will allow rows matching the attendance = 'ABSENT' predicate to be efficiently identified. And they will be ordered so can be processed by the stream aggregate without needing a sort.

One other option would be to pre-calculate the aggregation using an indexed view.


Percona and MariaDB-10.0 ignoring not null on table fields

This is taken from the MySQL documentation:

  Changes in MySQL 5.7.1 (2013-04-23, Milestone 11)

  ...
  If a column is declared as NOT NULL, it is not permitted to insert NULL into the column or update it to NULL. However, this constraint was enforced even if there was a BEFORE INSERT (or BEFORE UPDATE trigger) that set the column to a non-NULL value. Now the constraint is checked at the end of the statement, per the SQL standard. (Bug  #6295, Bug#11744964).
  ...

Basically to change this behavior can do the following:

MariaDB> SET SESSION sql_mode := 'NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
MariaDB> SELECT @@GLOBAL.sql_mode 'sql_mode::GLOBAL',
          @@SESSION.sql_mode 'sql_mode::SESSION';
+------------------------+-----------------------------------------------------------------------------------------------------------------+
| sql_mode::GLOBAL       | sql_mode::SESSION                                                                                |
+------------------------+-----------------------------------------------------------------------------------------------------------------+
| NO_ENGINE_SUBSTITUTION | NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+------------------------+-----------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB> SHOW CREATE TABLE `table_name`;
+------------+----------------------------------------------------------------------------+
| Table      | Create Table                                                               |
+------------+----------------------------------------------------------------------------+
| table_name | CREATE TABLE `table_name` (                                                |
|            |        `id` INT(11) UNSIGNED NOT NULL,                                     |
|            |        `field` VARCHAR(20) DEFAULT NULL,                                   |
|            |        `field_with_default_value` VARCHAR(20) NOT NULL DEFAULT 'myDefault' |
|            | ) ENGINE=InnoDB DEFAULT CHARSET=latin1                                     |
+------------+----------------------------------------------------------------------------+
1 row in set (0.00 sec)

MariaDB> INSERT INTO `table_name`(`id`, `field`, `field_with_default_value`)
   VALUES
   (1, 'Value', NULL);
ERROR 1048 (23000): Column 'field_with_default_value' cannot be null

I hope this a light on the road.


How to map a drive in a SQL Server script

I agree that you could use \\\server\share\Whatever without needing to map a drive at all (so UNC path only) but the security context running the T-SQL will need appropriate permissions to the SHARE and the NTFS of the full path to ensure it can do whatever commands you are running.

You could use PUSHD and POPD as:

PUSHD
\\server\share
Command \subfolder1\*.txt
POPD

You could also use:

IF NOT EXIST Z: NET USE Z: \\server\share
command z:\folder\files.txt

Or perhaps something like the below with plugging in your variables:

DECLARE @folder AS VARCHAR(1000) = '\\server\share\folder1\folder2\finalfolder\'

DECLARE @command VARCHAR(500) = 'DIR /B "' + @folder + '*.xml"'
DECLARE @file VARCHAR(100)
DECLARE @filesinafolder TABLE (filenameswithfolder VARCHAR(500))
DECLARE @sql NVARCHAR(4000)

-- create global temp table
IF OBJECT_ID('tempdb..##globalname') IS NOT NULL
DROP TABLE ##globalname

CREATE TABLE ##globalname (
StepResultID VARCHAR(50)
,StartTime VARCHAR(50)
,StopTime VARCHAR(50)
,ReturnCode INT
)

INSERT INTO @filesinafolder
EXEC master..xp_cmdshell @command


Recommendation for RDBMS for large table

The size of the tables really doesn't matter all that much. It's all about how well indexed the tables are and how many people are using the database at once.

In my case I've got clients with billions of rows in a single table where the table size is about 1 TB and another 2 billion row table which has blobs.  These tables crank without performance issues at all.

In this case the server has 1TB of RAM, 80 cores, and is running Microsoft SQL Server 2008 R2.  Granted not all that helpful if you want a *nix OS, but it gives you some ideas that most database platforms can handle just about everything.


Is there a quickish, straighforward way to measure Transactions Per Second on SQL Server 2000?

I would suggest using the SQL Server Counters in the Windows PerfMon Utility.

This is for SQL 2005 but the instructions provide the gist of things for you.


How to get overall rank based on data regardless of scope (all items, user specific, etc.)?

This query uses a variable for ranking:

SELECT t.id,
    t.user_id,
    t.slug,
    t.votes,
    @rank := @rank + 1 AS rank
    FROM (
    SELECT
        s.id,
        s.user_id,
        s.slug,
        (
            SELECT
                COUNT(sv.id)
            FROM
                server_votes AS sv
            WHERE
                DATE(sv.created_at) > '2016-01-24 19:11:15'
            AND
                sv.server_id = s.id
        ) AS votes
    FROM
        servers AS s
    LEFT JOIN
        server_votes AS sv
    ON
        sv.server_id = s.id
    WHERE
        s.game_id = 1
    GROUP BY s.id
) t, (SELECT @rank := 0) r
ORDER BY
    t.votes DESC,
    t.id ASC

See SQL Fiddle.

Output:

id  user_id slug    votes   rank
4   1       Server4 3       1
2   1       Server2 2       2
1   1       Server1 0       3
3   2       Server3 0       4
5   2       Server5 0       5

Once the above query is put in a subquery with a WHERE clause, it outputs data for a specific user:

SELECT
    r.id,
    r.user_id,
    r.slug,
    r.votes,
    r.rank
FROM (
   ...
) r
WHERE r.user_id = 2;

It will output id 3 and 5.

See SQL Fiddle


Can scheduled and continous replication configurations exist side-by-side on the same primary/secondary servers?

Can two different replication plans coexist side-by-side on the same environment?

Yes, you can create many publishers for the same articles that replicates to different subscribers.

  Is it possible to setup a second replication routine for this scenario (continuous transaction replication) besides the current replication schema for the existing databases?

Yes, if you are replicating a new database or subset of data to other server, it should be fine.

You need to think about if the distributor server is same on the publisher server or you are having a different distribution server.

When distributor and publishers share the same server instance, then it is highly likely that you will hit a performance bottleneck, depending on how much data you are replicating (all data vs subset of data) and your network latency between your publisher databases and subscriber databases.

  Does this create a high risk for a large existing scheduled replication job? Our DBA says that this replication scenario creates a high risk for the existing replication configuration (2x a day).

As I mentioned in above answer, it depends on how much data you are replicating and the amount of transactions happening on publisher database. Better to follow your DBA recommendations if he/she has proper stats or baseline that will show the current behavior.


How to change location of SQL Server 2012 database which is already configured with a merge replication

Oh Boy! System Databases on C Drive are recipe for disaster !

Change the default database location away from C:\ drive.

You can do it using ALTER DATABASE .. MODIFY FILE

Change the db file location

ALTER DATABASE db_name
MODIFY FILE (NAME = logical_data_file,
     FILENAME = 'D:\data\physical_file.mdf');

ALTER DATABASE db_name
MODIFY FILE (NAME = logical_file,
     FILENAME = 'L:\log\physical_file_log.ldf');

Stop log reader agent, distribution and merge agent (if they are running).
Offline the database using alter database db_name set offline
Once offline, you have to PHYSICALLY move the data and log files to the new location that you put in in step 1.
Bring the database ONLINE alter database db_name set ONLINE
Start the jobs that were disabled in step 2.


Mysql 5.5.25 must be restarted to connect

Please keep in mind what the MySQL Documentation says about max_connect_errors

If more than this many successive connection requests from a host are interrupted without a successful connection, the server blocks that host from further connections. You can unblock blocked hosts by flushing the host cache. To do so, issue a FLUSH HOSTS statement or execute a mysqladmin flush-hosts command. If a connection is established successfully within fewer than max_connect_errors attempts after a previous connection was interrupted, the error count for the host is cleared to zero. However, once a host is blocked, flushing the host cache is the only way to unblock it.

MySQL 5.5's default value for max_connect_errors is 10.
I have two suggestions on resetting the connection error count errors besides restarting mysql:
SUGGESTION #1
Create a one line SQL script (such as C:\FlushHosts.sql)
Setup a scheduled task to execute mysql -uroot < C:\FlushHosts.sql every hour
SUGGESTION #2
Goto my.ini and add the following line
[mysqld]
max_connect_errors=18446744073709547520

Then, login to mysql client and run this:
mysql> SET GLOBAL max_connect_errors = 18446744073709547520;

After setting max_connect_errors this high, you should never get connection errors. Otherwise, you must running mysql in some parallel universe.
Give it a Try !!!


Is a millions-row query fetched in the SQL Studio client slower than other applications?

does it take additional time for the SQL Studio Client to actual display all 30 million of these than it would an application to fetch (and process) the exact same query? (with no display requirements)

In short: It depends on what the application is doing with the data (aside from not displaying it), but yes SSMS will take some time to prepare and display the results. If you set SSMS to display client stats (one of the options in the default toolbars, or from the Query menu) you'll see "client processing time" as a separately counted value.

In most circumstances the fetch will be no slower from the database's point of view, because SQL Server's engine is running the same statements and sending the same resulting data over the same network, but there will be a delay on the client at the end as the results are parsed and prepared for display.

There are a few caveats: one being that if the application is not pulling the whole results down at once but instead opening a cursor and pulling rows down individually (or a block at a time) then it may impose more effective load on the server as it may end up holding locks for much longer. In this case the application will get the results slower because of the extra latency from trips to the server to request new rows. It may appear faster to the user though, because the application can start displaying information from the first rows as soon as they are available instead of waiting for the whole resultset to transfer. Getting the data in smaller blocks will increase the amount of bandwidth used if you are using a compressed VPN or tunnel to connect to the server, because the smaller segments may contain less redundancy for the compression algorithm to take advantage off.

  Should I aggregate or pre-calculate the 30 million?

We know nothing of your data, or what you are trying to do with it. What calculations? What aggregations? Without much more information we can't help there beyond wild guessing. The best thing would probably be to try it and see.

  Indices wouldn't help with full table fetches.

They might if you are requesting the data be presented in a specific order. Or if you are only requesting some of the fields ("some fields from all rows" could become an index scan instead of a table scan and require less data pages to be read).

  What's a reasonable time to fetch 30 million records? Select A,B,C from big_table?

There are too many variables for use to even guess an answer to that.

  the database is in Europe, and I'm in the United States

For remote databases using some form of link compression will help with large results, though be aware that this will increase latency for small requests.


Here's one approach:

SELECT  a.part,
        a.description AS "desc",
        a.job_numbers
        dn.delnote_number,
        a.qty_ordered,
        dn.qty_del
    FROM ( SELECT  part, description, jobitemid
                GROUP_CONCAT(job_number ORDER BY job_number desc) AS job_numbers,
                SUM(qty_ordered) as qty_ordered FROM  job
            where  type = 'part'
            GROUP BY  part
         ) AS a
    LEFT JOIN dn  USING(jobitemid)

OP says that this is the result:

part        desc     job_numbers    delnote_num     qty_ordered      qty_del

"part1"     "desc1"    "2,1"       "1"              "456"            "30"
"part3"     "desc3"    "2"         "4"              "6666"           "6000"
"part1"     "desc1"    "2,1"       "5"              "456"            "500"
"part2"     "desc2"    "2,1"       NULL             "1343"           NULL
"part4"     "desc4"    "3"         NULL             "1000"           NULL


Without an order by your query does not really make sense (IMHO). But nevertheless you can still use a query like:

select * from
( select A.x
  from A
  order by x
  fetch first 2 rows only
)
union all
(  select B.x
   from B
   order by x
   fetch first 3 rows only
)
;

I made up a small example for you to play with at Fiddle


Trying to upgrade PostgreSQL 9.1 to 9.4 on Debian

For the records, I gave up trying this method and did it via pg_dumpall instead.

Here is my earlier question in the unix area (with some more detail), and of course the PostgreSQL docs about upgrading where this is still the first method described (and obviously prefered).


Is it possible to specify Active Directory username and password in connection string?

You can have your app impersonate a windows user and do the connection that way. Example for ASP.net app.


Hash Match in Query has high cost and high number of excecutions

If it used to run in 30 seconds, that means that something changed: you have to identify what that something is and fix it.

That something is most probably the query execution plan. There must be a better execution plan than the one that you're getting now, but the optimizer doesn't feel like giving you that plan any more. The reason can be only one: outdated statistics.

Statistics become stale when you modify data in your tables and from time to time you need to update them. SQL Server will try to update statistics automatically when the number of changed rows exceeds a threshold that can be either static (20%) or adaptive (needs TF 2371 turned on). Either way, it's impossible to control the number of rows that SQL Server will scan to compile the statistics. This is particularly problematic when the distribution of the data is not uniform and you have skewed data. In that case, you'd better disable auto update statistics for the particular table/index and make sure that you update your statistics manually with FULLSCAN.

Another possible reason for getting an inefficient plan is parameter sniffing, but the reasoning behind is the same: your plan gets compiled for a non representative set of parameters, that determine  a performance nightmare when the plan is compiled for a more representative set of parameters. In this case, OPTIMIZE FOR hints might help, but usually fixing the stats is a better idea.

That said, if it used to run in 30 seconds you might still have the efficient plan lying around somewhere in the cache: try to find it in sys.dm_exec_cached_plans and compare it with the plan that you are getting now.


Is the PostgreSQL 'connection limit' shared across all users in a role?

The CONNECTION LIMIT attribute only affects roles that are able to log in (that is "users", or those roles which have the LOGIN attribute set).

Roles that are a member of another role with a CONNECTION LIMIT do not inherit that limit, so in the situation you describe above fred and ginger would have the default connection limit of -1 (unlimited).

To limit fred and ginger you would need to specifically set a CONNECTION LIMIT on those roles.


Data does not have to be memory-resident in NDB.  (That limitation existed long ago.)  So, there is much more than 4GB limitation per pair of servers.


Export a very big amount of files from a table

You can use the ORA_HASH function to split the table data in distinct groups. Look at the modified SELECT-FROM-MY_TABLE-clause. Run this script in three different sqlplus sessions and measure the number of files per seconds that are extracted.

script 0:

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_name      VARCHAR2(50);
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_count     INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  for x in (SELECT * FROM MY_TABLE where ora_hash(rowid,2,123)=0) loop
        l_blob_len := DBMS_LOB.getlength(x.GIF);
        l_file := UTL_FILE.fopen('BLOBS',x.ID||'-'||l_count||'.gif','wb', 32767);
        l_pos := 1;
        WHILE l_pos < l_blob_len LOOP
            DBMS_LOB.read(x.GIF, l_amount, l_pos, l_buffer);
            UTL_FILE.put_raw(l_file, l_buffer, TRUE);
            l_pos := l_pos + l_amount;
        END LOOP;
        UTL_FILE.fclose(l_file);
        l_count := l_count + 1;
  end loop;

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

script 1:

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_name      VARCHAR2(50);
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_count     INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  for x in (SELECT * FROM MY_TABLE where ora_hash(rowid,2,123)=1) loop
        l_blob_len := DBMS_LOB.getlength(x.GIF);
        l_file := UTL_FILE.fopen('BLOBS',x.ID||'-'||l_count||'.gif','wb', 32767);
        l_pos := 1;
        WHILE l_pos < l_blob_len LOOP
            DBMS_LOB.read(x.GIF, l_amount, l_pos, l_buffer);
            UTL_FILE.put_raw(l_file, l_buffer, TRUE);
            l_pos := l_pos + l_amount;
        END LOOP;
        UTL_FILE.fclose(l_file);
        l_count := l_count + 1;
  end loop;

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

script 2:

DECLARE
  l_file      UTL_FILE.FILE_TYPE;
  l_name      VARCHAR2(50);
  l_buffer    RAW(32767);
  l_amount    BINARY_INTEGER := 32767;
  l_pos       INTEGER := 1;
  l_count     INTEGER := 1;
  l_blob_len  INTEGER;
BEGIN
  for x in (SELECT * FROM MY_TABLE where ora_hash(rowid,2,123)=2) loop
        l_blob_len := DBMS_LOB.getlength(x.GIF);
        l_file := UTL_FILE.fopen('BLOBS',x.ID||'-'||l_count||'.gif','wb', 32767);
        l_pos := 1;
        WHILE l_pos < l_blob_len LOOP
            DBMS_LOB.read(x.GIF, l_amount, l_pos, l_buffer);
            UTL_FILE.put_raw(l_file, l_buffer, TRUE);
            l_pos := l_pos + l_amount;
        END LOOP;
        UTL_FILE.fclose(l_file);
        l_count := l_count + 1;
  end loop;

EXCEPTION
  WHEN OTHERS THEN
    IF UTL_FILE.is_open(l_file) THEN
      UTL_FILE.fclose(l_file);
    END IF;
    RAISE;
END;
/

If this works you can try to generate and start  more scripts.


Update selected data from same table column as currency

You really want to store data in fields of the correct Data Type.
If these values are monetary amounts, then hold them in a suitable Data Type, like Currency.

Any time you convert data from one type to another, you're make extra work for the database engine, having to do the conversion, and running the risk of losing the benefit on any Indexes you might have defined, because calling a function on a field in every row generally forces the database to Table Scan that table (unless you have function-based Indexes, but that's another story).

Using the "wrong" Data Type also means that some functionality "doesn't work" properly.  If you're storing Numerical values as Strings, then comparing these values "goes all wrong".

For example, in the Numeric World ...

10 > 2  =>  TRUE

... as you'd expect, but in the Character world ...

'10' > '2'  =>  FALSE


Cannot delete domain login from SQL SERVER 2012

Check to see if they own the endpoint itself:

SELECT SUSER_NAME(principal_id) AS endpoint_owner ,name AS endpoint_name
FROM sys.database_mirroring_endpoints;

If so, you'll need to change the endpoint owner. Say the endpoint is called Mirroring, and you want to change the owner to SA:

ALTER AUTHORIZATION ON ENDPOINT::Mirroring TO sa;


Query to extracting the 14th date from a date after excluding the sunday and saturday in Postgresql

WITH cand AS (
   SELECT DATE '2020-01-30' - i AS d
   FROM generate_series(1, 2*14+3) AS i
)
SELECT d
FROM cand
WHERE extract(isodow FROM d::timestamp) < 6
ORDER BY d DESC
OFFSET 13
LIMIT 1;

The 2*14+3 in the CTE is a safe upper limit and can be improved.


Can I change database compatibility level from 80 to 90 or higher with log shipping?

It's quite easy to change it back and forth. But you will have to test the performance. Here's a post on how to change it in SSMS.


For that you can use user defined variables
SELECT
    t.series AS week,
    @x:=(SELECT
            COUNT(*)
        FROM
            t1
                INNER JOIN
            t2 ON t2.id = t1.another_id
        WHERE
            t2.something = 1
                AND t1.date BETWEEN t.series AND t.series + INTERVAL 6 DAY) X,
    @y:=(SELECT
            COUNT(*)
        FROM
            t1
                INNER JOIN
            t2 ON t2.id = t1.another_id
        WHERE
            t2.something = 1
                AND t1.date BETWEEN t.series AND t.series + INTERVAL 6 DAY
                AND t1.some_state = 'x state') Y,
    ROUND((@x * 100) / @y, 2) Z
FROM
    series_tmp t

If you want to use your aliases, MySQL allows you only to use those in ORDER BY , GROUP BY and HAVING
As exampple you can use ORDER BY X DESC,Y ASC or HAVING X > 0.5


Does MySQL allows incoming queries while altering a table?

During a DDL operation (e.g. alter table) MySQL locks only the objects in question - in this case the table.

Other tables and databases remain unaffected and new connections are accepted as usual.

On a slave, the DDL operation can't be executed in parallel, so it will delay the replication by whatever time it takes to execute.

The alter operation can increase the general server load CPU/MEM/IO, which can cause it to run out of resources. This rare (with a server with at least a few cores), and pretty much impossible with an empty table, because the alter takes < 1 sec.

The only way a DDL directly affects other connections is if they use the table/s which are modified.


Cannot connect to SQL Server from Management Studio (both 2012)

With a default instance,  specify only the host name when connecting.  As a shorthand when connecting locally, specify only ..


How to get first value from list of value

You can use the DISTINCT ON of SELECT as in the documentation

SELECT DISTINCT ON (id)
  id, col1, col2
FROM TABLE
ORDER BY id, col1, col2

The difference between SELECT DISTINCT <columns> and SELECT DISTINCT ON (<columns>) <columns> is that the first gives you unique rows across all selected columns .. the second gives you one unique row per column set defined within the parenthesis, but allows for additional columns to be returned.

To specify WHICH additional data is returned, you need to ORDER BY..  DISTINCT will then return the first row for each DISTINCT ON set of columns.


Hardening further mysql with sqlmap suggestion

There isn't much you can do to "improvise", you need to fix the issue at hand: you're vulnerable to SQL injections.

The most straightforward solution to this is to implement parameterized queries in your code. This will involve changing all the queries manually, but will protect you.

You can read up on it from the article What's the Right Way to Prevent SQL Injection in PHP, but here's an excerpt from the article:

  For example, consider the following login script:

  $sql = "SELECT * FROM UserTbl WHERE Username = '$username' and Password = '$password'";

  Let’s use the same script, but I’ll define the SQL query with parameter placeholders:

  $sql = "SELECT * FROM UserTbl WHERE Username = ? and Password = ?";

  Now, I’ll define an array that holds the parameter values:

  $params = array($_POST['Username’], $_POST['Password’]);

  When I execute the query, I pass the $params array as an argument:

  $stmt = sqlsrv_query($conn, $sql, $params);


which command to execute to run multiple OLAP queries?

You can use i.e. GNU Parallel which provides possibility to run arbitrary commands/programs at the same time, the syntax would be something like:

parallel psql -h localhost -d databasename -U user -p port -a -q -f input{}.sql -o output{}.txt ::: 1 2

this command will execute 2 queries located in input1.sql and input2.sql at the same time.

Another option would be going for a dedicated load testing tool like Apache JMeter, if you add Postgres JDBC Driver to JMeter Classpath you will be able to provide your connection details under JDBC Connection Configuration and the query(ies) in the JDBC Request Samplers. Check out The Real Secret to Building a Database Test Plan With JMeter article for more details if needed.

Then you can increase the number of threads in Thread Group and JMeter will execute the queries according to your workload model. You will be able to observe performance metrics either using Listeners or via HTML Reporting Dashboard. This way you will get more comprehensive results and be able to correlate increasing number of concurrent threads with increased queries execution time, identify saturation points, bottlenecks, etc.


It should work ok. Try using a view for your filter instead. See this article here


Choose Install database software only, then later you can upgrade your databases one by one with DBUA (Database Upgrade Assistant) from the new Oracle Home, or by performing a manual upgrade.


Using a data warehouse staging database as a source of operational data

There is a forum discussion which discusses this briefly, but quite well IMO: Tek Tips - Using a data warehouse as a source system
Some points for consideration:
Duplication of effort / Single source of the truth
Will the operational system need to apply the same logic to the source data that the data warehouse is already performing?
Read-only source
Does the operational system expect to write changes/updates back to the warehouse?
Timely information
Is the operational system happy with the latency of the warehouse? (Generally T-1)
Service-level agreement
What is the impact to the operational system if there is an outage to the warehouse? In my experience warehouses, inherently, have a lower priority than transactional systems and may have, for example, up to 24 hours to become available (for querying) and up to 4 days for the ETL to be restored, running, and for the warehouse to be up to date.
If the operational system is internal and non-critical, this may be acceptable. If it is customer-facing, and retrieving FX rates from the warehouse for pricing, probably not.
I think the quote from that forum post that sums it up nicely is:

The business drives the technical solution with their requirements.

Your role would be to provide the facts to the business. If the business is willing to accept the risks and proceed with using the data warehouse as a source for an operational system, then I suggest you get that in writing and triplicate.
A "best-of-both-worlds" solution would be for the warehouse to publish the data once processed for the operational system to consume. The data could be extracted to a file or replicated to another/the-operational-system's database. This assumes that your warehouse is not "real-time".
I must admit that I get the heeby-jeebies whenever someone suggests connecting an operational system to our warehouse. Within our environment, we made the architectural decision that we would not control how users could consume the data, provided it does not unfairly impact our ETL processes or other users. An operational system becomes another "user query", and as such we provide the same level of service wrt to availability and accuracy as we do to Joe Bloggs, the junior analyst in Finance.
If a user requires a higher level of service, then we provide the data (via FTP'd files) rather than the user pulling the data (via queries/direct access). This assists in impact analysis for future changes because the extracts are visible within our ETL tool/suite.


Licensing of Oracle Standard One multiple homes

The correct answer for licensing questions is: contact your oracle sales representative.

With Oracle you license cpu's. If your test and prod run on the same cpu's, they don't need a new license. As long as it all runs on the same cpu's, it does not matter if you have multiple databases (and instances) or run it all in one single database. Normally we don't mix test and prod in the same database so at least make sure to create separate databases for test and prod.

If you run this on the same machine, it could be wise to run test using a test account and prod database using a prod account. This also requires 2 separate ORACLE_HOME's on that single machine but this does give you some control about the separation of your environments.


Highly recommend to use latest SSMS.

You can download the latest version of SSMS  from https://msdn.microsoft.com/en-us/library/mt238290.aspx

Previous versions are https://msdn.microsoft.com/en-us/library/mt238488.aspx


Why does my parallel query sometimes process billions of extra rows?

Let's start by discussing why the fast query processes a little more than exactly 20 million rows. Here's a screenshot:

In my testing, the final nested loop operator running with MAXDOP 4 always generates 20008861 rows before the rows are reduced to exactly 20 million by the TOP operator. This is because parallel queries in SQL Server work with packets of data (it's the "PACKET" in the common CXPACKET wait event). We can see evidence of this by running a similar query that returns just 100 rows and with trace flag 8649 to force a parallel plan:

SELECT TOP (100)
  ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
, 100000 + ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM master..spt_values t1
CROSS JOIN master..spt_values t2
CROSS JOIN master..spt_values t3
OPTION (QUERYTRACEON 8649);

Most of the time I see around 9000 total rows processed:

The rows processed by each thread are usually close to multiples of 1023. So perhaps the packet size for this query is 1023? If threads are working with packets of data and the other threads are already in the process of getting more data when we finally get the last row, it seems reasonable that we could see more rows processed than strictly necessary due to parallelism.

For another way to analyze the packet size we can reduce the MAXDOP of the query. With a row goal of 100000 I consistently see 108442 rows processed by the nested loop join. The number of rows processed by each thread can change but the overall total was always the same in my tests. Changing MAXDOP to 3 reduces the number of rows processed to 106395. Change MAXDOP to 2 reduces the number of rows processed to 104348. For this query and a few other similar ones, I always observed a reduction of 2047 rows per DOP. Suspiciously this is nearly double the value from before, 1023. I'm not sure why it's not consistent, but at the very least we can conclude that we process more extra rows with more DOP.

That was somewhat educational but it doesn't explain why the query sometimes processes billions of rows. To get insight into that we can use the sys.dm_exec_query_profiles dmv introduced in SQL Server 2014. Here's a snapshot of some of the rows from dmv during one of the slow runs:

Here's the query plan with the relevant nodes labeled to give some context to the data:

There are a few interesting things to note here. We've inserted 19997604 rows into the table, so the query is very close to done. Threads 1-3 for the nested loop join at node_id 8 have finished all of their work. The TOP operator at node 4 has already met its row goal of 20000000 rows and has closed. However, thread 4 for the nested loop join continues to fetch rows. Those rows aren't being sent anywhere according to the last_row_time column. Also, the parallelism node at node 3 is still open. If I let the query run nothing changes except the row count for thread 4:

Eventually it will run out of packets to send and the query will finish.

It seems as if the problem is directly related to parallelism. The query gets "stuck" at node 3 in the plan, which is the distribute steams operator for the parallel insert. SQL Server 2016 supports parallel inserts into existing heap tables. SQL Server 2014 does not, so this could explain why I couldn't reproduce the issue with a lower compatibility level. So perhaps the problem is related to the parallel insert.

The most obvious workaround is to make the entire query run serially. However, suppose I still need part of the query to run in parallel for performance reasons. Parallel insert requires a TABLOCK hint. If I remove the TABLOCK hint I'm unable to reproduce the issue, but I also lose minimal logging. If I don't want to lose minimal logging I can set trace flag 9495 at the global level. It's important to also include a NORECOMPILE hint or to wipe the plan from the cache because cached plans can cause parallel inserts even with trace flag 9495 enabled. An alternative to the trace flag is to something else which prevents parallel inserts, such as adding a nonclustered index to the table. Any index will do, including a filtered one that doesn't actually contain any rows:

CREATE INDEX X_DUMMY_INDEX ON dbo.X_HEAP (ID) WHERE (ID = -1 AND ID = -2);

In summary, I can't say for sure why the query sometimes processes billions of rows but it definitely seems like unintended behavior. Perhaps it is some kind of parallelism-related race condition. For this query disabling parallel insert seems sufficient to prevent the bug from reappearing.


The server principal "loginname" is not able to access the database "SSISDB" under the current security context

"EXECUTE AS" works by default only within one database.

You can overcome this by enabling cross-database ownership chaining. But this implies security issues!

Please have a look at books online: https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-cross-database-access-in-sql-server

An alternative solution is to move the calling procedure into the SSIS database and grant the VB script user execution permission on that particular procedure. Within that procedure you can then use "EXECUTE AS" to call the second procedure with the privileges needed.


Column names - (created, modified) vs. (creation, modification)

Of those two options creation/modification as that is less ambiguous.

Looking at some SQL or documentation for the structure that doesn't include column type information, created/modified could be mistaken for boolean values.

Furthermore, I'd be inclined to be more specific and use creationTime/modificationTime or createdTime/modifiedTime, probably the latter because it is vaguely quicker to type.


How to resynchronize replica in Postgresql

Since you deleted from pg_xlog, not from the archive, your first concern should be that you might lose the master, not just the replica.

Cross your fingers, and immediately perform a checkpoint on the master.

If you get into the situation again where the archive process can't keep up and is threatening to fill up all space on the pg_xlog partition, rather than manually deleting from pg_xlog you should instead change archive_command to 'true'.  (Or change it to copy to somewhere else which is faster than its current setting is, but still preserves the files.  Make sure to then manually copy those files to their final, slow, destination once the emergency is over.)

As for saving the replica, please answer my other two questions, preferably by editing the question to incorporate that information.


That one query can be optimized with a composite index: INDEX(SuministrosId, Fecha). Let's see some more. (Meanwhile, that pie chart is useless, it rarely says anything other than "sending data", and that gives no clues.)

Also, it may help to set innodb_buffer_pool_size to about 20G assuming you are using 64-bit OS and MySQL.


Unable to start SQL Server Browser service in Sql server 2005

Check if another process has claimed port 1434:

netstat -ano | findstr 1434

This will give you the process id of the offending process, which you can then identify:

tasklist | findstr /c:"<process id>"


AWS RDS Showing CPU spikes on a MySQL system every 5 minutes -- how can I track the cause?

See the slow query list in mysql. Using that you could track which one is the culprit and taking more time to respond.


Changing IP address of SQL Server 2008 R2

Changing the IP Address for a machine running SQL Server is not a problem at all.  You'll need to restart the SQL Server after changing the IP Address so that the SQL Server will start listening on the new IP address.  But that's it.

If no users actually connect to the machine then there's nothing else to do.

If users did connect to the machine they'd need to refresh their DNS cache on their workstations by running "ipconfig /flushdns" or by simply restarting their computers.

Unless you've done something very unusual with the TCP listeners within SQL there's nothing else that needs to be done with SQL Server other than the quick restart.


How to get when MySQL instance went down last

The file mysql.log shows startup and shutdown.

SHOW VARIABLE LIKE 'log_error'; says where the file is.  However, also check SHOW VARIABLE LIKE 'log_output';.  (I don't know if this log is controlled by that.)

SHOW STATUS LIKE 'Uptime'; says how many seconds it has been up.


connecting to an oracle sql database from sql developer on the same computer. When the computer is a domain computer

The last number of the log entry is the return code of the operation. On a successful connection, it is 0. In your case, it is 12505.

12505, 00000, "TNS:listener does not currently know of SID given in connect descriptor"
// *Cause:  The listener received a request to establish a connection to a
// database or other service. The connect descriptor received by the listener
// specified a SID for an instance (usually a database instance) that either
// has not yet dynamically registered with the listener or has not been
// statically configured for the listener. This may be a temporary condition
// such as after the listener has started, but before the database instance
// has registered with the listener.
// *Action:
//  - Wait a moment and try to connect a second time.
//  - Check which instances are currently known by the listener by executing:
//    lsnrctl services <listener name>
//  - Check that the SID parameter in the connect descriptor specifies
//    an instance known by the listener.
//  - Check for an event in the listener.log file.

This simply means, you provided an incorrect SID.

xe is the default SID in SQL Developer.

Check the correct SID from the output of lsnrctl status, and use that for connecting.

Or even better, let's just finally forget the SID, and use SERVICE_NAME.

Another possibility is that your database instance was not started, or you do not even have a database created yet.


Saving into a VARCHAR(MAX) throwing error in some scenarios

It would greatly help to know:

the current Collation of the VARCHAR(MAX) column
the exact string length (in bytes) at which you start seeing the error
What software is reporting this error?
Does the error occur if only a single Unicode character is present?
Does a 10,000 character string with no Unicode characters get inserted correctly?
For a short-enough string that gets inserted without error yet contains one of these Unicode characters, what value exactly is in the table? You can check by converting the column to VARBINARY(MAX). Did the Unicode character get translated or something else?

It is possible that the string that errors is just over a 4000 or 8000 character limit that then requires using a MAX type and the driver is initially guessing (or being told) that it is one size and then finding out that it is another. The error message says "invalid precision value" which has to be referring to how the column is being configured (i.e. similar to setting up a SqlParameter in .NET and declaring the max size, such as new SqlParameter("@name", SqlDbType.VarChar, 8000)). So this type of error should only really happen if the software is attempting to set up the column as VARCHAR of over 8000 or NVARCHAR of over 4000, as either of those conditions would be an invalid "precision". Now, in order to get such a number I suspect that some code in there is counting the number of bytes (i.e. DATALENGTH ) of the string, which will be larger than the number of characters. Of course, this would be true of any character in a .NET string or C++ wchar, so I am suspecting that the difference with having some Unicode characters vs not having any is that without any Unicode characters it can convert the string to an 8-bit encoding (i.e. single byte for use with VARCHAR), but somehow having a Unicode character prevents that. It's a long-shot (until there is more info provided), but it is pretty clear where the error is occurring.

  If I just insert the character alone it won't cause an issue.

How are you inserting the character? Through the same software that is generating the error, or in SSMS? If you are doing this manually via an INSERT statement, then that isn't a good test as SQL Server converts U+3000 into a regular space, U+0020 (one byte), instead of two questions marks (still 2 bytes, as would happen if there was no equivalent character):

SELECT N'-' + NCHAR(0x3000) + N'-',
       CONVERT(VARCHAR(MAX), N'-' + NCHAR(0x3000) + N'-'),
       CONVERT(VARBINARY(MAX), CONVERT(VARCHAR(MAX), N'-' + NCHAR(0x3000) + N'-'));

returns:

-　-    - -    0x2D202D

If possible, trying passing in both of the strings generated by the following code via the software, not via SSMS:

DECLARE @String4k NVARCHAR(MAX);
SET @String4k = REPLICATE(N'a', 3999) + NCHAR(0x3000);
SELECT LEN(@String4k);

DECLARE @String8k NVARCHAR(MAX);
SET @String8k = REPLICATE(CONVERT(NVARCHAR(MAX), N'a'), 7999);
SET @String8k += NCHAR(0x3000);
SELECT LEN(@String8k);

The @String4k string is 3999 characters that can convert cleanly into an 8-bit encoding (i.e. VARCHAR) plus the U+3000 character that will likely remain as 2 bytes. So maybe this comes across, through that software, as 4001 characters. I doubt that this is the issue, but can't hurt to test.

The @String8k string is 7999 characters that can convert cleanly into an 8-bit encoding (i.e. VARCHAR) plus the U+3000 character that will likely remain as 2 bytes. So maybe this comes across, through that software, as 8001 characters.


CDC isn't really designed as a way to keep a reporting database up to date, it's more for tracking changes.

What exactly is the end goal here, are you just going to be taking some select tables\data off to another server to report against or are you looking for a way to take an entire database?

You've got a couple of options, if you're just interested in a handful of tables then you probably want to look at replication.  Replication doesn't really need much infrastructure, just another SQL Server to replicate to.

If you want to have a complete copy of the database for reporting purposes, the easiest option that you've got is log shipping although be aware that the reporting database will be unavailable while the log shipping process plays in the logs.

Another option that you've got is to use an availability group with a secondary node acting as a read only reporting copy.


“SQL command” vs “Table or view” when importing entire tables

At one point in the product's past, selecting the table name from the drop down versus writing an explicit SELECT * FROM MyTable resulted in one fewer operation being performed. The table name selection did...something I can't reproduce now. It'd issue a command like though that resulted in a SELECT * but with an outer wrapper around it.

Profiler reports for 2014, it issued the command select * from [SB].[EventLog] A 2005/2008 instance would have indicated it did something like sp_somethingweird [SB].[EventLog]


Difference between "Field Specification" and "Field Domain"

Attribute domain defines the range of acceptable values for an attribute at the logical model level, e.g. "an arbitrary string representing a person's name", "a non-negative integer", "a boolean (true or false) value" etc.

Column specification defines SQL properties of a database column that is used to store the corresponding attribute values in an SQL database (i.e. at the physical model level). Sometimes they are more restrictive than the attribute specification, e.g. you must specify the maximum length of a character column, although the attribute domain may not prescribe it). Sometimes they are less restrictive, for example you cannot represent a "non-negative integer" by an SQL INTEGER data type without additional constraints. Some column specifications, such as input mask or display format, have nothing at all to do with the domain specification and exist solely as technical implementation details of the application in question.

I prefer not to use the term "field" when discussing relational databases.  Relational tables have rows and columns. Using "fields" and "columns" interchangeably often brings confusion. Input elements of a user interface or components of a sequential file records can be referred to as "fields".


Configuration file missing after successful SSRS migration

Everything is working fine, including the encryption and all the security,
so as the file is not there, I will not need to include it on my backups.

this link below shows where all the configuration files should be:

Reporting Services Configuration Files

  Install Paths   C:\Program Files\Microsoft SQL
  Server\MSRSxx.MSSQLSERVER  (where xx is the MS SQL version number)
  or   C:\Program Files\Microsoft SQL Server Reporting Services\SSRS
  depending on the SSRS version

There it also explains the role of the RSMgrPolicy.config file.

  Stores the code access security policies for the web portal. For more
  information about this file,
  see
  Using Reporting Services Security Policy Files.

Looking at this second link, that explains the Security policy, I understood that the file RSMgrPolicy.config was not needed in my setup. It was not clear in the first documentation but all is good now.


Oracle 10g slow performance on first query

The usual reason for such issues is that the first execution of the query requires physical reads to fetch the required blocks from disk, and the second and subsequent executions find that they are already cached.

You can test this by running the queries from SQL*Plus (and timing them) prior to first opening these windows of which you speak, to see if that affects performance.

If you can come back with comparison of the timings when running the queries through SQL*Plus and then through the application it would help further analysis.


How to check which table is called which trigger in postgresql?

You need to join that to pg_class:

SELECT trg.tgname,
       ns.nspname||'.'||tbl.relname as trigger_table
FROM pg_trigger trg
 JOIN pg_class tbl on trg.tgrelid = tbl.oid
 JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
WHERE NOT tgisinternal


Use the DateTimeOffset datatype.  Each server would store the data locally in its own timezone, storing the offset from UTC as well.  When data is gathered to a central area, you can easily convert to UTC for comparison purposes.


How to check if a subquery has exactly one distinct result and a specified value concisely?

Concise, fast (especially with many rows), my favorite concerning readability and would work with dupes, too:

SELECT count(*) = 1 AND min(val) = 1 FROM foo;

Returns TRUE / FALSE .. or NULL - only in the case of exactly one row with val IS NULL, because count() never returns NULL or no row.

The second 1 in the example just happens to be the same as the first, because of your example.

The query in the question fails with NULL values. Consider the simple demo:

CREATE TABLE foo (id int, val int);
INSERT INTO foo VALUES (1, 1),(2, NULL);

SELECT 'yes'
WHERE      EXISTS(SELECT * FROM foo WHERE val =  1)
AND    NOT EXISTS(SELECT * FROM foo WHERE val <> 1);

IS DISTINCT FROM would fix this, but it could still fail with duplicates in val - which you have ruled out for this case.

Your answer works fine.
Returns 'yes' / no row.

I would prefer this shorter form, though. Don't forget that PostgreSQL (unlike Oracle) has a proper boolean type.

SELECT array_agg(val) = array[1] FROM foo;

Returns TRUE / FALSE / NULL.


Best way for naming schemas in SQL Server 2019

My personal opinion is that it is not a good idea. I tend to think of the schema as a namespace, to logically group related things together.

Versioning should be handled (IMO) no different from other code, i.e. via a source control system. Changes to the data model should be handled via upgrades/migrations.

That aside, do you make a new copy of every procedure function when you change one procedure, or do you have an individual "latest schema" for every object? How does your code figure out what is the latest version of a particular procedure?


Update matching keywords from first table to second table in SQL

Refer the below solution for the Primary Keyword.

create table Kwd_UploadRecored(ID int, Primary_Kwd varchar(200),Sec_Kwd varchar(200))
insert into Kwd_UploadRecored values
(1,'Man,One Man,architecture,Boy','Arrival,Sigle Man , Business Man ,Male'),
(2,'Woman,attire,Girl','Girl,Girls,Female,Blueprint,Carrying,'),
(3,'Grand father,Man,caucasian appearance','cheerful, Family,Fatherhood,Family Member,'),
(4,'Baby ,clothes,color image','growth,Babies,Child,Happiness')

create table Englishgermankwds_tbl([English Keywords] varchar(50), [German Keywords] varchar(50))
insert into Englishgermankwds_tbl values
('architecture','Architektur'),
('Arrival','Ankunft'),
('clothes','Kleidung')

Query

;WITH ctePrimary AS (
    SELECT ID,CAST('<t>' + REPLACE(Primary_Kwd, ',', '</t><t>') + '</t>' AS XML) AS p
    FROM Kwd_UploadRecored
), ctePrimary1 AS (
    SELECT ID, x.value('.','VARCHAR(20)') AS Primary_Kwd
    FROM ctePrimary CROSS APPLY p.nodes('//t') a(x)
), ctePrimary2 AS ( --get German keyword if exist else English/Primary keyword
    SELECT A.ID,CASE
    WHEN B.[German Keywords] IS NULL THEN A.Primary_Kwd ELSE B.[German Keywords]
    END [German Keywords]
    FROM ctePrimary1 A LEFT OUTER JOIN Englishgermankwds_tbl B ON A.Primary_Kwd=B.[English Keywords]
), ctePrimaryGroup AS (
    SELECT ID,
       Stuff((SELECT ',' + [German Keywords]
              FROM   ctePrimary2
              WHERE  ID = a.ID
              FOR xml path('')), 1, 1, '') [German Keywords]
    FROM   ctePrimary2 a
    GROUP  BY  ID

)
Update Kwd_UploadRecored set Primary_Kwd = [German Keywords]
from Kwd_UploadRecored a inner join ctePrimaryGroup b on a.id=b.id


delete WSFC 2012 cluster role/group fails

As a quick workaround ended up deleting whole cluster:

PS C:\Windows\system32> Import-Module FailoverClusters
PS C:\Windows\system32> clear-clusternode
Clear-ClusterNode
Are you sure you want to forcefully clean up cluster node SRVGNPW049?
[Y] Yes  [N] No  [S] Suspend  [?] Help (default is "Y"): Y
PS C:\Windows\system32>

And then recreating a new one.


Microsoft SQL Server 2008 - Catching user role changes with SQL Server Profiler?

The "Security Audit>Audit Add Login to Server Role Event" will capture role drops as well, not just role adds.  However, that one alone might not give you the information you're looking for, depending on what you need (such as the specific statement executed if necessary).  So you could also add the Stored Procedure>RPC: Completed event, and if you want to get really granular add the SP: StmtCompleted as well.

An alternative could also be to use a server-level DDL trigger.  I use a trigger on the ADD_SERVER_ROLE_MEMBER event so I can catch if one of my admins decides to throw someone into the sysadmin role without my knowledge.  In my case I have an admin database with a table designed to store DDL event info and I insert a record for this type of event, among others.  The full list of events you can create a trigger on is here: http://msdn.microsoft.com/en-us/library/bb522542.aspx and there are good basic examples of how to use these events in T-SQL in the trigger body here: http://msdn.microsoft.com/en-us/library/ms175941.aspx


Multiple Update with Multiple Conditions

In MySQL, you can use tuple comparison:

WHERE (TestId, TestSubId) IN ((10,25), (11,22))

That looks nice and succinct, although, as ypercubeᵀᴹ mentioned in a comment, it may not work well performance-wise.

However, given how the conditions are re-used in your UPDATE statement, you could also take a different approach altogether: represent the affected IDs and the new values as a derived table and use an update with a join:

UPDATE
    Tests AS old
    INNER JOIN
    (
        SELECT 10 AS TestId, 25 AS TestSubId, 1000 AS TestScore, 2000 AS TestScore2
        UNION ALL
        SELECT 11, 22, 1100, 2100
    ) AS new
    ON old.TestId = new.TestId AND old.TestSubId = new.TestSubId
SET
    old.TestScore  = new.TestScore,
    old.TestScore2 = new.TestScore2
;

This way the new derived table acts both as a filter for Tests and a supplier of new values for the update.


MySQL non-sequential primary key of certain length

I would suggest that the "right direction" would be to go a different direction entirely.

A sufficiently random and difficult-to-guess distribution of keys, assuming that is even an achievable objective, is likely to be otherwise sub-optimal for space and performance.  But that's the second problem.

The first problem is that exposing the internals of your database in such a fashion is intrinsically unsafe.

Instead, create a separate column for this "PIN" -- of whatever desired length and data type, with a unique key constraint on the column so no more than one record can possibly have the same PIN associated with it.

You can then populate this value using whatever algorithm you want, and you can subsequently change the algorithm, change the min/max lengths of the value, etc., without any other impact on the rest of your application or database as a result of such changes.


Is it harmful to update columns which aren't changed?

Assuming network overhead can be ignored (e.g. you're not updating megabytes of text with the same values), it should not affect performance.  However, if you have triggers for UPDATE with column lists, they will behave differently, depending on whether the column is mentioned as a target of UPDATE.  For instance,

create  trigger test_au after update of t1 on t
    for each row
    execute procedure after_update_t1();

update t set t2=<new value> where key = 180; -- trigger does not fire
update t set t1=<exact the same value as before>,
 t2=<new value> where key = 180; -- trigger does fire

I also tested for HOT updates - no difference in Postgres 10.5. If the value of the indexed column doesn't change, it does a heap only update, at least according to pg_stat_user_tables - the same as if the indexed column doesn't participate in the update at all.


Is it safe to add more than primary data file to Temp DB in SQL server 2008 R2 as we have database server with 4 Core processor?

The article linked by @McNets is a good one.
The One And Only One article has code you can use: https://www.brentozar.com/blitz/tempdb-data-files/

Because you only have 4 cores, you can probably just create a total of 4 TempDB files.

If you use the code snippet in Brent's article, make sure you match up the beginning of the file name section:

FILENAME = N'

with the folder directories you already have.

You don't have to restart the engine if you're not shrinking the the original file while adding these. As for the size, it's not necesaarily a bad thing that it is bigger than your prod database, though it could be indiciative of something. Do you delete a bunch of records out fo there recently?


Sync SQL Server Transaction Logs to Redshift

Nothing exists natively from SQL Server so 3rd party is your best bet. Homegrown is not recommended because SQL Server's transaction log format is not published so it'll be a pretty steep curve.

sp_BlitzErik's suggestion to talk to the vendors is the best place to start. If you don't have the right contacts or not enough vendors to evaluate, check out the RS data integration partners on https://aws.amazon.com/redshift/partners/1

You can also contact AWS directly or post a feature request on the AWS RS forum https://forums.aws.amazon.com/forum.jspa?forumID=155&start=0


The reading with secondaryPreferred when chunk migration occurred often fail

Nowadays, balancer doesn't honor _secondaryThrottle, nor w: when w:<=1. But, this case that w:6 means that when data is removed (range deleter at end of chunk move), we are removing documents one by one AND every document delete must have 6 acknowledge. If moved chunk had 100.000 documents, then there is 100.000 individual deletes with acknowledges. (slooooow)

If your query at secondary hits documents in the range what is under range delete, there will be a collision.


To start with, more interesting than the number of records is the size on disk, which we can then compare to your available memory.

Consider the following settings:

innodb_flush_log_at_trx_commit = 1(2)?

This variable appears twice in your cnf file. You probably meant to give it the value 2, which make for less I/O. But you may loose 1 second's worth of transactions upon failure.

You also have:

innodb_doublewrite             = OFF

Which is risky. True, it will make for less I/O, but may also lead to corruption upon failure. I only set this on servers I can spare and do not mind losing.

Set

query-cache-type               = 0

Enough has been written on the query cache that I'll not repeat here; it is an old technology and does not scale to today's machines.

Do yourself a favour and set

long_query_time = 1

Since the 10 second default is way too high to catch the bad guys.

I know nothing about the usage of your DB, but I'm guessing here that

max-connections                = 15000

is waaaay out of proportion. Will you really have 15000 concurrent connections? If you will, then a 18G for innodb_buffer_pool_size is way too high. Connections have their own memory.

All the above off the top of my head. Certainly need more information about your use case.


SSIS or SQL Server way of sectioning a flat file

using ssis and some c# / vba

add a foreach loop to iterate over the files
create a variable to use for the key (increment on each iteration)
in the loop add a dataflow consisting of

a script transform (source) to read first 40 lines from the file and pivot
a derived column to add the key (from var)
a destination to the table

in the loop add a dataflow consisting of

a script transform (source) skipping the first 40 lines and reading the rest
a derived column to add the key (from var)
a destination to the table

[optional] delete or move the file with a file task

the code for pivoting would read the first 40 lines into an array then just set the output columns mapping to the array's index.

consideration may need to be given to insure the key remains unique on subsequent runs and may need initialising to the max(key) + 1 from exiting data, use an sql task for this.


Why not have Persons for every person in schema?

Many databases are designed exactly this way.  This pattern is known as class/subclass in object modeling, generalization/specialization in ER modeling, and sometimes referred to as type/subtype in other places.

There are several techniques for accomodating class/subclass patterns in table design.  For more details, see the subtypes tag.


Does MySQL reserve memory for MEMORY tables based on max_heap_table_size

max_heap_table_size defines maximum size of table, but mysql do not allocate that in advance.


What is the query that updates the Column in a table

sys.sql_modules isn't going to help you - you won't know which one is causing a specific update, or who's calling it. Here's a start - create a logging table:

CREATE TABLE dbo.UpdateLog
(
  EventType  SYSNAME,
  Parameters INT,
  EventInfo  NVARCHAR(MAX),
  EventDate  DATETIME NOT NULL DEFAULT GETDATE(),
  AppName    SYSNAME  NOT NULL DEFAULT APP_NAME(),
  HostName   SYSNAME  NOT NULL DEFAULT HOST_NAME(),
  LoginName  SYSNAME  NOT NULL DEFAULT SUSER_SNAME()
);

Then create a trigger for updates to the source table:

CREATE TRIGGER dbo.tr_whatever
ON dbo.whatever
WITH EXECUTE AS OWNER
AFTER UPDATE
AS
BEGIN
  SET NOCOUNT ON;
  IF EXISTS
  (
    SELECT 1 FROM inserted AS i
      INNER JOIN deleted AS d
      ON i.id = d.id -- use key column(s) here
      AND i.x <> d.x -- assumes column x is not nullable
  )
  BEGIN
    INSERT dbo.UpdateLog(EventType, Parameters, EventInfo)
      EXEC sys.sp_executesql N'DBCC INPUTBUFFER(@@SPID) WITH NO_INFOMSGS;';
  END
END
GO

Now you'll be able to see not only what query fired the trigger, but also who ran it, when, and from which application.


Not able to restore a backup to SQL Server 2019

Restoring it on SQL MI rather than Azure SQL DB or SQL Server worked perfectly