No, it it's not possible to restore a database from an ldf file.   The ldf file would be restored along with the mdf files.

No, it's not redundant as they have two different purposes.

It's important to take full backups, and transaction log backups. Only having a copy of the ldf file doesn't help you restore the database.

As to what a ldf file is for, the ldf is the transaction log.  Think of it as a circular buffer that records changes to your database.  When you update a row, the change is immediately written to the ldf.  At some point in the future (usually less than five minutes), the modified data is written to the mdf file.

If the server crashed or there was a power failure, when SQL starts, it reads the ldf and re-applies (REDO) those changes.

Additionally, if you have a transaction that hasn't been commited and the sever crashes, all changes made by that transaction have to be undone to make the database consistent. The ldf file has that task as well. (UNDO)

I mentioned above that the ldf file is circular.  Taking a transaction log backup (.trn)  copies out a portion of the ldf file.  After a trn file is safely created, sql can reuse that portion of the ldf file.  The series of trn backups create a chain that together record every modification made to the database. Of course, if you never took a tranaction log backup, the ldf file would grow and grow and grow.

In a disaster scenario, restoring the full backup gets you a copy of the database as of the time the full backup finished.  You can then restore the trn files in order and bring the database current to any point in time including up to the last trn backup.

I'm glossing over some important details, but the gist is the that ldf is a working file that records recent changes to the database.  The trn files are copies of parts of the ldf made under the assumption that you will keep then safe so that sql can reuse the space in the ldf and if disaster strikes, you'll have them in an alternate location.


The sequence number of a day in a year that begins on april 1 can be calculated this way:

SELECT 1+
       DATEDIFF(day,
           DATEFROMPARTS(
               DATEPART(year, DATEADD(month, -3, [Date])),
               4,
               1),
           [Date]) AS FYdayNumber
FROM tableWithDates;

In essence, we're subtracting three months from the current date, then using that year to create a start-of-year date (year, april, 1), then calculating the number of days between that start-of-year date and the current date. Finally, add 1 in order to make the sequence start at 1.

If you just want the first day of your fiscal year, you can use

SELECT DATEFROMPARTS(
           DATEPART(year, DATEADD(month, -3, [Date])),
           4,
           1) AS firstDayOfFY
FROM tableWithDates;

As @MarkSinkinson pointed out in another answer, avoid (user-defined) scalar functions for performance and parallelisation reasons.


Bulk update an address column where the address was duplicated with a delimiter separating the duplicate?

You can do this by using the CHARINDEX() function to find the position of the semicolon, coupled with SUBSTRING() to update the field with the data prior to the semicolon.

UPDATE test_table SET addr=SUBSTRING(addr, 0, CHARINDEX(';',addr))

If there are any legitimate semicolons in the data then it will of course remove some of the address, so please check the data first to ensure there is only one semicolon in each record.


What does "copy of" mean when doing "delete archivelog"?

Based on the documentation:

  An image copy is an exact copy of a single data file, archived redo
  log file, or control file. Image copies are not stored in an
  RMAN-specific format. They are identical to the results of copying a
  file with operating system commands. RMAN can use image copies during
  RMAN restore and recover operations, and you can also use image copies
  with non-RMAN restore and recovery techniques.

Even the original archivelogs count as copies, as they are a copy of an archived redo log. The 2 statements are equivalent even if you have the same archivelogs 1 or 10 times on the disk. The COMPLETED BEFORE 'SYSDATE - 7' clause will delete archivelogs that are older than 7 days.

This is not true for datafiles however. The original, actual datafiles don't count as copies. If you create a copy of a datafile with the BACKUP AS COPY command, that copy will have the data from the file the time it was copied, and the database will not use that copy, except for recovery procedures. If you switch a datafile to its copy with the SWITCH command, the copy becomes the original, and the former original becomes the copy.


SQL Server 2016 SP1 + strategy for long term version retention (snapshots, temporal tables, other?)

Since you're dealing with a single database (or a known set of databases), simply create a SQL Server Agent Job that restores the month end backup to a "month-end" database.

Schedule the job to occur early on the first day of each month.  The job would simply restore "last nights" full backup.  You are taking nightly backups, right?

In order to ensure the immutability of the month-end data, simply mark the database as read-only after the restore finishes.  Something like this:

ALTER DATABASE [xyz] SET READ_ONLY WITH ROLLBACK IMMEDIATE;

Details for the above command are here.


SQL Server 2005 - Query optimization for fetching large number of rows from table with 750 million rows

If you are determined to retain your existing table structure, the best indexes will be something like:

create index ix1 on fp_core(hashkey, note_id, timeoffset);
create index ix1 on fp_core_keys(hashkey);

You mention that the hashkeys can be different for the same song, but it sounds like you have some mechanism for detecting similar songs - if possible, add an additional column called 'songid' to your fp_core table and then query on this single value using a WHERE clause.


Deleting old postgresql log files (Ubuntu)

You can use the configuration below;

log_truncate_on_rotation = on
log_rotation_age = 1d
log_filename = 'postgresql-%a.log'
log_rotation_size = 0  #just rotate daily

This says create a log file with a name like 'postgresql-Mon.log' and when rotation occurs override the old file with the same name.


How to read and process xml data from stored procedure in sql server?

You don't need to SET the XMLRESULT.

You should pass it in as an OUTPUT parameter.

https://technet.microsoft.com/en-us/library/ms187004(v=sql.105).aspx

DECLARE @XMLRESULT xml;
EXEC usp_ReportResults @query = '759,905,1048,170,725,80129', @ReportName ='GenRepot', @XMLResult = @XMLRESULT OUTPUT;

You can then use the @XMLRESULT parameter, which has been populated with the result of the Stored Procedure.

INSERT INTO dbo.my_table(xml_field)
SELECT @XMLRESULT;


Does SQL Server ever delete old error log files?

In SQL Server Management Studio, in Object Explorer > Management, there is a setting if you right click on SQL Server Logs and select Configure. You can specify how many log files you want to keep. Once it reaches that number it will start removing the old ones.


Create an Azure Synapse temp table from a SELECT with ORDER BY

I would imagine this would work for you:
CREATE TABLE #demo
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT
    ROW_NUMBER() OVER (
        ORDER BY S.[name], T.[name], C.column_id) AS ID,
    S.[name] as [schema],
    T.[name] as [table],
    C.[column_id] as [ordinal],
    C.[name] as [column],
    TY.[name] as [type]
FROM sys.columns AS C
JOIN sys.types AS TY
    ON TY.system_type_id = C.system_type_id
JOIN sys.tables AS T
    ON T.[object_id] = C.[object_id]
JOIN sys.schemas AS S
    ON S.[schema_id] = T.[schema_id];

The key difference is moving the ordering to the ROW_NUMBER window function, to determine the order in which numbers are assigned.


Breaking point for SQL server database based on connections from app to SQL server

You can use a load testing tool which supports the protocol which your application servers use for the database communication (OLE DB, ODBC, JDBC, etc.) in order to mimic the increased amount of application servers talking to the database instance, this way you will be able to collect metrics and correlate increasing load with the increasing query execution times, eventual deadlocks and so on.

Just make sure to configure the load testing tool to use the same connection pattern: connection pool settings, transaction isolation level, etc. so your load test would accurately represent the network footprint of the real application.

Check out The Real Secret to Building a Database Test Plan With JMeter article for more information on the concept of databases load testing using a performance testing tool, in the above case Apache JMeter


Error: Specified cast is not valid. (SqlManagerUI)

Generate the restore script from the GUI, instead of using the GUI to do the restore.

Then add the PASSWORD option...

--generated from the GUI dialog box
RESTORE DATABASE foo FROM  DISK = N'somepath' WITH ...

-- and add this
PASSWORD = '**password_is_this**'
GO


Firstly as @jonhg points out the servers can give you different execution plans as the tables and indexes are rebuilt. The server hardware (memory, cpu, hard drives) can be different and finally you don't have the same version of SQL server in both cases. Any of these would be sufficient to give different results.

Other queries could be interfering, as the cache on the servers can be different. All the answers to this are found here: http://download.red-gate.com/ebooks/SQL/eBOOK_SQLServerExecutionPlans_2Ed_G_Fritchey.pdf


How to setup an ssh connection to a postgres 12 db using dbeaver

The default name is pg_hba.conf, not .pg_hba.  The file still exists in v12.

It is possible to pick any name you want for this file by changing the setting hba_file in postgresql.conf or from the start-up command line.  But if someone has done that, it is a difference in how your server was configured, not a inherent difference between versions of PostgreSQL.


Should I disable autovacuum on a table while I do a bulk update?

No

There is simply no point. autovacuum will not clean up any rows locked in a running transaction. So autovacuum

may not do anything useful.
may report that the rows are dead and nonremovable
may do something useful with the other rows (if there are any).

But, it won't block or slow down the update in any meaningful sense that I'm aware of.


How do I get the adjacent value of MAX/MIN aggregate function?

This it a variation of a greatest-n-per-group problem.
I would do this with a join to a derived table:
select *
from application app
  join (
    select distinct on (application_id) *
    from asset
    order by application_id, last_modified desc
  ) ast on ast.application_id = app.id;


PG_HBA=$(su postgres -c "psql -t -P format=unaligned -c 'SHOW hba_file'")

That seems potentially problematic.

That line above shows createdb ultimaterehab. That line doesn't however connect to the ultimaterehab database. It connects to the default database.
Debian/Ubuntu already does the setting up for the right pg_hba. You don't have to do anything whatsoever. If you absolutely need the location of the pg_hba file, which you do not need, you'd be better of working with the system utility pg_conftool to get that location: pg_conftool show hba_file.

Moreover, ubuntu doesn't use su, it uses sudo.

sudo -u postgres createuser app
sudo -u postgres createdb ultimaterehab

And you're done.


AttributeError: 'NoneType' object has no attribute 'value' - pgAdmin4

I performed the same steps and ran into the same problem.  I found that the problem was resolved by removing the ~/.pgadmin directory (created during installation) and then re-running "python pgAdmin4.py"


Statistics. Are multicolumn histograms possible?

Are multicolumn histograms possible?

Not true multi-dimensional histograms, no.

  Is this artificial and inelegant solution the only available option to achieve accurate estimations when dealing with filtering by two or more not independent columns?

SQL Server does support "multi-column" statistics, but they only capture average density (correlation) information in addition to a histogram on the first named column. They are only useful for equality comparisons.

The average density information does not capture any detail, so you will get the same selectivity for any pair of values on a two-column statistics object. In some cases, multi-column statistics can be good enough, and better than nothing. Multi-column statistics are automatically built on multi-column indexes.

Depending on SQL Server version, you may also be able to make use of filtered indexes and filtered statistics:

-- Filtered statistics example
CREATE STATISTICS stats_StatsTest_col2_col1_eq_1
ON dbo.StatsTest (col2)
WHERE col1 = 1;

