You can't control plan cache size manually. The following Connect item describes both the fact that they won't be adding a knob any time soon and also that Resource Governor might be marginally useful in a few narrow use cases:

http://connect.microsoft.com/SQLServer/feedback/details/293188/amount-of-ram-for-procedure-cache-should-be-configurable

However, as I've alluded to in the comments, there are other ways you can make the plan cache more effective (e.g. use the "optimize for ad hoc workloads" setting). Kimberly explains this in greater detail, but essentially, this setting places much smaller stubs in the cache until the same plan is used more than once, and only then does it cache the whole thing. Under the default configuration, and especially in cases where a lot of dynamically assembled SQL is in place, single-use plans often waste a lot of space in the cache. If you reduce the amount of wasted space these occupy, you leave more room for plans that are used more than once, and those are precisely the ones you want to keep around anyway.


Wrong results on queries with join for some MariaDB versions

I don't think it's a bug. You use GROUP BY and at that moment server leaves only one random value for f_date in each row. After that you apply your filter for results returned by the query inside the view.
According to MariaDB documentation:

  You can use any of the grouping functions in your select expression.
  Their values will be calculated based on all the rows that have been
  grouped together for each result row. If you select a non-grouped
  column or a value computed from a non-grouped column, it is undefined
  which row the returned value is taken from. This is not permitted if
  the ONLY_FULL_GROUP_BY SQL_MODE is used.
  https://mariadb.com/kb/en/group-by/

There is no guarantee that value for non-grouped column will be returned from the same group of rows.
I would recommend you to enable ONLY_FULL_GROUP_BY SQL_MODE and add all non-grouped columns to GROUP BY part or apply grouping functions for them (for example, you could use MAX(f_date)).


What is CP1 in SQL_Latin1_General_CP1_CI_AI?

CP1 denotes code page 1252 as described in the SQL Server Books Online.  You can also use the query below to identify the collation code page.

SELECT name, COLLATIONPROPERTY(name, 'CodePage') AS CodePage
FROM fn_helpcollations()
ORDER BY name;


Add value to a column in a while loop in SQL Server

Here is an example of how using recursion might solve your problem.

Assuming that my demo table variable @Folder is equal to what comes our of your #folder at the very bottom of your procedure:

--demo setup
declare @Folder Table (
  Id INTEGER,
  Foldername VARCHAR(8),
  rank INTEGER,
  status INTEGER
);

INSERT INTO @Folder
  (Id, Foldername, rank, status)
VALUES
  ('1', 'folder 1', '1', '1'),
  ('2', 'folder 2', '2', '1'),
  ('3', 'folder 3', '3', '1');

--the solution
;
WITH x
AS (
    SELECT id
        ,Foldername
        ,convert(VARCHAR(10), rank) AS rank
        ,STATUS
    FROM @Folder
    WHERE id = 1

    UNION ALL

    SELECT y.id
        ,y.Foldername
        ,convert(VARCHAR(10), convert(VARCHAR(10), x.rank) + convert(VARCHAR(10), y.rank)) AS rank
        ,y.STATUS
    FROM x
    INNER JOIN @Folder AS y
        ON y.Id = x.Id + 1
    )
SELECT id
    ,Foldername
    ,rank
    ,STATUS
FROM x
ORDER BY id
OPTION (MAXRECURSION 10000);

| id | Foldername | rank | STATUS |
|----|------------|------|--------|
| 1  | folder 1   | 1    | 1      |
| 2  | folder 2   | 12   | 1      |
| 3  | folder 3   | 123  | 1      |


Can we shrink datafile of a database which is configured with log shipping

No, shrink won't break Your log shipping configuration. But You must be aware that both shrink (and rebuild/reorganize You will have to do afterwards) will make Your transaction log files grow a lot. All of those operations cause a lot of I/O load that is logged to transaction logs.

This, in practice, might mean that while Your log shipping won't break, it will make restore last a lot longer, depending on Your backup/copy/restore jobs frequency of course. That might lead to secondary falling behind a bit till shrink's (and defrag's) end.

Remember that shrink has serious issues, read Paul Randal's post about it, if You haven't already:

http://www.sqlskills.com/blogs/paul/why-you-should-not-shrink-your-data-files/

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-930-data-file-shrink-does-not-affect-performance/

https://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/


Find SSIS package which calls stored procedure

As far as I know, there is not a built-in function that provides the package name. I think the closest you are going to get is to use the APP_NAME function. This does depend on parameters being passed as part of the connection string to specify the name - which means that it could be changed at anytime, but on the positive side it also means it can be as flexible as you need it to be.

However, because it is set on the client-side the documentation does come with this warning:

  The client provides the application name, and APP_NAME does not verify the application name value in any way. Do not use APP_NAME as part of a security check.

If the client did set the value in the connection though, this is the result you would get (example using Sql Server Management Studio from the link above):

USE AdventureWorks2012;
GO
IF APP_NAME() = 'Microsoft SQL Server Management Studio - Query'
PRINT 'This process was started by ' + APP_NAME() +
      '. The date is ' + CONVERT ( varchar(100) , GETDATE(), 101) + '.';
ELSE
PRINT 'This process was started by ' + APP_NAME() +
      '. The date is ' + CONVERT ( varchar(100) , GETDATE(), 102) + '.';
GO


Update a dynamic number of rows with an amount proportionally

You can solve it this way:

declare @a table
(groupID int, reservationID  int, [Date] date, Amount decimal(10,2));

insert into @a values
(26,      1,              '2018-06-01',  NULL),
(26,      2,              '2018-06-01',  NULL),
(26,      3,              '2018-06-01',  NULL),
(27,      4,              '2018-06-04',  NULL),
(27,      5,              '2018-06-04',  NULL);

declare @b table
(groupID  int, [Date] date,  Amount decimal(10,2));

insert into @b values
(26,       '2018-06-01',      60),
(27,       '2018-06-04',     100);

with cte as
(
select a.*,
       b.Amount / count(a.reservationID) over (partition by a.Date, a.groupId) new_amount
from @a a join @b b
        on a.Date = b.Date
        AND a.groupId = b.groupID
)

update cte
set Amount = new_amount;

select *
from @a;


Connect tables in a cube based on historical data

First your query doesn't look right, as you aren't accounting for NULL end dates. Fix that first, so that you have a flattened view of both tables.

Once, you have the query working properly, you will use it as your DSV. In the DSV editor add that query as a 'named query'. That named query will be the source for your cube.

Once the cube is created off of that view, add your QTY & UnitPrice as measures. You can use ProductNumber, ProductName, CreatedDate, and EndDate as dimension attributes, depending on what you want to see. The important part is that you build the dimension off of the same named query that the measures came from.

Here is the important part: Once you have the facts and measure setup, go to the 'Dimension Usage' tab on the cube editing window. You should only have one relationship to configure - set it to Fact.

You're done. A date dimension could be useful here, but that isn't related to your question.


Difference in behaviour between `DBCC TRACEON` and `QUERYTRACEON`

The query you were sending from the .NET application was still likely to be using the "old" plan (which used the legacy cardinality estimator).

When you enabled the trace flag globally, and then removed the QUERYTRACEON hint, the query text was different, resulting in a new plan being compiled for the query.  This newly compiled plan used the newer cardinality estimator, and thus met your expectations related to the plan shape, and having results return immediately.

Changing the query text in this case had the same effect as when you added OPTION (RECOMPILE) in SSMS while doing your testing.

Regarding your follow up question in the comments:

  although I am curious, why is it that we have to do this when a trace flag is enable/disabled?

The reason for this is that enabling trace flag 2312 globally does not clear the execution plan cache.  So for queries that you need to leverage the new CE, you'll need

to manually evict those plans from the cache one by one (with DBCC FREEPROCCACHE (plan_handle)),
temporarily add OPTION (RECOMPILE) to them, or
just clear the entire plan cache (if you need most queries to use the new CE, and your environment can handle this action).


Concern regarding auto_increment in MySQL InnoDB

Don't delete records for which there is a logical collation that you want to keep. Rather than marking deleting employees, mark them as terminated, keeping the row there. Otherwise you'll have an audit record without knowing who it corresponds too.

Option b), for an Audit record, denormalize it, if necessary create a shadow table for the employee. The audit should reflect the employee record at the time it occurred.

No, you shouldn't use MyISAM.

MySQL-8.0 changes behaviour as its manual states (the primary source you should consider for important decisions). If in doubt, test it.


How does SQL Server determine how much memory to allocate to Data cache

With that in mind, how does Enterprise Edition decide how much memory it can use for the data cache?

SQL Server memory( Buffer Pool) is designed to adjust its requirement according to available memory on the system (I mean RAM present on the Windows Server). The exact algorithm is something no one knows but what I can tell you is when SQL Server boots up it start allocating memory keeping in mind three flags, these are resource monitor flags.

MEMPHYSICAL_HIGH
MEMPHYSICAL_LOW
RESOURCE_MEM_STEADY

Actually there is lot of things underneath but I am trying to simplify things for you. SQL Server will keep allocating memory based on its need as long as  MEMPHYSICAL_HIGH notification is signaled in window or untill it feels it no longer requires memory and most pages it needs are cached in memory. If there is a memory shortage MEMPHYSICAL_LOW is flagged and SQL Server will try to trim its various caches. When a steady state is reached flag RESOURCE_MEM_STEADY is flagged and this means SQl Server has memory somewhere between high and low flag. I would quote from Karthick PK's blog

  The default level of available memory that signals a
  LowMemoryResourceNotification event is approximately 32 MB per 4 GB,
  to a maximum of 64 MB. (By default, the threshold is 64mb on most
  systems).

  The default level that signals a high-memory-resource notification
  event is three times the default low-memory value (By default, the
  threshold is 64*3=192 MB on most systems).

  Key points:

  Once the available memory on the system goes below 192 MB HighMemoryResourceNotification (MEMPHYSICAL_HIGH) signal is revoked by
  windows and SQL Server will not grow its Bpool.
  Once the available memory on the system goes below 64 MB LowMemoryResourceNotification (MEMPHYSICAL_low) is signaled by windows
  and SQL Server will shrink its Bpool (reduce its memory usage).
  When the available memory in the system is between 192Mb and 64 Mb (I.e between LowMemoryThreshold and HighMemoryThreshold) SQL Server
  will not grow or shrink its usage (With some exceptions which we will
  see in a while)

You of course have option to change the threshold values from the registry.

  How does SQL Server decide how much of this 400GB goes to each respective area?

Again its dynamic and depends on requirement. So you have 400 GB memory and suppose you have set max server memory 350 GB. SQL Server will not, at startup, try to grab all 350 GB. It depends on workload. If, after startup you are running heavy workload it will take at max to  350 GB and if it further needs memory it will take help of Lazywriter and Checkpoint process to create some space in buffer pool by flushing dirty pages to disk and pull more pages. The moral here is there is no fixed rule, lot of things depend on workload and SQL Server configuration.

The above also holds true for data cache which takes chunk of buffer pool.

PLAN CACHE:

