Changing string in all objects in all databases on one server Is there a way to find all instances of a string in all objects of all databases on one server?

The reason behind this question is that we need to change a reference to another database (located on another server).

So, we need to find every view, stored procedure, function, etc. that has a reference to the old server. This search also needs to include objects that are not specific to a single database, like system jobs.

So far, the best way I know of doing this is to use the Generate Script wizard on each database individualy and then search for the string in the generated files. The problem with this approach is that it ignores system jobs.

Is there a better way of doing this?

UPDATE 1

I looked into the sys.sql_modules table and that looks like a good alternative to the Generate Scripts wizard.

I have a query for one database that works fine by itself

select
'MyDatabaseName' as DbName,
o.name as ObjectName,
o.type_desc as ObjectType,
m.definition as Definition
from MyDatabaseName.sys.sql_modules m
inner join sys.objects o on m.object_id = o.object_id
WHERE m.definition Like '%MyString%'

But when I try to use it with sp_msforeachdb, I don't get the same amount of results.

DECLARE
    @SQL nvarchar(4000)
SET @SQL='select ''?'' as DbName, o.name as ObjectName, o.type_desc as ObjectType, m.definition as Definition from [?].sys.sql_modules m inner join sys.objects o on m.object_id = o.object_id where m.definition like ''%MyString%'''
EXEC sp_msforeachdb @SQL

Even when removing the condition for MyString I still don't get the same number of results (from 267 to 27).

Using msforeachdb would really help me because I need to run this on many databases over many servers.

UPDATE 2

Found the issue in my query, it was missing a [?] in front of the joined sys.objects. Here's the corrected query

DECLARE
    @SQL nvarchar(4000)
SET @SQL='select ''?'' as DbName, o.name as ObjectName, o.type_desc as ObjectType, m.definition as Definition from [?].sys.sql_modules m inner join [?].sys.objects o on m.object_id = o.object_id where m.definition like ''%MyString%'''
EXEC sp_msforeachdb @SQL

sp_msforeachdb is undocumented. There are viable alternatives to it.

Try changing:

SET @SQL='select ''?'' as DbName, o.name as ObjectName, o.type_desc as
 ObjectType, m.definition as Definition from [?].sys.sql_modules m inner join sys.objects o on m.object_id = o.object_id where m.definition like ''%MyString%'''

to:

SET @SQL='USE [?]; select Db_Name(), o.name as ObjectName, o.type_desc as ObjectType, m.definition as Definition from sys.sql_modules m inner join sys.objects o on m.object_id = o.object_id where m.definition like ''%MyString%'''

As originally written, the @SQL's sys.objects will reference the database being used for sp_msforeachdb's execution and thus the statement's 2 object_id values may or may not join.


If I manually failover my AlwaysOn AG primary replica to the secondary and take the primary database offline will it auto-sync when back online? I have an AlwaysOn AG setup between Database A (primary replica) on Server 1 and Database B (secondary replica) on Server 2.

If I force a manual failover from my primary replica on Server 1 to the secondary replica on Server 2 and then take Database A (on Server 1) offline, will it automatically sync back up from Database B (on Server 2) when I bring it back online?

Do I have to wait for the sync to finish before manually failing back over to Database A from Database B?

Can I track it's sync progress in the AG Dashboard?

Note: I took Database A offline without removing it from the AG by stopping the SQL Server service. So I'm wondering what will happen when I start the service again?

This is not possible since database is added to AOAG
You cannot take the db offline unless you remove it from AG

You can see and verify this yourself simply by adding some "TestDB" to your AG and try to take if offline

  Do I have to wait for the sync to finish before manually failing back over to Database A from Database B?

Yes you have to wait for sync to complete in any scenario, if you do not want to lose any data


How to execute SQL Server query in SSMS using PowerShell I have a script file in my local machine. I have to open SSMS and open the query in SSMS and to execute the query.

For opening the SSMS and query, i have used the following PowerShell script to do it.

ssms.exe 'E:\PowerShell_PROD\Screenshot\ServerDetails.sql' -E -S localhost

How can i run this query using PowerShell and how can i close this query window?

The reason I'm doing this is I have to take the evidence of the SQL Server properties and other information. For that I have a PowerShell script to capture the screenshot.

I'm preparing the PowerShell script that runs every query in SSMS and captures the outcome of that and save into specified path. Now am able to open the query in SSMS and capture the screenshot, but I'm unable to run the query.

You probably shouldn't do this, but it's possible to simulate user activity by using SendKeys.

  Sends keystrokes to the active application.

This does what you want:

add-type -AssemblyName System.Windows.Forms

ssms.exe 'c:\temp\test.sql' -E -S localhost

# wait to make sure the application is open
start-sleep -Milliseconds 10000

# press F5
[System.Windows.Forms.SendKeys]::SendWait("{F5}")

# wait for the query to finish
start-sleep -Milliseconds 10000

# press ALT-F4
[System.Windows.Forms.SendKeys]::SendWait("%{F4}")


How to properly stop MySQL server on Mac OS X? I installed MySQL Community Server following instructions at

http://dev.mysql.com/doc/refman/5.0/en/macosx-installation.html

with the Startup Item and the MySQL Preference Pane.

I can stop the server with

$ /usr/local/mysql/bin/mysqladmin -u root -p shutdown
Enter password:
$ ps aux | grep mysqld
.... grep mysqld

Is this the proper way?

Every time when I try to stop the sever using System Preference -> MySQL -> Stop MySQL Server, it hangs.  I will need to force quit the System Preference from the dock.  Any idea why the hang or how to find out?

DISCLAIMER : Not a MacOS user

That is an alternative way. This shutdown method can be done in MacOS, Linux, Windows, any platform MySQL is supported in.

I actually prefer your mysqladmin method for a reason...

In Linux, I have seen the mysql.sock (the socket file) file just up and disappear without warning. The standard way to shutdown mysql in Linux is service mysql stop. Whenever the socket file disappears, the mysqld_safe program cannot shutdown without first seeing in the socket file. Such a standard shutdown would just hang.

I usually connect using TCP/IP

/usr/local/mysql/bin/mysqladmin -u root -h127.0.0.1 -p shutdown

Doing it this way bypasses checking for a socket file. Of course, you must make sure root@127.0.0.1 exists. Run these lines to create that user:

SET SQL_LOG_BIN=0;
GRANT ALL PRIVILEGES ON *.* TO root@'127.0.0.1' IDENTIFIED BY 'whateverpassword';

CAVEAT If you do DESC mysql.user;, you will see that one of the privilege columns is Shutdown_priv. This evidently would permit a user who connects to mysqladmin to issue a shutdown. Users with this privilege cannot issue a shutdown from the mysql client. Users with shutdown_priv='Y' can only be done from mysqladmin.


TRUNCATE in transaction: dead lock In a unittest we run this:

BEGIN;
TRUNCATE table1;
TRUNCATE table2;
...
UPDATE ...;
ROLLBACK;

Unfortunately this results in a dead lock if a cron job starts and this job operates on table1 and table2.

DatabaseError: deadlock detected
DETAIL:  Process 15815 waits for AccessExclusiveLock on relation 291262 of database 290999; blocked by process 16391.
Process 16391 waits for RowShareLock on relation 291431 of database 290999; blocked by process 15815.

Is there a way to isolate the unittest from the cron job (to avoid the dead lock)?

We run PostgreSQL 9.0.10 on x86_64 linux

TRUNCATE should be used only when it is certain that no other session is using the table at the same time. Otherwise DELETE should be used. It's slower (if the number of rows is significant), but it's designed to support concurrent access within the rules of the current isolation mode.

The doc says:

  TRUNCATE acquires an ACCESS EXCLUSIVE lock on each table it operates
  on, which blocks all other concurrent operations on the table. When
  RESTART IDENTITY is specified, any sequences that are to be restarted
  are likewise locked exclusively. If concurrent access to a table is
  required, then the DELETE command should be used instead.

If DELETE's slowness is not acceptable, to avoid the deadlock with 100% certainty, you'd need to put the entire sequence into a critical section, both in the unit test and in the cron job. A critical section is entered by requesting an explicit lock before any other operation, such as in:

BEGIN
 SELECT pg_advisory_xact_lock(1); -- (1 is arbitrary: choose any value reserved for this purpose by your apps)
 TRUNCATE ...;
 TRUNCATE ...;
 ...processing...
ROLLBACK;


In what data type should I store an email address in database? I understand that an 254 character email address is valid, but implementations I have researched tend to use a varchar(60) to varchar(80) or equivalent.  For example: this SQL Server recommendation uses varchar(80) or this Oracle example

Is there a reason to not use the full 254 character maximum?  Doesn't a varchar by definition use only as much storage as needed to hold the data?

Are there significant performance implications/trade-offs which cause so many implementations to use less than the full 254 possible characters?

I've always used VARCHAR(320). Here's why. The standard dictates the following limitations:

64 characters for the "local part" (username).
1 character for the @ symbol.
255 characters for the domain name.

Now, some folks will say you need to support more than that. Some folks will also say that you need to support Unicode for domain names (meaning you have to switch to NVARCHAR). While the standard may change in the meantime (it's been a while since I've had skin in the game), I am quite confident that at this time most servers in the world will not accept Unicode e-mail addresses, and I am sure many servers will have issues creating and/or accepting addresses with > 320 characters.

That said, you can prepare for the worst now, if you like (and if you are using Data Compression in SQL Server 2008 R2 or better, you will benefit from Unicode compression, meaning you only pay the 2 byte penalty for characters that actually need it). This way you can make your column as wide as you want, and you can let people stuff any too-long junk in there that they want - they won't receive an e-mail if they give you junk just like they won't receive an e-mail if the insert fails. The problem is if you let invalid junk in, you have to deal with it. And no matter what size you make it - if someone will try to stuff 400 characters into a 320-character column, someone will try to stuff 1025 characters into a 1024-character column. There is no reason any sensible person should have an e-mail address > 320 characters unless they are using it to explicitly test system boundaries.

But stop asking for opinions on this - and stop looking at other implementations for guidance (it just so happens in this case that the ones you referenced did not bother to do their own homework and just picked numbers out of their, well, you know). You have direct access to the standard - make sure you consult the most current version, support that as a minimum, and stay on top of the standard so you can adapt to changes in specs.

EDIT thanks to @ypercube for the ping in chat.

As an aside, perhaps you don't want to dump the whole address into a single column in the first place. Normalization might suggest that you don't want to store @hotmail.com 15 million times when a much skinnier FK int would work just fine and not have the additional overhead of variable length columns. You could also normalize the username, as john.smith@hotmail.com and john.smith@gmail.com share a common username - they don't know each other but your database doesn't care about that.

I talked about some of this here:

http://www.mssqltips.com/sqlservertip/2657/storing-email-addresses-more-efficiently-in-sql-server/

http://www.mssqltips.com/sqlservertip/2671/storing-email-addresses-more-efficiently-in-sql-server--part-2/

