Microsoft SQL AI Developer Associate (DP-800) — Practice Questions and Mock Exam

The flags argument modifies how the pattern is interpreted rather than what it contains, and the supported modifiers cover case sensitivity, multi-line anchoring and whether the dot matches a newline. Because the argument is a string rather than a set of separate parameters, the engine has to resolve conflicts inside it, and it does so by letting the last character win, which makes the outcome deterministic but easy to get wrong when flags are assembled from configuration. A character outside the supported set is a hard error rather than a silent ignore, and an empty string is treated as the default. The practical habit is to build the flags string in one place and test the resolved behaviour, because a silently case-sensitive filter returns fewer rows rather than failing. Exam tip: contradictory flags resolve to the last one, and the default is case-sensitive.

Passwordless access to Azure SQL rests on the idea that a workload should prove what it is rather than what it knows. A managed identity is issued to an Azure resource, and the Azure Identity platform vouches for the link between the identity and that resource, so the application requests a token instead of presenting a secret. Nothing has to be stored, rotated or handed to a developer, which removes the largest single cause of leaked database credentials. The alternative for applications, a service principal with a client secret, still works but reintroduces a password that can be guessed or leaked, so the documentation marks it as not recommended. Human users take a different set of methods, including integrated authentication and multifactor authentication. Groups sit on top of all of this, letting permissions be managed once and inherited by many identities. Exam tip: workload plus passwordless means managed identity.

The restriction here is narrower than a general rule about determinism. Determinism is a documented property of a function — deterministic functions return the same result any time they're called with a specific set of input values and given the same state of the database, while nondeterministic ones might return different results under those same conditions — and what that property governs is indexability: whether the Database Engine can index the results of the function, through indexes on computed columns that call it or through indexed views that reference it. A Transact-SQL user-defined function is not required to be deterministic. What the article forbids is a short, named list of nondeterministic built-ins inside a UDF body — NEWID, NEWSEQUENTIALID, RAND and TEXTPTR — and referencing one of them raises Msg 443, invalid use of a side-effecting operator within a function. That sits alongside the function's other limitations: it can't perform actions that modify the database state, can't contain an OUTPUT INTO clause with a table as its target, and can't make use of dynamic SQL or temp tables, though table variables are allowed. Side effects are the through-line the documentation draws: changes to a global state of the database, such as an update to a table, or to an external resource such as a file or the network, and functions that create them aren't recommended. The view workaround exists because a view is a query and the restriction is written against what a function body may reference directly: wrap the side-effecting function in a view and call the view from within the function. It is a documented escape hatch rather than an invitation, and a design that needs a new identifier per row can generate it in the calling statement instead.