Plan cache is bit interesting. Let us talk after SQL Server 2005 Sp2 when there was no upper limit on plan cache in SQL Server. The plan cache would normally try to grow when queries start hitting SQL server but it cannot grow indefinitely. There is a formula which keeps track on what maximum plan cache can grow before old/stale plans be removed from it to create space for new one.  See below pic taken From Kalen Delaneys's blog.

The Cache pressure limit  defines after what limit old entries from plan cache would be removed. To calculate Target memory which would be used in calculating pressure limit see This Q&A Blog from Kalen

I do not have the formula for newer versions. The things changed with arrival of query store in SQL Server 2016 but I am not sure whether this formula changed. Anyways I do not think there is whole point in discussing this formula.


You have two problems:

High I/O load:
This may or may not be caused by autovacuum, but since autovacuum is an important system process, the solution is not to subdue it.
Tune your queries so that they cause less I/O. If that doesn't suffice, get a faster I/O system or more RAM.

Long running autovacuum workers:
This can be exacerbated by I/O contention, but the problem is almost always that autovacuum is working too slow. Decrease autovacuum_vacuum_cost_delay to make it faster (but of course that will cause more I/O).

Renaming a table won't make autovacuum go away (dropping would). You will have to let it finish.
Make sure that you have no long running transactions or stale replication slots, otherwise autovacuum cannot do its job properly and keep trying again and again.


missing WAL file on standby server following a backup

This part generating recovery.conf is suspicious:

echo "restore_command = 'cp -f $PGDATA/pg_xlog/%f $PGDATA/pg_xlogRestored'" >>$PGDATA/recovery.conf

It seems that the source and destination of this restore_command are reversed.

Compare yours to the example from the doc in Continuous archiving:

restore_command = 'cp /mnt/server/archivedir/%f %p'