CREATE STATISTICS stats_StatsTest_col2_col1_eq_2
ON dbo.StatsTest (col2)
WHERE col1 = 2;

Or you could build an indexed view (which can support indexes and statistics of its own). Indexed views are the mechanism behind the DATE_CORRELATION_OPTIMIZATION database setting, a little-used feature for inter-table correlations, but which applies to the spirit of the question.

  Is the computed column and the filter by the computed column strictly necessary for obtaining actual precision?

It is not the only method. In addition to the things already mentioned, you could also specify the exact textual definition of the computed column and the optimizer will generally match it to statistics on the computed column.

There are also trace flags that change the assumptions made about multi-column correlations. Also, the default correlation assumption in SQL Server 2014 (with the new cardinality estimator enabled) is changed from Independence to Exponential Backoff (more details here and here). Ultimately, this is just a different assumption though. It will be better in many cases, and worse in others.

Exact precision in cardinality estimation is not always needed to obtain a good execution plan. There is always a trade-off between generating a plan that can be reused for different parameter values, and a plan that is optimal for a specific execution, but not reused.


Help with tuning this UPDATE Query or possible re-write

Warnings

The forced serialization is because of the OUTPUT (with no target or to a @table variable will cause this, but not a #temp table)
The expensive
sort is because you're ordering by ModifiedTime without a supporting index and query pattern
The non-SARGable
warning is because (if I remember how I wrote the check correctly),
of the local variables.

How to fix it
You'll need a good supporting index:
CREATE INDEX super_awesome
ON dbo.BatchChangeTable
    (id, ModifiedTime)
INCLUDE
    (EnabledDate);

But the way the query is written, IN will be interpreted as a range predicate, and the ORDER BY ModifiedDate will likely still cause a Sort operation. You'll need to change the form to use two equality predicates with a UNION ALL in order to take full advantage of the index.
You're also going to need to rewrite your query to use dynamic SQL so that you don't get hit with the side effects of local variables.
The finished product will look something like this:
BEGIN TRAN;

/*I'm guessing here, address local factors*/
CREATE TABLE #output(col1 int, col2 int, col3 int, col4 int, id int)

/*Use parameterized dynamic SQL*/
DECLARE @sql nvarchar(MAX) = N''

/*Hello I am your friend*/
SET @sql += N'
UPDATE u
SET
    u.id = @StatusIP,
    u.EnabledDate = GETDATE()
OUTPUT
    deleted.col1,
    deleted.col2,
    deleted.col3,
    deleted.col4,
    Inserted.id
INTO #output
FROM
(
    SELECT TOP (5000)
        b.*
    FROM
    (
         SELECT
             b2.*
         FROM BatchChangeTable AS b2 WITH (ROWLOCK, UPDLOCK)
         WHERE b2.id = @status_D

         UNION ALL

         SELECT
             b2.*
         FROM BatchChangeTable AS b2 WITH (ROWLOCK, UPDLOCK)
         WHERE b2.id = @Status_F
         ORDER BY b2.ModifiedTime
    ) AS b
    ORDER BY b.ModifiedTime
) AS u;
';

EXEC sp_executesql @sql,
                   N'@status_D int,
                     @StatusIP int,
                     @Status_F int',
                     @status_D,
                     @StatusIP,
                     @Status_F;

SELECT
    o.*
FROM #output AS o

COMMIT TRAN;

You'll need to tailor this a little to suit your actual situation, but it should get you going in the right direction.


Cassandra Table data modeling for a event system monitoring data

that didn't work, as it keeps 2 states for each id.

I like that you tried this.  For anyone who sees this post in the future, it'll underscore the point that primary keys in Cassandra are unique.
Here's how I would do it.
CREATE TABLE alarms_by_date (
    id UUID,
    state TEXT,
    start_date DATE,
    end_date DATE,
    node TEXT,
    PRIMARY KEY((day,hour,m15),start_date,id))
WITH CLUSTERING INDEX ON (start_date DESC,id ASC);

CREATE INDEX ON alarms_by_date(state);

What this does:

Stores your data partitioned in 15 minute intervals.  With 100k/hour, that's too much to throw into a single partition.  Breaking that into 4 parts should keep it from growing too large.  This could be done with a single partition key, as long as the key was made up of those components (ex: 202102171045).
Applying a clustering index of start_date and id.  Not sure which of the two dates (start/end) are more important to your business case.  But this way, the data will be in descending order by time.  Also, I like using id columns as the last clustering key, to help ensure uniqueness.
Applying a secondary index on status is really the way to go here.  Yes, there's a lot wrong with secondary indexes in Cassandra.  But, the main problem with them, is that they can result in scans of multiple nodes.  By pre-empting queries using that index with the date/partition, queries will be restricted to only a single node.  So I think that'd be the way to go.


Why does Veritas backup take longer through CMDEXEC than cmd?

I can't tell you why with the information provided, but I can give you some guidance to figuring it out.

Monitor your wait statistics while the longer job is running. You should be able to figure out which session is running the backup, see what it is waiting on. sys.dm_exec_requests will tell you this (check the wait_type, and also look at blocking_session_id in case it is getting blocked), you'll just need to refresh often.

Or you could roll up your sleeves and run a targeted extended events session, or turn on trace flags that give more insight into slow backups (see here).


"Active Directory - Password" login in SSMS for Azure AD - Password expired

When you reset a password in Azure AD, it automatically forces the user to change the PW on the next login.  If you immediately attempt to login to Azure SQL it will give you an error that the PW is expired.

Log into the Azure Portal as sqladmin@myuserhotmail.onmicrosoft.com, set a new password and this will resolve your issue.


Zooming in on the icon it looks like a red push pin.

Searching specifically for red pin and management studio I found this thread which explains

If a principal is disabled, then it has a red arrow pointing down in
the icon ... Here you have a red pin icon and
this means you can't modify it. Since SQL Server 2014 we can create
user defined server role ... which
wasn't possible in SQL Server 2012 and lower version ... user defined
roles don't have a red pin and can be modified, while predefined
server roles do have a pin

One exception is that the permissions of the public role can be altered (but membership can't). For all other pre-defined server roles the reverse is true.
In more recent versions of management studio it appears a lock icon is used for that instead.


What is the best practice when an index with key/include columns are inversed for a second index on the same table

You may read more about non-clustered index here from Brent Ozar and Team. Thumb rule for column in the index should be used in where clause and include is the column which is in the select statement.

Since, you have not posted query here, you may understand with below examples:

/*Query1
********/
select [COL_ID_B]
from [TABLE]
where [COL_ID_A] = some value

Above query Query1 will use index IDX1, whereas Query2 will use IDX2.

/*Query2
*********/
select [COL_ID_A]
from [TABLE]
where [COL_ID_B] = some value

Now, there is difference between index column and include column, you can read more about this on article written by Mr. Ahmad Yaseen. There is also concept of hidden column in case you have clustered index on this table, clustered column will be part of all non-clustered index, you may refer this article.

Whether one index should contain multiple column or multiple index should be created with one column, it depends on application and query pattern. Thumb rule is to have five non-clustered index on 5 columns however this varies from application to application.

First column in index is extremely important, if your query selects col1 and looks mostly for colA followed by colB, colC and colD respectively then, you should go for a non-clustered index with below definition:

CREATE NONCLUSTERED INDEX [IDX3] ON [TABLE]
(
    colA,
    colB,
    colC,
    colD
)
INCLUDE
(
    col1
)

however if your queries have where clause on all columns and heavily uses all of them i.e. colA, colB, colC and colD then, five separate non-clustered index on each of them would help. Again column in select could be chosen as include.

There are tonnes of article on each of these topic by many experts. you may refer below for more details:

https://www.brentozar.com/archive/2018/11/tales-from-overindexing-too-many-one-column-indexes/

https://www.brentozar.com/archive/2015/04/index-included-columns-multi-column-statistics/

Similar question was asked and you may find answers here

Please let us know if this helped.


Ensuring that 1 row has 5 specific records referencing it

This can be done with DDL alone but it's not at all trivial. The following will ensure that any test will either have 0 or 5 test cases:

create table test (
   test_id  integer  not null,
   constraint pk_test
      primary key (test_id)
)   ;

create table test_case (
   test_id       integer   not null,
   case_no       smallint  not null,
   case_no_prev  smallint  not null,
   case_ok       text   not null,
   constraint  pk_test_case
      primary key  (test_id, case_no),
   constraint  fk_test__test_case
      foreign key  (test_id)
         references  test (test_id),
   constraint  ck_case_ok
      check  ( case_ok in ('PASS', 'FAIL', 'INVESTIGATE') ),

   constraint  ck_case_numbers
      check  ( case_no between 1 and 5 ),
   constraint  fk_test_case__test_case
      foreign key  (test_id, case_no_prev)
         references  test_case (test_id, case_no),
   constraint  ck_cases_are_5
      check  ( case_no > 1 and case_no_prev = case_no - 1
            or case_no = 1 and case_no_prev = 5 )
)   ;


You said that you have already installed the feature,
but when you look at the sql server configuration manager do you see it there?

according to the documentation:

IsFulltextInstalled The full-text component is installed with the current instance of SQL Server.

0 = Full-text is not installed.

1 = Full-text is installed.

NULL = Invalid input, or error.

Here you can find a detailed way to install Full Text on SQL Server 2012:

Directions:

Open the Programs and Features control panel.
Select Microsoft SQL Server 2012 and click Change.
When prompted to Add/Repair/Remove, select Add.
Advance through the wizard until the Feature Selection screen. Then select Full-Text Search.

On the Installation Type screen, select the appropriate SQL Server instance.
Advance through the rest of the wizard.

Source (with screenshots): http://www.techrepublic.com/blog/networking/adding-sql-full-text-search-to-an-existing-sql-server/5546

This link is also a GREAT article, but a bit more comprehensive:

Step-by-Step: Enabling Semantic Search on SQL Server 2012

  Semantic Search is a powerful new addition to SQL Server 2012.
  Semantic Search builds upon the full-text index feature and adds the
  ability for SQL Server to determine key words in documents. Uses can
  include analysis of text to determine similarity between two
  documents, extract key words from documents, or find documents that
  contain key words.

  Step-by-step, enabling Semantic Search on a column in SQL Server 2012
  involves:

1.Install the Full-Text and Semantic Extractions for Search feature

2.Install the Microsoft Office 2010 Filter Packs and Service Pack 1

3.Install, attach and register the semantic language database

4.Create a full-text catalog

5.Create a full-text index with the Statistical_Semantics option enabled

Please visit this link for more details on the above instructions. There are pictures and more detailed info you can find there.

If you have this error message:

  Full-Text Search is not installed, or a full-text component cannot be
  loaded

when you run these procedures below:

EXEC sp_fulltext_service @action='load_os_resources', @value=1;
EXEC sp_fulltext_service 'update_languages';
EXEC sp_fulltext_service 'restart_all_fdhosts';

Then:

Check up what you find in the files below:

File    Path
Summary.txt   %programfiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log

Summary__Date.txt  %programfiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log\YYYYMMDD_hhmmss

Detail.txt  %programfiles%\Microsoft SQL Server\nnn\Setup
Bootstrap\Log\YYYYMMDD_hhmmss

Datastore   %programfiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log\YYYYMMDD_hhmmss\Datastore

MSI Log Files   %programfiles%\Microsoft SQL Server\nnn\Setup
Bootstrap\Log\YYYYMMDD_hhmmss\.log

ConfigurationFile.ini   %programfiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log\YYYYMMDD_hhmmss

SystemConfigurationCheck_Report.htm %programfiles%\Microsoft SQL Server\nnn\Setup Bootstrap\Log\YYYYMMDD_hhmmss

For unattended installations    %temp%\sqlsetup*.log

and also check sql server error log

Based on the information you get from the logs, you might need to deal with it accordingly, hopefully avoiding having to reinstall anything.

If you see this error message:

  The specified schema name "sys" either does not exist or you do not
  have permission to use it.

Check if you have enough permissions to run these procedures.

In regards to permissions the minimum requirement is:

  On SQL Server, requires VIEW SERVER STATE permission. On SQL Database
  Premium Tiers, requires the VIEW DATABASE STATE permission in the
  database. On SQL Database Standard and Basic Tiers, requires the
  Server admin or an Azure Active Directory admin account.


How to perform the dreaded double hop with linked servers?

Security Account Delegation

And for SQL Server 2000 but still valid too

Basically

Configure the server in AD
Set up the SPN
Use sp_addlinkedsrvlogin so useself = true


Is it safe to rely on auto increments even after restoring data?

A quick overview:

PostgreSQL does not have AUTO INCREMENTS. There is no special type.
In order to set this up PostgreSQL uses the standard IDENTITY COLUMNS which associates a SEQUENCE with the int type.

Let's create a table,

CREATE TABLE foo (
  foo_id int PRIMARY KEY,
  bar    int GENERATED BY DEFAULT AS IDENTITY
);

When you pg_dump that table with an IDENTITY COLUMN you'll see this,

CREATE TABLE public.foo (
    foo_id integer NOT NULL,
    bar integer
);

That's because the type is simply int type. Now, when you load you'll see right after the table this,

ALTER TABLE public.foo ALTER COLUMN foo_id ADD GENERATED BY DEFAULT AS IDENTITY (
    SEQUENCE NAME public.foo_foo_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

This tells the pg_restore the state associated with the sequence the IDENTITY COLUMN was using.

# \d foo
                             Table "public.foo"
 Column |  Type   | Collation | Nullable |             Default
--------+---------+-----------+----------+----------------------------------
 foo_id | integer |           | not null | generated by default as identity
 bar    | integer |           |          |

Then the data is loaded and when it's loaded all of the values of that sequence for each row are specified in the load -- they maintain the old values from the dump.*

Note that the PostgreSQL implementation of an IDENTITY COLUMN which is more less sugar for SEQUENCE does not ensure a gaplessness.

Footnotes

I've left off the permission stuff for brevity.
*I've removed all of the stuff about serial, bigserial and what serial was and how it worked because moving forward it should not be a thing. PostgreSQL users should be using IDENTITY COLUMNS in all circumstances.


SQL Server 2005 database engine login failed error?

As per MSDN you can't install SQL Server 2005 Enterprise Edition on Windows XP. It's not supported. It seems that you can install client tools, BOL and some legacy tools, but not the database server engine itself.

So to answer your question: the SQL Server engine has very likely not been installed when you run setup, so neither you can connect to it, nor you can start any SQL Server service.


Need advice on how to migrate from existing database mirroring to availability groups

As a general rule, this migration should be simple. AGs use the database mirroring framework and endpoints for transmitting log blocks, so the transition is quite straightforward.

One thing to note is you currently run your mirrors async but you've stated you want to run your AG sync to provide HA. This shouldn't be a problem, but you need to check your latency between the servers to ensure you're not creating a bottleneck that is going to hamper write performance.

Synchronous commit AGs require all transactions to be hardened to the log on the secondary prior to committing them on the primary, so if your latency across the 150 miles between servers is too high, this can cause substantial performance issues.

  Clusterless AG came to my find but it seems HA wont be available as
  automatic failover might not work. Am I missing something here or is
  it possible?

No, you are correct. Clusterless AGs, or read-scale availability groups, are not an HA option. There is no automatic failover in a read-scale availability group. See this link here which states:

  This is not a high-availability setup. There is no infrastructure to
  monitor and coordinate failure detection and automatic failover.
  Without a cluster, SQL Server can't provide the low recovery time
  objective (RTO) that an automated high-availability solution provides.
  If you need high-availability capabilities, use a cluster manager
  (Windows Server Failover Cluster on Windows or Pacemaker on Linux).

  The read-scale availability group can provide disaster recovery
  capability. When the read-only replicas are in synchronous-commit
  mode, they provide a recovery point objective (RPO) of zero.

To your second point-

  We can have an outage up to 8 hours to convert db mirroring to AG if
  needed.

You could do the following:

Enable clustering on Server A and Server B.
Configure the Availability Group with both SQL instances on Server A and Server B as replicas
Stop log backups on both servers
Remove mirroring from DBs on Server A/B
Add databases to AG on Server A
Join databases to AG on Server B - This should be fairly seamless as the DBs on Server B should in recovery since mirroring was disabled. As long as no log backups have occurred, you should be able to sync the DBs in the AG without backup/restore or seeding.
Update App connections to use the listener name.

Some of these steps require the servers or SQL Server instances to be restarted, so it will be multiple outages but each is shorter than your allowed 8 hours.

This is a very simple overview, and the process needs to be tested and any problems identified and resolved prior to running through this in production. If you have a non-production environment, start there and script every step so you can replay the actions in production accurately.

If you don't have a non-production environment, ask your sysadmins to clone your servers in an isolated network where you can practise the change. If you're running physical servers, then testing without a non-production environment is going to be difficult, but you could build a similar environment in Azure or AWS to dry-run the process.

Here is a useful link on configuring clusters for AGs. Its a bit older but the information is still valid. If you're not confident with this process, especially if you do not have a non-production environment available, it may be better to engage a third-party contractor/consultant/expert who can plan and execute this for you.


While passing integer numbers, you can either cast the whole array:
TG_ARGV::int[]

Or you can cast an element, then it must be the element type:
TG_ARGV[0]::int

I used it that way in my answer to your previous question:

SELECT in trigger function in two tables

However, you are not passing integer numbers, but the text representation of integer arrays: an integer array literal - with illegal syntax, too: values must be enclosed in single quotes, double quotes are for identifiers:
FOR EACH ROW EXECUTE PROCEDURE insaft_function("{101, 111, 121}", "{101, 111, 121}");
FOR EACH ROW EXECUTE PROCEDURE insaft_function('{101, 111, 121}', '{101, 111, 121}');

Since you are not passing integer but integer array literals, you cannot do either in your code. Consider (using a clearer example with distinct numbers):
SELECT (ARRAY['{101, 111, 121}', '{201, 211, 221}'])[1]::int[];
SELECT (ARRAY['{101, 111, 121}', '{201, 211, 221}'])::text[];
SELECT (ARRAY['{101, 111, 121}'::int[], '{201, 211, 221}'])::int[];
SELECT (ARRAY['{101, 111, 121}'::int[], '{201, 211, 221}'])[1][1];
SELECT (ARRAY['{{101, 111, 121},{201, 211, 221}}'])[1];
SELECT (ARRAY['{{101, 111, 121},{201, 211, 221}}'])[1]::int[];

@Chris provided more for that.
Solution for your function
I suggest you either pass all integer numbers or a 2-dimensional array literal. Demonstrating code for the latter:
'{{101,111,121},{201,211,221}}'

Or rather (the array pivoted, based on my educated guess):
'{{101,201},{111,211},{121,221}}'

So, this is one parameter:
CREATE TRIGGER ...
FOR EACH ROW EXECUTE PROCEDURE insaft_function('{{101,201},{111,211},{121,221}}');

As for the loop, use FOREACH m SLICE 1 IN ARRAY:

Postgres - array for loop

Function could look like this:
CREATE OR REPLACE FUNCTION insaft_function()
    RETURNS TRIGGER AS
$func$
DECLARE
   m int[];
BEGIN
   FOREACH m SLICE 1 IN ARRAY TG_ARGV[0]::int[]  -- reference 1st param
   LOOP
      INSERT INTO events (measurement_id, event_index_start, event_index_end)
      SELECT NEW.measurement_id, m[1], m[2];  -- Postgres array starts with 1 !
   END LOOP;
   RETURN NULL; -- result ignored since this is an AFTER trigger
END
$func$ LANGUAGE plpgsql;

But I have my suspicions there might be a simpler overall approach. This makes only sense if you have many tables that need a similar trigger, just with different integer numbers ...


Not able to select the disk during Oracle 11g Installation

You should check if it is not a ownership issue. The disks should be owned by Oracle.

A common workaround to this issue is to delete and recreate the disks from scratch.


Error while restoring from Enterprise Edition to Standard Edition

Unfortunately SQL Server Standard Edition does not support vardecimal storage format or data compression.

You need to restore to a Developer Edition (or Enterprise Edition), remove the non-supported features by modifying the objects as necessary, then backup the modified database, prior to restoring it on Standard Edition.

Technet has a great page detailing what is and what is not supported by each edition.

MSSQLTips has an article that shows how to use the sys.dm_db_persisted_sku_features system DMV to identify features that won't be supported on Standard Edition.


How can I tell which type of deployment a package has taken?

Look at the definition of your SQL Agent Job Step.

If it specifies /file then the package has been to deployed to the file system somewhere.

If it specifies /DTS then it's using a well known location on the SQL Server installation path (precise location eludes my memory as of this posting but \Program Files\Microsoft SQL Server\XXX\DTS\Packages and/or C:\Program Files (x86)\Microsoft SQL Server\XXX\DTS\Packages are good guesses)

If it specifies /sql then it is deployed to the specified SQL Server in msdb.dbo.sysdtspacakges90 (SQL Server 2005) or msdb.dbo.sysssispackages (SQL Server 2008/2008R2+)

As to scheduling a package that you can't see, this sounds suspiciously like you're not on 2008 R2 as you think but much more like 2012+ as this is the SSISDB with the folder/project/package structure. If this is the case, packages can be specified as being Entry Point Packages or not. It's just a UI convention, there's nothing that prevents them from running. There's a selector that specifies whether you want Entry Point Packages or All. Switch to All packages


Select total number of records from common table expression

No, because you can't mix assignments and result set generation

But what you can do is make the total row count into an extra column

WITH Results AS
(
    select
        ---,
        COUNT(*) OVER () AS TotalRowCount -- this
    ...
)
SELECT
   *
FROM
   Results
WHERE
   Results.RowNum BETWEEN @StartIndex + 1 AND @StartIndex + @MaxCount


You might try this, however as I do not have any understanding of your schema, the result might not be what you search for.

select
  leave_detail_id
  ,emp_id
  ,Employee_Name
  ,leave_type
  ,leave_name
  ,sum(leave_total_days) total_leave
  ,leave_current_status_id
from employee emp
left join  tbl_emp_leave_details eld
  ON emp.PK = eld.emp_id
left join tbl_leave_types lt
  ON lt.leave_id = eld.leave_type
  and leave_current_status_id = 1
  and leave_start_date >= '01-07-2019'
  and leave_end_date <= '31-07-2019'
group by
  leave_detail_id
  ,emp_id
  ,Employee_Name
  ,leave_type
  ,leave_name
  ,leave_current_status_id


PostgreSQL : Is this because of locks(row level)?

Running a DML statement inside a loop is never a good idea. You are multiplying the amount of work to be done. Relational databases are best when operating on sets, when you do a loop you are operating on a single row at a time.

You can achieve the same by doing the update in a single statement:

UPDATE list_of_location
   SET location = ST_MakePoint(longitude,latitude)::geography;

I'm not 100% about the syntax for calculating the actual point, as I don't use geometric stuff, but I guess you'll get the idea.


Is Non-Clustered index with all others columns included equivalent to a Clustered Index

The answer will vary according to your understanding of the word "equivalent".

From a logical / application perspective they are the same. Both reference all columns of the table and define an ordering on all rows.

Physically the image on disk and the thread of execution through the server software are likely to be different. The optimiser may treat them differently, too.


Migrating SQL Server 2008 databases to new 2-node WSFC SQL2014 Alwayson AG

To perform a server migration to an AG using log shipping, you should log ship to both servers. When you are ready to actually cut over to the new AG, follow the following steps:

Take a final tail log backup on the old instance (BACKUP DATABASE...WITH NORECOVERY)
Restore the backup to both AG nodes (or allow your log shipping to apply it) WITH NO RECOVERY.
On Node 1, bring the the database online (RESTORE DATABASE MyDb WITH RECOVERY)
Add the DB to the AG on Node 1.
Join the secondary DB to the AG.

I'd recommend practicing this routine on your QA system before the real production upgrade. There are a lot of scripts/statements to be run, and lots of switching between servers to do it. It's best to have those steps carefully documented and scripts staged in advance.


It might be easier to simply backup the database to a .bak and then create a new database from a .bak restore.

BACKUP DATABASE [aaa] TO DISK = N'E:\aaa.bak'

Then check the logical names and locations for the current files in the .bak:

Restore filelistonly from disk ='E:\aaa.bak'

And finally restore the database, renaming the files to make sure you don't overwrite your existing database

restore database Newdatabasename
FROM disk = 'E:\aaa.bak'
WITH replace,
MOVE 'Logical data name' TO 'E:\Newdatabasename.MDF',
MOVE 'Logical log name' TO 'E:\Newdatabasename.LDF',
recovery --force


Using LONGBLOB can be very risky in light of the fact that the biggest max_allowed_packet is only 1GB.

Back in August 2006, someone asked a similar question. in which Peter Zaitsev said:

  http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

  As you can see in theory it is about 4GB.

  It is however also limited by max_packet_size which is 16M by default.

  I would be very careful using blobs larger than 100MB.   MySQL will
  need some 3 times of this size of memory allocated on the server for
  blob processing.

I am not a PostgreSQL DBA, but I would have to venture the following suggestion:

If you must store objects that big, you should go with PostgreSQL because it has a storage infrastructure called the The Outside Attribute Storage Technique (TOAST). This allows rows not to be too big for SQL processing.
If you must use MySQL, you are much better off storing URL File Links than the BLOB itself.


You say you want the rank of a particular user, but that is not what your query does.

  Heap Fetches: 500021

Vacuum your table to reduce the heap fetches.  If the table is updated a lot, you may need to make changes to its autovac parameters to keep autovacuum running on it often enough.

For example,

alter table leaderboard_scores set (autovacuum_vacuum_scale_factor =0);
alter table leaderboard_scores set (autovacuum_vacuum_threshold =<rel_pages / 10>);

Where <rel_pages / 10> needs to manually computed and the numeric value plugged in.


How to select distinct TOP but group by column

SELECT InvNr, DetailLine
FROM tbl_Invoice
where InvNr in (select distinct  top (2) InvNr from tbl_Invoice order by InvNr);


Does encapsulating a statement in a transaction include the triggers it fires?

The effects of a trigger 'belong' to the statement that triggered it, so everything will be rolled back together (with or without explicit transactions):

> create table a(x int);
> create table b(y int check(y > 0));
> insert into b values(1);
> create function t() returns trigger as $$
  begin
    update b set y = -1;
    return null;
  end;
  $$ language plpgsql;
> create trigger at after insert on a execute procedure t();
> insert into a values(0);
ERROR:  new row for relation "b" violates check constraint "b_y_check"
DETAIL:  Failing row contains (-1).
CONTEXT:  SQL statement "update b set y = -1"
PL/pgSQL function t() line 1 at SQL statement
> select * from a;
 x
---
(0 rows)


How can I alter an existing Primary Key on SQL Azure?

Note: as of Azure SQL Database v12, these restrictions no longer apply.

The is no such thing as a 'primary index'. There is such a thing as a 'primary key' and also there is such a thing as a 'clustered index'. Distinct concepts, often confused. With this distinction in mind, lets revisit the question:

Q1) Can the clustered index in a SQL Azure table be modified?
A: Yes. Use  WITH (DROP_EXISTING=ON):

create table Friend (
    UserId int not null,
    Id int not null);
go
create clustered index cdxFriend on Friend (UserId, Id);
go
create clustered index cdxFriend on Friend (Id, UserId) with (drop_existing=on);
go

Q2) Can the clustered index of a table that has a primary key constraint be modified?
A: Yes, same as above, as long as the primary key constraint is not enforced via the clustered index:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key nonclustered (Id));
create clustered index cdxFriend on Friend (UserId, Id);
go
create clustered index cdxFriend on Friend (Id, UserId) with (drop_existing=on);
go

