Database Administrators Stack Exchange
Database Administrators Stack Exchange

Most Popular

more tags
1500 questions
24
votes
1 answer

Using a CREATE TABLE AS SELECT how do I specify a WITH condition (CTE)?

There is an old and deprecated command in PostgreSQL that predates CREATE TABLE AS SELECT (CTAS) called SELECT ... INTO .... FROM, it supports WITH clauses / Common Table Expressions (CTE). So, for instance, I can do this.. WITH w AS ( SELECT * …
Evan Carroll
  • 63,051
  • 46
  • 242
  • 479
24
votes
1 answer

(NOLOCK) vs NOLOCK

I was investigating some blocking when I saw a query that looked something like this: SELECT SomeField FROM SomeTable NOLOCK I saw the NOLOCK and was curious how it could be blocking other queries, in this case DELETE statements. I took a quick…
Brian
  • 353
  • 2
  • 5
24
votes
1 answer

Does SQL Server cache the result of a multi-statement table-valued function?

A multi-statement table-valued function returns its result in a table variable. Are these results ever reused, or is the function always fully evaluated every time it is called?
Paul White
  • 83,961
  • 28
  • 402
  • 634
24
votes
1 answer

Postgres: How is SET NOT NULL "more efficient" than CHECK constraint

In PostgreSQL docs for Constraints, it says A not-null constraint is functionally equivalent to creating a check constraint CHECK (column_name IS NOT NULL), but in PostgreSQL creating an explicit not-null constraint is more efficient. I'm…
Robin Joseph
  • 341
  • 2
  • 6
24
votes
2 answers

Postgres JOIN conditions vs WHERE conditions

Postgres newbie here. I'm wondering if this query is optimized or not? I tried to JOIN ON only the values that are 100% necessary and leaving all the dynamic conditions in the WHERE clause. See below. SELECT * FROM myapp_employees …
Dan
  • 447
  • 2
  • 5
  • 11
24
votes
2 answers

Percona vs MySQL

What is Percona? How does it differ from MySQL? When should we consider switching (or upgrading) from stock MySQL to Percona? To add some specifics in our situation we almost exclusively use InnoDB (which I understand Percona has done a lot of…
Noah Goodrich
  • 510
  • 1
  • 5
  • 14
24
votes
2 answers

Understanding "max_wal_size" and "min_wal_size" parameters default values from postgresql.conf file

Default values are, according to documentation for min_wal_size and max_wal_size parameters: For max_wal_size: The default is 1 GB For min_wal_size: The default is 80 MB Then I look this parameters from my database config: select name, setting, unit…
Oto Shavadze
  • 515
  • 1
  • 7
  • 14
24
votes
1 answer

CREATE DATABASE vs. CREATE ANY DATABASE permissions

In Microsoft SQL Server, what is the difference between the CREATE DATABASE and CREATE ANY DATABASE permissions? I am unable to find an authoritative answer. The best I can infer is that either (a) CREATE ANY implies I can create the database to be…
Tohuw
  • 601
  • 1
  • 5
  • 9
24
votes
4 answers

What is your workflow for planning a data migration?

So many times I've been brought in at the end of a software development effort and been told something like "okay, we've got all this new code and it requires tables to change and data to be migrated". It seems like every time it's a one-off,…
randomx
  • 3,934
  • 4
  • 30
  • 43
24
votes
3 answers

How to join to the same table multiple times?

I have two tables, "hierarchy_table" and "name_table". The hierarchy table contains an object which has multiple parents and children. Each parent and child is referenced by id. | object_id | parent_id_1 | parent_id_2 | child_id_1 | …
jase81
  • 541
  • 2
  • 4
  • 10
24
votes
5 answers

From the DMVs, can you tell if a connection used ApplicationIntent=ReadOnly?

I have an Always On Availability Group set up, and I want to make sure my users are using ApplicationIntent=ReadOnly in their connection strings. From the SQL Server via DMVs (or Extended Events or whatever), can I tell if a user connected with…
Brent Ozar
  • 42,952
  • 47
  • 220
  • 375
24
votes
1 answer

Why does this MERGE statement cause the session to be killed?

I have the below MERGE statement which is issued against the database: MERGE "MySchema"."Point" AS t USING ( SELECT "ObjectId", "PointName", z."Id" AS "LocationId", i."Id" AS "Region" FROM @p1 AS d JOIN "MySchema"."Region"…
Mr.Brownstone
  • 13,102
  • 4
  • 36
  • 54
24
votes
3 answers

What's the best way to archive all but current year and partition the table at the same time

Task Archive off all but a rolling 13 month period from a group of large tables. The archived data must be stored in another database. The database is in simple recovery mode The tables are 50 mil rows to several billion and in some cases take up…
Kenneth Fisher
  • 24,127
  • 12
  • 61
  • 114
24
votes
2 answers

How to check for Non-Ascii Characters

What is the best way to check if a VARCHAR field has Non-Ascii Characters? CHAR(1) through CHAR(31) and CHAR(127) through CHAR(255). I tried using PATINDEX and have run into the following issue. Checking the lower range worked correctly. SELECT * …
Gerhard Weiss
  • 343
  • 1
  • 2
  • 6
24
votes
9 answers

Test if a string is a palindrome using T-SQL

I am a beginner in T-SQL. I want to decide whether an input string is a palindrome, with output = 0 if it is not and output = 1 if it is. I am still figuring out the syntax. I am not even getting an error message. I am looking for different…
MSIS
  • 875
  • 9
  • 19
1 2 3
…
99 100