The restore must copy from the archive directory (which typically is outside $PGDATA and entirely under the DBA's control) into $PGDATA/pg_xlog (as expanded by %p).

I'm assuming that $PGDATA/pg_xlogRestored is your archive directory since I don't see what else it could be. Personally I'd place it outside of $PGDATA by principle but that's not mandatory.


In SSMS, open Tools -> Options and go to the "SQL Server Object Explorer" item in the left-hand pane. Expand that and you should see a "Scripting" subitem. From there, navigate to the "Table and View Options" section and you should see an option to say whether statistics are scripted or not.


This can done using window functions
select id, value, count(*) over (partition by value order by id)
from the_table
order by id, value;


How to use language PL/PythonU on a PostgreSQL database via phpPgAdmin?

The trick is to uncheck the paginate results checkbox before executing the query. This creates that SELECT COUNT(*) ... wrapper.

Without that checkbox selected, creating the plpythonu query is working as expected.

I created the bug report #449 on sourceforge to see if this is really the desired behaviour.


What sort of downtime do you have available for the migration?

There are a couple of ways that I'd approach this, as you're 24\7 and need to migrate 20TB of data, I'd be wanting to stage it beforehand.  You could either do this with logshipping, setup logshipping to your new server ahead of time and then when it comes to the cutover, take a final log backup, play it into the new server and bring online.

Another option and the one that I tend to use when migrating large databases is to mirror over to the new server.  When it comes to cutover, it's a simple job of just failing the mirror over and then then stop mirroring.  There are a few advantages of that approach, it can be setup days ahead of time, cutover is quick with no risk of data loss and it also preserves the log chain.

I wrote about using mirroring in a migration situation here, https://sqlundercover.com/2017/01/21/sql-mirroring-preserving-the-log-chain-during-database-migrations/

Just make sure that any logins you script across from the old server includes the SID, otherwise you could end up with a ton of orphaned users.  Not the end of the world but a hassle you don't need on migration day.


ALTER DATABASE SET ALLOW_SNAPSHOT_ISOLATION ON takes forever

I ran into this exact situation and can confirm there was no blocking in my case as the setup basically altered the snapshot isolation immediately after creating the database.

In my case the wait type was ENABLE_VERSIONING.

When investigating I found this feedback item Enabling snapshot isolation in 2016 SP1 CU6 and CU7 hangs which is still under investigation.

One of the comments seem to suggest a SERVER AUDIT could be the case.

If you don't have a server audit running I'm afraid your option for now will be to roll back to an older CU and watch that feedback item for updates.


Grouping similar rows together based on time

I would start with a query that finds the latest row in each group of similar rows that are less than 15 minutes apart, and picks the latest five of those:

SELECT DISTINCT model, model_id, timestamp
FROM changes c1 WHERE (model, model_id) NOT IN
  (SELECT model, model_id FROM changes c2
   WHERE c2.timestamp > c1.timestamp
     AND c2.timestamp < c1.timestamp + INTERVAL 15 MINUTE)
ORDER BY timestamp DESC LIMIT 5;

To get a unique number for each group, you can use a variable:

SELECT model, model_id, timestamp, @num:=@num+1 grp
FROM (<query#1>) last, (SELECT @num:=0) init;

Using this query as a derived table (subquery in FROM clause), you can find all rows that belong to these groups:

SELECT c3.*, groups.grp action_group
FROM changes c3 JOIN (<query#2>) groups USING(model, model_id)
WHERE c3.timestamp <= groups.timestamp
  AND groups.timestamp < c3.timestamp + INTERVAL 15 MINUTE
ORDER BY c3.timestamp DESC;

The full query then becomes:

SELECT c3.*, groups.grp action_group
FROM changes c3 JOIN
    (SELECT model, model_id, timestamp, @num:=@num+1 grp
     FROM (SELECT DISTINCT model, model_id, timestamp
           FROM changes c1
           WHERE (model, model_id) NOT IN
               (SELECT model, model_id FROM changes c2
                WHERE c2.timestamp > c1.timestamp
                AND c2.timestamp < c1.timestamp + INTERVAL 15 MINUTE)
           ORDER BY timestamp DESC LIMIT 5) last,
           (SELECT @num:=0) init
    ) groups USING(model, model_id)
WHERE c3.timestamp <= groups.timestamp
  AND groups.timestamp < c3.timestamp + INTERVAL 15 MINUTE
ORDER BY c3.timestamp DESC;

If your changes table is very big, the above query might take some time.  Indexes on (model, model_id) and timestamp might help.  Also, it might be possible to rewrite the query to use JOIN instead of NOT IN.


What does @RC mean in SSDT SQL Server Unit Testing?

@RC = Return code. The boilerplate unit test code will assign the stored procedure return code value to this variable, where it can later be used in a test case assertion.

Assuming there's a subsequent SELECT @RC after the proc execution in the unit test code, the scalarValueCondition1 assertion in your screen image will verify the return code value is 1 in order for the test to succeed.


USING construct in JOIN clause can introduce optimization barriers in certain cases?

Erwin:  I would concur with the idea that USING causing rigid ordering could well create many edge cases where optimal plans would be ruled out.  I recently helped someone out who had something like this in his query:

LEFT JOIN (
     a
     JOIN b ON a.id = b.a_id
     JOIN c ON b.c_id = c.id
) ON a.id = something.a_id
LEFT JOIN (
     table1 t1
     JOIN table2 t2 ON t1.some_field = t2.other_field
     JOIN talbe3 t3 ON t2.yafield = t3.something_else
) ON ....
repeat a few more times

In his case the worst of these join blocks was causing a nested loop join through some 200k rows, about 20k times (do the math), and since keys couldn't be pushed to indexes, it was a sequential scan.  This meant that the overall query took about 3 hours to run due to cascading plan changes.  By distributing the left join, the keys could be pushed down and the query ran in a matter of seconds.  Of course this isn't exactly equivalent which is why the planner can't treat them as equivalent and so it was left figuring out that plan as a hash join and then doing a nested loop in, which was painfully slow.

Any time you rigidly force the joins to go through in a certain order you introduce cases where key filter information may not be available yet in the execution of the plan, and so what might be possible to do later in a quick index scan/hash join might be have to be done much slower in a nested loop/sequential scan and so while the above fragment is not immediately equivalent, it shows the same problem.


Extracting Extended Entity Relationship Diagram from a Relational Database

The only significant reference to the notation I've ever read is the Elmasri and Navathe textbook - EER gets taught in database papers from time to time, but I don't think it's widely used in practice..  I'm not aware of any data modelling tools with support for the notation.


how do I change this query from SQL Server 2016 to MySQL?

You can do the following:

(See also the PostgreSQL fiddle and MySQL one with your data)

CREATE TABLE zip_unique
(
  store_nbr integer
  ,zipcode varchar(30)
  ,distance float
);

Then populate:

INSERT INTO zip_unique (store_nbr, zipcode, distance)
VALUES (123, '76135', 2.356)
     , (456, '76135', 3.002)
     , (789, '76135', 9.521);

And then perform the query:

SELECT store_nbr
      ,zipcode
      ,distance
      ,ROW_NUMBER() OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS ROW_RANK
  FROM zip_unique;

Result:

store_nbr   zipcode     distance    row_rank
      123     76135        2.356           1
      456     76135        3.002           2
      789     76135        9.521           3

At @Rick_James ' suggestion, I also ran this:

SELECT store_nbr
      ,zipcode
      ,distance
      ,ROW_NUMBER() OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS ROW_RANK
      ,DENSE_RANK() OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS D_RANK
      ,RANK()       OVER(PARTITION BY ZIPCODE
                             ORDER BY DISTANCE) AS R_RANK
  FROM zip_unique;

Result

store_nbr   zipcode     distance    row_rank    d_rank  r_rank
123     76135   2.356   1   1   1
456     76135   3.002   2   2   2
789     76135   9.521   3   3   3

See the PostgreSQL fiddle here and the MySQL one here.

The ranks will vary according to the data - in this case they're the same. This was just to show that PostgreSQL will perform standard Window function code, as does MySQL (PostgreSQL is still the superior db though!).

One of the things I didn't mention previously was PostgreSQL's almost fanatical pursuit of the standards laid down by the ISO/ANSII committee on SQL.

The following is the answer to the original question - see edits to trace what's gone on prior to the above answer.

You can do the following, (see the MySQL fiddle here and the PostgreSQL one here):

CREATE TABLE fred (mary int, paul varchar(20));

Populate it:

INSERT INTO fred VALUES (1, 'sdf'), (2, 'xxx'), (3, 'yyy');

MySQL syntax seems to require this - CTAS, i.e. `CREATE TABLE AS which more or less does what you want:

CREATE TABLE bill AS SELECT * FROM fred WHERE mary <= 2;

Then

SELECT * FROM bill;

Result:

mary    paul
   1     sdf
   2     xxx

PostgreSQL does it this way (only differences shown):

SELECT * INTO TABLE bill FROM fred WHERE mary <= 2

The result is the same.

So, it appears, at least as far as I have been able to determine, that PostgreSQL does exactly what you want. MySQL will do the same thing, but it requires a slight (but hardly crippling) change to the syntax of the query. You will see from the fiddles that both syntaxes are accepted in PostgreSQL


Tracking errors from prepared SQL statements

The code you have does return errors from prepared statements. The below PowerShell executes a prepared statement against SQL Server that will intentionally fail. This appears in Profiler with RPC:Completed as you've described:

$SqlConn = [System.Data.SqlClient.SqlConnection]::New("Server=localhost;Database=master;Trusted_connection=true;")

$SqlConn.Open()

$SqlCmd = [System.Data.SqlClient.SqlCommand]::New($null, $SqlConn)

$SqlCmd.CommandText = "SELECT @Val/0"

$idParam = [System.Data.SqlClient.SqlParameter]::New("@Val", [System.Data.SqlDbType]::Int, 0);
$idParam.Value = 1;
$SqlCmd.Parameters.Add($idParam);

$SqlCmd.Prepare()
$SqlCmd.ExecuteNonQuery()

$SqlConn.Close()

When I run this with Profiler and your Extended Events session running, I can see the error reported in both tools:

Extended Events:

Profiler:

One issue may be that the messages are below severity 11, and so your EE session is excluding them. If you run a Profiler trace using the TSQL_Replay template and include "User Error Messages" under Errors and Warnings do your errors from prepared statements appear then?

If so, check the error column in Profiler and run the below against SQL Server replacing 8134 with the error number from Profiler:

SELECT * FROM sys.messages WHERE message_id = 8134

Check the severity column in that to identify the severity of the error returned by your prepared statements.


"ORA-00942: table or view does not exist" when deleting from a very real table in the user's schema

Once I had a similar Problem. To shorten a rather long story, just flush your shared pool.

ALTER SYSTEM FLUSH SHARED_POOL;

Somehow Oracle seems to create an corrupted entry in the statement cache. Problem has been resolved after flush. After that, we were unable to reproduce it.

You need some administrative privileges to flush it.

One final note: ORA-00942 does not exist in the database documentation at all.

According to my notes, we used this Oracle version.

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production


Move very large DB from single to multple drives with not a lot of space

Yes you can achieve that based on the size of the data files and log. You can do a restore with a move option to split the locations of the database files (mdf/ndf and ldf) to the mentioned drives based on the size of the files and available space on the drives.
Sample script will perform this task:

RESTORE DATABASE [BigDatabase7TB] FROM disk = 'C:\backup.bak'
WITH NORECOVERY, STATS=10,
    MOVE 'BigDatabase7TB_Data' --logical file name of primary db
        TO 'C:\MyData\BigDatabase7TB.mdf', --desired data file
    MOVE 'BigDatabase7TB_Data1' --logical file name of primary db
        TO 'D:\MyData\BigDatabase7TB1.mdf', --desired data file
    MOVE 'BigDatabase7TB_Data2' --logical file name of primary db
        TO 'E:\MyData\BigDatabase7TB2.mdf', --desired data file
    MOVE 'BigDatabase7TB_Log'
        TO 'F:\MyLog\BigDatabase7TB.ldf'
GO

NOTE: You will face issues if any of the database file sizes is bigger than available space on any of the drives. So you probably want to pay attention to the sizes of the files first and match that to the drives based of available space.


Even if you have a UNIQUE constraint on username.
You must replace all referenced Columns with the username and change the dataatype of column.
A text field is also usually slower that an Integer.
So when you see all negative points, there is no good reason to change it now.
Besides, what does it really matter, if you have a numeric id that you have to use to identify the user or another unique text filed.


Dropped table not showing up in recycle bin

It's likely that recyclebin isn't turned on. Check in your init.ora file to ensure it is. You can check your parameter:

select *
from   V$PARAMETER
where  NAME = 'recyclebin';

And turn it on at session level...

ALTER SESSION SET RECYCLEBIN=ON;

...and then try a drop.

Also, check both USER_RECYCLEBIN and DBA_RECYCLEBIN for entries.

EDIT: I see you've updated to say you are logged in as the SYSTEM user. For some reason, flashback/recyclebin does NOT work for SYS. I can't find a good reason why but it just doesn't. Create a new user and try creating and dropping a table while logged in as them. I'm sure you'll find that the recyclebin works fine then.


Select all from A where attribute exists in B

The (somewhat trivial) answer seems to be:

Cars ⋉ Ford

Relational algebra, semijoin (Wikipedia)

Alternatively (in response to a comment):

πR(R ⋈ S)


How to get list of tables, views etc. a users has rights to on Sybase IQ?

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36273.1570/html/sprocs/X22708.htm lists the system stored procedure, sp_helprotect which provides a mechanism for displaying all the rights a user has in a given database.
From that document:

Displays all the permissions that “judy” has in the database:
sp_helprotect judy


Use column values in ORDER BY and LIMIT clauses

Query
While your solution is clever, it's poison for performance because the value to order by has to be computed separately for every row. More importantly, your query cannot use a plain index.
I suggest to move the trick to the LIMIT clause and use UNION ALL. This way the query gets cheaper overall and can use an index (which nukes competitors that can't).
WITH l AS (
   SELECT CASE WHEN ordr = 'a' THEN lim ELSE 0 END AS lim_a
        , CASE WHEN ordr = 'd' THEN lim ELSE 0 END AS lim_d
   FROM   test
   LIMIT  1
   )
(SELECT * FROM test ORDER BY first_name      LIMIT (SELECT lim_a FROM l))
UNION ALL
(SELECT * FROM test ORDER BY first_name DESC LIMIT (SELECT lim_d FROM l));

One of both SELECT gets LIMIT 0 and is never executed. You'll see "never executed" in the EXPLAIN ANALYZE output.
About twice as fast without index in a quick test on Postgres 9.4 (35 rows out of 50k), but several orders of magnitude faster with index. The difference grows with the size of the table, obviously.
Table layout
Don't use reserved key words as identifier (as you found yourself already): lim and ordr instead of limit and order.
Don't bloat your table with redundant values. If you cannot avoid storing lim and ordr for every row, at least make it small. Basic layout:
CREATE TABLE test (
  id         int
, lim        int
, ordr       "char" CHECK (ordr IN ('a', 'd'))
, first_name text
, email      text
);

"char" is perfect as a simplistic enumeration type and occupies just 1 byte.
Create an index:
CREATE INDEX test_first_name_idx ON test(first_name);

Details:

Configuring PostgreSQL for read performance
Measure the size of a PostgreSQL table row

SQL Fiddle.


This sounds like a job for SP_BLITZ,SP_WHOISACTIVE etc. By Brent Ozar & Team.

https://www.brentozar.com/first-aid/

Edit, I'm not advertising for them (it's a free resource anyway).  This is exactly what I've used for this exact scenario.  3rd party apps and their rogue developers killing the DB.

You might also want to limit them to using views/procedures that you write/have control of in order to get their data.

Edit2: And I just realized this is an old question that got bumped somehow.
My answer stands.


Follow your INSERT statement with:

UPDATE bar SET count = (count+1) WHERE foo_id=1

And on delete:

UPDATE bar SET count = (count-1) WHERE foo_id=1


The error means mysqld does not have the access rights to the directory.

When you recreated /var/lib/mysql, you need to make sure the /var/lib/mysql directory is owned and writeable by the mysql user.


How to efficiently sort a column from two tables?

You might get improved performance by doing the following:

Use UNION ALL instead of UNION (as suggested by Thangamani Eraniyan).
Make sure both tables have an index on due_date (or with due_date as the first column, if there are more columns indexed).
Use the ORDER BY and the LIMIT 20 on the separate queries of the two tables, as well as on the final combined result. The individual queries do have to be subqueries; otherwise, we can't use ORDER BY on them.

The principle is to limit the number of records that need to be looked at in both individual tables. With an index on due_date in both tables, it should be able to find the most recent records quickly. We know that the top 20 records from both tables can't include more than 20 tasks or more than 20 checklists, so if we just grab the top 20 from each table, we've definitely got the top 20 form the combined tables. The final ORDER BY ... LIMIT simply drops out rows 21-40.

Try this; I tested the same basic query via db-fiddle, and it worked (so, barring any cut-and-paste errors combining that with your actual query, this should work as well).

SELECT type, id, due_date FROM
   (SELECT a.type, t.id, t.due_date
      FROM assignments a
             INNER JOIN tasks t on t.id = a.task_id
     ORDER BY t.due_date ASC
     LIMIT 20) sq1
UNION ALL
SELECT type, id, due_date FROM
   (SELECT a.type, c.id, c.due_date
      FROM assignments a
             INNER JOIN checklists c on c.id = a.checklist_id
     ORDER BY c.due_date DESC
     LIMIT 20) sq2
 ORDER BY due_date ASC
 LIMIT 20;


may i reduce the restore time with pgsql 9.2?

pg_restore has an option to run the time-consuming parts of the restore, such as the index rebuild process, with multiple "jobs".
From the pg_restore documentation for PostgreSQL 9.2:

-j number-of-jobs
--jobs=number-of-jobs
Run the most time-consuming parts of pg_restore — those which load data, create indexes, or create constraints — using multiple concurrent jobs. This option can dramatically reduce the time to restore a large database to a server running on a multiprocessor machine.
Each job is one process or one thread, depending on the operating system, and uses a separate connection to the server.
The optimal value for this option depends on the hardware setup of the server, of the client, and of the network. Factors include the number of CPU cores and the disk setup. A good place to start is the number of CPU cores on the server, but values larger than that can also lead to faster restore times in many cases. Of course, values that are too high will lead to decreased performance because of thrashing.
Only the custom archive format is supported with this option. The input file must be a regular file (not, for example, a pipe). This option is ignored when emitting a script rather than connecting directly to a database server. Also, multiple jobs cannot be used together with the option --single-transaction.


PostgreSQL utilize data from SELECT in BEGIN or use WITH?

You would probably be better suited to do the whole operation inside an anonymous block.. DO ..

DO $$
  DECLARE
    SEQUENCE_NAME VARCHAR;
  BEGIN
    SELECT s.relname INTO SEQUENCE_NAME
    FROM pg_class AS s JOIN pg_depend d ON d.objid = s.oid
    INNER JOIN pg_class AS t ON d.objid = s.oid AND d.refobjid = t.oid
    INNER JOIN pg_attribute AS a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
    INNER JOIN pg_namespace AS n ON n.oid = s.relnamespace
    WHERE s.relkind = 'S'
    AND n.nspname = 'public'
    AND t.relname='parts';

    LOCK TABLE parts;
    CREATE TABLE parts1 (LIKE parts INCLUDING ALL);
    INSERT INTO parts1 SELECT * FROM parts;
    -- change select to perform
    --SELECT setval(SEQUENCE_NAME::regclass, (SELECT max(id) FROM parts)+1);
    PERFORM setval(SEQUENCE_NAME::regclass, (SELECT max(id) FROM parts)+1);
  END;
$$ LANGUAGE plpgsql;

SQL Fiddle


I ended up changing the .metadata.json file to look like this:

{"options":{},"indexes":[{"v":1,"key":{"_id":1},"name":"_id_","ns":"Loc8r.locations"},{"v":1,"key":{"coords":"2dsphere"},"name":"coords_2dsphere","ns":"Loc8r.locations","background":true,"2dsphereIndexVersion":2}]}


Selecting distinct rows from two tables, using multiple conditions

Schematically:

SELECT offices.id
-- join tables
FROM offices
JOIN meetings ON offices.id = meetings.office_id
-- select only rows for Jan and Feb 2020
WHERE DATE(meeting_date) BETWEEN '2020-01-01' AND '2020-02-29'
-- grouping - prepare calculations for separate offices
GROUP BY offices.id
-- check that the amount of `allowed_lunch` in Feb is zero
HAVING NOT SUM(DATE(meeting_date) BETWEEN '2020-02-01' AND '2020-02-29' AND allowed_lunch)
-- but total is not zero (taking into account prev. check - they're in Jan)
   AND SUM(allowed_lunch)


Yes, this is expected behaviour when you are using the non-standard MySQL "feature" of unorthodox GROUP BY. See this related post at SO: Why does MySQL add a feature that conflicts with SQL standards?

Your query basically says to the engine that you want the maximum date and arbitrary values for the other columns. And MySQL does that, exactly. It gets you the maximum date and arbitrary values for the other columns. You should consider yourself lucky that the values came form the same row. They might have been from different rows as well!

Now, to solve your issue, don't use this feature - unless you know very well what you are doing!. Here is another way to get the expected results. Don't use MAX() in the SELECT - which does an implicit GROUP BY the whole set - but do an ORDER BY date DESC and then LIMIT 1 to get only the row with the highest date:

SELECT mod_coli_id, line_number,total_ cost,total_sell, modify_date
FROM mod_customer_order_line_item
WHERE customer_order_id = 17761
  AND modify_date <= '2016-09-14'
ORDER BY modify_date DESC
LIMIT 1 ;


Handling SQL updates for production deployments

I am not sure if you still need an answer, but I had the same problem with managing and deploying schema changes. Sqitch was the first thing I tried to mechanize migrations, but I did not really like it.

Just as you described, we have separate .sql files with CREATE TABLE and other instructions reflecting our database schema. We created simple tool to generate SQL patches when schema changes. Take a look at it: https://github.com/condograde/sqlibrist. There is detailed tutorial, how to use it.


How do I get only those values that has related elements from other table?

Do it as GROUP BY aggregation

select
    c.id, c.name,
    count(distinct co.country_id) as amount,
    count(distinct u.id) usercount
from countries as c
left join companies as co on co.country_id = c.id
left join users as u on u.company_id = co.id
group by c.id, c.name
order by amount desc;


Is it possible for increasing the SQL Server max memory limit to degrade performance?

Generally speaking, no, there is no detrimental effect from maxing out memory once the OS and other applications have been accounted for. This is exemplified through the default settings of SQL Server:

  The default setting for min server memory is 0, and the default setting for max server memory is 2147483647 MB.

SQL Server Memory Configurations (MSDN)

While this setting allows dynamic re-sizing of the memory SQL Server acquires, it also goes to show that there isn't a number that is "too much" (aside from OS/app/version considerations). SQL Server will only use the memory it requires, even though it may reserve more based on the min/max settings. Giving it access to as much as possible simply gives it the room it may need to operate efficiently.

As Shanky mentioned, in 2012 and onwards, SQL also uses this setting for thread stacks, CLR allocations, and more which may have other effects in specific scenarios when it comes to memory optimization.


detaching database results in error "SQL Server detected a logical consistency-based I/O error"

Torn page usually. Do you have backups? DBCC may be able to fix it though

The king of DBCC is Paul Randal: to avoid my copy/paste, read these from him

SQL Server 2008: Automatic Page Repair with Database Mirroring
Whitepaper

And search his site too for "logical consistency"


is there any reason to explicitly grant the server permission CONNECT SQL?

When you create a login, it is granted CONNECT SQL.

It has nothing to do with user.
When you create a user, it does not automatically create a corresponding login,
and CONNECT SQL is a server level permission that can be granted only to a server level principal, i.e. to login or a server role.

The common use of this permission is the following:

Suppose you have a Windows group as a login, in this case all the members of this group can access the server. One day it happens that one member of the group must have no access to server, but it cannot be removed from Windows group that accesses the server. In this case you can DENY CONNECT SQL to that individual account, so the whole group will still access the server and only that account will not


For starters, why are you reversing your fields in the datediff?

SELECT DATEDIFF(d, GETDATE(),crDate), COUNT(*)
FROM tracexml
GROUP BY DATEDIFF(d,crDate, GETDATE())

Number two, why does this not work for you?

SELECT crDate, COUNT(*)
FROM tracexml
GROUP BY crDate


SSIS How can I write to a Specific Cell in an Excel Sheet

INSERT INTO [Sheet1$D2:D2] VALUES ('DOB')

Worked. It appears that you can only use UPDATE if there is already data in the cell and you can only use INSERT if the cell is blank (which it was in my case).

There was a lot of very useful information relating to this topic in the following link

http://www.winautomation.com/forum/updating-excel-through-sql-statement


Are indexes need for parts of primary key?

A compound index can also support queries that compare columns that are at the beginning of the indexed values (but aren't all). So an index on (field1, field2) (which is implicitly created for a primary key) would also support queries that compare only field1.
But it cannot support queries that only compare columns of its end (but aren't all). So (field1, field2) won't support queries that only compare field2.
So yes, you need to create an extra index only on (field2) to have a chance it supports queries only comparing field2.


MySQL select takes 0.2 sec; equivalent UPDATE takes 1.5 minutes!

I don't know the answer to your question, but you might try a regular update like:

UPDATE skus S
    SET S.active = 0
      , S.updated_by=101355
      , S.updated_at = NOW()
WHERE EXISTS (
    SELECT 1
    FROM group_frames GF
    WHERE GF.frame = S.frame
      AND GF.groupNum = 204
      AND GF.frameDesc LIKE '%lumbar%'
);

Indexes like:

CREATE INDEX ... ON skus (frame);
CREATE INDEX ... ON group_frames (groupNum, frame, frameDesc);

will probably help


Data Pipeline load bulk tables in one time

I did this... i made a shell script and include all my tables there into a loop and store it in S3 and in pipeline ...i mentioned the path to script ...at scheduled time...the script runs and sync the data daily to Redshift. :) :Y


MySQL to SQL Server, How to replicate data in a set interval?

As listed in the comments Linked Server is the best connection method, it integrates into your views and stored procedures.

The way I have done it in situations like this is create a view or a job on the source database that filters out the changed rows.  I have gone as far as using temporary tables on the MySQL side, then from the MSSQL side read the temporary table you created. If you created joins in a query on the MSSQL side looking at the MySQL, the MySQL will just send all of the data over and MSSQL will have to process it, that's what you are trying to avoid.  You just want the changed data.

Another method probably is the easiest is to create matching tables on the MSSQL side and use a product list SQL Data Examiner, then set the command line utility to run a project in the task scheduler. http://www.sqlaccessories.com/sql-data-examiner/  This will compare the actual data on both tables.  If you are talking millions of rows or a slow connection this isn't the way.  Just like doing it all in MSSQL you will have the issue where it pulls all of the data for the comparison.

So the best way depends on how many tables, how much data, and the connection capabilities.  The easiest would be SQL Data Examiner.


If you want to use the linked server defined in a SQL Server instance, then you need to connect to the SQL Server instance that hosts the linked server, and make sure that your commands reference the linked server objects using 4-part naming. The SQLCMD program has no idea what a linked server is, so it can't connect to a linked server directly.

Let's say your linked server is named MyLinkedServer then your .sql file should look something like this:

INSERT SQLServerDatabase.dbo.TableName(columns) -- SQL Server table
  SELECT columns FROM MyLinkedServer..[Schema].[TableName]; -- Oracle table

Or:

INSERT SQLServerDatabase.dbo.TableName(columns) -- SQL Server table
  SELECT columns FROM OPENQUERY
  (
    MyLinkedServer,
    N'SELECT columns FROM [Schema].[TableName]' -- Oracle table
  );

And your SQLCMD would not say anything about the linked server name at all, because it gets information about the linked server from inside the script:

SQLCMD -E -S Server\Instance -i g:\...\file.sql -o g:\...\log.txt

Of course there are other ways to run queries against Oracle if you don't need to rely on (a) SQLCMD or (b) the linked server defined in the SQL Server instance.


This is a Gap & Island problem. Plenty of sites talk about it if you Google it. Here is just the first link I clicked among many others: Solving Gaps and Islands with Enhanced Window Functions

Here is how the query works:

YYYYMM is a varchar and does not give consecutive numbers. Therefore it first changes them to proper date format.
then ROW_NUMBER() will number them by group of Member and [Plan]
consecutive months will have consecutive rn and once rn is removed from the date, they will have identical values for DATEADD(month, rn, l1.dt)

Member  Plan    dt          rn  => DATEADD(month, rn, l1.dt)
2       123     2015-12-01  1   => 2016-01-01
2       123     2015-11-01  2   => 2016-01-01
2       123     2015-10-01  3   => 2016-01-01
2       123     2015-06-01  4   => 2015-10-01
2       123     2015-05-01  5   => 2015-10-01

DATEADD(month, rn, l1.dt) is finally used to group them along with Member and Plan

Query Oracle:

WITH dates AS(
    SELECT DISTINCT Member, "Plan"
        , dt = CAST(YYYYMM || '01' as date)
    FROM data
), list AS (
    SELECT *
        , rn = ROW_NUMBER() OVER(PARTITION BY Member, "Plan" ORDER BY dt DESC)
    FROM dates d
)
SELECT l1.Member
    , MIN(l1.dt) as YYYYMM_Begin
    , MAX(l1.dt) as YYYYMM_End
    , l1."Plan"
FROM list l1
GROUP BY Member, "Plan", ADD_MONTHS(l1.dt, rn);

Oracle [SQL Fiddle][]

Query SQL Server:

WITH dates AS(
    SELECT DISTINCT Member, [Plan]
        , dt = CAST(YYYYMM+'01' as date)
    FROM @data
), list AS (
    SELECT *
        , rn = ROW_NUMBER() OVER(PARTITION BY Member, [Plan] ORDER BY dt DESC)
    FROM dates d
)
SELECT l1.Member
    , MIN(l1.dt) as YYYYMM_Begin
    , MAX(l1.dt) as YYYYMM_End
    , l1.[Plan]
FROM list l1
GROUP BY Member, [Plan], DATEADD(month, rn, l1.dt);

SQL Server SQL Fiddle

Output:

Member  YYYYMM_Begin    YYYYMM_End  Plan
1       2016-01-01      2016-02-01  123
1       2015-10-01      2016-01-01  456
2       2015-05-01      2015-06-01  123
2       2015-10-01      2015-12-01  123


Why am I seeing high execution counts for the system stored procedure sys.sp_columns_managed

This script below shows you the execution count of the stored procedures that are currently in your plan cache.

set transaction isolation level read uncommitted

SELECT DB_NAME(st.dbid) DBName
      ,OBJECT_SCHEMA_NAME(st.objectid,dbid) SchemaName
      ,OBJECT_NAME(st.objectid,dbid) StoredProcedure
      ,max(cp.usecounts) Execution_count
 FROM sys.dm_exec_cached_plans cp
         CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st
 where DB_NAME(st.dbid) is not null and cp.objtype = 'proc'
   group by cp.plan_handle, DB_NAME(st.dbid),
            OBJECT_SCHEMA_NAME(objectid,st.dbid),
   OBJECT_NAME(objectid,st.dbid)
 order by max(cp.usecounts) desc

That would be an starting point, if you could see the procedure there and from which database it is fired.

Then have a look who and what is using that particular database\calling that procedure.

here you can find the code of the sys.sp_columns_managed stored procedure.


Calculate Inventory Costing Based on First in, First Out (FIFO)

I want to apologize up front.  I don't work with MySQL but maybe this SQL Server answer will help give you some guidance.

I think what you really want is a Remaining_Inventory column added onto each row of purchase_order_items (or a separate table).  Then each transaction can update the appropriate row in your FIFO methodology.  Then your inventory cost report is simply something like:

SELECT Item,
SUM(Cost_Of_Remaining_Inventory) AS Sum_Of_Inventory
FROM
(
    SELECT Item AS 'Item',
    (Cost * Remaining_Inventory) AS 'Cost_Of_Remaining_Inventory'
    FROM Purchase_Order_Items_Tracker
) Window_View
GROUP BY Item

You would then need to fill this table out with your initial data, and I think I have a CURSOR which will do what you need.  Again written for SQL Server.

First I recreated your tables with Table Variables just so i could test everything out:

DECLARE @Purchase_Order_Items TABLE
(
    PO VarChar(5),
    [Date] DATETIME,
    Quantity INT,
    Item VarChar(4),
    Cost DECIMAL(10,2)
)

DECLARE @Item_Inventories TABLE
(
    ID VarChar(5),
    [Date] DATETIME,
    Adjustment_Qty INT,
    Item VarChar(4)
)

INSERT INTO @Purchase_Order_Items (PO, [Date], Quantity, Item, Cost)
VALUES('PO001', '2019-01-01', 1, 'AO21', 12.50),
('PO002', '2019-01-02', 3, 'AO21', 10.99),
('PO003', '2019-01-09', 2, 'AO21', 12.00)

INSERT INTO @Item_Inventories (ID, [Date], Adjustment_Qty, Item)
VALUES ('1O001', '2019-01-01', 1, 'AO21'),
('1O002', '2019-01-02', 3, 'AO21'),
('1O003', '2019-01-04', -1, 'AO21'),
('1O004', '2019-01-05', -1, 'AO21'),
('1O005', '2019-01-09', 2, 'AO21')

Then I created a "Tracker" table for keeping track of the remaining inventory for a given PO as we work through the cursor.

DECLARE @Purchase_Order_Items_Tracker TABLE
(
    PO VarChar(5),
    [Date] DATETIME,
    Quantity INT,
    Item VarChar(4),
    Cost DECIMAL(10,2),
    Inventory_Remaining INT
)

Then running the below code to populate said table and then query off it should give you what you are looking for

DECLARE @Date DATETIME,
        @Adjustment_Qty INT,
        @Item VarChar(4),
        @Adjustment_Qty_Transaction INT,
        @Item_Inventories_PO_Transaction VarChar(5)

DECLARE Inventory_Cursor CURSOR FOR
--getting the absolute value of Adjustment_Qty so that we can only work with positive number
---The WHERE condition makes sure we are only working with records where the inventory was subtracted from, not added to
SELECT [Date], ABS(Adjustment_Qty) AS Adjustment_Qty, Item
FROM @Item_Inventories
WHERE Adjustment_Qty < 0 --That way we are only looking at removing of inventory
                         --Not logs where inventory was added

OPEN Inventory_Cursor
FETCH NEXT FROM Inventory_Cursor INTO @Date, @Adjustment_Qty, @Item

WHILE(@@FETCH_STATUS = 0)
BEGIN
    --Load in any new inventory
    INSERT INTO @Purchase_Order_Items_Tracker (PO, [Date], Quantity, Item, Cost, Inventory_Remaining)
    SELECT
    I.PO,
    I.[Date],
    I.Quantity,
    I.Item,
    I.Cost,
    I.Quantity
    FROM @Purchase_Order_Items I
        LEFT OUTER JOIN @Purchase_Order_Items_Tracker T
            ON I.PO = T.PO
    WHERE I.Item = @Item --Transaction is for our item in scope
    AND I.[Date] <= @Date --Purchase order recieved at or before this inventory change
    AND T.PO IS NULL --Record not already inserted into the tracker

    --Then Subtract from inventory
    WHILE(@Adjustment_Qty > 0)
    BEGIN
        SET @Adjustment_Qty_Transaction = NULL
        SET @Item_Inventories_PO_Transaction = NULL

        --You will need some kind of error handling for if there are no records found
        --which means at the time of trying to take out inventory
        --our records show that there was no inventory for that item
        SELECT TOP(1)
            @Adjustment_Qty_Transaction = Inventory_Remaining,
            @Item_Inventories_PO_Transaction = PO
        FROM @Purchase_Order_Items_Tracker
        WHERE Item = @Item
        AND Inventory_Remaining > 0
        AND [Date] <= @Date
        ORDER BY [Date] DESC

        IF(@Adjustment_Qty <= @Adjustment_Qty_Transaction)
        BEGIN
            SET @Adjustment_Qty_Transaction = @Adjustment_Qty_Transaction - @Adjustment_Qty
            SET @Adjustment_Qty = 0
        END
        ELSE
        BEGIN
            SET @Adjustment_Qty = @Adjustment_Qty - @Adjustment_Qty_Transaction
            SET @Adjustment_Qty_Transaction = 0
        END

        UPDATE @Purchase_Order_Items_Tracker
        SET Inventory_Remaining = @Adjustment_Qty_Transaction
        WHERE @Item_Inventories_PO_Transaction = PO
    END

    FETCH NEXT FROM Inventory_Cursor INTO @Date, @Adjustment_Qty, @Item
END
CLOSE Inventory_Cursor
DEALLOCATE Inventory_Cursor

--Then make sure that we have all other inventory additions added
--In case we got new inventory but have not had inventory taken out since it was added

INSERT INTO @Purchase_Order_Items_Tracker (PO, [Date], Quantity, Item, Cost, Inventory_Remaining)
    SELECT
    I.PO,
    I.[Date],
    I.Quantity,
    I.Item,
    I.Cost,
    I.Quantity
    FROM @Purchase_Order_Items I
        LEFT OUTER JOIN @Purchase_Order_Items_Tracker T
            ON I.PO = T.PO
    WHERE T.PO IS NULL --Record not already inserted into the tracker

SELECT *
    FROM @Purchase_Order_Items_Tracker

SELECT Item AS 'Item',
    [Date],
    (Cost * Inventory_Remaining) AS 'Cost_Of_Remaining_Inventory'
    FROM @Purchase_Order_Items_Tracker

SELECT Item,
SUM(Cost_Of_Remaining_Inventory) AS Sum_Of_Inventory
FROM
(
    SELECT Item AS 'Item',
    (Cost * Inventory_Remaining) AS 'Cost_Of_Remaining_Inventory'
    FROM @Purchase_Order_Items_Tracker
) Window_View
GROUP BY Item

The 3 final SELECT queries return the below 3 Tables:

+-------+-------------------------+----------+------+-------+---------------------+
|  PO   |          Date           | Quantity | Item | Cost  | Inventory_Remaining |
+-------+-------------------------+----------+------+-------+---------------------+
| PO001 | 2019-01-01 00:00:00.000 |        1 | AO21 | 12.50 |                   0 |
| PO002 | 2019-01-02 00:00:00.000 |        3 | AO21 | 10.99 |                   2 |
| PO003 | 2019-01-09 00:00:00.000 |        2 | AO21 | 12.00 |                   2 |
+-------+-------------------------+----------+------+-------+---------------------+

+------+-------------------------+-----------------------------+
| Item |          Date           | Cost_Of_Remaining_Inventory |
+------+-------------------------+-----------------------------+
| AO21 | 2019-01-01 00:00:00.000 |                        0.00 |
| AO21 | 2019-01-02 00:00:00.000 |                       21.98 |
| AO21 | 2019-01-09 00:00:00.000 |                       24.00 |
+------+-------------------------+-----------------------------+

+------+------------------+
| Item | Sum_Of_Inventory |
+------+------------------+
| AO21 |            45.98 |
+------+------------------+

Again all of this is based around SQL Server but it should at least give you a framework to build off of.  And I think in the real implementation you want a Remaining_Inventory Column (or a separate table) which keeps track of what is left on that PO


Should I split my tables into different tables when all of the data is dependant on the primary key?

I think we are short on a little bit of information such as what exactly the license is for and how it is to be used. For example, do all products have exactly one license? Can licenses contain multiple products? As the term license is used, I would imagine that each individual would have their own license to use the product (thus one to many relationship). With that scenario, I will assume that a license is only valid for one product and that several licenses can reference a single a product (a one-to-many relationship).

So to satisfy that requirement, we will need two tables. One for the products, and one for the licenses. The product table would contain all product information such as:

Products
-----------
ProductID
Name,
Description,
Price,
(etc)

Licenses
--------
LicenseID,
ProductID,
StartDate,
EndDate,
Amount, -- I believe this is to represent how many individual licenses of this one is used
Limit,  -- And this is to represent how many people can use this current license
(etc)

With this, we have a few benefits. Now, we can license out an individual product to multiple people (again, I am assuming the product is something like software) and we can now change licenses with out affecting the product and if we need to make changes to the product, all the licenses will automatically be updated. A good example of this is the changing of a product description. If products were stored with the licenses, we would have to change all the licenses that used that same product. That's not good and prone to having inconsistent data.

If I am off base, feel free to let me know. As you can see, I made quite a few assumptions.


More than 20 columns in database table MySQL

There is nothing wrong with tens of columns in a table. Depending on the property types it can be more efficient in terms of space than a separate properties table, and it is almost always faster to query if you want several of the properties at once in one row of a resultset, i.e. it you want to return:

Thing  Prop1  Prop2  Prop3
-----  -----  -----  -----
T1     T1P1   T1P2   T1P3
T2     T2P1   T2P2   T2P3

instead of:

Thing  Property  Value
-----  --------  -----
T1     P1        T1P1
T1     P2        T1P2
...

because you won't have to have many joins (or, worse, subqueries) to the properties table to transpose the data. Of course if you regularly need the data in this transposed format then this recommendation is reversed as you'll need to transpose the other way (with several unions) otherwise. Even if your DB has efficient transposition functions, make sure the data is in the arrangement you are most likely to need it in most of the time.

Most DBs allow many columns per row and are often not much less efficient with wide tables than they are with thin ones (unless you "select *" all the time and/or have bad index choices).

The massive caveat to the above is that you should be careful that you are not creating a nightmare for yourself by way of denormalisation. We could only give advice on that matter if we knew what the column contents were expected to be.


How can I effectively filter a huge table using another big list?

Just JOIN the big table to list.
Just make sure your list is a table format:

External Table
Global Temporary Table
APEX Collection (for APEX apps)
an SQL collection (variable)

PL/SQL collection (18c+)


How can I find out if a user's password has expired in MySQL?

This information, along with other user properties, is found in the user table in the mysql schema (database). The value 'Y' would indicate an expired password

select password_expired from mysql.user where user = 'whatever'


When to prefere a truncate over a batch-delete?

There are certain circumstances where you cannot use the TRUNCATE TABLE command and must use the DELETE command instead.

  You cannot use TRUNCATE TABLE on tables that:

  • Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

  • Participate in an indexed view.

  • Are published by using transactional replication or merge replication.

  For tables with one or more of these characteristics, use the DELETE statement instead.

  TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. For more information, see CREATE TRIGGER (Transact-SQL).

There is a good explanation on MSDN:

https://msdn.microsoft.com/en-gb/library/ms177570.aspx

Also, see this question on Stack Overflow which has a lengthy discussion on the topic.


Allow user to do anything within his own schema but not create or drop the schema itself

There is no need to grant CONTROL on the schema.
The permission required to DROP SCHEMA is either CONTROL on the schema or ALTER ANY SCHEMA at the database level, and that is why your user was able to drop the schema. Removing these two permissions will prevent the role-associated users from creating and droping the schema (unless they have higher level permissions of course).

The required permission to CREATE ALTER and DROP other objects is the CREATE permission for the object type (table\procedure\function\view) combined with ALTER permission on the schema.
You already have these permissions in your script, so all that you have to do is remove the CONTROL permission. For reference, here is a BOL list of DDL statements where You can find the required permission for all object types.

For the lazy, here is your code after removing the unnecessary permission:

CREATE ROLE myrole AUTHORIZATION dbo;
EXEC sp_addrolemember 'myrole', 'myuser';

CREATE SCHEMA myschema AUTHORIZATION dbo;

GRANT ALTER, DELETE, EXECUTE, INSERT, REFERENCES, SELECT,
          UPDATE, VIEW DEFINITION ON SCHEMA::myschema TO myrole;

GRANT CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, CREATE VIEW TO myrole;


The best solution to this problem is nearly always method 2, but with two tables UNION ALL instead of LEFT JOIN together (one to hold the deleted, one for the active). There are several reasons why this is superior:

You can maintain separate statistics on active and deleted rows. Assumption: you will have more deleted than active  over time. This means that the "active rows" table stays small and doing fullscans is easier. It also makes things like index rebuilds (if you do them) faster on the active rows.
Statistics of old rows will not affect news rows. This helps you avoid skew problems.
Each table can live on it's own filegroup. This means you can move deleted rows off to cheaper storage.
You can have different indexing/partitioning strategies on the deleted rows vs. the active ones. For example, you may choose to use a column store index on the old rows if they are read via a scan often, but changed very rarely.
The deleted table can be taken offline with a table SWITCH (for maintenance) without disrupting the system too much.

The two table solution can be implemented with a DELETE instead of trigger that moves the rows to deleted instead of deleting them.

The only downside of solution 2 is that you will need to modify your queries to distinguish between deleted and not deleted rows and tables. This can be done via a view, but it is safer to avoid this if possible. The view can confuse the optimiser and there are cases where using a view instead of a table gives you horrible execution plans. If you do use a view, you should add an `IsDeleted' column to both tables and put a check constraint on it. This ensure that the optimiser will not try to seek BOTH table for every query (which would double your IOPS).

For completeness, and to answer your last question, there are some other ways to "solve" this:

Method 4: Add an IsDeleted column and partition on this column. Drawback: Does not allow separate statistics on the two partitions (statistics are table, not partition based, in SQL Server)

Method 5: Use a filtered index to apply different indexing (and secondary storage) on deleted and active rows. Drawback: Good luck getting the optimiser to behave properly with this method.


What you need to do is refactor the query as follows:

Subquery #1 : Collect all the property_id values that have all 3 amenities.

SELECT property_id,COUNT(1) amenity_count
FROM apartment_amenity
WHERE amenity_id IN (1,2,3)
GROUP BY property_id
HAVING COUNT(1) = 3;

Subquery #2 : Collect pid and propertytype values from property table by address

SELECT property_id FROM property
WHERE approval='Y'
AND country like '%india%'
AND (city like '%indore%' OR state like '%madhya pradesh%')
AND Maximum_Capacity >=1;

Subquery #3: Join Subquery #1 and Subquery #2

SELECT B.pid,B.propertytype FROM
(SELECT property_id,COUNT(1) amenity_count FROM apartment_amenity
WHERE amenity_id IN (1,2,3) GROUP BY property_id HAVING COUNT(1) = 3) A
INNER JOIN
(SELECT pid,propertytype FROM property
WHERE approval='Y' AND country like '%india%'
AND (city like '%indore%' OR state like '%madhya pradesh%')
AND Maximum_Capacity >=1) B

Impose ORDER BY and LIMIT 10 on Subquery #3

SELECT B.pid,B.propertytype FROM
(SELECT property_id,COUNT(1) amenity_count FROM apartment_amenity
WHERE amenity_id IN (1,2,3) GROUP BY property_id HAVING COUNT(1) = 3) A
INNER JOIN
(SELECT pid,propertytype FROM property
WHERE approval='Y' AND country like '%india%'
AND (city like '%indore%' OR state like '%madhya pradesh%')
AND Maximum_Capacity >=1) B
ORDER BY is_online_bookable LIMIT 10

Join Subquery #3 to property and property_type tables

SELECT PR.*,PT.property_type
FROM
(
    SELECT B.pid,B.propertytype FROM
    (SELECT property_id,COUNT(1) amenity_count FROM apartment_amenity
    WHERE amenity_id IN (1,2,3) GROUP BY property_id HAVING COUNT(1) = 3) A
    INNER JOIN
    (SELECT pid,propertytype FROM property
    WHERE approval='Y' AND country like '%india%'
    AND (city like '%indore%' OR state like '%madhya pradesh%')
    AND Maximum_Capacity >=1) B
    ORDER BY is_online_bookable LIMIT 10
) K
INNER JOIN property PR      ON K.pid = P.pid
INNER JOIN property_type PT ON K.propertytype = PT.property_type_id;

CAVEAT #1

You have to rebuild Subquery #1 every time you get a new set of amenities and make sure the HAVING COUNT(1) = match the sum of the amenities selected.

CAVEAT #2

Subquery #2 is going to have the worst performance because of

using LIKE
approval='Y' (Cardinality of 2, Y and N values)
Maximum_Capacity >=1 would just be a full scan of all properties

A compound index of (approval,Maximum_Capacity) would be useless

SUGGESTION

Change Subquery #2 from

(SELECT pid,propertytype FROM property
WHERE approval='Y' AND country like '%india%'
AND (city like '%indore%' OR state like '%madhya pradesh%')
AND Maximum_Capacity >=1) B

to

(SELECT pid,propertytype FROM property
WHERE approval='Y' AND country = 'india'
AND (city like 'indore' OR state like 'madhya pradesh')
AND Maximum_Capacity >=1) B

You could then create a compound index on (country,city,state)

You could also create a FULLTEXT index and change Subquery #2 to do FULLTEXT searching

There are other things you could change for Subquery #2. The main point I am making ? Optimize Subquery #2 as much as you can for the best performance.

EPILOGUE

I have suggested refactoring queries before

May 16, 2011 : Fetching a Single Row from Join Table (StackOverflow)
May 21, 2011 : How can I optimize this MySQL query further?
Jun 24, 2011 : What is more efficient, a where clause or a join with million plus row tables?
Dec 04, 2012 : How do I terminate a running SELECT query without restarting MySQL?


Better way of re-arranging the indexes for better performance

Some of those indexes seem to have a lot of included columns. A table definition will provide context into the width of these columns. What is the clustered index key?

You need to look at the workload being run against this table. What queries are being executed, how often are they being executed, is this going to change in the future?

I would probably start with looking at current index usage statistics to see how indexes are being used (Seeks, Scan, Key Lookups, Updates).

SELECT
OBJECT_NAME([ddius].[object_id]) AS [Table name] ,
[i].[name] AS [index name] ,
i.type_desc,
user_seeks,
user_scans,
user_lookups,
user_updates
FROM sys.indexes i
left outer join [sys].[dm_db_index_usage_stats] ddius on  ddius.index_id = i.index_id
and [ddius].[object_id] = [i].[object_id]
WHERE [ddius].[database_id] = DB_ID()
order by [Table name]

You can also look at the missing index DMV. However, you need to take the information returned with a grain of salt. SQL will allow you to create duplicate indexes, an index creation suggestion is "recommended" for a specific query but there may be combined indexes. Unfortunately, indexing is a bit of trial and error.


SQL Server Redundancy without Client Application Changes

Clustering:
For minimal changes to your client application clustering is transparent, you wouldn't need to change a thing.  Although it will require the most work to setup.  To go from a stand-alone installation which you have to a clustered setup, this will be a lot of work.  You'd basically have to build a new clustered instance & move the database across.  You'll also need shared storage to make this work.

Mirroring:
With SQL Server Standard you are stuck with High Safety mirroring which can mean a performance hit. Good news is that you don't need shared storage and you should be able to build a new 2005 instance and then setup mirroring. Bad news is you'll  need to change your connection string to include a fail-over partner if you want to have a seamless fail-over.  This also only fails over the user database, so you'll need a process to manage moving/synching SQL Agent jobs and logins.

I wouldn't recommend using SQL Server replication for redundancy.

You might also want to consider log-shipping. This would be a lower-complexity alternative to mirroring although failover is only manual.


How to login to a remote SQL Server instance via IP and specify the Server name?

The solution that worked was to create an alias of (server name => IP address of the publisher) on the local machine on the configuration manager.

Though I suspect that this could also have been added to the windows host file on the local server

On configuration manager, I added the following:

Name: <@@SERVERNAME>
Server: IP address
Protocol: TCP (not sure why this isn't named pipes)
port: 1433


Prevent T-SQL code that is not compatible to break the script

Instead of doing the sysdatabases.comptlevel check inside of your dynamic SQL,  pull the sysdatabases.cmptlevel check out to the top-level batch and use it to determine which databases to build/run dynamic queries against.  Alternatively, use the cmptlevel to customize the main query as needed.

Whether you build one big SQL query to hit all desired databases, or use a cursor/loop to build/run a dynamic query for each desired database, is up to you.

A quick, pseudo-code outline of a cursor/loop solution:

declare @dbname varchar(30), @cmptlevel int, @sql varchar(max)
declare dbcur cursor for select name, cmptlevel from sysdatabases
open dbcur
fetch dbcur into @dbname, @cmptlevel
while @@sqlstatus = 0
begin
    select @sql='... common query code ... add '@dbname..' prefix to tables as needed ... '+
                case when @cmptlevel <  90 then '... cmptlevel< 90 specific code ... add '@dbname..' prefix to tables as needed ...'
                     when @cmptlevel >= 90 then '... cmptlevel>=90 specific code ... add '@dbname..' prefix to tables as needed ...'
                end +
                '... common query code ... add '@dbname..' prefix to tables as needed ... '
    exec sp_excecutesql @sql
    fetch dbcur into @dbname, @cmptlevel
end
close dbcur


PostgreSQL Common Table Expression: Ambiguous Column

While there is no real solution that matches your requirements, there are a few things to consider about the requirements.

First, let's see the hint that an error raised about a misnamed column produces:

WITH ts AS (
    SELECT * FROM t1 JOIN t2 USING (t_id)
) SELECT col2 FROM ts;

ERROR:  column "col2" does not exist
LINE 1: ...AS (SELECT * FROM t1 JOIN t2 USING (t_id)) SELECT col2 FROM ...
                                                             ^
HINT:  Perhaps you meant to reference the column "ts.col1" or the column "ts.col1".

Even the database suggests to choose from two indentically named columns - which, as you already guessed, impossible.  (Interestingly, you cannot do such a thing in a definition of a view, not to mention tables.)

So, about the requirements:

It is usually wise not to use * in the first place.  Apart from being faster to type, it does not bring too much.  When writing the main SELECT, we usually have an idea which columns we need - just add those inside the CTE.  This might be in some cases even beneficial for the performance of the query (rendering the size of the data set smaller).

When you want to make it simpler to type, you could try something like

SELECT f.*, s.data AS s_data
FROM first AS f JOIN second AS s ON f.id = s.key

In my practice, it is even better to name columns with a prefix derived from the table name:

CREATE TABLE first(
    f_id serial PRIMARY KEY,
    f_data VARCHAR,
    f_date DATE
);

CREATE TABLE second(
    s_id serial PRIMARY KEY,
    s_first_id INT REFERENCES first(id), -- an exception, for making more clear
                                         -- what the column refers to
    s_data VARCHAR,
    s_date DATE
);

This way, even in big databases, it is very rare to have columns with the same name.  And this completely removes the problem you are fighting with.


column {table_name} of relation {table_name} does not exist SQL state: 42703

You cannot (and need not) use table aliases (or tablename qualified column names) in the SET clause of an UPDATE.  This even makes sense, as you can only update a single table in a single UPDATE, so there is no ambiguity in column names there.

Fortunately, the ever helpful documentation explicitly mentions your case:

  column_name

  The name of a column in the table named by table_name. The column name can be qualified with a subfield name or array subscript,
  if needed. Do not include the table's name in the specification of a
  target column — for example, UPDATE tab SET tab.col = 1 is invalid.

So, the solution is to simply remove temp. from the SET clause:

UPDATE temp SET id = '234', name = 'Test' WHERE id = '245'

Notes:

Are you really storing numbers as text?  If yes, why?  It is usually a recipe for disaster.  For example, how do you prevent something like 'mkjcvnd7y78r3tgbhvcjh' entering your id column?
The way you are using object names starting with capital letters is confusing.  Without double-quoting its name, your table in reality is called temp as opposed to Temp.  Using it the latter way may decrease readability (depending on your preferences and habits, of course).


How do I deal with items that I need to repeat monthly?

You shouldn't try to do too many things with one data store.

If you have a table for transactions, then just keep transactions in that table.  Don't mix in scheduled future transactions with current transactions.  Actual (historical) transactions will have different attributes (columns) than future, scheduled transactions.

Keep the recurring/scheduled transactions (like rent) in a separate table.  When the rent is paid, create a new record in the (actual) transaction table.

The scheduled transaction table could have a totally different structure.  Instead of keeping a record for each expected transaction, you could keep one record with a date range and recurrence fields.  This would save having to update multiple records when the rent price changes, for example.  Consider something like this for your scheduled/recurring transaction table:

Scheduled_Transaction:=

CustomerID
StartDate
EndDate
Recurrence (monthly, weekly, every two weeks, - whatever you need)
Amount


Can a partitioned table span on multiple files across different file groups?

Yes, it can. (BOL reference on Partitioned Tables and Indexes)

  SQL Server supports table and index partitioning. The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database.


Postgresql regex match all digits in column

Better use regexp_replace, globally replacing non-digits by an empty string:

select regexp_replace('dsa8a552a5a2a5?', '\D', '', 'g');

 regexp_replace
----------------
 8552525


group by and display contents of last row for each result

You can use DISTINCT ON for this type of queries:

SELECT DISTINCT ON (object_id) *
FROM data
ORDER BY object_id, dt DESC ;


How to synchronize multiple instances of a stored procedure executing at the same time in SQL Server 2012?

You have not posted any code so I'll make some assumptions based on the additional information you added to your question.  Once a batch_id is assigned for the upload, the application should use that value when inserting related rows rather than retrieve a batch_id again, which may be different due to concurrency.  It seems you may be inserting rows individually and retrieving the batch_id repeatedly, either in code or the proc.

There are a couple of ways to fix the problem.  One is to add a @batch_id parameter to the proc and change the code to pass the value from the related MASTER_CDT insert (i.e. returned with SCOPE_IDEINITY()) for each spreadsheet row insert.

A more elegant solution is to pass the spreadsheet contents as a table-valued parameter like the example below.  Not only will this address the concurrency problem, it will be much more efficient than individual inserts.  Whether or not this is feasible depends on the language/API you are using.

--create table type for TVP
CREATE TYPE dbo.EmployeeSpreadsheetRows AS TABLE(
      employee_id int
    , name nvarchar(100)
    , email varchar(255)
    , phone_number varchar(20)
    , date_of_birth date
    , address nvarchar(100)
    , department nvarchar(100)
    , manager nvarchar(100)
    , validation_message nvarchar(1000)
    );
GO

CREATE PROC dbo.InsertSpreadsheet
      @FileName varchar(255) --values needed for MASTER_CDT table insert
    , @EmployeeSpreadsheetRows dbo.EmployeeSpreadsheetRows READONLY --spreadsheet rows
AS
SET XACT_ABORT ON;

BEGIN TRY
    BEGIN TRAN;
    INSERT INTO dbo.MASTER_CDT(FileName) VALUES(@FileName);
    DECLARE @batch_id int = SCOPE_IDENTITY();
    INSERT INTO EMPLOYEE_SPREADSHEET(
          employee_id
        , name
        , email
        , phone_number
        , date_of_birth
        , address
        , department
        , manager
        , validation_message
        , batch_id
        )
    SELECT
          employee_id
        , name
        , email
        , phone_number
        , date_of_birth
        , address
        , department
        , manager
        , validation_message
        , @batch_id
    FROM @EmployeeSpreadsheetRows;
    COMMIT;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 1 ROLLBACK;
    THROW;
END CATCH;
GO


How can I connect to SQL Server from All IPs?

Finally

I found the reason of problem.

I don't know for what SQL or other services had changed the value type of TcpPort fields in registry
(HKEY_LOCAL_Machine\Software\Microsoft\Microsoft SQL Server\Instance ID\MSSQLServer\SuperSocketNetLib\TCP\IPALL\TCPPort ).

It's converted to  Binary or DWORD type.

but the solution :
I removed TcpPort value and then create a new string value and named it TcpPort and set it to 1433 and then service started successfully.

there is no need to say that I checked that field over and over and it doesn't has any whitespaces.

Thanks to all experts whom participate in this discussion and helped me.


How do I truncate the transaction log in a SQL Server 2008 database?

You could backup the log to the null device:

backup log [databasename] to disk = 'nul';

Or you could switch the recovery model to simple and then back to full/bulk again.


How to deploy install of MS SQL Server 2017 Developer Edition via command-line script - not working

In the error is states the reason which seems fairly self-explanatory:

  You have selected a feature that is not supported for Windows Server Core... [Features Supported]

One of your input arguments is SQL which translates into:

SQL Engine
Replication
Fulltext
DQ
Polybase
Advanced Analytics (R)
R Packages

Instead of putting SQL in the features list, only put what you need. If what you need isn't supported on Windows Core, then don't install on Windows core.


Should I split this large table into three smaller tables?

@CoderAbsolute's answer gives you a good design for your tables. Since he or she did not go into detail about why this approach is better, I thought it was worth adding another answer.

First of all, design your table structure in accordance with how your data fits together. Don't try to smoosh different types of things into one table, don't add several tables for the same kind of records. Try to 'normalize' - if a table will have the same info repeated many times, then move that info into a different table, and link it from the first table using a foreign key. You should be aware of what first normal form, second normal form and third normal form are. Many real-world databases do not match these standards completely, but being aware of what you should aim for will help you make a much cleaner design.

I would say, don't worry about optimization until you've already got a correct design. First of all, you don't yet know how many entries you will have in your tables. Secondly, database engines are designed to make queries as fast as possible, even if you have a lot of entries. Don't second guess the developers of your database software.

When you've figured out that you really do have a bottleneck, you should look first at indexing. We think of a database table as being something like an array. In reality, it could be stored as a bunch of 'blobs', one for each row, which might all have different locations on a disk, not necessarily in order. (This is not a super accurate technical description of DB internals. But it should give you a clearer picture of how the pieces fit together.) How does the database find all the blobs? It has a somewhere, a list of 'pointers' which tell it where to find each blob. So typically, finding every single row of a table is an efficient process. It goes through the list and records each one.

Now, suppose that you most commonly retrieve all the photos for a given user. This might be a slow process, since it has to go through every single row of the Photo table, and look at the UserId field. In this case, you should add an Index on that field. An index is something like a lookup table, which allows the software to quickly find the location of all the rows with a given UserId. So it doesn't have to check every row in turn.

There are different kinds of indexes. Some are optimized for matching a particular value. This is probably the type you want for the UserId column. Some are optimized for finding things greater or smaller than some value. This might make sense for timestamps. (Give me all the photos from the last month.) You can have an index on multiple columns if this is what you regularly query on, or even on some function of one or more columns. Some indexes mean that similar items are stored close to each other on disk, to take advantage of caching to retrieve them more quickly.

You should familiarize yourself with the possibilities for your DBMS. Experimentation can also be very valuable here, since the exact speedups depend on your settings and also your hardware configuration.


Need a way to query a table, and JOIN it with the TOP 1 related record from an other table

You can do this pretty easily with OUTER APPLY (if you're on 2005 or newer). Note that there may be better performing ways of achieving the result, such as using ROW_NUMBER() - check execution plans if in doubt. Also, SELECT * is lazy and inadvisable; I'm just doing it here for illustrative purposes, and because I don't know the real structure of the Heartbeats table.

SELECT
    dv.Name,
    hb.*
FROM [Devices] as dv
    OUTER APPLY (
        SELECT TOP 1 *
        FROM Heartbeats
        WHERE Heartbeats.DeviceID = dv.ID
        ORDER BY DateEntered DESC
    ) hb
WHERE ISNULL(hb.DateEntered, '1900-01-01T00:00') < '2013-03-04T00:00'

See Books Online for the finer points of OUTER APPLY vs. CROSS APPLY (it's much like OUTER JOIN vs. INNER JOIN). It was always such a pain doing queries like this in SQL Server 2000 where you didn't have OUTER/CROSS APPLY or the ROW_NUMBER() function.


Restarting One Database Not Entire Server

You can try taking it offline, and then online again:

use master
go

alter database YourDatabase
set offline
with rollback immediate
go

alter database YourDatabase
set online
go

As per BOL:

  OFFLINE : The database is closed, shut down cleanly, and marked offline. The database cannot be modified while it is offline.


Re
Column  Indicates
Key
A unique key identifying this backup set.
If you are connected to a recovery catalog, then BS Key is the primary key of the backup set in the catalog. It corresponds to BS_KEY in the RC_BACKUP_SET view. If you are connected in NOCATALOG mode, then BS Key displays the RECID from V$BACKUP_SET.
TY
The type of backup: backup set (B) or proxy copy (P).
LV
The level of the backup: NULL for nonincrementals, level 0 or 1 for incrementals.
S
The status of the backup: A (available), U (unavailable), or X (all backup pieces in set expired). Refer to the CHANGE, CROSSCHECK, and DELETE commands for an explanation of each status.
Device Type
The type of device on which the backup was made, for example, DISK or sbt.
Completion Time
The date and time that the backup set completed. Note that the format of this field depends on the NLS_LANG and NLS_DATE_FORMAT environment settings.
Pieces
The number of backup pieces in the backup set.
Copies
The number of copies made of each backup piece in the set. The number is 1 if no duplexing was performed. Otherwise, the value ranges from 2 to 4.
Tag
The tag applied to the backup set; NULL if none. An asterisk (*) indicates multiple copies with different tags. Note that tag names are not case sensitive and display in all uppercase.
Reference --> https://docs.oracle.com/cd/B13789_01/server.101/b10770/rcmsynta36.htm#83097
Thanks


It's hard to say if your query will use the index or not because that will be based on what MySQL's optimizer decides is best based on your data distribution and statistics.

In any case, a wildcard at the end is Sargable, so at least the wildcard isn't what's preventing your index usage.
See for example this post by Brent Ozar or this by Rob Farley

On the other hand, if you search for year only my guess would be the query won't be selective enough to use an index, year and month might but that's hard to tell.

See my reply to this question for an explanation: Why is the SQL Server query plan not using indexes in a query almost similar to one that uses indexes?
It's a SQL Server question but the same principle applies.

  Since SQL server reads entire pages, and not records, your query (as
  indicated by statistics) needs to be selective enough for the index to
  be  used, if SQL server estimates that it's going to have to read
  every page  anyway it will go for a table scan.


How to determine if an Oracle table is locked or not?

Following query gives details of all locks.

SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID


How do redundant indexes impact query performance?

If you want to really split hairs, at the very least they affect the compilation time, since the optimizer has more indexes to consider. They also consume time to load and memory to store their metadata. It may seem a trivial amount, but consider the extreme case of an index with 16 columns over 1000 partitions: it requires 16000 metadata objects to read and store in memory, it may add up to quite a bit. And by simply existing, they add storage space which translates into larger/slower full backups, more space needed for ops, slower restores etc.

But, realistically, a redundant index does not affect query performance. I know of some examples when certain indexes may fool the optimizer into generating a bad (slow) plan, but that is a separate topic.


How do you fix Autogrowth being set to 12800%

Unfortunately it's a bug.

You can find all the affected databases by looking in sys.master_files. Write ALTER DATABASE scripts to change it.


ER model between video, tagbundle and tag

What is purpose of tagbundle? For such kind of problems you should put something between tags and tagbundle tables.
Consider table (could be called tag_mapping) that has 2 foreign keys and allows to join tags with bundles together (it has even fancy name - associative_entity).


Interpreting sp_BlitzCache: # Executions but since when?

Scroll across to the right side of the results, and there's a "Created At" column. That column shows when the plan first went into the cache. In most cases, SQL Server tracks the execution count since that time - but there can be exceptions in some cases involving recompiles of a query.
The source is the execution_count column in sys.dm_exec_query_stats.


You could reverse the relationship between passenger and ticket so that the ticket has a nullable FK to the passenger. When you create a new trip, create ticket entries for the number of passengers in the trip / seats on the bus. Then relate a passenger to each ticket as the ticket is sold. When a trip has no more tickets with an empty passenger id that trip is sold out.


SQL Server : CRUD Editor that will track data changes

I won't use a own product for this.

You have two simple ways to do it on your own.

You can use CDC (Change-Data-Capture) which will do this automatically. You can retrieve the changes afterwards for some time. (If you don't need that every value is hold back for ever)
You can create an Before-Update trigger, which will generate a own log for a record before and after the insert. You can just query those log table and everything is fine. The end-user won't see anything of this, he can use Access, SSMS or whatever to achieve the changes.


SELECT Query is slow when run remotely in MySQL

LIMIT will work for the first 5000, but will get slower and slower as you move on (with OFFSET) to subsequent chunks.  So that's not a good way to process a large table in chunks.

Does the table have a PRIMARY KEY?  (If not, why not?)  Use that for very efficient chunking.  It will avoid the timeouts you are getting.

A discussion of chunking.  (That is aimed at DELETE, but it can easily be adapted to SELECT.)  The trick is to "remember where you left off".


How to update information of the data of column in the table matching the certain text?

Its easy : REPLACE (string_expression,string_pattern,string_replacement)

CREATE TABLE FirstLastName
    ([Firstname] varchar(9), [Lastname] varchar(6))
;

INSERT INTO FirstLastName
    ([Firstname], [Lastname])
VALUES
    ('ABCDEF', 'GHIJKL'),
    ('BFGABCF', 'FGYUTY'),
    ('CDFSERABC', 'VVVVVV')
;

select * from FirstLastName

--- now update the table

update FirstLastName
set Firstname = replace (Firstname,'ABC', 'XYZ')
-- optional (else it will do an upadte to entire table) !!
--where somecondition

SQLFIDDLE HERE


Getting all databases which a sql logon can connect

You should be able to do something like this:

EXECUTE AS login = 'loginname';

SELECT name
FROM sys.databases
WHERE HAS_DBACCESS(name) = 1;

REVERT;

For reference see EXECUTE AS and HAS_DBACCESS


Does the spec permit column-count mismatch on INSERT?

Draft documents can be found at:

http://www.wiscorp.com/sql20nn.zip

The part that is of interest for this question is:

7IWD2-02-Foundation-2011-12.pdf

At first I could not find any support for leaving out the column specification at all. However, in 14.11  the BNF looks like:

<insert statement> ::=
INSERT INTO <insertion target> <insert columns and source>
[...]
<insert columns and source> ::=
    <from subquery>
  | <from constructor>
  | <from default>

<from subquery> ::=
  [ <left paren> <insert column list> <right paren> ]
    [ <override clause> ]
    <query expression>

<from constructor> ::=
  [ <left paren> <insert column list> <right paren> ]
    [ <override clause> ]
    <contextually typed table value constructor>

<insert column list> ::=
    <column name list>

I.e. the "column name list" is contained within square-brackets so there may or may not be a column specification. However, in the text under Syntax Rules it mentions:

7) If the <insert column list> is omitted, then an
   <insert column list> that identifies all columns of T in the
   ascending sequence of their ordinal positions within T is implicit.

So, it seems that if the column list is omitted, all columns are assumed and that they should be interpreted from left to right. My guess is that PostgreSQL is too generous in this regard.

For clarity I think it is a good idea to always declare the column-list (except for ad-hoc situations). I can add Db2 (10.5) to the products that require a column-list for a partial tuple:

db2 "INSERT INTO t VALUES (42)"
DB21034E  The command was processed as an SQL statement because it was not a
valid Command Line Processor command.  During SQL processing it returned:
SQL0117N  The number of values assigned is not the same as the number of specified or implied columns or variables.  SQLSTATE=42802


Will MyISAM prevent multiple users inserting data at the same time?

One of the biggest problems with MyISAM is the low write concurrency. In the most general case, a single write (update, delete or insert) to a single MyISAM table will block all other writes and reads. Multiple reads, however, can happen concurrently.

You can force MyISAM to perform concurrent inserts at the end (not for updates and deletes), but it normally requires a defragmented table. Read more about concurrent inserts for MyISAM on the manual. Multiple writes can always happen to different tables.

InnoDB doesn't have those problems, and also has better memory management, corruption resilience, support for foreign keys and much more features. With InnodB you can write to the same table in concurrency as long as you do not write to the same exact row (disabled to avoid race conditions). It can provide also reads and writes at the same time, even on the same rows thanks to multi-versioning.

If MySQL FULLTEXT is good enough for you, I would recommend you to search for a hosting that provides MySQL 5.6, where InnoDB Fulltext is available. Otherwise, you will have to search for an alternative application, like the mentioned sphinx.


Difference between table fillfactor and index fillfactor

From the CREATE TABLE manual page (emphasis added):

  The fillfactor for a table is a percentage between 10 and 100. 100 (complete packing) is the default. When a smaller fillfactor is specified, INSERT operations pack table pages only to the indicated percentage; the remaining space on each page is reserved for updating rows on that page. This gives UPDATE a chance to place the updated copy of a row on the same page as the original, which is more efficient than placing it on a different page. For a table whose entries are never updated, complete packing is the best choice, but in heavily updated tables smaller fillfactors are appropriate.

From the CREATE INDEX manual page (emphasis added):

  The fillfactor for an index is a percentage that determines how full
  the index method will try to pack index pages. For B-trees, leaf pages
  are filled to this percentage during initial index build, and also
  when extending the index at the right (largest key values). If pages
  subsequently become completely full, they will be split, leading to
  gradual degradation in the index's efficiency. B-trees use a default
  fillfactor of 90, but any value from 10 to 100 can be selected. If the
  table is static then fillfactor 100 is best to minimize the index's
  physical size, but for heavily updated tables a smaller fillfactor is
  better to minimize the need for page splits. The other index methods
  use fillfactor in different but roughly analogous ways; the default
  fillfactor varies between methods.


How to get an actual execution plan from the linked server?

I don't believe it's possible to get the execution plan via OPENQUERY() remotely, and the only way would be to connect to the server directly and run the query there, unfortunately.
That being said, you should definitely see an instant performance improvement for all of your linked server queries, if you migrated the remote server data to the local server (e.g. via one of the data synchronization features such as Replication or AlwaysOn Availability Groups), assuming your local server can handle the additional query load. Just an idea.


How can I get the actual SSAS error when processing a cube from a SQL Agent job?

It's been a while since I did this and I don't have a test setup, but to get a single event you should probably use the OnTaskFailed event instead of the  OnError.

That won't get you the error you see when processing the cube manually but should at least give you a single mail.

See this article on SSIS error handling.

  OnTaskFailed

  This event is generated only once when a Task "failure", independently
  of failure occurrence be in the Package context or just in a Task
  specific.