Q3) Can the primary key constraint of a table be modified?
A: Yes, as long as the primary constraint is not enforced via the clustered index:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key nonclustered (Id));
go
create clustered index cdxFriend on Friend (UserId, Id);
go
alter table Friend drop constraint pk_Friend;
alter table Friend add constraint pk_Friend primary key nonclustered (UserId)
go

Q4) Can the primary key of a table be modified when is enforced via the clustered index?
A: Yes, if the table never had any rows:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key clustered (UserId, Id));
go
alter table Friend drop constraint pk_Friend;
alter table Friend add constraint pk_Friend primary key clustered (Id, UserId)
go

Q5) Can the primary key of a table be modified when is enforced via the clustered index if the table is populated?
A: No. Any operation that converts a populated clustered index into a heap will be blocked in SQL Azure, even if the table is empty:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key clustered (UserId, Id));
go
insert into Friend (UserId) values (1);
delete from Friend;
go
alter table Friend drop constraint pk_Friend;

As a side note: the constraint can be modified if the table is truncated.

The workaround to change the PK constraint of a populated table is to do the good old sp_rename trick:

create table Friend (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend primary key clustered (UserId, Id));
go
insert into Friend (UserId) values (1);
go

create table FriendNew (
    UserId int not null,
    Id int not null identity(1,1),
    constraint pk_Friend_New primary key clustered (Id, UserId));
