According to me cases somewhat would be like:
It's not clear which repository you are migrating to TFS from? It is also not clear why to use Microsoft technologies (available in view of TFS) when there are repositories that have proven themselves to be excellent? For example GIT, SVN, etc.
But if you really want to use Visual Studio Test Professional, then the Microsoft documentation says that there is such a possibility: https://docs.microsoft.com/en-us/vsts/build-release/test/continuous-test-java
I had a similar situation in one of the projects. A separate test database is created before starting the tests and the structure of the prod database is copied into it and filled with fake data. Then it is removed.
Tests use a mock object to work with a database, where a test database is substituted instead of a prod.
Sometimes you need to run non-destructive tests on existing records in a prod or stage database; or if it is not convenient to update the test data - you can switch the used database.
Your server is listening on both a TCP socket, and a Unix domain socket (i.e. a special file, which has a name and directory like regular files do, but doesn't have any content and is just there to help client and server find each other).
That last line is about the Unix domain socket. 75271 is the inode of that file. Your grep has stripped off the header lines which netstat outputs, so of course you can't see them. It might be better to pipe the output to less rather than grep. Or sent it to a file, then use your favorite text editor to look at the file.
The short answer is: changing the schema names is probably the best solution.
Databases (or schemas as I prefer to call them, because "database" has multiple meanings depending on context) are basically a namespace for tables and other SQL identifiers.
Just like any other namespace, if you have two entities of the same name, they can't share a schema. Their names conflict if they're in the same schema. If they have different names, they don't conflict.
So if all the tables from one PROD_1 schema are different from the tables of another PROD_1 schema, then you can merge them. But it's not likely that all the tables will be different.
You could change the table names so tables from one project have p1_ prefixes, and tables from another project have p2_ prefixes, etc. But you'd have to change all the query code in all of your applications.
You could run more than one MySQL instance on your single server. In a way, this creates another level of namespacing, because schemas can have the same name if they are in different MySQL instances.
At my last job, we ran MySQL instances in Docker containers with a Docker network so each Docker pod had its own IP address, like a virtual server.
Alternatively, you could run multiple MySQL or MariaDB instances on the server without Docker, but you'd have to change the port, socket, and data directory that each one uses. See https://mariadb.com/kb/en/running-multiple-mariadb-server-processes/
There is no way to use Causality tracking in the Extended Events collection filter.
What you can do is enable Causality tracking
GUI
TSQL
CREATE EVENT SESSION [TestEvent] ON SERVER
…
WITH (TRACK_CAUSALITY=ON)
and then collect everything and filter afterwards using the attach_activity_id.guid
which makes sense because you don't know the guid before so you cannot use it to filter. This way is usually not feasible due to how many wait_info events can be generated on a busy instance.
Alternatively, you can enable Query Store with wait stats collection (starting with SQL Server 2017 (14.x))
ALTER DATABASE
SET QUERY_STORE = ON ( WAIT_STATS_CAPTURE_MODE = ON );
The wait stats will be aggregated and grouped by category, but you can use Query Store to filter for a specific query hash or procedure name.
The main difference between https://www.erikdarlingdata.com/category/common-table-expression/ and temporary objects is that CTEs do not materialize a result set, and every time you reference them the inner query needs to be re-executed. I'm not bringing https://www.erikdarlingdata.com/category/table-variables/ into the picture because it introduces additional performance complexities.
I'm using this demo as a portable example. There are no performance issues here because it's a one row table, but it's sufficient to get the point across.
CREATE TABLE
#t
(
id int NOT NULL PRIMARY KEY
);
INSERT
#t
(
id
)
SELECT
id = 1;
We have a one-row table, and now let's hit it with a CTE:
WITH
t AS
(
SELECT
t.id
FROM #t AS t
)
SELECT
t.*
FROM t
JOIN t AS t2
ON t2.id = t.id
JOIN t AS t3
ON t3.id = t.id;
The resulting query plan looks like this:
For each join to the Common Table Expression, we end up hitting the base table. If you have CTEs that are referenced multiple times, you're usually better off using a #temp table to materialize the result. In Common Table Expressions with more complex queries in them, this can be especially painful.
While row goals do not materialize Common Table Expression results, they do (at least up to the current version of SQL Server) offer an https://dba.stackexchange.com/q/221555/32281 around the query inside of them.
As an example of how this can work, take a look at these two queries:
SELECT
c = COUNT_BIG(*)
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.AccountId
JOIN dbo.Comments AS c
ON c.PostId = p.ParentId
JOIN dbo.Votes AS v
ON v.PostId = c.PostId
WHERE c.Score > 1000;
SELECT
c = COUNT_BIG()
FROM dbo.Users AS u
JOIN dbo.Posts AS p
ON p.OwnerUserId = u.AccountId
JOIN
(
SELECT TOP (2147483647)
c.
FROM dbo.Comments AS c
JOIN dbo.Votes AS v
ON v.PostId = c.PostId
WHERE c.Score > 1000
) AS c ON c.PostId = p.ParentId;
Here are the query plans for them:
First query:
Second query:
The join order is altered because SQL Server has to honor the https://sqlperformance.com/2018/02/sql-plan/setting-and-identifying-row-goals . There's no real performance difference here, but you get the idea. If there's a particular thing you want to happen together, this can be one way to achieve that without a temporary object, but sans the materialization.
For an example of tuning a query that misused Common Table expressions by using #temp tables instead, check out this post of mine:
There's a video over there that I can't embed here that walks through identifying things like poor cardinality estimates coming out of Common Table Expressions, and using each one as a stopping point to materialize results.
What is the intended use case for virtual columns with a function-based index?
The reason I ask (novice):
I've been exploring options for pre-computing columns in a table. For example, in a roads table, populate a field using the spatially-intersecting zone number from a zone table. More information here: https://community.esri.com/t5/data-management-questions/options-for-computing-fields/m-p/1175929
My experience is that spatial queries are often slow — whether using Oracle's SDO_GEOMETRY datatype/functions or using a user-defined object type like Esri's ST_GEOMETRY datatype/functions. So I definitely do want to pre-compute that calculation — to avoid constantly making costly calculations every time the query is used.
At first, I was tempted to create a virtual column with a function-based index. But now (with input from others), I'm starting to wonder if precomputing a column using a function-based index might not be the right use for FBIs. Instead, I'm wondering if simply calculating a field in the table using a trigger might be a better option. (I'm aware that materialized views are also an option. But I don't have CREATE MATERIALIZED VIEW privileges.)
So, long story short, I suspect a virtual column and an FBI isn't appropriate for my use case. With that said, what is the right use case for a virtual column and an FBI?
On an Azure VM, does it matter if I partition a large disk into multiple drives (to hopefully be more contiguous - not sure if it'd matter virtually) where if space is needed, it'd then need to be a dynamic allocation (as far as OS)? Or should I generally stick to a disk per drive when expansion would seem necessary?
I am trying to import a bacpac file to my SQL Server Express 2016. An exception is thrown during the import with
GRANT ADMINISTER Database Bulk Operations TO [MyUser];
Returns:
Incorrect syntax near 'ADMINISTER'.
I am running the import as the sa user. Later when I try to run the SQL line directly, the same error is thrown. What can I do to resolve this?
Update: SQL Server Express 2019 throws "is not supported on this version of SQL Server" on the same line
https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-database-permissions-transact-sql?view=sql-server-ver15 suggests that the syntax is correct on both 2016 and 2019 without mentioning the exact applicable edition (Windows or Linux, Express or others)
you will need to have SQLite3 installed on your system, the database in question on your system and then navigate to the installation directory (or have the program directory in your PATH) before you type the sqlite command. You could also see if your DB Browser GUI program has the ability to export directly to a CSV. Or, if you do really only do expect to deal with 4 lines of data at a time, you can probably control-A (highlight all), control-C (copy) and then paste into excel directly.
As your question is more of a how-do-i-use-a-dos-prompt question than a database question, further follow-up should probably be on one of the other stackexchanges (stack overflow?) or by reading the documentation for SQLite.
STR_TO_DATE doesn't work like I expect.
| STR_TO_DATE('2007', '%Y') | STR_TO_DATE('2007', '%y') |
|---|---|
| 2006-11-30 | 2019-11-30 |
10.6.7-MariaDB-1:10.6.7+maria~bullseye-log
Right. You cannot GRANT the right to ALTER or DROP a table. Only the table owner or a superuser can do that.
https://www.postgresql.org/docs/current/ddl-priv.html describes that:
The right to modify or destroy an object is inherent in being the object's owner, and cannot be granted or revoked in itself. (However, like all privileges, that right can be inherited by members of the owning role; see https://www.postgresql.org/docs/current/role-membership.html )