This introduces challenges however to the 254-character limit above, since there doesn't seem to be consensus about what happens when a valid 255-character domain is combined with a valid 1-character localpart. This should be accepted by most servers around the world but seem to violate this 254-character limit. So do you create a Domains table that has an artificially lower restriction on length for e-mail addresses, when the domain could be re-used as a valid 255-character URL?


SSAS tabular browse Excel relationships not working I'm currently building an education data warehouse.

I have three factless fact tables for distinct set of grades due to the fact I have no measures (I'm not aggregating anything), the grades are completely unrelated to each other hence why they're in different tables. I'm only slicing student dimension by grade fact tables.

In Excel, when I drag a student onto a row and then grades into columns, I would expect to see grades for the student.  However, I am just seeing all students/all grades. The relationships do not seem to be working even though I can clearly see the relationships in the tabular diagram view.

Any ideas as to why this is?

As you can see below, SSAS has imported the relationships; however when I browse in Excel there does not seem to be any relationship between the data. If I filter by one row for student and select grades, I just get all grades not just the grades relevant for that student.

If I drop student keys in the row section and grades in the column section, it will just display all/all.  If I change the student key filter to display a few records nothing seems to change, so SSAS is fairly useless to me at the moment.

I think i've found the problem.  SSAS doesn't seem to relate data between tables if there are no measures defined.  I added a simple measure like grade count and voila i have my data relationship.


Creating database with a relative path I am supposed to be testing a piece of software that uses mssql express as the backend for data storage. When it is creating its databases, it tries to create them in my home directory that is on a network share, which does not work. I.e. it does

create database [db] on primary (name=N'db' filename=N'g:\my docs\app\db\db.mdf' ...

and a bunch more of parameters.

I tried to disconnect the computer from the network, then it tried to run

create database [db] on primary (name=N'db' filename=N'\app\db\db.mdf' ...

which also did not work.

Basically my question is then: How can I convince mssql express to use another base directory when creating new files?

The software installer is reading the %userprofile% or %homepath% environment variables to create the user database, you will have to change the installation script or temporary change the environment variables while running the installer.

This has nothing to do with Express itself as it is creating the databases where it's asked to do so by the installation script.
Now if you want to have the database in the home directory you are better off by using SQL Server Express LocalDB. I would consider this software a failure.


How to get the MAX row In SQL Server I've always found it a pain to get the max rows for a dataset, I'm looking for a list of the methods to retrieve the max rows with some guidance on performance and maintainability.

Sample Table:

DECLARE @Test TABLE (ID INT IDENTITY(1,1), name VARCHAR(50),
                     dateOfBirth DATETIME, TaxNumber varchar(10))

INSERT INTO @Test (name, dateOfBirth, TaxNumber)
SELECT 'Fred', convert(datetime, '25/01/1976', 103), '123' UNION ALL
SELECT 'Bob', convert(datetime, '03/03/1976', 103), '234'  UNION ALL
SELECT 'Jane', convert(datetime, '13/06/1996', 103), '345' UNION ALL
SELECT 'Fred', convert(datetime, '14/02/1982', 103), '456' UNION ALL
SELECT 'Bob', convert(datetime, '25/10/1983', 103), '567' UNION ALL
SELECT 'Jane', convert(datetime, '12/04/1995', 103), '678' UNION ALL
SELECT 'Fred', convert(datetime, '03/03/1976', 103), '789'

select * from @Test

Gives:

ID          name      dateOfBirth             TaxNumber
----------- --------- ----------------------- ----------
1           Fred      1976-01-25 00:00:00.000 123
2           Bob       1976-03-03 00:00:00.000 234
3           Jane      1996-06-13 00:00:00.000 345
4           Fred      1982-02-14 00:00:00.000 456
5           Bob       1983-10-25 00:00:00.000 567
6           Jane      1995-04-12 00:00:00.000 678
7           Fred      1976-03-03 00:00:00.000 789

If I want to retrieve the oldest person (group by name) full details, what methods can I use?

Desired output:

ID          name      dateOfBirth             TaxNumber
----------- --------- ----------------------- ----------
1           Fred      1976-01-25 00:00:00.000 123
2           Bob       1976-03-03 00:00:00.000 234
6           Jane      1995-04-12 00:00:00.000 678

Two usual methods: aggregate and ranking function.

The aggregate works on SQL Server 2000. Both ways can use a CTE or derived table

For performance, I've found the aggregate works better. However, it looks like SQL Server 2008 ranking functions run far better than on SQL Server 2005. I'm not using SQL Server 2008 day to day yet (large dinsoaur corporate) so can't comment.

There are 2 relevant SO Questions but I can't find them currently. One is a questions about high logical IO with ranking functions, another is testing of ranking in comments over SQL 2k5 vs 2k8. Sorry.

--aggregate + CTE
;WITH cOldest AS
(
    SELECT name, MIN(dateOfBirth) AS MinDOB FROM @Test GROUP BY name
)
SELECT
    T.*
FROM
    @Test T
    JOIN
    cOldest C ON T.name = C.name AND T.dateOfBirth = C.MinDOB
ORDER BY
    T.ID

--aggregate + derived table
SELECT
    T.*
FROM
    @Test T
    JOIN
    (
    SELECT name, MIN(dateOfBirth) AS MinDOB FROM @Test GROUP BY name
    ) C ON T.name = C.name AND T.dateOfBirth = C.MinDOB
ORDER BY
    T.ID

--ranking + CTE
;WITH cOldest AS
(
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY dateOfBirth) AS rnDOB FROM @Test
)
SELECT
    C.*
FROM
    cOldest C
WHERE
    C.rnDOB = 1
ORDER BY
    C.ID

--ranking + derived table
SELECT
    C.*
FROM
    (SELECT *, ROW_NUMBER() OVER (PARTITION BY name ORDER BY dateOfBirth) AS rnDOB FROM @Test) C
WHERE
    C.rnDOB = 1
ORDER BY
    C.ID


Can I setup database triggers to run on a defined secondary set of servers only? This might sound a bit off, but here's what I have been thinking for a while now:
Use Case
You want to build an activity log for each user action on your application using database (postgreSQL) triggers on every relevant table, outputting to an activity_log table.
The triggers should do the trick, but how do we eliminate the burden of every user action triggering an action on the production servers, delaying the whole application?
Purposed Architecture
What I have in mind is a complex structure where one or more secondary postgres nodes would take the entire activity_log trigger activity.
The triggers would be disabled on all primary nodes (the ones the application reads and writes to) and enabled on some/all secondary nodes (let's call them "workers").
Data would be written to a primary server (no trigger runs) and replication would forward it to all other nodes. When a "worker" node get's the data, the triggers process it and update the activity_log.
activity_log table should be replicated across ALL servers which means that a "worker" node should be able to read, write and send selected data upstream.
Is there anything even close to this?
Is this even possible to achieve without having to rewrite a replication model from scratch?

This currently isn't practical. Writes may not be done on a downstream replica, whether it replays WAL via streaming or archives.

However, the logical decoding feature in PostgreSQL 9.4 sets the foundations for making this sort of thing possible. You could have a logical decoding plugin that reads the change-stream from the master server, then writes audit information about it to a separate PostgreSQL master. If you want to query the audit server from the main production server you could use a foreign data wrapper like postgres_fdw.

The team I'm working with is building an asynchronous multi-master system for PostgreSQL named Bi-Directional Replication for PostgreSQL (BDR). At the moment it can't do what you want, but it's built on top of logical decoding, and the sort of thing you describe is something we're looking at adding in a later release of BDR, or its one-way single-master replication variant UDR.

Alternately, you may wish to look at adopting a system like Londiste. Combined with PostgreSQL's ENABLE REPLICA triggers (which only fire on replication writes via session_replication_role = 'replica', not for local writes) you might be able to rig up something like what you want. Note that Londiste doubles the write load on the master, though, as it has to write a replication queue entry as well as the original write. This might be almost as expensive as updating your activity log directly.

Logical decoding avoids this write amplification, which is why I think it'll be the long term solution for what you want to do. Right now using it would involve some non-trivial software development, though.


neo4j taking 12 hours to load I'm loading the panama papers into neo4j using this tutorial,and the last step, loading the edges, has been running for 12 hours. This is the import statement.

USING PERIODIC COMMIT LOAD CSV WITH HEADERS FROM 'file:/path/all_edges.csv' AS csvLine
MATCH (n1 { id: toInt(csvLine.node_1)}),(n2 { id: toInt(csvLine.node_2)})
CREATE (n1)-[:ACCOC {role: csvLine.rel_type}]->(n2)

The edges file is 89MB. Is this normal, or how can I check on the status of this process?

The query itself is problematic.

It's not using labels in the match pattern, which means it will have to perform an AllNodesScan to find both n1 and n2. So that's happening twice per row in your CSV file. An EXPLAIN of the query would have showed you this in the query plan.

You need to add labels into these match patterns for n1 and n2, which would at least get you to two NodeByLabelScans per row, but to make this performant you need to also have an index on the relevant label for the id property.


Bulk percentile calculation in Postgres I'm trying to find the best way to calculate percentiles for a dataset in bulk on Postgres, returning both the percentile and the value.

generate_series() plus percentile_cont() and lateral join will work, though this can be very slow with many percentiles, as percentile_cont() is called for each item in the series:

select percentile, value
from generate_series(0, 1, 0.2) as percentile,
lateral (select percentile_cont(percentile) WITHIN GROUP (ORDER BY SIZE)
  as value from mytable
) values

This is much faster but doesn't return the percentile corresponding to each value:

select unnest(
percentile_cont(
    (select array_agg(s) from generate_series(0, 1, 0.2) as s)
) WITHIN GROUP (ORDER BY SIZE))
from mytable

And trying to join that one with a second generate_series() seems to require an ugly hack to get a column to join on:

select percentile, value
from (select row_number() OVER () as rownum, value
    from (select unnest(
        percentile_cont(
            (select array_agg(s) from generate_series(0, 1, 0.2) as s)
        ) WITHIN GROUP (ORDER BY SIZE)) as value
        from mytable) s2
    ) s3
join (select row_number() OVER () as rownum, percentile
    from (select generate_series(0, 1, 0.2) as percentile) s) p
    on p.rownum = s3.rownum

Or taking another approach, ntiles() makes for a cleaner query, but values don't quite match with percentile_cont(). (Maybe they'd match with percentile_disc()?) And this is about half as fast as 2nd/3rd queries above.

select ((nt-1)/5::float) as percentile,  min(size) as value
from (select size, ntile(6) over (order by size) as nt
   from mytable
 ) as dt
group by nt
order by nt asc

I've gotten this far, but it's still not quite right, and something tells me there's a clean, performant approach out there. Any thoughts on how to improve on these?

EDIT: modifying Sven's answer slightly to return named columns:

SELECT unnest(
  (select array_agg(fraction) from generate_series(0, 1, 0.2) AS fraction)
) as percentile,
unnest(
  (select percentile_cont((select array_agg(s) from generate_series(0, 1, 0.2) as s)) WITHIN GROUP (ORDER BY SIZE) FROM mytable)
) as value;

The following query should get you both the percentile and the value

SELECT * FROM unnest(
  (select array_agg(fraction) from generate_series(0, 1, 0.2) AS fraction),
  (select percentile_cont((select array_agg(s) from generate_series(0, 1, 0.2) as s)) WITHIN GROUP (ORDER BY SIZE) FROM mytable)
);


MySQL Replication : Duplicate entries - SQL_SLAVE_SKIP_COUNTER or delete row? I have a system which replicates one database to four slave servers. Every now and then, when traffic is high, one or more of the slave servers hits a duplicate insert error and the slave stops running.

When this happens, I have two choices - I can either SET GLOBAL SQL_SLAVE_SKIP_COUNTER or I can delete the offending row in the slave. Both seem to work and my logic says that given something happened to cause this problem, there is a possibility that the data in the slave is corrupted. Given that this can only happen on INSERTs, by deleting the row I guarantee the slave data will match the master once replication resumes. By skipping, if the data for that row is corrupted in the slave, it will remain corrupted.

Am I missing anything?

Further, given that this happens once every couple of months on two specific tables, is there any reason I shouldn't automate a process that triggers when this error is encountered, deletes that row in the slave and re-starts the slave?

EDIT: MySQL 5.5.29 and statement replication I believe.

You don't have to use any trigger, If you want to safely skip duplicate key errors, please add this to my.cnf on slaves

[mysqld]
slave-skip-errors=1062

Then restart mysql

Check if all the slaves have different serverIDs


SELECT Causes Performance Issue I have a SQL SELECT statement that has joins on 27 of our warehouse tables.
Original statement was taking more than an hour everyday so I decided to optimize it.

I created some indexes but still it was taking some time. To find out the main culprit, I changed my select statement to SELECT COUNT(*), commented all the joins and started uncommenting the joins one by one.

With some NOLOCKS and more indexes, I managed to bring down the execution time from 65 mins to 6 mins but as soon as I added my columns back in SELECT statement instead of COUNT(*), it started taking more than 70 mins.

So can some one please explain why this is happening and how I can resolve it.

P.S.: I don't have any case statement or any other calculations going on in my select statement. Its purely selecting columns. (around 150 columns)

I'm guessing you don't have any covering indexes for the select columns.  Because your Count(*) runs faster, you probably have the columns in the joins or where clause indexed.  As soon as you add your select columns back, the query engine now has to scan the whole table again.  If possible, add the select columns as included columns to the indexes (not sure if this is ms sql server).


sql*plus 11.2 client connection to oracle timing out on Linux One of our DBAs is seeing connection issues with his sql*plus client. Whenever he connects to the Oracle v10g server, the connection seems to time out for no apparent reason.

We tried checking iptables but we have no state-based rules.

We also have older servers on the same switch so we know it isn't a switch issue. My hypothesis: It's a client issue.

This is kind of a shot in the dark but can anyone suggest some troubleshooting steps to help out? I'll update the question with whatever details you may need to make a recommendation. Just ask in comments.

Update

Per comments:

I tested telneting to the Oracle DB server to port 1521. That worked.
Also, the initial test query works. However, after an hour, when I try the query again I get ORA-03135: connection lost contact. Full error below.

ERROR at line 1:
ORA-03135: connection lost contact
Process ID: 14848
Session ID: 1219 Serial number: 1551

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

SQLNET.EXPIRE_TIME = 10

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


MySQL rows in explain extended keeps increasing while the rows are being deleted continuosly I have a table in Mysql with userid, status and other columns. I keep updating the status of the row as and when the job is complete. Periodically these rows are deleted from the table.
JobTable (userid, status, ...)
   userid_status_index (userid, status)

When I run some queries:
   select count(*) from JobTable where userid="1" and status="ready"
   Result: 20

For explain:
   explain extended select count(*) from JobTable force index (userid_status_index) where userid="1" and status="ready"
   rows: 2500

When I do explain, the number of rows keeps increasing (as I keep inserting, but the deleted rows are probably not removed from the index), but when i do count(*) on the actual table, it shows lesser rows.
After few rows, the DiskIOPS increases and things are back to normal. I suppose this is due to some change buffer update. Can I force it to happen more periodically so that the queries are not slow?
Forcing the index also doesn't help.
Also note that the queries are actually slow when it's run, not just the explain is incorrect.
Mysql 5.6

Is this the real query?
select count(*) from JobTable where userid="1" and status="ready"

If so, here's how it works when you have INDEX(userid, status) or INDEX(status, userid)

It drills down the index's BTree to the first entry with that pair of values.
Count 1.
Scan forward until the pair no longer matches (else back to step 2).

The only thing that can change is the details of the BTree.  INSERTs and DELETEs may lead to block splits and block merges.
Don't trust the numbers in EXPLAIN.
In this case, there is no benefit in using FORCE/USE INDEX; the index to use is too obvious.  (Or do you have a case where it used some other index?  If so, let's see SHOW CREATE TABLE.)
While we are at it, neither ordering in the INDEX is better.  That is, the cardinality of each of the two columns does not matter.
ANALYZE TABLE JobTable will refresh the statistics; it is fast, but imprecise, for InnoDB.


How to load data to a table from SQL Server Audit File? I have enabled Auditing in SQL Server 2008 R2 to monitor DML and DDL operations.

Is there any way to load SQLAudit files to the database? Or any other method to automatically load audit details directly to a database?

The following process has worked well for us.

We write our audit files to a file share.  I have a Sql Server instance dedicated to processing these audit files.  On this SQLAudit instance, I have a job that runs every minute and executes a stored procedure.

The stored procedure:

Uses Powershell to move any audit files (that are not currently being written to) to a staging fileshare.  The powershell command uses the -ErrorAction SilentlyContinue parameter which allows the command to 'skip' over files that are currently being written to when copying to the staging fileshare.  Eventually, the 'locked' files will become available to be copied on a future interval.
Now that I have available SQLAudit files to read from the staging
fileshare, we're able to use sys.fn_get_audit_file to process all
SQLAudit files from the staging fileshare by using wildcards.
After consuming the files from the staging fileshare, I use Powershell to delete the files from the staging fileshare.

Here's the stored procedure.

CREATE PROCEDURE [dbo].[SqlAuditCaptureAuditLogs]
AS
BEGIN
    SET XACT_ABORT ON

    EXEC xp_cmdshell 'powershell.exe "Move-Item \\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\*.sqlaudit \\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\SQLAuditLogs_Staging -ErrorAction SilentlyContinue"'
        ,no_output

    INSERT INTO SQLAUDIT.[dbo].[SQLAUDIT_HISTORY] (
        event_time
        ,sequence_number
        ,action_id
        ,server_principal_name
        ,server_instance_name
        ,database_name
        ,schema_name
        ,object_name
        ,statement
        )
    SELECT event_time
        ,sequence_number
        ,action_id
        ,server_principal_name
        ,server_instance_name
        ,database_name
        ,schema_name
        ,object_name
        ,statement
    FROM sys.fn_get_audit_file('\\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\SQLAuditLogs_Staging\*.*', DEFAULT, DEFAULT)
    WHERE server_principal_name NOT IN (
            ,'mfoperational'
            ,'mfreader'
            ,'aperioapplication'
            ,'aperioreader'
            ,'aperiosync'
            ,'aperiobuild'
            ,'DataFix'
            ,'mapinfo'
            ,'GeoSpatialUpdater'
            ,'sa'
            ,'ussql1'
            ,'usweb'
            ,'uswebs'
            ,'vmview'
            ,'dba-admin'
            )

    EXEC xp_cmdshell 'powershell.exe "Remove-Item \\FBPISILON01.sfbcic.com\SQLAuditLogs-SC\SQLAuditLogs_Staging\*.* -ErrorAction SilentlyContinue"'
        ,no_output
END


Connection problems with ManagePartition executable I'm currently running SQL Server 2014 and we have some tables that have been partitioned and I'm trying to use the ManagePartition application here (http://sqlpartitionmgmt.codeplex.com/)

I've typed into the command line:

C:\>ManagePartition.exe /C:CreateStagingFull /d:<Databasename> /s:<schema>
                        /t:<TableName> /p:5 /A:<NewTablename> /f:C:/scripts.sql
                        /O:i /U:<My Login> /P:/<My Password>

And I'm getting the following error:

  ConnectionFailureException: Failed to connect to server (local). ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I've doubled checked all the spellings (dbname, schema, table, etc...) and the switches.

What are some things I can do to trouble shoot this (I'm kind of new to table partitioning)?

Or are there other strategies to easily manage/move data from partitioned table to a staging table?

Edit(I missed to the /S: switch)

I'm now getting this error:

  System.NullReferenceException: Object reference not set to an instance of an object.
  at PartitionManagement.PartitionManager.createStgIndex(Index i, TableViewBase parent)
  at PartitionManagement.PartitionManager.createStgNonclusteredIndexes()
  at PartitionManagement.PartitionManager.CreateStgIndexes(Boolean createNonClusteredIndexes)
  at PartitionManagement.App.Run(AppArgs& parsedArgs)
  Object reference not set to an instance of an object.

It did create the new table, with the indexes, constraints(including the partition constraint), but no data.

You are not using the

 /Server:<string>                   /S Server name, default = (local)

  Failed to connect to server (local). ---> System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

From the readme page :

ManagePartition
    /Command:<string>       short form /C command, default = CreateStagingFull
    /Server:<string>                   /S Server name, default = (local)
    /User:<string>                     /U User name, default = null
    /Password:<string>                 /P Password, default = null
    /Integrated[+|-]                   /E Integrated Security, default +
    /Database:<string>                 /d Database name
    /Schema:<string>                   /s Schema name – use quotes as delimiter if needed
    /PartitionTable:<string>           /t Partition table name - use quotes as delimiter if needed
    /PartitionNumber:<int>             /p Partition Number
    /PartitionRangeValue:<string>      /v Value string to input to partition function to specify partition number
    /StagingTable:<string>             /A Staging Table Name, default = null
          /ScriptOption:<char>               /O Scripting Option -- i à Include Script, o à Script Only (no execute)
          /ScriptFile:<string>               /f path and name of file for generated TSQL -- if excluded, script any output to stdout
    /Keep[+|-]                         /K Keep Staging Table if ClearPartition, default +

<Command> = ClearPartition | CreateStagingFull | CreateStagingNoindex | CreateStagingClusteredIndex | IndexStaging


Stored Procedure imported from file, but tables have no data Here is the Stored Procedure (import from file) that I'm having trouble:

DELIMITER //
CREATE PROCEDURE `books_database`()
BEGIN
  DECLARE n INT DEFAULT 0;
  WHILE n < 5 DO
    INSERT INTO books.science (Title,Author) VALUES ('Data Structures Using C','Aaron Tenenbaum');
    INSERT INTO books.science (Title,Author) VALUES ('C Programming Language','Brian W. Kernighan, Dennis Ritchie');
    INSERT INTO books.science (Title,Author) VALUES ('My Inventions','Nikolas Tesla');
    SET n = n + 1;
  END WHILE;
END //

When I import the SP file, it finishes without error. When I check database/table, there is no data inserted from the SP.

What is lacking or wrong with this Stored Procedure?

The fact of importing or creating a Stored Procedure, does not include its execution.

After creating the SP (via MySQL Shell or via bootstrap file) you must call it, in order to process the routines which are stored in it.

Stored Procedure

DELIMITER //
CREATE PROCEDURE `books_database`()
BEGIN
   SELECT...
   INSERT...
END //

Call your Stored Procedure...

CALL books_database();

Extra Comment: Creation of Stored Procedures via MySQL Shell

Before creating your SP, first, select the database (use your_database)  where this SP will be used, or else, as you try to create the SP, the MySQL Shell will return: ERROR 1046 (3D000): No database selected.
Don't forget to rollback to the default delimiter (DELIMITER ;) in order to perform the normal commands on MySQL Shell, after creating your SP.


Oracle 11gR2, duplicating and refreshing cloned database Assume I've cloned PROD database to TEST with RMAN "duplicate target database to CLONE from active database;". After several days I need to refresh CLONE. Will RMAN transfer all the datafiles from PROD to CLONE or only datafiles with changed data if I will issue "duplicate from active database again" ?

It will transfer all datafiles again.

The previous duplicate already changed all your datafiles. You have a new database incarnation, with a new name, a new dbid, which was written in the datafile headers.

RMAN duplicate skips existing datafiles in case of re-running a failed duplicate so it does not perform the same work again, but that is a different use case.


Is there a plugin to add a map behind the spatial results? Using SSMS 18.5.1 and dealing with spatial results. I would like to visualize this data against a map of the world using Google Maps, Bing Maps, or any other mapping software. Is there a plugin or a way to accomplish this within SSMS?

There is but as you have found out it is rather limited. I think you will have to try another tool / software Power BI has quite good geographical support or you could try real GIS software such as ArcGIS or MapInfo or one of the many opensource GIS projects.
Can't give a proper answer re BingMaps or Google maps, pretty sure you need to write some code and also purchase some cloud credits for the relevant service.


Limit user memory usage I'm using SQL Server 2014. There are some users who can query the tables. The problem is, that most of the time these queries use a lot of memory. I want to know if there is any way to limit resources per user?

There is not a specific query. I just want to control users. Sometimes they do crazy things.

As stated before, you can use Resource Governer to limit memory usage for specific users.

To summarize shortly the steps you should do:

Create a resource pool. This example has max 30% of memory, be careful with that.

CREATE RESOURCE POOL UserMemoryPool
WITH
( MIN_MEMORY_PERCENT=0,
MAX_MEMORY_PERCENT=30)
GO

Create a workload group which will use resource pool.

CREATE WORKLOAD GROUP UserMemoryGroup
USING UserMemoryPool;
GO

You should define a Classifier function to tell SQL Server who is gonna be in this group.

CREATE FUNCTION dbo.UserClassifier()
RETURNS SYSNAME WITH SCHEMABINDING
AS
BEGIN
    DECLARE @Workload AS SYSNAME
    IF(SUSER_NAME() = 'UserNameToLimit')/*Specify the user*/
        SET @Workload = 'UserMemoryGroup'
    ELSE
        SET @Workload = 'default'
    RETURN @Workload
END
GO

Lastly to enable Resource Governor

USE master
GO
ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = dbo.UserClassifier);
GO
ALTER RESOURCE GOVERNOR RECONFIGURE;

You can find additional info at Resource Governor at MSDN


"set-variable = innodb_thread_concurrency=4" or "innodb_thread_concurrency=4" Based on this question, the asker wrote:

set-variable = innodb_buffer_pool_size=2M
set-variable = innodb_additional_mem_pool_size=500K
set-variable = innodb_log_buffer_size=500K
set-variable = innodb_thread_concurrency=2
set-variable = innodb_flush_log_at_trx_commit=0

All of the variable has set-variable. I've tested this way on MySQL 5.1 standard install, and it works fine.

But I dont like it that way because it too long.

Lots of other guide including in MySQL website didn't say about using set-variable. Even in MyISAM setting example (MySQL 5.1), it only use thread_concurrency = 10

I've just installed MySQL 5.5 on new server and now is the time for configuration.

So, do MySQL really need set-variable?

MySQL works fine without the set-variable in the my.cnf file


Database Design for for Drug store I have designed a db for Drug store which will sell medicine both wholesale and retail. I store each strip, capsule and vial of medicine in a separate fields for retail selling. Is there any better approach for this? As a programmer, I can't think of whether it is necessary to store amount paid by each customer in the order table. And is it necessary to have in and out inventory in separate table? Here is my DB:

Could there be any issue with this db design? Please suggest a better approach.

You might want to consider abstracting product into a SKU for the order detail.  Instead of having order details show which specific pills were sold, show more generically which medicine was sold.  The reason for this is that you may sell, for example, 100 pills of a certain type.  Perhaps you only have 40 left of one batch and so you fill the order with those plus 60 from another batch.  That would show as two order details on a sales receipt, which would be less than ideal, since the customer doesn't want or need to know this detail.

Another advantage of abstracting the product list from the actual product batches is that you can get a simple list of products without repetition.  This is just good database normalization.  The name of the product and the batch number of the product have different functional dependencies, so they don't belong in the same table under Third Normal Form (3NF).

You can and should have inventory movement records that record the specific stock used to fill each order.

You should store the amount actually paid with the order details.  This is because the MSRP price can change over time so failing to store the price actually paid could lose information.  Similarly, you may decide to sell for a promotional price, which would be lost information if you only store the regular price (or MSRP).

I recommend against having inventory ins and outs in separate tables.  All inventory movements should be in one table with either two quantity columns (in/out) as per old-style accounting methods or preferably, a single quantity column with positive and negative numbers for receipts and sales, as per newer computerized accounting methods.  Keeping inventory in a single table reduces your code complexity considerably.


Determine the PackageFormatVersion for multiple .DTSX packages files in a folder Kenneth Fisher put a blog post out about how to determine What SQL Version is My SSIS Package? in April, 2015.

It has a table of which SQL versions map to which PackageFormatVersion of the SSIS package found in the XML metadata. This is useful when looking at 1 single individual package.

I have a folder of about 100 SSIS .DTSX packages that I need to know which SQL Version they all are.

How can I determine, in bulk, what the PackageFormatVersion (i.e. the SQL version) for multiple .DTSX packages in a folder (file system)?

The end goal is to determine which is the proper TFS version to get and implement to put these packages into, as no source control system exists currently. The table Kenneth presents will help me answer this question, but first I need to confirm what the package SQL versions are.

Assume that I do NOT have BIDS nor SSDT installed.

Assume a desired output would be something like this, where pipe designates a new column:

PackageFilename | PackageFormatVersion
--------------------------------------
Package1.dtsx   | 3
Package2.dtsx   | 4

PowerShell, TSQL, 3rd party tools that can crawl a directory structure, or other tools are welcome.

Retrieve dtsx info programmatically

You can read my detailed answer on StackoverFlow:

Automate Version number Retrieval from Dtsx files

Demo App

I Created A Demo Application to achieve this procedure you can download it from the following link:

Dropbox download link

Also i created a new Git-repository for this demo app

App screenshot

Retrieve dtsx PackageFormatVersion using TSQL

I wrote an SQL query that get files from a specific directory, filter on *.dtsx file then read PackageFormatVersion property from them.

--Result Table
CREATE TABLE #TblResult (filepath varchar(MAX) , packageformatversion INT)

--Get Files From Directory

declare @files table (FileName nvarchar(4000))

--Get files fullpath
declare @myPath nvarchar(4000) =  'C:\Users\Admin\Desktop\Stack Overflow';

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

CREATE TABLE #DirectoryTree (
   id int IDENTITY(1,1)
   ,subdirectory nvarchar(512)
   ,depth int
   ,isfile bit
   , ParentDirectory int
   ,flag tinyint default(0));

-- top level directory
INSERT #DirectoryTree (subdirectory,depth,isfile)
   VALUES (@myPath,0,0);
-- all the rest under top level
INSERT #DirectoryTree (subdirectory,depth,isfile)
   EXEC master.sys.xp_dirtree @myPath,0,1;

UPDATE #DirectoryTree
   SET ParentDirectory = (
      SELECT MAX(Id) FROM #DirectoryTree
      WHERE Depth = d.Depth - 1 AND Id < d.Id   )
FROM #DirectoryTree d;

-- SEE all with full paths
WITH dirs AS (
    SELECT
       Id,subdirectory,depth,isfile,ParentDirectory,flag
       , CAST (null AS NVARCHAR(MAX)) AS container
       , CAST([subdirectory] AS NVARCHAR(MAX)) AS dpath
       FROM #DirectoryTree
       WHERE ParentDirectory IS NULL
    UNION ALL
    SELECT
       d.Id,d.subdirectory,d.depth,d.isfile,d.ParentDirectory,d.flag
       , dpath as container
       , dpath +'\'+d.[subdirectory]
    FROM #DirectoryTree AS d
    INNER JOIN dirs ON  d.ParentDirectory = dirs.id
)
insert into @files (filename)
SELECT dpath FROM dirs
WHERE subdirectory like '%.dtsx'

DECLARE @file NVARCHAR(4000)

--Loop over dtsx files

DECLARE csr CURSOR FOR SELECT [FileName] FROM @files

OPEN csr

FETCH NEXT FROM csr INTO @file

WHILE @@fetch_status <> - 1
BEGIN

--Read xml from dtsx file

CREATE TABLE #TblTemp (data varchar(MAX));

DECLARE @strQuery NVARCHAR(4000)

SET @strQuery = 'BULK INSERT #TblTemp
   FROM ''' + @file + '''
   WITH
      (
         ROWTERMINATOR = ''''
      )'

EXEC(@strQuery)

--Get PackageFormatVersion

INSERT INTO #TblResult (filepath, packageformatversion)
SELECT @file, SUBSTRING(data
                 ,CHARINDEX('DTS:Name="PackageFormatVersion">',data,1) + LEN('DTS:Name="PackageFormatVersion">')
                 ,CHARINDEX('<',SUBSTRING(data,CHARINDEX('DTS:Name="PackageFormatVersion">',data,1) + LEN('DTS:Name="PackageFormatVersion">'), 3) ,1) - 1)

FROM #TblTemp

DROP TABLE #TblTemp

FETCH NEXT FROM csr INTO @file

END

CLOSE csr
DEALLOCATE csr

--Read Result

SELECT DISTINCT * FROM #TblResult

--Drop temp Table

DROP TABLE #TblResult

Result looks like

References

Retrieve files with full path in sql server


Is it against normal form to include columns with date parts if there is a datetime column in the same table? I am working on a table that will hold batch data.

The table will have a BatchDate column as a DateTime type. I'm considering adding a column for BatchYear as an integer and BatchPeriod (which could be a week number, a month number or a quarter number) as an integer, even though both of these values can be derived from the BatchDate and using another column in the table.

I'm considering doing this because I'm thinking it would be easier to work with queries where someone doesn't have to pull the date from the table first, compute the period they want and then query the table again. Also, if someone is looking for a batch from the 3rd quarter of 2012, the server would have to convert each BatchDate to it's quarter equivalent and do the same for the year and then test for a match, but if the values are there in separate columns, one could simply query the BatchPeriod and the BatchYear column for a match.

Is this a bad idea to break normal form this way?

It is against either 2nd or 3rd normal form, depending on whether the datetime column is part of the key.

You could be harmed by an update anomaly if you change a batch date, but forget to change one of the fields that are derived from it.  Now the database is self cntradictory.

When I was faced with this, I adopted an alternative approach.  I created a table called almanac, with a date type key, and all those other fields like weekday, fiscal quarter, and so on.  I then wrote a program to fill this table with ten years worth of dates.  Then, whenever I wanted all those fields with respect to any date, I just did a join.  This was especially useful because fiscal quarter was a company defined attribute, and wasn't in any built in function.  We had dozens of date fields, and reporting by fiscal quarter was a requirement.


SQL Server 2017 log file growth with memory optimized table I'm start to using the memory optimized table, the problem that I have is the log file growth also with the database put in simple mode.

On my maintenance schedule I checkpoint and shrink, but still has the issue. If I revert back the tables to normal disk table, everything looks fine.

Did somebody know the reason?

The database has the column LOG_REUSE_WAIT_DESC to 'nothing' in sys.databases.

All the mem opt tables are in SCHEMA_ONLY modality, but still full logging.

SQL Server 2017 version 14.0.3037

I also see these messages in the SQL Server error log:

  spid38s,Unknown,[INFO] Database ID: [5]. Deleting unrecoverable checkpoint table row (id: 669)
  spid102,Unknown,[INFO] HkHostFreezeCkptTrimming(). Database ID: [5]. HkTrimLSN Frozen: 1
  spid102,Unknown,[INFO] getMaxUnrecoverableCheckpointId(). Database ID: [5]. Start of Log LSN: 00001714:000004E0:0002 used to trim unrecoverable checkpoint files tables during full backup

Sizes situation with memory optimized tables:

Sizes situation with disk based table:

First of all, if you use durable memory-optimized tables, everything from the memory-optimized side will be fully logged - the database recovery setting is not respected.

Next, is your 2017 install fully patched? There have been a number of fixes to the In-Memory engine since RTM.

In order for the log to be cleared of memory-optimized transactions, they must also be written to the checkpoint file pairs. If the CFPs needed to be expanded/added, and the drive/volume that hosts them can't do that, then the transaction log cannot be cleared.


Why does AttachDBFile make everything slower? In my ASP.NET app, I had tested a query against a 200.000 rows table with two connection strings, one connect to a remote SQL Server and once use AttachDBFile to attach to a database file in App_Data.

The query with the first connection is much faster than the later. Anybody could shed some light on this for me?

You are comparing two different servers. Likely different hardware specs, possibly different versions. Possibly even querying different data. Apples and oranges. The attach is probably a red herring (there is a cost in attach and querying cold data, but will amortize over many queries. You do measure hot cached and repeated runs, don't you?)

Read How to analyse SQL Server performance to root-cause the performance problem.


Ola's Maintenance Solution Plan script newer version We have very old version of Ola's Maintenance Solution Plan script implemented in our many servers and wondering if i have to update to his latest scriptm how can i upgrade easily on all my servers?

Thank you!

how can i upgrade easily on all my servers?

You should use dbatools - Install-DbaMaintenanceSolution

  This script will download and install the latest version of SQL Server Maintenance Solution created by Ola Hallengren

Alternatively, Ola's solution is on github


Index fragmentation while continuously processing SQL Server 2005

I need to be able to continuously process about 350M records in a 900M record table. The query I'm using to select the records to process becomes badly fragmented as I process and I have a need to stop the processing to rebuild the index. Pseudo data model & query ...

/**************************************/
CREATE TABLE [Table]
(
    [PrimaryKeyId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    [ForeignKeyId] [INT] NOT NULL,
    /* more columns ... */
    [DataType] [CHAR](1) NOT NULL,
    [DataStatus] [DATETIME] NULL,
    [ProcessDate] [DATETIME] NOT NULL,
    [ProcessThreadId] VARCHAR (100) NULL
);

CREATE NONCLUSTERED INDEX [Idx] ON [Table]
(
    [DataType],
    [DataStatus],
    [ProcessDate],
    [ProcessThreadId]
);
/**************************************/

/**************************************/
WITH cte AS (
    SELECT TOP (@BatchSize) [PrimaryKeyId], [ProcessThreadId]
    FROM [Table] WITH ( ROWLOCK, UPDLOCK, READPAST )
    WHERE [DataType] = 'X'
    AND [DataStatus] IS NULL
    AND [ProcessDate] < DATEADD(m, -2, GETDATE()) -- older than 2 months
    AND [ProcessThreadId] IS NULL
)
UPDATE cte
SET [ProcessThreadId] = @ProcessThreadId;

SELECT * FROM [Table] WITH ( NOLOCK )
WHERE [ProcessThreadId] = @ProcessThreadId;
/**************************************/

Data content...
While the column [DataType] is typed as a CHAR(1), about 35% of all the records equal 'X' with the remainder equaling 'A'.
Of only the records where [DataType] equals 'X', about 10% will have a NOT NULL [DataStatus] value.

The [ProcessDate] and [ProcessThreadId] columns will be updated for every record processed.The [DataType] column is updated ('X' is changed to 'A') about 10% of the time.The [DataStatus] column is updated less than 1% of the time.

For now my solution is to select the primary key of all the records to process into a separate processing table. I delete the keys as I process them so that as the index fragments I'm dealing with less records.

However, this doesn't fit the workflow I want to have so that these data are processed continuously, without manual intervention and significant downtime. I do anticipate downtime on a quarterly basis for housekeeping chores. But now, without the separate processing table, I can not get through processing even half of the data set without the fragmentation becoming so bad as to necessitate stopping and rebuilding the index.

Any recommendations for indexing or a different data model? Is there a pattern I need to research?
I have full control of the data model and the process software so nothing is off the table.

What you're doing is you're using a table as a queue. Your update is the dequeue method. But the clustered index on the table is a poor choice for a queue. Using tables as Queues actually impose quite stringent requirements on the table design. Your clustered index must be the dequeue order, in this case likely ([DataType], [DataStatus], [ProcessDate]). You can implement the primary key as a nonclustered constraint. Drop the non-clustered index Idx, as the clustered key takes its role.

Another important piece of the puzzle is to keep the row size constant during processing. You have declared the ProcessThreadId as a VARCHAR(100) which implies the row grows and shrinks as is being 'processed' because the field value changes from NULL to non-null. This grow-and-shrink pattern on the row causes page splits and fragmentation. I can't possibly imagine a thread ID that is 'VARCHAR(100)'. Use a fixed length type, perhaps an INT.

As a side note, you do not need to dequeue in two steps (UPDATE followed by SELECT). You can use OUTPUT clause, as explained in the article linked above:

/**************************************/
CREATE TABLE [Table]
(
    [PrimaryKeyId] [INT] IDENTITY(1,1) NOT NULL PRIMARY KEY NONCLUSTERED,
    [ForeignKeyId] [INT] NOT NULL,
    /* more columns ... */
    [DataType] [CHAR](1) NOT NULL,
    [DataStatus] [DATETIME] NULL,
    [ProcessDate] [DATETIME] NOT NULL,
    [ProcessThreadId] INT NULL
);

CREATE CLUSTERED INDEX [Cdx] ON [Table]
(
    [DataType],
    [DataStatus],
    [ProcessDate]
);
/**************************************/

declare @BatchSize int, @ProcessThreadId int;

/**************************************/
WITH cte AS (
    SELECT TOP (@BatchSize) [PrimaryKeyId], [ProcessThreadId] , ... more columns
    FROM [Table] WITH ( ROWLOCK, UPDLOCK, READPAST )
    WHERE [DataType] = 'X'
    AND [DataStatus] IS NULL
    AND [ProcessDate] < DATEADD(m, -2, GETDATE()) -- older than 2 months
    AND [ProcessThreadId] IS NULL
)
UPDATE cte
SET [ProcessThreadId] = @ProcessThreadId
OUTPUT DELETED.[PrimaryKeyId] , ... more columns ;
/**************************************/

In addition I would consider moving successfully processed items into a different, archive, table. You want your queue tables to hover near zero size, you do no want them to grow as they retain 'history' from unneeded old entries. You may also consider partitioning by [ProcessDate] as an alternative (ie. one current active partition that acts as the queue and stores entries with NULL ProcessDate, and another partition for everything non-null. Or multiple partitions for non-null if you want to implement efficient deletes (switch out) for data that has passed the mandated retention period. If things get hot you can partition in addition by [DataType] if it has enough selectivity, but that design would be really complicated as it requires partitioning by persisted computed column (a composite column that glues together [DataType] and [ProcessingDate]).


Does SQL Server allow moving backwards with standby file? I have a database in standby mode.

-- STEP 1
-- Restore path F1 > T7
RESTORE DATABASE [TestRestore] FROM DISK = N'C:\Temp\F1.BAK' WITH FILE = 1,
NORECOVERY, NOUNLOAD, REPLACE, STATS = 5
GO

-- STEP 1
RESTORE LOG [TestRestore] FROM DISK = N'C:\Temp\T7.TRN' WITH FILE = 1,
STANDBY = N'C:\Temp\TestRestore_RollbackUndoFile.tuf', NOUNLOAD, STATS = 5
GO

And now I'm gonna do log shipping. My database is accessible(read only)so it will be easy to determine specific point in time where I need to stop at.

-- STEP 3
-- Restore T8
USE [master]
RESTORE LOG [TestRestore] FROM DISK = N'C:\Temp\T8.TRN' WITH FILE = 1,
STANDBY = N'C:\Temp\TestRestore_RollbackUndoFile.tuf',
NOUNLOAD, STATS = 10, STOPAT = N'2013-10-25T19:55:26'
GO

-- STEP 4
-- ckecking
SELECT * FROM TestRestore.dbo.TranRecord
GO

I can perform the same transaction log restore repeatedly until I arrive at the required point-in-time by incrementing the time specified in the STOPAT clause.

But, for example, do I need to repat the whole restore again in case I've realized that I've restored too far ahead ?(I mean delete the current restored db and do restore F1 > T7 > T8 again and stop at exactly where I need). Or it is possible to undo just the last log shipping ?(in my example it's STEP 3).

Msdn gives it pretty controversial connotation.

  Specifies a standby file that allows the recovery effects to be
  undone.

You must restart the restore sequence.

I believe SQL Server could theoretically allow  moving backwards in time because the logs contain both undo and redo information. But this is either not implemented or it is not possible for some reason.


Why use both TRUNCATE and DROP? In the system I work on there are a lot of stored procedures and SQL scripts that make use of temporary tables. After using these tables it's good practice to drop them.

Many of my colleagues (almost all of whom are much more experienced than I am) typically do this:

TRUNCATE TABLE #mytemp
DROP TABLE #mytemp

I typically use a single DROP TABLE in my scripts.

Is there any good reason for doing a TRUNCATE immediately before a DROP?

No.

TRUNCATE and DROP are almost identical in behavior and speed, so doing a TRUNCATE right before a DROP is simply unnecessary.

Note: I wrote this answer from a SQL Server perspective and assumed it would apply equally to Sybase. It appears that this is not entirely the case.

Note: When I first posted this answer, there were several other highly rated answers -- including the then-accepted answer -- that made several false claims like: TRUNCATE is not logged; TRUNCATE cannot be rolled back; TRUNCATE is faster than DROP; etc.

Now that this thread has been cleaned up, the rebuttals that follow may seem tangential to the original question. I leave them here as a reference for others looking to debunk these myths.

There are a couple of popular falsehoods -- pervasive even among experienced DBAs -- that may have motivated this TRUNCATE-then-DROP pattern. They are:

Myth: TRUNCATE is not logged, therefore it cannot be rolled back.
Myth: TRUNCATE is faster than DROP.

Let me rebut these falsehoods. I am writing this rebuttal from a SQL Server perspective, but everything I say here should be equally applicable to Sybase.

TRUNCATE is logged, and it can be rolled back.

TRUNCATE is a logged operation, so it can be rolled back. Just wrap it in a transaction.

USE [tempdb];
SET NOCOUNT ON;

CREATE TABLE truncate_demo (
    whatever    VARCHAR(10)
);

INSERT INTO truncate_demo (whatever)
VALUES ('log this');

BEGIN TRANSACTION;
    TRUNCATE TABLE truncate_demo;
ROLLBACK TRANSACTION;

SELECT *
FROM truncate_demo;

DROP TABLE truncate_demo;

Note, however, that this is not true for Oracle. Though logged and protected by Oracle's undo and redo functionality, TRUNCATE and other DDL statements can't be rolled back by the user because Oracle issues implicit commits immediately before and after all DDL statements.
TRUNCATE is minimally logged, as opposed to fully logged. What does that mean? Say you TRUNCATE a table. Instead of putting each deleted row in the transaction log, TRUNCATE just marks the data pages they live on as unallocated.  That's why it's so fast.  That's also why you cannot recover the rows of a TRUNCATE-ed table from the transaction log using a log reader.  All you'll find there are references to the deallocated data pages.

Compare this to DELETE.  If you DELETE all the rows in a table and commit the transaction you can still, in theory, find the deleted rows in the transaction log and recover them from there. That's because DELETE writes every deleted row to the transaction log. For large tables, this will make it much slower than TRUNCATE.

DROP is just as fast as TRUNCATE.

Like TRUNCATE, DROP is a minimally logged operation.  That means DROP can be rolled back too. That also means it works exactly the same way as TRUNCATE.  Instead of deleting individual rows, DROP marks the appropriate data pages as unallocated and additionally marks the table's metadata as deleted.
Because TRUNCATE and DROP work exactly the same way, they run just as fast as one another.  There is no point to TRUNCATE-ing a table before DROP-ing it. Run this demo script on your development instance if you don't believe me.

On my local machine with a warm cache, the results I get are as follows:

table row count: 134,217,728

run#        transaction duration (ms)
      TRUNCATE   TRUNCATE then DROP   DROP
==========================================
01       0               1             4
02       0              39             1
03       0               1             1
04       0               2             1
05       0               1             1
06       0              25             1
07       0               1             1
08       0               1             1
09       0               1             1
10       0              12             1
------------------------------------------
avg      0              8.4           1.3

So, for a 134 million row table both DROP and TRUNCATE take effectively no time at all. (On a cold cache they take about 2-3 seconds for the first run or two.) I also believe that the higher average duration for the TRUNCATE then DROP operation is attributable to load variations on my local machine and not because the combination is somehow magically an order of magnitude worse than the individual operations. They are, after all, almost exactly the same thing.

If you're interested in more detail about the logging overhead of these operations, Martin has a straightforward explanation of that.


avoiding unique key collisions across tables This might be related to the idea of data warehousing, but i'm not sure, though it is extreme theory.

We have a table where records get written to first, let's call it tbl_recording which looks like:

CREATE TABLE tbl_recording (
  id INT NOT NULL AUTO_INCREMENT,
  name VARCHAR(45) NULL,
  registration VARCHAR(45) NULL,
  legacyid VARCHAR(45) NOT NULL,
  UNIQUE INDEX legacyid_UNIQUE (legacyid ASC),
  PRIMARY KEY (id));

Then, we have yearly tables based off of tbl_recording so like tbl_recording_2012, tbl_recording_2013 etc etc,  their structure is the same.

We also have tbl_recording data off to the yearly table every hour (insert to yearly, delete from recording).  Now, the problem I see is that the uniqueness of legacyid is not remembered across all these tables. So, while we generate a hash for the legacyid, there could be a collision (even though terribly unlikely) at some point:

After data is removed from tbl_recording the same hash could be generated and re-entered into tbl_recording, thus causing a blockage further down the line when trying to insert into the yearly table
Between years, the same hash could be generated for 2013 and 2014 EDIT: This would never happen if i append the year to the hash

The best way I've come up with to mitigate against this is to keep a hashtable,  one that stores all hashes created, and is checked against before a record is inserted, and then that fresh hash inserted into the hashtable.

Are there better solutions to this or is that the only real solution?

edit:  I've created an SQL Fiddle of an example of how my system works

http://sqlfiddle.com/#!2/231fa/1

pretend that: tbl_data_2012 will never be written to again.  tbl_recording is constantly being written to but every hour or so, it's the contents it's being emptied to tbl_data_2013.  tbl_recording has already dumped it's contents to tbl_data_2013 so when the 4th record was written there was nothing in there previously, thus it has created a new legacyid, however that legacyid already exists in tbl_data_2013

You should separate the table on year to get faster insert's, updates or deletes because we expect lots lots lots lots lots lots off records in this table..

**But... your problem is precisely the reason why separating tables on meta data (year in your question) is an SQL anti pattern, you could not have giving us an better use case*

If you want an better solution you should read about mysql partitioning see http://dev.mysql.com/doc/refman/5.5/en/partitioning.html


Best Practice for Folder strucure? I don't like my data files in C:\Program Files... , especially with a new system that has a smallish SSD as C: which holds mainly the operating system.

So on a recent install I put my data files into D:\apps\MSSQL\DATA

Where do you put your Sql Server data files, if you are not going with the default?

I am mainly looking for good habits to make my sql server deployments easier to use.

Never on C:\ if it can be avoided, though with smallish SSDs and VMs on laptops it's hard to have anything but C:.

So where else? Depends on the config. I've done like you for simple workstation installs: data, logs, tempdb all on D:. But then you get into more advanced implementations where you have a SAN and many spindles to choose from - there I've had groups of data files on M:\ and N:\, log files on L:\, tempdb on T:\, etc. Making deployment easier isn't the only goal you should consider... it's also reducing the need for re-deployment and/or moving of data/log files later. :-)


Different calculations in EXCEL and TERADATA SQL The calculation select (75/979.86)*979.86 in Teradata SqL gives 78.3888. In excel, it would give 75 simply and that is logically correct. I get it that SQL is rounding off 75/979.86 to 0.08 ie the number of decimal digits in 979.86. And hence the result is different.So, I can simply do (75*979.86)/979.86 instead and get the result.But is there any way to match these exactly maybe by changing data type or rounding off?

Teradata rounds after each calculation based on the resulting datatype (Standard SQL compliant), to get the expected reault you can either CAST the 1st operand to a NUMBER (no need to think about precision) and finally back to DECIMAL:

Cast((Cast(75 AS NUMBER)/979.86) *979.86 AS DECIMAL(10,2))

Ot you rearrange the order of calculation to multiply first and then divide:

979.86 * 75 / 979.86


Can I install SQL Server Management Studio locally? Can I install SQL Server Management Studio on my desktop to access a database on a SQL Server 2012 instance?

If so, where can I find the installer? Google searches only return 'express' versions whereas, I am looking for the full SQL Server Management Studio.

If you want the shiny new 2016 monthly releases of Management Studio, which include a handy check for updates mechanism, negates the need for a full setup program and download (since it only retrieves the components you need), as well as a release cycle completely independent from SQL Server, go here (and you can read about it here and here).

If you use this new web installer, much of the below no longer applies. If you want to use previous versions (currently 2012 SP2 and 2014 SP1), they've made it easier to find and download (see this page), but much of the below will still apply.

Well, if you have a legal copy of SQL Server, you should be able to install Management Studio from that media wherever you need to manage SQL Server from.

That said, the Express version of Management Studio, since 2012 Service Pack 1, has been a fully functional product with absolutely no differences (except you can download it for free instead of finding your licensed media). I would recommend downloading the 2014 version, because why use an older version that will be out of support sooner? My philosophy is that SSMS should always be >= the highest engine version you need to manage.

Anyway, I found the link by searching - the hint to not jumping through hoops set up by Microsoft marketing or MSDN is to look for the page with download in the URL:

This brings me here, where I click Download:

Then pick either the 32-bit or 64-bit version, depending on your platform, and click Next:

And success. I even get handy tips for installation if I need them.

If you really, really, really want to use the 2012 version instead of the 2014 version, well first I'd be interested why, but the same process will work, just use this link instead. Note that the 2012 version is a 950 MB download vs. 684 MB for 2014.

Since a lot of people get confused on how to install JUST SSMS with this installer, here are some other things you'll want to follow once you launch the .exe. First it will open the installation center. You want Installation > New SQL Server stand-alone [blah blah]...

Clicking that link will launch a new wizard, and leave the installation center open. Leave it open (see the end of this post for why). On the new screen, you may have product updates offered, and you definitely want to select them if so (currently this should only happen for 2012 SP1 or 2014, since 2012 SP2 was not affected by Security Bulletin MS14-044).

Next, and this is where most people get confused, you want to choose Perform a new installation - even though you are not trying to install an actual instance of the SQL Server database engine.

Then, you want to select both Management Tools options, basic and complete. From here you should be ok on your own.

Regarding the installation center window above, you need to leave the "SQL Server Installation Center" window open during setup, or it will fail with an error like this (the actual folder name is some random GUID-based thing):

  SQL Server Setup has encountered the following error: Could not find file 'c:\1d3c284ce0e72239e2f4e51c802d\MediaInfo.xml'

I filed a Connect item about it, but so far it remains active.


make MariaDB Auto-Increment-Increment dynamic In a certain replication setup I have created multi-master replication in STAR topology using MariaDB. I have an unknown number of 'client-masters', so I have set the auto_increment_increment to 30 and the auto_increment_offset accordingly. When I go over the 30 masters, this system is going to crash, so I would like the following:

The Auto-increment value is created by using the following calculation:

auto_increment_offset + N × auto_increment_increment

Is it possible to make the auto-increment-increment dynamic like this:

(SELECT COUNT(id) FROM Clients) + N x (SELECT COUNT(id) FROM Clients)

When I add a client to the table, the other clients will automatically be upping the increment.

to be on the safe side I probably should add a buffer:

(SELECT COUNT(id) FROM Clients) + N x ((SELECT COUNT(id) FROM Clients) + 5)

Can anyone tell me if this could be done, or if there is another way to accomplish this?

No, the auto increment functionality of MySQL (or MariaDB) does not provide some kind of "dynamic" auto increment values like you request. The formula you arranged is all MySQL can do.

In your case you might be better of in just abandoning the auto increment column and use a custom column instead. You can fill this for example with UUIDs. This does not guarantee uniqueness of generated IDs, but the possibility to generate the exact same IDs is very low.


Inserting in PostgreSQL Foreign Table Violates Primary Key Constraint10 I  have two local databases in one of them I have created a foreign table that is referencing a table in the second database. When I want to insert in foreign table it inserts the primary key all over from beginning from 1 although when I insert in the actual table it continues from the last value entered.
Is there any way so if I insert in actual or foreign table it goes on the sequence of primary key?

CREATE TABLE Actual_Table (
  id   serial   PRIMARY KEY
  name varchar
);

Then I insert some data in the actual table

INSERT INTO Actual_Table (name) VALUES
  ('AHMAD'),
  ('MAHMOOD');

Then I create the following foreign table in the second database

CREATE FOREIGN TABLE Foreign_Table (
  id serial,
  name varchar
) server some_server options(schema_name 'public', table_name 'Actual_Table')

And then when I user insert in foreign table using the following insert statement I get an error that is violating the primary key constraint

INSERT INTO Foreign_Table (name) VALUES ('John');

This is the error I get

ERROR:  duplicate key value violates unique constraint "to_copy_pkey"
DETAIL:  Key (id)=(2) already exists.
CONTEXT:  Remote SQL command: INSERT INTO public.to_copy(id, name) VALUES ($1, $2)

Can you ignore ID field in the Foreign_Table script and try inserting? It should use the current serial value in the Foreign Table.

{
CREATE FOREIGN TABLE Foreign_Table (name varchar) server
    some_server options(schema_name 'public', table_name 'Actual_Table')
}


MYSQL databes recovery using .ibd files My problem is finding MySQLL database using .ibd files, the problem is that I do not have access to the .frm file and due to problems with the hard drive I had to change the operating system and restart the entire MySQL I installed, how can I recover these databeses ??? !!!

If you have the original SQL statements used to define the tables, you can create new empty tables. That will give you the .frm files you need.
Then you can try to attach the .ibd files you have using importable tablespaces. But those .ibd files probably don't have the required import files. There are blogs about how to binary-edit tablespace files to modify the tablespace id so you can attach it. This is not a solution for a novice.
Honestly, you're better off restoring from a recent backup.
If you didn't make a recent backup, then this will be an unfortunate learning experience about the importance of making backups.


Select the max from two max values I need to find the airport(departure or arrival) that had the most passengers in the last year.

The tables are:

PASSENGER(id,name,surname)
TAKES(id_passenger,id_flight)
FLIGHT(id,departure_id,arrival_id,departure_date,arrival_date,id_plane)
AIRPORT(id,name,city,country)
PLANE(id,type,capacity)

What I think I need to do is:
Count the number of passengers each airport has and return that
but I do not know how to pick the one with the most passengers among those two.
Do I need to use nested select queries? Would it be useful to use something like

WITH maxdep as
  SELECT ( max passengers from departure airport)
maxar as SELECT  ( max passengers from arrival airport)
select max (val1, val2),  from ... ?

How do I return just one?

EDIT:
I was thinking of a case select from the two previous with statements.
Can that work?

WITH departures as (SELECT departure_id, count(id_passenger)
    from passenger, takes, flight
    WHERE passenger.id=takes.id_passenger
    and takes.id_flight=flight.id
    and flight.departure_date between '01-JAN-2013' AND '01-JAN-2014'
    group by 1
    order by 2 desc
    where ROWNUM <2)

arrivals as (SELECT arrival_id, count(id_passenger)
    from passenger, takes, flight
    WHERE passenger.id=takes.id_passenger
    and takes.id_flight=flight.id
    and flight.arrival_date between '01-JAN-2013' AND '01-JAN-2014'
    group by 1
    order by 2 desc
    where ROWNUM <2)
SELECT CASE
        WHEN departures.count > arrivals.count THEN departures.departure_id
        WHEN departures.count < arrivals.count THEN arrivals.arrival_id
        ELSE departures.departure_id || 'has the same number of visitors as'|| arrivals.arrival_id
        END;

I am sorry if this is messed up

Will this work?

Here's one possibility:

with departures(departure_id, cnt) as (
    select f.departure_id, count(t.id_passenger)
    from flight f
    join takes t
        on f.id = t.id_flight
    where year(departure_date) = ?
    group by f.departure_id
),
arrivals as (arrival_id, cnt) as (
    select f.arrival_id, count(t.id_passenger)
    from flight f
    join takes t
        on f.id = t.id_flight
    where year(arrival_date) = ?
    group by f.arrival_id
)
select position, max(cnt)
from (
    select arrival_id as position, cnt
    from arrivals
    union
    select departure_id as position, cnt
    from departures
) as t;


how to group data against more than one conditions I have a table named Employees with below two columns.

==================

Employees

1- EmpId  (auto incremental id)

2- EmpName  (varchar)

There is another table named 'LateDays' with below five columns.

==================

LateDays

1- LateDayId  (auto incremental id)

2- EmployeeId  (foreign key)

3- LateDate   (DateTime)

4- FinePaid   (bool)

5- Informed   (bool)

Now I am trying to write a SQL statement to fetch employees along with their Total Fine, Paid Fine and their Allowed Quota Availed. Each employee has 4 allowed days to come late.
I came up with the following (incorrect) SQl, which works fine when there is only one employee in Employees table but fails for more.

SELECT
    Employees.name as EmpName,
    (select count(LateDays.LateDate)*200 from Employees inner join LateDays on Employees.EmpId = LateDays.EmployeeEmpId) as TotalFine,
    count(LateDays.LateDate)*200 as PaidFine,
    (select COUNT(LateDays.Informed)from Employees inner join LateDays on Employees.EmpId = LateDays.EmployeeEmpId where LateDays.Informed='true') as InformedDays
FROM
    Employees inner join LateDays
    on Employees.EmpId = LateDays.EmployeeEmpId
WHERE
    LateDays.FinePaid = 1
GROUP BY
    name

Kindly help me writing a proper SQL for my situation.

Edit:
I am trying to add sample data.

Employees table:

EmpId   Name
1       Pervaiz
2       Nabeel
3       Muhammad
4       james

Late Days table:

LateDayId   EmployeeEmpId   LateDate    FinePaid    Informed
4           1               2014-09-21  0           0
5           1               2014-09-20  0           1
6           6               2014-09-21  0           0
7           6               2014-10-01  1           0

The reason this isn't working for you when you have multiple rows in the employee table is that it is not correlated back to the main query.  By this I mean that SQL has no way of knowing which employee to count in your subqueries for a given row in your main query.  You can avoid this buy adding a reference back to your main query like this:
SELECT
    Employees.name as EmpName,
    (SELECT COUNT(LateDays.LateDate)*200
        FROM Employees E
        INNER JOIN LateDays LD
            ON E.EmpId = LD.EmployeeEmpId
        WHERE E.EmpId = Employees.EmpId)
    as TotalFine,

Notice how I aliased the tables in the subquery so they had a different name from those in the outer query.  Then in the WHERE clause I referenced the table name from the outer query.
However this is not how I would write your query.  It will probably be less than well performing.  Instead you can use a pattern where you use a CASE statement to define your condition returning a 1 or a 0 then SUM the values.
The final query looks like this:
SELECT
    Employees.name AS EmpName,
    COUNT(LateDays.LateDate)*200 AS TotalFine,
    SUM(CASE WHEN LateDays.FinePaid = 1 AND LateDays.LateDate IS NOT NULL THEN 1 ELSE 0 END) AS PaidFine,
    SUM(CASE WHEN LateDays.Informed = 1 THEN 1 ELSE 0 END) AS InformedDays
FROM Employees
INNER JOIN LateDays
    ON Employees.EmpId = LateDays.EmployeeEmpId
GROUP BY Employees.EmpId, Employees.Name

A couple of things of note.  I am checking that LateDate is not NULL in the first SUM to match your COUNT.  COUNT doesn't actually count a value if it is NULL.  Second is that I changed the GROUP by to Employees.EmpId.  You generally do not want to GROUP BY a NAME column.  What happens when two employees have the same name?  It's rare but it does happen.  You are better off grouping on the ID column.
EDIT:

Based on having test data I made a few corrections to the query and have a question.
Change1: I had missed a comma in the SELECT list.
Change2: I had forgotten to add Employees.Name to the GROUP BY.  You should still have Employes.EmpId to ensure grouping by distinct employees but because you are pulling Employees.Name you do have to include it.
Question: In your test data rows 6 & 7 have the same date but the same EmployeeEmpId.  Should this be counted as two for the TotalFine calculation?
Note: Rows 6 & 7 of your test data have an EmployeeEmpId of 6 but that Id doesn't exist in your Employees table test data.  I changed it to a 3 for my testing.


Mysql query performance We have a database table and we found a weird query from old code. We can't really explain it, so we decided to analyze why it could have been done that way. The weird thing is that the results are the same, but the performance is quite interesting.

SELECT * FROM table WHERE column='97807dsad1' group by indexedColumn ORDER BY CreatedDate ASC;

SELECT * FROM (SELECT * FROM table WHERE column='978asdad' ORDER BY CreatedDate ASC) as thetable GROUP BY indexedColumn;

column has no index.
CreatedDate has index.
indexedColumn has index.

The first query takes 30 seconds, the second 2.26 seconds.
I tested it a bit more and inner select:

SELECT * FROM table WHERE column='978asdad' ORDER BY CreatedDate ASC;

It is slow without the ORDER BY and quick with the ORDER BY.

May matter: the total results for the query is 4 rows (out of the 1.8 million records in the table)

The results of EXPLAIN for the first:

+----+-------------+-----------+-------+---------------+------+---------+------+---------+----------------------------------------------+
| id | select_type | table     | type  | possible_keys | key  | key_len | ref  | rows    | Extra                                        |
+----+-------------+-----------+-------+---------------+------+---------+------+---------+----------------------------------------------+
|  1 | SIMPLE      | table | index | NULL          | indexedColumn  | 202     | NULL | 1806555 | Using where; Using temporary; Using filesort |
+----+-------------+-----------+-------+---------------+------+---------+------+---------+----------------------------------------------+

The results of EXPLAIN for the second:

+----+-------------+------------+------+---------------+------+---------+------+---------+---------------------------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows    | Extra                           |
+----+-------------+------------+------+---------------+------+---------+------+---------+---------------------------------+
|  1 | PRIMARY     | <derived2> | ALL  | NULL          | NULL | NULL    | NULL |     108 | Using temporary; Using filesort |
|  2 | DERIVED     | table  | ALL  | NULL          | NULL | NULL    | NULL | 1806555 | Using filesort                  |
+----+-------------+------------+------+---------------+------+---------+------+---------+---------------------------------+

The result of explain for only the inner select of the 2nd query:

+----+-------------+-----------+------+---------------+------+---------+------+---------+-----------------------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows    | Extra                       |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-----------------------------+
|  1 | SIMPLE      | table | ALL  | NULL          | NULL | NULL    | NULL | 1806555 | Using where; Using filesort |
+----+-------------+-----------+------+---------------+------+---------+------+---------+-----------------------------+

What is the explanation for this?

The order by column if have index gets highest priority when engines has to decide which index to use.

Hope it helps


Cross Apply Fails When DateTime Added In trying to achieve a Pivot-ed goal for a two row data table into one row, my first thought was to use a Cross Apply. By using the Cross Apply one can identify/generate each of the rows with a specific column name derived from the row's unique id. (e.g. 'Lat1' and 'Lat2 for a 'Latitude' source column).

All is fine when I use INT or FLOAT data types but when I try to use a DateTime all  VALUES become DateTime.

Example Shipping Lat/Long/DateTime

We have two points in a ships journey reported on two different days:

CREATE TABLE #Shipping
(
     [RouteID]     [INT]  NOT NULL,
     [Latitude]    FLOAT NOT NULL,
     [Longitude]   FLOAT NOT NULL,
     [Time]        DATETIME NOT NULL

);

INSERT #Shipping(RouteID, [Latitude], [Longitude], [Time])
VALUES          (1,         18.0221,    -63.1206,  '24-Jan-2016'),
                (2,         17.8353,    -62.99667, '25-Jan-2016');

Successful CrossApply
 If we use CrossApply against the data for the first three columns

  SELECT  col+cast([RouteID] as varchar(1)) new_col
         , X.value
       FROM #Shipping
  CROSS APPLY
  (
      VALUES
         (RouteID,   'Id')
      ,  (Latitude,  'Lat')
      ,  (Longitude, 'Lon')
  ) X (value, col)

Our results are as expected:

Great!

Failure CrossApply With DateTime

But once we add DateTime to the mix:

      SELECT  col+cast([RouteID] as varchar(1)) new_col
             , X.value
           FROM #Shipping
      CROSS APPLY
      (
          VALUES
             (RouteID,   'Id')
          ,  (Latitude,  'Lat')
          ,  (Longitude, 'Lon')
          , ([Time],    'Time')
      ) X (value, col)

All become DateTimes

How should one work around this if my end goal is to pivot all the values from the original table into one row?

In the result, the value column must have a data type, like always. SQL Server determines the type using the rules for data type precedence (more precisely, the VALUES clause is a UNION, so types are matched there).

In your first example, the precedence rules give a column of type float. In the second example, it is datetime.

Solve the problem by explicitly casting each value in the VALUES clause explicitly to a common type, e.g. varchar or sql_variant:

SELECT
    new_col = X.col + CAST(S.RouteID as varchar(10)),
    X.value
FROM #Shipping AS S
CROSS APPLY
(
    VALUES
        (CONVERT(sql_variant, S.RouteID),   'Id'),
        (CONVERT(sql_variant, S.Latitude),  'Lat'),
        (CONVERT(sql_variant, S.Longitude), 'Lon'),
        (CONVERT(sql_variant, S.[Time]), 'Time')
) AS X (value, col);

or:

SELECT
    new_col = X.col + CAST(S.RouteID as varchar(10)),
    X.value
FROM #Shipping AS S
CROSS APPLY
(
    VALUES
        (CONVERT(varchar(8), S.RouteID),   'Id'),
        (CONVERT(varchar(8), S.Latitude),  'Lat'),
        (CONVERT(varchar(8), S.Longitude), 'Lon'),
        (CONVERT(varchar(8), S.[Time], 112), 'Time')
) AS X (value, col);

Note: Not everything can be cast to sql_variant. From that link:

Everything except the old deprecated image type can be cast to char or varchar. See the conversion matrix in Data Type Conversion (Database Engine):


Your database files were created by PostgreSQL version 13 OS is openSUSE Tumbleweed. Previously I had PostgreSQL 13 installed. I just removed it by:
> sudo zypper rm 'postgres*'

> sudo zypper rr PostgreSQL

> sudo rm -rf /usr/local/var/postgres

Then I install PostgreSQL 12 by instructions here:
> sudo zypper addrepo http://download.opensuse.org/repositories/server:database:postgresql/openSUSE_Tumbleweed/ PostgreSQL

> sudo zypper ref

> sudo zypper in -f postgresql postgresql-server postgresql-contrib

> sudo zypper in -f postgresql-plperl postgresql-plpython postgresql-pltlc

> sudo systemctl enable postgresql

> sudo systemctl start postgresql

But the database status is faulty:
> sudo systemctl status postgresql.service
[sudo] password for root:
● postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
     Active: failed (Result: exit-code) since Thu 2021-05-27 23:08:43 EDT; 2h 10min ago
    Process: 1576 ExecStart=/usr/share/postgresql/postgresql-script start (code=exited, status=1/FAILURE)
        CPU: 21ms

May 27 23:08:43 localhost systemd[1]: Starting PostgreSQL database server...
May 27 23:08:43 localhost postgresql-script[1576]:  Your database files were created by PostgreSQL version 13.
May 27 23:08:43 localhost postgresql-script[1576]:  Could not find executables for this version.
May 27 23:08:43 localhost postgresql-script[1576]:  Please install the PostgreSQL server package for version 13.
May 27 23:08:43 localhost systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
May 27 23:08:43 localhost systemd[1]: postgresql.service: Failed with result 'exit-code'.
May 27 23:08:43 localhost systemd[1]: Failed to start PostgreSQL database server.

For some reason, whatever I try, I cannot get rid of these status errors:

Your database files were created by PostgreSQL version 13.
Could not find executables for this version.
Please install the PostgreSQL server package for version 13.

How can I install PostgreSQL 12 and get rid of PostgreSQL 13 database files?

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.


ORA-13506 An attempt was made to perform operation with an invalid Start/End Snapshot Pair during AWR report creation When I create an AWR Report, the following error occurs:

  ORA-13506
  Cause: An attempt was made to perform operation with an
  invalid Start/End Snapshot Pair.

What does this mean, and what should I do about it?

BEGIN
  DBMS_WORKLOAD_REPOSITORY.create_baseline (
    start_snap_id => --ANY Integer Number_Min Value,
    end_snap_id   => --ANY Integer Number_Max value, ,
    baseline_name => 'batch baseline');
END;
/

So here min and max value are already in DBA_HIST_BASELINE view. You can't chose random value for snapshot.

Remember value already in oracle system, you can't drop value but not overbound.


Oracle error handling strategy I am developing a project using oracle. I am writing functions and stored procedures to handle CRUD statements.

My question is in addition to oracle check constraints do I have to check error situations myself or let oracle do the job?

For example if I have a column in a table which is unique and I want to insert into this table I have to check the value to be unique or let the oracle unique constraint do the job and produce an error if it is a repeated value?

In my experience it's slightly faster to let oracle check it and throw an excpetion if the constraint is violated. If you want to do something special when that happens, you can catch the exception and handle it.

For instance:

EXCEPTION
      WHEN DUP_VAL_ON_INDEX
      THEN
         UPDATE ...


Is there benefit from having 2 tables for 1 entity? I'm using mysql 5.7
I'm making app that has 2 activities: list of entities and viewing single entity (full).

The full entity has 7 related entities and 100 columns.

On the list activity I need only 5 columns and 2 related entities.

I'm thinking if I make separate table for the listing (5 columns + 2 related entities) denormalized. And then when searching through normalized columns get only ids. And get all deneromalized stuf with single where id in 1,5,7 manner.

Would it singificantly increase performance or should i don't bother?

I'm making an android app and want the best speed i can get but dunno if it really matters

Generally it is best to hold data in a normalised structure.

If testing at scale, on production-grade hardware, proves there is a problem, and that the problem is because of an over-large table, and index tuning and SQL re-factoring do not bring the problem withing acceptable bounds, then splitting the table is a recognised technique.

Moving some rows to a different table is called horizontal partitioning or sharding. Moving some columns to a different table is call vertical partitioning. Both entail additional design and maintenance work. There will be run-time implications if values from several partitions are required by a single query.

There is some further discussion at this answer.


Create a runtime table in PostgreSQL Suppose that I have two tables:

tableA (id, name);
tableB (id, name);

I also have a textual file with a list of couples of ids:

id1a, id2a
id1b, id2b
...

I would like to run the following query:

select *
from (
id1a, id2a
id1b, id2b
...)
join tableA as a on a.id = id1
join tableB as b on b.id = id2;

How can I write such a query?

You can prepare a query with a VALUES expression:

SELECT a.*, b.*
FROM  (
   VALUES
      (id1a, id2a)
     ,(id1b, id2b)
     , ...
   )   t(id1, id2)
JOIN   tablea a ON a.id = t.id1
JOIN   tableb b ON b.id = t.id2;

Or you COPY the values from the text file to a temporary table and proceed from there:

CREATE TEMP TABLE t(id1 int, id2 int);

COPY t FROM '/path/to/file.csv';

SELECT a.*, b.*
FROM   t
JOIN   tablea a ON a.id = t.id1
JOIN   tableb b ON b.id = t.id2;


Methods for automating SQL CU patching? For reasons that are not mine to address, we are looking for alternatives to WSUS and SCCM to automate SQL CU patching (particularly on SQL 2017+)

There are some neat Powershell solutions like this one by Adam Bertram which I found via the related post Automate the patching of SQL Severs on Windows

I know there are a plethora of commercial solutions, that Google finds easily.  I am looking for solutions we can manage in house.

I am looking for other approaches to consider as well.  After extensive searching the only other approach I can even imagine is a SQL job, that runs on server restart (server patching occurs regularly generally with restart) that would go look in a shared folder for the most current CU and apply it if not installed. But I don't find any mention of solutions other the those above.

Are there any methods for automating SQL CU patching, that I have not found examples of?

If SQL job patching is viable, are there any examples?

I would not go with SQL agent running Patch to upgrade sql server. During patching and/or after patching SQL server needs to be restarted along with a reboot of windows machine - depending on the files that are locked during patching.

Alternatives are described in my answer here : https://dba.stackexchange.com/a/105837/8783

Also, dbatools has Update-DbaInstance.ps1 that can perform installation of SQL Server Service Packs and Cumulative Updates on local and remote servers.