go

set identity_insert FriendNew on;
insert into FriendNew (UserId, Id)
select UserId, Id
from Friend;
set identity_insert FriendNew off;
go

begin transaction
exec sp_rename 'Friend', 'FriendOld';
exec sp_rename 'FriendNew', 'Friend';
commit;
go

sp_help 'Friend';

The sp_rename approach has some issues, most importantly being that permissions on the table do not carry over during the rename, as well as foreign key constraints.


SQL vs NoSQL: Fetching All records in DynamoDB vs SQL Database

Scan operations aren't usually optimal but 500,000 records isn't something too crazy for the standard RDBMS. For example, I've seen Microsoft SQL Server serve up 1 million records on a decently wide table (about 100 columns) in a few seconds on a pretty basic server (4 core, 32 GB of RAM).
I'm not an expert on NoSQL databases or DynamoDB but I've researched into its use cases in the past and my understanding is it's most performant when caching smaller amounts of data, and recalling smaller "singletonish" datasets, such as paging product information on a catalog, or a specific user's settings. So I would say NoSQL databases (or at least DynamoDB) is likely not great for scanning large amounts of data, but a modern RDBMS should be able to handle scanning a few million records on basic architecture these days.


SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_free' and
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total' will enable you to derive this percentage.

Keep in mind a page_free of 0 isn't fatal or even necessary bad - it just means every once in a while a read will come from disk or page cache. Its the rate at this occurs that is more important to performance of related to the size of the innodb_buffer_pool.


Need to find the number of employee who are not in all department

Unless you are e.g. counting or summing the entire contents of a table, aggregate functions will require a group by statement. For example, if you are aggregating by counting the number of departments by employee, you must tell it to group by employee

select empname, count(*)
from t1
group by empname
having count(*) < 37


A good way to verify if the update has no problems?

As TheGameiswar has mentioned, the upgrade adviser is incredibly helpful for pointing out issues that can arise with the upgrade. However, it will only check for things IN the database. So if you have ad-hoc queries being ran, or worse yet sql files scattered all over the place, those will not be tested.

When I upgraded our SQL 2000 servers to 2014, we had ad-hoc queries and sql files all over the place that were being executed and we needed to test to make sure they would not break as best we could. So what I ended up doing was copying our production databases and upgrade them to 2014. After that, I started doing replay traces on production for a week to capture all queries being ran. This allowed me to replay those traces on the 2014 servers to see if there would be any issues. It helped me find a number of issues that needed to be fixed.

One very important thing to note when doing replay traces is you need to make sure the database IDs match between the production and dev environments as that's how the traces know what database to run each query on.


You don't have many options here:

1) if you are on OE11.7 and later, you can use OpenEdge Change Data Capture to track data and replicate it http://knowledgebase.progress.com/articles/Article/How-to-implement-CDC-Change-Data-Capture-with-OpenEdge

2) if you are on older version, you might use Pro2 software. I am aware of Pro2SQL for MS SQL target and Pro2Oracle for Oracle DBA.

https://www.progress.com/campaigns/openedge/Pro2v5-Webinar

EDIT: did not read the question in full: your best option is to ask Progress itself, but I don't see why not: your agent will be just another user connecting to database, so if you have enough user licences, you should be fine.

Edit#2: on older versions of OE Pro2 uses older solution to track changes: it installs triggers for tables you want to replicate, they fire up and record change in auxhillary database, then replication agent ran on Management Server actually replicate records to Target. Management server also contains tools to generate SQL schema updates, new triggers if Source schema changes, etc.

CDC by itself allows you to track changed records but the rest is your responsibility: creating schema for target, generating SQL loads, etc. With newer version of OE, Pro2 utilizes CDC to speed up replication.


SELECT dir.name
    FROM directors dir
    INNER JOIN ddirectos ddir ON    dir.id = ddir.directorsid
    INNER JOIN movies mov ON mov.id = ddir.moviesid
    WHERE mov.id = 289

I believe this is what you are looking for.


You are connected to a SQL Server 2008 instance. The error message clearly indicates that. See How to determine the version and edition of SQL Server and its components, the section on SQL Server Database Engine version lists no less than 4 methods:

  Method 1: Connect to the server by using Object Explorer in SQL Server Management Studio. After Object Explorer is connected, it will
  show the version information in parentheses, together with the user
  name that is used to connect to the specific instance of SQL Server.
  Method 2: Look at the first few lines of the Errorlog file for that instance.
  Method 3: Connect to the instance of SQL Server, and then run the following query:

  Select @@version;
  Method 4: Connect to the instance of SQL Server, and then run the following query:

  SELECT SERVERPROPERTY('productversion'),
       SERVERPROPERTY ('productlevel'),
       SERVERPROPERTY ('edition');


A definite answer is really difficult. However, some trials might give us some hints.

We start with a few data:

CREATE TABLE customers
(
    customer_id integer PRIMARY KEY,
    last_name varchar(50) NOT NULL
) ;

CREATE TABLE payments
(
    rental_id integer not null,
    customer_id integer not null, --  references customer(customer_id),
    amount numeric(10,2) not null
) ;

INSERT INTO customers
VALUES
  (1, 'A - Customer 1'),
  (2, 'B - Customer 2'),
  (3, 'C - Customer 3'),
  (4, 'D - Customer 4') ;

INSERT INTO payments
VALUES
  (1, 1, 900.0),
  (2, 1, 800.0),
  (3, 1, 500.0),
  (4, 1, 900.0),
  (5, 2, 500.0),
  (6, 2, 500.0) ;

We first define a DETERMINISTIC (although it has side effects) function to log calls:

CREATE FUNCTION log_numeric(_n numeric(10,2), _counter character(2))
    RETURNS numeric(10,2)
    LANGUAGE SQL
    DETERMINISTIC
    BEGIN
      UPDATE log SET n = n + 1 WHERE counter = _counter ;
      RETURN _n ;
    END ;

We have previously defined a log table, to just store counts:

CREATE TABLE log
(
    counter character(2) primary key,
    n integer default 0
) ENGINE=MyISAM;
INSERT INTO log
VALUES
    ('x', 0),
    ('L', 0),
    ('T', 0),
    ('N', 0),
    ('L2', 0),
    ('T2', 0),
    ('N2', 0) ;

At this point, if MySQL were able to just perform the common subexpression elimination optimization (common for compilers), it would just make ONE call when we issue:

SELECT
    log_numeric(30, 'x') AS c1,
    log_numeric(30, 'x') AS c2 ;

Because log_numeric(30, 'x') would return the same value in both cases (because the function is deterministic).

In fact, it makes two calls to this function, as proven by watching counter 'x';

SELECT
    *
FROM
    log
WHERE
    counter = 'x' ;

[NOTE: Tested with MySQL 5.7.12]

It might be the case that MySQL has decided that the log_numeric function has some side effects, or that User Defined Functions do not work the same way as built-in functions. Built-in functions could be optimized, whereas UDF would not. In any case, if MySQL would have optimized the two expressions with just one call, it would be clear that this optimization is standard. As it did not, we can make an educated guess (which is not the same as a proof) and assume that the functions are called as many times as they're present.

The same can be done with the case posted:

SELECT
    log_char(LEFT(c.last_name, 1), 'L') AS last_names,
    log_numeric(SUM(p.amount), 'T') AS total_payments,
    log_numeric(COUNT(p.rental_id), 'N') AS num_rentals,
    log_numeric(SUM(p.amount), 'T') / log_numeric(COUNT(p.rental_id), 'N') AS avg_pay
FROM
    customers c
    JOIN payments p ON p.customer_id = c.customer_id
GROUP BY
    log_char(LEFT(c.last_name, 1), 'L')
ORDER BY
    log_numeric(SUM(p.amount), 'T') DESC;

And the results point to all calls being evaluated as many times as they appear.

You can check all theses tests at http://rextester.com/ZKS91815.

NOTE: For curiosity, postgreSQL (9.6.1) does a little better: it recognizes the ORDER BY and GROUP BY as "already computed" on the SELECT, but it doesn't optimize either for Common subexpression elimination. Room for improvement.

Alternative query avoiding repeating functions

If you're worried things are computed more than necessary, use:

SELECT
    last_names, total_payments, num_rentals,
    total_payments / num_rentals AS avg_pay
FROM
(
    SELECT
        log_char(LEFT(c.last_name, 1), 'L') AS last_names,
        log_numeric(SUM(p.amount), 'T') AS total_payments,
        log_numeric(COUNT(p.rental_id), 'N') AS num_rentals
    FROM
        customers c
        JOIN payments p ON p.customer_id = c.customer_id
    GROUP BY
        log_char(LEFT(c.last_name, 1), 'L')
) AS q0
ORDER BY
    total_payments DESC ;

I honestly don't think the difference will be much. The weight of the functions in the time spent for the whole query is probably (ver) small. It might be signficant if the functions were complex. Depending on how MySQL deals with Subqueries (that might be temporary tables), this could actually be worse.


Data deleted with sqlplus still showing up in SQL Developer

Does your SQLPlus script execute a COMMIT when it finishes the DELETE? If not, then the DELETE transaction hasn't been committed (persisted) yet. From the same connection in SQLPlus it'll look like the rows were deleted, but that's because it's still in the middle of the same active transaction.


After server move a query doesn't work anymore

How big are the tables? Sounds like an index problem. Do you have the same indexes on both servers? Did you rebuild the indexes after restoring?

As TiCL suggested, the best way is to compare the execution plans of both servers.


Can we use innodb to store latitutude and logitude and retrieve 20 closest points well?

An efficient, but complex, solution:
http://mysql.rjweb.org/doc.php/latlng
(The design actually prefers InnoDB.)


MySQL 5.7: REGEXP version of PostgreSQL REGEXP_REPLACE

What about

...
WHEN mobilenumber REGEXP '^09[0-9]'
THEN concat('+63', substring(mobilenumber, 2))
...

? As far as I understand it, you only want the first zero to be replaced. If the pattern matches, you know that the first digit is a zero. So just simply take the substring after the first character and contact the '+63' in the beginning.


Will repeated, identical expressions get processed again?

SQL Server is usually pretty smart about not computing the same thing multiple times at the same level of scope. So you should only see one compute scalar in either variation.

In fact, even in this more complex case, SQL Server still manages to only require a single compute scalar, and all the costing is the same:

SELECT DISTINCT LEFT(fiscalyear,6)
FROM dbo.Transactions
  WHERE LEFT(fiscalyear,5) >= '2009'
  OR LEFT(fiscalyear,4)>'2008';

(And it should go without saying that the better approach - if fiscalyear is date/time - is to have a sargable where clause, like WHERE fiscalyear >= '20090101'. If it is a string, then like @ypercube mentions above, you don't need the left expression - WHERE fiscalyear >= '2009' does the same thing.)

But as a generic answer, if you ever have any doubts about this, run the two queries with actual execution plan turned on, and see if they're different. However, I will say that the subquery form is often used not to prevent an expression from being evaluated multiple times, but to avoid having to type it multiple times. And even if you haven't typed it multiple times, there are still cases where it can still be evaluated multiple times. So there is no absolute "this method is better than the other one."


Is this good/bad database design and why?

Bad. Here are some random notes which come from experience dealing with this sort of system. Take as you will:

A big offender is xyz_N columns (i.e. answer_2 and a5_comment). When you see this, think incorrectly normalized data. What happens if there are 6 questions? 100 questions? (Oh, yes, I develop this sort of software and you wouldn't believe how many questions people are expected to answer!)
Another bad design is trying to "share" a table: Question also serves as "Answers"? No; again, de-normalized data! How will multiple people answer without duplicating data? It's not possible because each "Answer" is a Question! Think in terms of actions/queries and ask if they can be fulfilled.
Keeping the above in mind, there are very crucial missing relationships for Surveys/Exams (which "contain Questions") and Responses (which "contain Answers"). These relationships are very important for normalization (no xyz_N columns) and being able to ask questions like: "Which questions should I show now?"
There are no "Users" relating to Courses. There are Teachers (Faculty) and Students. Students can not "own" courses. This is easily and cleanly separable and allows better DRI. Also, different information needs to be captured for different "user" types - trust me. Also, sometimes a Teacher is a Student as well!
Redundant relationship from "Answer"/Question to Course; this relationship exists Question to Section to Course and does not need to be duplicated.


SQL Server passing variable to a string in a stored proc

The parameters supplied to a procedure must be constants or variables.
A function or the output of a function or an expression cannot be used.

You would have to first set a variable with the required expression:

declare @investigation varchar(100);
set @investigation = 'Investigation is pending for  ['+ @investigationidout +'] ';

EXEC sp_wf_create_notification
    @processid,
    @vactivityid,
    @vstepid,
    1,
    @vidColumn,
    @vidColumnTable,
    @investigationidout,
    @owner,
    'Pending Investigation',
    @investigation
    @nextstepurl,
    @vstatus OUTPUT,
    @verror OUTPUT
;

For OUTPUT variables, don't forget to add OUTPUT after its name.

See MSDN Specify Parameters:

  The parameter values supplied with a procedure call must be constants or a variable; a function name cannot be used as a parameter value. Variables can be user-defined or system variables such as @@spid.


Oracle 11g database- performance enhancement and sql tuning methods

I'd recommend reading Oracle's official performance tuning guide for 11g:

https://docs.oracle.com/cd/E11882_01/server.112/e41573/toc.htm


SQL Server Extended event filters: can you use an IN predicate with a subquery?

No.  Extended Event filters can't run queries.  Extended event filters need to be very cheap, so you should favor over-collecting events to over-filtering them.


SQL Server Hardware Specs for Analyzing TBs of Data

Favor the fastest processors you can get, over pure core count.  Get as much memory as you can afford up to the maximum size of data you expect to process in one chunk.  The NVMe SSDs you have specced are exactly what I would recommend.

Without more precise requirements, it is difficult to recommend specifics.

Take a look at this answer for details about clock speed vs core count.  Which edition of SQL Server are you going to use?


What does it mean if the spt_monitor - table in SQL Server is empty?

To answer your specific question: "What would it tell me if this table were empty?"  - It would tell you that you or some process somehow truncated or deleted this table. It should always have a row, and when you run the sp_monitor system stored procedure this row gets overwritten with a new row.

More Information on spt_monitor and sp_monitor

spt_monitor is a system table stored in the master database. This table gets populated for the first time when you instal SQL Server. It contains various information about some system resources utliization that are tracked inside of SQL Server (described below).

The purpose of this table is to provide a way for you to compare some of these metrics over time. The table will only ever have one row. The work of giving the differences is done in the stored procedure described below -

sp_monitor is a system stored procedure stored procedure in the master database. This stored procedure reads the current row in spt_monitor into a temporary table and then inserts a new row and compares the difference between all of the various counters tracked (including the lastrun column to give an elapsed time.

This stored procedure then gives the current value for each counter as well as the difference in the time between executes in parenthesis.

I've included a quick sample output from my one of my virtual machines. This is activity on a mostly sleeping VM for a 46 second timeframe. To explain the counters, look at the packets_received column. There have been a total of 777 packets received on this instance since it was last restarted according to the @@pack_received system function, and in the past 46 seconds there has been on received.

Sample Output:

sp_monitor
------------------------*/
last_run                current_run             seconds
----------------------- ----------------------- -----------
2012-12-15 22:39:56.770 2012-12-15 22:40:42.500 46

cpu_busy                  io_busy                   idle
------------------------- ------------------------- -------------------------
5(0)-0%                   3(0)-0%                   12366(44)-95%

packets_received         packets_sent             packet_errors
------------------------ ------------------------ ------------------------
777(1)                   627(1)                   0(0)

total_read          total_write         total_errors        connections
------------------- ------------------- ------------------- ------------------
723(0)              153(1)              0(0)                275(0)

What Do You Do if You Find The Table Empty?

Again, I am pretty sure that an empty table here is an indication that you, or some monitoring tool or process you are running emptied the table. Or possibly that there was an issue with a portion of the installation. If you find this table empty, there should be a file in your SQLRoot\MSSQL\Install directory called u_tables.sql This file creates some system tables and populates them. If you search for spt_monitor you'll see the create and insert script for this table. The reelinsert script (in SQL Server 2012 RTM) looks like this:

insert into spt_monitor
    select
    lastrun = getdate(),
    cpu_busy = @@cpu_busy,
    io_busy = @@io_busy,
    idle = @@idle,
    pack_received = @@pack_received,
    pack_sent = @@pack_sent,
    connections = @@connections,
    pack_errors = @@packet_errors,
    total_read = @@total_read,
    total_write = @@total_write,
    total_errors = @@total_errors
go

A Final Word

I'm not sure why you are using this table. I would suggest that you read up on each of the system functions above in books online. Some of them have notes of warning about various conditions where they can return incorrect data. For instance the note for [@@cpu_busy][1] talks about the data type and a warning that if it accumulates more than 49 days of time, it is no longer accurate or valid. Same with some of the other counters.

I have actually never run sp_monitor on an instance of mine. It wasn't until this question that I dug deeper and learned about what it is and what it does. It looks interesting, it provides data on a host of counters (you can learn about each of those functions above here) that look to provide some useful data at first blush.

But then when you look deeper, most of the information grabbed is probably best collected in other ways. For most of the information,  I would look at a combination of perfmon counters, DMVs and virtual file stats. The total_errors counter is interesting but I can't really think of a useful case for the information presented that way. I want to get my disk write errors  (total_errors) and alerts as they happen and not worry about a cumulative count so much. Looking at total connections since a restart could be interesting and if you didn't want to track this information through perfmon or repeated queries at DMVs and adding it, that could be helpful.


This query worked for me. It uses GEOLOC from Oracle Spartial

    create table my_result_table as  (
    select n.nodeid, n.geoloc.sdo_point.y as node_y, n.geoloc.sdo_point.x as node_x,
        r.geoloc.sdo_point.y as sl_y, r.geoloc.sdo_point.x as sl_x,
        sdo_nn_distance(1)  as sld
       from  my_nodes n,  my_restaurants r
       where
       sdo_nn   ( r.GEOLOC, n.geoloc,  'Unit = MILE sdo_num_res = 1', 1) = 'TRUE'
        );

Note that first argument is restaurants, not nodes.


How to speed up SQL Server 2014 Spatial functions

it seems you need to add date_to column to index on date_from column (create multicolumn index on dates)
your geometry index is not used in both execution plans (probably because the boundaries in filter are outside of the boundaries in index). But it seems with correct multicolumn index you don't need this spatial index at all

Let's try this:
SELECT graveforespoergsel_id, graveperiode_fra, graveperiode_til
INTO #t
FROM graveforespoergsel
WHERE "graveperiode_til" >= '2019-01-10 00:00:00' AND "graveperiode_fra" <= '2022-10-10 00:00:00'

SELECT "the_geom", graveperiode_fra, graveperiode_til
FROM #t
INNER JOIN graveforespoergsel_polygon ON graveforespoergsel_polygon.graveforespoergsel_id =  #t.graveforespoergsel_id
WHERE "the_geom".Filter(geometry::STGeomFromText('POLYGON ((434527 5928957, 434527 6282033, 1214815 6282033, 1214815 5928957, 434527 5928957))', 25832)) = 1

DROP TABLE #t


Why does this MySQL query take 25+ seconds?

Your index may be finding the records relatively quickly, but it has to perform a double lookup for every single one of those rows that it finds for that userId.

Try adding the following index:

(userId, A, T, category)

to table1.

This will be a covering index, and should yield extremely quick results.


The answer is right there on the last line of the error message:

  Sql client not registered

You need to install the SQL Server Client Library on the machine in question.

You can obtain that package from http://go.microsoft.com/fwlink/?LinkID=239648&clcid=0x409

Also, Isolation Level = ReadUncommitted may be problematic if you don't fully understand the implications of ReadUncommitted See http://msdn.microsoft.com/en-us/library/ms173763.aspx


For learning, teaching, and assessing I'd recommend an open source system called moodle  (https://moodle.com/moodle-lms/).  It can probably do all you ask for, and much more eg online assessments with time locks (or protected by passwords).  It is also good for keeping students' work, for sending messages to students (or giving feedback on documents or archives sumitted by students), for grouping students into classes - which then have different access permissions for teaching materials etc etc.

There is a lot of documentation available for it ( see https://docs.moodle.org/33/en/Main_page ).  I recommend that you have a look at this.  You can configure a server (or VM) so that it is only accessible on your institute's LAN.

I'd recommend against developing your own application.  Rather spend the time/money/energy to set this up the way it is beneficial for you.  There's - of course - much more to be said or written about this, but: have a look, try it out, maybe you will find it useful.  I am not associated with the company or the project.  However, having used other VLEs (virtual learning environments) in the past, I would preferably use moodle.


Mysql trigger not working, syntax issues

For 5.5 and later it is possible to use signals:

delimiter @

create trigger checkcollision
after update on players
for each row
begin
    declare dummy int default 0;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y;
    if (dummy = 1) then
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Any Message';
    end if;
end @

delimiter ;

For 5.1 and earlier version there is no support for signal. You could try to mimic it with an forced exception, like division by zero or by referencing something that does not exist. You don't get a nice error message though:

delimiter @

create trigger checkcollision
after update on players
for each row  begin
    declare dummy int default 0;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y;
    if (dummy = 1) then
        select 1/0 into dummy;
    end if;
end @

delimiter ;

A slightly more elegant way is to use an exit handler which is supported in 5.1, still no error message though:

create trigger checkcollision
after update on players
for each row
begin
    declare dummy int default 0;
    DECLARE EXIT HANDLER FOR NOT FOUND begin end;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y;
    select 1/0 into dummy;
end @

If no wall is found an empty exit handler is invoked, otherwise the trigger continues and an deliberate error is made.

By adding a dummy table like:

create table dummy (msg varchar(100) primary key);

We can force a primary key violation by inserting the same value twice from the trigger:

delimiter @
create trigger checkcollision
after update on players
for each row  begin
    declare dummy int default 0;
    DECLARE EXIT HANDLER FOR NOT FOUND begin end;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y;
    insert into dummy (msg) values ('ERROR: Collision')
                                 , ('ERROR: Collision');
end @
delimiter ;

We will get an error message like (tested in 10.0.20-MariaDB):

ERROR 1062 (23000): Duplicate entry 'ERROR: Collision' for key 'PRIMARY'

You might want to encapsulate this into a stored procedure:

create procedure my_signal (msg varchar(100))
begin
    insert into dummy (msg)
    values ('ERROR: Collision')
         , ('ERROR: Collision');
end @

which can be called from the trigger:

create trigger checkcollision
after update on players
for each row
begin
    declare dummy int default 0;
    DECLARE EXIT HANDLER FOR NOT FOUND begin end;
    select 1 into dummy from walls where x=NEW.x and y=NEW.y;
    call my_signal('ERROR: Collision');
end @


Log Shipping with Failover Cluster Server Cores

No, you don't need to match hardware. There's no technical requirement for that.

Lots of people choose to use slightly less robust hardware at DR

Saves on licensing costs
Offloaded reads may not need full production power
Assume that DR failovers aren't permanent

That said, whatever lower tier of hardware you choose has to be able to support your current workload.

In general, you want to make sure you're not hitting any really bad resource waits like THREADPOOL -- if you're hitting it on 6 cores, you'll definitely hit it on 4 cores.

If you have less RAM at DR, be aware of RESOURCE_SEMAPHORE waits.

The only way to tell for sure is to coordinate a failover and measure your server's response to your workload.

Hope this helps!


How to prevent logins using Windows Authentication in SQL Server?

Natively you cannot prevent using Windows Authentication in SQL Server.

  SQL Server supports two authentication modes, Windows authentication
  mode and mixed mode.

  Windows authentication is the default, and is often referred to as
  integrated security because this SQL Server security model is tightly
  integrated with Windows. Specific Windows user and group accounts are
  trusted to log in to SQL Server. Windows users who have already been
  authenticated do not have to present additional credentials.

  Mixed mode supports authentication both by Windows and by SQL Server.
  User name and password pairs are maintained within SQL Server.

Microsoft also recommends using windows authentication over SQL Server logins.

  We recommend using Windows authentication wherever possible. Windows
  authentication uses a series of encrypted messages to authenticate
  users in SQL Server. When SQL Server logins are used, SQL Server login
  names and encrypted passwords are passed across the network, which
  makes them less secure.

As commented by Nick you can programmatically restrict the creation of logins from Windows user account, trusted domain account, or Windows groups.

Reference: Authentication in SQL Server

A similar question was also answered here by Paul White.


select users who do something on each date in sql server

The query to list the users and dates for a given building (defined by @BuildingID):

DECLARE @BuildingID int = 42;

SELECT
    user_id,
    [Date] = CONVERT(date, [datetime])
FROM dbo.tablename
WHERE building_id = @BuildingID
GROUP BY
    user_id,
    CONVERT(date, [datetime])
ORDER BY
    [Date], user_id;

Beyond that, the requirement is not clear. In the title you say "on each date" - if you have a set of dates and you want the users who accessed @BuildingID on all of those dates, one approach could be:

DECLARE @BuildingID int = 42;

DECLARE @dateCount int;
DECLARE @dates TABLE([Date] date UNIQUE);

INSERT @Dates([Date]) VALUES('20190606'),('20190609');
SELECT @dateCount = COUNT(*) FROM @Dates;

;WITH users AS
(
  SELECT
    t.user_id
  FROM dbo.tablename AS t
  INNER JOIN @Dates AS d
     ON t.[datetime] >= d.[Date]
    AND t.[datetime] < DATEADD(DAY, 1, d.[Date])
  WHERE t.building_id = @BuildingID
  GROUP BY
    t.user_id
)
SELECT user_id
  FROM users
  GROUP BY user_id
  HAVING COUNT(*) = @dateCount;

Not sure if you are querying the data more often than you are writing it, but this might be a use case for an indexed view.


I would do it like this:
 - Create a table users, where you store your users.
 - Create a table champions, where you store the champions and also in a column champions.users_id the id for that user who created this champion.
 - If a user wants to edit a champion, you check if he belongs to him by checking if champions.users_id is the same as the users ID.

And by the way, no one should be able to connect to your DB. You can define, that you can only grant access to the DB, if the IP is right. For example only localhost or whatever. Your queries should all run through one mysql user.

This are my thoughts about this topic. Maybe possible that there are better ways. But i would go on like this.


How to migrate windows login in from one sql server to another sql server on another domain

  So whatever logins the database has in DMN1 cannot be used in DMN2
  anymore. it would take a lot of time to sort out a lot of windows
  logins in DMN1, get their equivalent login in DMN2 and map them to the
  right databases. What would be an easier way of doing it

Take a look at the two below scripts I've used for similar needs in the past. You will need to run these on each DB you need to mirror permissions for the two different domain accounts.

You will need to search the results of script #1 for the principal you need to mirror the access which is changing domains, etc. Once you get the logic out you need, run it with the new value of the domain credential from the new domain and then run that logic on each DB.

Use the script #2 query before and/or after if you wish per each DB to check the records for the security principals.

Script 1

/* Script DB Level Permissions v2.1 Source: http://www.sqlservercentral.com/scripts/Security/71562/
*/

DECLARE
    @sql VARCHAR(2048)
    ,@sort INT

DECLARE tmp CURSOR FOR

/*********************************************/ /*********   DB CONTEXT STATEMENT    *********/ /*********************************************/ SELECT '-- [-- DB CONTEXT --] --' AS [-- SQL STATEMENTS --],
        1 AS [-- RESULT ORDER HOLDER --] UNION SELECT  'USE' + SPACE(1) + QUOTENAME(DB_NAME()) AS [-- SQL STATEMENTS --],
        1 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        2 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/ /*********     DB USER CREATION      *********/ /*********************************************/

SELECT '-- [-- DB USERS --] --' AS [-- SQL STATEMENTS --],
        3 AS [-- RESULT ORDER HOLDER --] UNION SELECT  'IF NOT EXISTS (SELECT [name] FROM sys.database_principals WHERE [name] = ' + SPACE(1) + '''' + [name] + '''' + ') BEGIN CREATE USER ' + SPACE(1) + QUOTENAME([name]) + ' FOR LOGIN ' + QUOTENAME([name]) + ' WITH DEFAULT_SCHEMA = ' + QUOTENAME([default_schema_name]) + SPACE(1) + 'END; ' AS [-- SQL STATEMENTS --],
        4 AS [-- RESULT ORDER HOLDER --] FROM    sys.database_principals AS rm WHERE [type] IN ('U', 'S', 'G') -- windows users, sql users, windows groups

UNION

/*********************************************/ /*********    DB ROLE PERMISSIONS    *********/ /*********************************************/ SELECT '-- [-- DB ROLES --] --' AS [-- SQL STATEMENTS --],
        5 AS [-- RESULT ORDER HOLDER --] UNION SELECT  'EXEC sp_addrolemember @rolename ='
    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(USER_NAME(rm.member_principal_id), '''') AS [-- SQL STATEMENTS --],
        6 AS [-- RESULT ORDER HOLDER --] FROM    sys.database_role_members AS rm WHERE   USER_NAME(rm.member_principal_id) IN (
                                                --get user names on the database
                                                SELECT [name]
                                                FROM sys.database_principals
                                                WHERE [principal_id] > 4 -- 0 to 4 are system users/schemas
                                                and [type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group
                                              )
--ORDER BY rm.role_principal_id ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        7 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/ /*********  OBJECT LEVEL PERMISSIONS *********/ /*********************************************/ SELECT '-- [-- OBJECT LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        8 AS [-- RESULT ORDER HOLDER --] UNION SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc
            ELSE 'GRANT'
        END
        + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(SCHEMA_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)
--select, execute, etc on specific objects
        + CASE
                WHEN cl.column_id IS NULL THEN SPACE(0)
                ELSE '(' + QUOTENAME(cl.name) + ')'
          END
        + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(usr.principal_id)) COLLATE database_default
        + CASE
                WHEN perm.state <> 'W' THEN SPACE(0)
                ELSE SPACE(1) + 'WITH GRANT OPTION'
          END
            AS [-- SQL STATEMENTS --],
        9 AS [-- RESULT ORDER HOLDER --] FROM
    sys.database_permissions AS perm
        INNER JOIN
    sys.objects AS obj
            ON perm.major_id = obj.[object_id]
        INNER JOIN
    sys.database_principals AS usr
            ON perm.grantee_principal_id = usr.principal_id
        LEFT JOIN
    sys.columns AS cl
            ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id
--WHERE usr.name = @OldUser
--ORDER BY perm.permission_name ASC, perm.state_desc ASC

UNION

SELECT '' AS [-- SQL STATEMENTS --],
    10 AS [-- RESULT ORDER HOLDER --]

UNION

/*********************************************/ /*********    DB LEVEL PERMISSIONS   *********/ /*********************************************/ SELECT '-- [--DB LEVEL PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        11 AS [-- RESULT ORDER HOLDER --] UNION SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
            ELSE 'GRANT'
        END
    + SPACE(1) + perm.permission_name --CONNECT, etc
    + SPACE(1) + 'TO' + SPACE(1) + '[' + USER_NAME(usr.principal_id) + ']' COLLATE database_default --TO <user name>
    + CASE
            WHEN perm.state <> 'W' THEN SPACE(0)
            ELSE SPACE(1) + 'WITH GRANT OPTION'
      END
        AS [-- SQL STATEMENTS --],
        12 AS [-- RESULT ORDER HOLDER --] FROM    sys.database_permissions AS perm
    INNER JOIN
    sys.database_principals AS usr
    ON perm.grantee_principal_id = usr.principal_id
--WHERE usr.name = @OldUser

WHERE   [perm].[major_id] = 0
    AND [usr].[principal_id] > 4 -- 0 to 4 are system users/schemas
    AND [usr].[type] IN ('G', 'S', 'U') -- S = SQL user, U = Windows user, G = Windows group

UNION

SELECT '' AS [-- SQL STATEMENTS --],
        13 AS [-- RESULT ORDER HOLDER --]

UNION

SELECT '-- [--DB LEVEL SCHEMA PERMISSIONS --] --' AS [-- SQL STATEMENTS --],
        14 AS [-- RESULT ORDER HOLDER --] UNION SELECT  CASE
            WHEN perm.state <> 'W' THEN perm.state_desc --W=Grant With Grant Option
            ELSE 'GRANT'
            END
                + SPACE(1) + perm.permission_name --CONNECT, etc
                + SPACE(1) + 'ON' + SPACE(1) + class_desc + '::' COLLATE database_default --TO <user name>
                + QUOTENAME(SCHEMA_NAME(major_id))
                + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(USER_NAME(grantee_principal_id)) COLLATE database_default
                + CASE
                    WHEN perm.state <> 'W' THEN SPACE(0)
                    ELSE SPACE(1) + 'WITH GRANT OPTION'
                    END
            AS [-- SQL STATEMENTS --],
        15 AS [-- RESULT ORDER HOLDER --] from sys.database_permissions AS perm
    inner join sys.schemas s
        on perm.major_id = s.schema_id
    inner join sys.database_principals dbprin
        on perm.grantee_principal_id = dbprin.principal_id WHERE class = 3 --class 3 = schema

ORDER BY [-- RESULT ORDER HOLDER --]

OPEN tmp FETCH NEXT FROM tmp INTO @sql, @sort WHILE @@FETCH_STATUS = 0 BEGIN
        PRINT @sql
        FETCH NEXT FROM tmp INTO @sql, @sort     END

CLOSE tmp DEALLOCATE tmp

source

Script2

CREATE VIEW vwObjectPermissions
AS
SELECT schema_name(o.schema_id) AS [Schema_Name]
    ,o.NAME AS [object_name]
    ,u.NAME AS [principal_name]
    ,u.type_desc AS [principal_type]
    ,r.minor_id
    ,r.permission_name
    ,r.state_desc
    ,o.schema_id
    ,o.principal_id AS [alt_owner]
    ,o.type_desc
FROM sys.database_permissions r
LEFT JOIN sys.database_Principals u ON r.grantee_principal_id = u.principal_id
LEFT JOIN sys.all_objects o ON o.object_id = r.major_id
WHERE class_desc NOT IN ('database')
GO

--1. Check if Public or guest is granted any permission on an object (database role and server role)
SELECT *
FROM vwObjectPermissions
WHERE principal_name IN ('Public','Guest')

--2. Check if any user is granted permissions on an object rather than roles.
SELECT *
FROM vwObjectPermissions
WHERE principal_type NOT LIKE '%ROLE%'

--3. Check if a user has "with grant" previliges on an object
SELECT *
FROM vwObjectPermissions
WHERE state_desc = 'WITH GRANT' --check the spelling on this one

--4. Check who has access to extended stored procedures (which I get from select name from sysobjects where xtype='X')
SELECT *
FROM vwObjectPermissions
WHERE type_desc LIKE '%X%Proc%'
GO

DROP VIEW vwObjectPermissions;

source


The REOPT(ALWAYS) option conflicts with the explain facility behaviour, because in this case the statement compilation is delayed until the bind variable value is known -- which is the whole point of reoptimization, isn't it. The manual says in particular:

  The REOPT bind option defers statement compilation from PREPARE to OPEN or EXECUTE time, when the input variable values are known. The values are passed to the SQL compiler so that the optimizer can use the values to compute a more accurate selectivity estimate.

You are not providing the expected value, so compilation cannot proceed.

The EXPLAIN statement succeds I believe because it doesn't get to the re-optimization phase therefore not expecting a value for the bind variable. As a result, the plan it generates will probably differ from the reoptimized one.

You can address this by creating a global variable: CREATE VARIABLE myparm INT DEFAULT 5 (or whatever data type and value you need) and then referencing myparm in the statement where you have the parameter marker now. You will also need to create a new profile that matches the new statement text.

Tested and works on v11.1 but I assume the behaviour would be the same in 10.5.


Table with image data base64 per row causes deadlocks/block

In general, you should always change this approach:

IF EXISTS ( /* scan the table with a where clause */ )
    UPDATE ( /* scan the table AGAIN with a where clause */ )
ELSE
    INSERT

To this:

UPDATE ( /* scan the table JUST ONCE with a where clause */ )
IF @@ROWCOUNT = 0
  INSERT

Forget you ever learned the first approach. It's like going to the grocery store to check if they have any eggs, then going back home to get your wallet, and back to the grocery store again to buy your eggs.

Also, yes, a clustered index can help in many scenarios. Is MasterKey really a key (e.g. is it unique)? Or is the combination of MasterKey + DetailKey the candidate key? In the latter case, an index (clustered or not) with both columns as key columns will help reduce any residual searching the query has to do to locate a specific row after it finds the rows with that MasterKey value (which prolongs blocking and can lead to deadlocks depending on other factors).

Actual use case. First, change the indexes:

DROP INDEX [ix_MyTable_details] ON [dbo].[MyTable];

CREATE UNIQUE CLUSTERED INDEX cix_MyTable_details
  ON dbo.MyTable(MasterKey, DetailKey);

Then change the query to:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;

UPDATE dbo.[MyTable]
  SET [JSON] = @JSON
  WHERE MasterKey = @MasterKey AND DetailKey = @DetailKey;

IF @@ROWCOUNT = 0
BEGIN
  INSERT dbo.[MyTable]
       ([MasterKey]
       ,[DetailKey]
       ,[JSON])
  VALUES
       (@MasterKey
       ,@DetailKey
       ,@JSON);
END

COMMIT TRANSACTION;


I've had Access databases of 1.5 GB (7 years ago on a machine much weaker than what you've described) run fine - for some queries. It really depends on what you are querying and how it's indexed. Without knowing the details of the queries and reports you're running, I can't say if it's too slow or not (and you don't actually define slow: 10 seconds? 2 seconds? 3 hours?) . I know in the past that Access had a 2GB file-size limit. I don't know if recent versions have changed that.


Using non-replicated read/write databases on a MySQL slave server

You can safely write to a schema on the slave if that schema does not exist on the master or if you correctly use replication filters to prevent its replication to the slave.

"Correctly" is the important word in that sentence.  Refer to this page:

https://dev.mysql.com/doc/refman/5.7/en/replication-options-slave.html

Check the --replicate-do-db and other filter settings, and note that the behavior changes based on whether binlog events are formatted as rows or as statements.  When statement-based replication is being used, replication filters only apply to the database in use as the current database.  When row-based replication is used, the filters are applied regardless of current database.

Be sure you get this right, otherwise, you could have a situation like this:

replicate-ignore-db=monkey
binlog_format=STATEMENT

Then you do this:

USE zebra;
TRUNCATE monkey.tablex;

Guess what?  tablex just got truncated on both master and slave.  However, if you had this instead:

replicate-ignore-db=monkey
binlog_format=ROW

and did the same TRUNCATE on the master, monkey.tablex would not be truncated on the slave.

Note that this all applies according the binlog_format in use at the time the event is logged, and since binlog_format can be changed on a per-session basis, you can get some surprises unless you know exactly what's going on.

Having said all that, if the schema you're writing to does not even exist on the master, then you have nothing to worry about, other than the fact that you can't have read_only or super_read_only set on the slave if you plan on writing to it, so you can't count on it preventing write activity.


Consistency errors in database table after upgrade to SQL Server 2012

Hm... That's interesting...

So the index is somehow corrupted before the upgrade and when you see an error as above you can both fix this before and after upgrading, but only after upgrading DBCC will detect the problem.

Steps:

Figure out which index is failing

Run DBCC CHECKDB to get a column name.
Find index that spans over this column (for me it was a primary key).

Disable index: ALTER INDEX [my index name] ON [my table name] DISABLE; (some foreign keys might be disable along with it, don't worry about that).
Rebuild/re-enable index: ALTER INDEX [my index name] ON [my table name] REBUILD;.
Run DBCC CHECKDB; to make sure everything is fine.

That worked for me but YMMV. Again, you can do above in MS SQL 2008 R2 and then restore fresh backup on MS SQL 2012 or do above when already on MS SQL 2012.


Any recommendation on my.cnf on aws.large instance?

With that kind of AWS instance, you must be planning heavy writes.

I would add the following

innodb_log_buffer_size  = 32M
innodb_read_io_threads  = 16
innodb_write_io_threads = 16


After bashing my head for a good while, I could make the ODBC 32 bits run. It was the VC ++ 2013 Redistributable.

My install is version 12.2.0.1.0. I can't believe how stupidly hard it is to get the simple ODBC. You must uncompress the Instant CLient plus ODBC in the same folder and then manually install VCRedist to get this running. GEEEZ.

UPDATE:
Adding a few extra details on my exact steps:

Download Oracle Instant client from the download portal:
https://www.oracle.com/database/technologies/instant-client/downloads.html

Make sure you download the version that you need: X64 or X32. NOTE: DO NOT MIX THE x64 and X32 folders!!!

After you choose the x64 or x86 version, you will be presented with a huge list of packages. You want to make sure you scroll down to the proper version, in my case, it was 12.2.0.1.0.

You must download 2 packages:

Basic Package
ODBC Package

If you are installing x32 and x64, you must then download 4 packages:

Basic Package x32
ODBC Package x32
Basic Package x64
ODBC Package x64

Then, download and install VCredist 2010 and VCredist 2013. Here is a good resource to download them, for x32 you must install x32 bits VCRedist.
https://www.itechtics.com/microsoft-visual-c-redistributable-versions-direct-download-links/

Then, you must uncompress the Basic and ODBC package in the same folder. DO NOT MIX x32 and x64, you must create separate folders if you are going to install both bit parity versions.

Then, open CMD as administrator, navigate to your uncompressed folder and run odbc_install.exe.

Then go to ODBC Datasources (Start menu, type ODBC) and add a new DSN, you will be presented with the Oracle ODBC screen.
Here is a tutorial to create the DSN:
http://dba.fyicenter.com/faq/oracle/Define-DSN-with-ODBC-Manager.html

That's it! the key part here is the VCRedist, if you don't have it, you will have the error stated by the Original Poster.

I hope this helps.


How much does table size affect performance?

This would depend upon the queries you issue (suppose the large table is mydb.logtable).
ASPECT #1
For starters, think about the MyISAM storage engine. It only caches index pages in the MyISAM key cache (sized by key_buffer_size). If any queries against mydb.logtable reads a significant number of index pages from /var/lib/mysql/mydb/logtable.MYI, the query mya purge out index pages from other tables. This would causes other queries for those other table to have to reread them index pages from disk again.
ASPECT #2
Any queries against a large MyISAM table that is infrequently accessed may have old index statistics, possibly resulting in full table scans down the road. During off hours, you should setup a crontab job that runs this SQL command:
ANALYZE TABLE mydb.logtable;

This will rebuild the index statistics.
ASPECT #3
Regardless of Storage Engine (or even RDBMS), any query that requests more that 5% of a table's total size would cause the MySQL Query optimizer to stop using indexes and do full table scans. For example, if you query data for a year and a table contains less than 20 years of log info, it is very probable that a full table scan will happen.
ASPECT #4
If the cardinality of the indexes is very low, a certain key combination could result in a query not using any indexes and do a full table scan.
For a demonstration of low cardinality affecting query performance and EXPLAIN plain generation, see my Nov 13, 2012 post Must an index cover all selected columns for it to be used for ORDER BY?
SUGGESTION
You should schedule some form of log rotation. Perhaps this:
SET @tb1 = 'mydb.logtable';
SET @tb2 = CONCAT(@tb1,'_',date_format(now(),'%Y%m%d_%H%i%s'));
SELECT CONCAT('ALTER TABLE ',@tb1,' RENAME ',@tb2) INTO @sql_1;
SELECT CONCAT('CREATE TABLE ',@tb1,' LIKE ',@tb2) INTO @sql_2;
PREPARE s FROM @sql_1; EXECUTE s; DEALLOCATE PREPARE s;
PREPARE s FROM @sql_2; EXECUTE s; DEALLOCATE PREPARE s;

This will keep the log file lean and mean and archive the most recent entries.


Technically you can do it directly to \\NetworkLocation\C$ like your boss wants, but the c$ share is one of the windows Adminstrative Shares ( http://en.wikipedia.org/wiki/Administrative_share ) and is only available to users in the Admistrators group. So to use it in this case you'd need to add the user account running your SQL Agent to the Administrators group on NetworkLocation

Using these shares like this is a really bad idea. It would be much better for example to share c:\Backups\ as \\NetworkLocation\Backups$  (the $ here indicates a hidden share, so it doesn't show up in the list when a user looks at the machine). As it's not an administrative share you can set the permissions however you want for any account, without giving them Administrator on NetworkLocation

The files are in exactly the same place and you've not compromised your security so all is good.


Cannot allocate memory means you have not enough ram.
check the memory requirements  in the oralce guide


Most probably this is fake alarm. the space UNDO tablespace is allocated differently then on other tablespaces. Oracle simply does not clean out old versions of block unless necessary. There are no real data in this tablespace.

You UNDO space was really full you would get ORA-01555 Snapshot too old, or other types of errors. Check the view v$undostat.


Different services using different port pointing to same datadir folder

NO!  Do not attempt to have the same files accessed by different instances of MySQL.  The code has now way of coordinating access between them.  (If it did, it would slow MySQL down to a crawl.)


What happens if two mySQL queries both try to lock the same row at the same time?

This is not a deadlock.

One transaction will simply block -- waiting to acquire the lock. The other transaction will proceed. As soon as the other transaction is done -- either by commit or rollback, the first transaction will proceed.

A deadlock happens when a transaction has acquired a lock on object A, and attempts to acquire a lock on object B at the same time as another transaction has already acquired a lock on object B and is attempting to acquire a lock on object A. Both transactions will then block, waiting on each other. That's the definition of a deadlock: two transactions blocked waiting on a lock that the other has.


How to select records where NOW is between two dates in fields?

You can use the NOW() function in mysql. Note, this assumes that termDate is the later date:

Select content, image, imageHeight, imageWidth, link, linkDescription
  From news
  Where appearanceDate < NOW()
    And termDate > NOW()
  Order By appearanceDate Desc;


Analyse differential backup to see why it is so big

Take snapshot of sys.dm_db_file_space_usage, the modified_extent_page_count column. This is exactly what is says. How many extents has been modified since last full backup, measured in pages.
This column was introduced in 2016. Prior to that you would crack the DIFF map pages. Just as explained and exemplified by Paul Randal here, and as suggested by @Mo64 in a comment.
Take such a snapshot every 5 minutes into a table, and see when it blows up. Possibly tis is enough for you to determine what is causing this. My guess is some batch job.
If that isn't enough, keep going so you have a reliable pattern ("every Tuesday between 3 pm and 3:15 pm"). You now can run an XE trace on your system at that time and catch the SQL submitted to your SQL Server.